Use the keyboard shortcut Ctrl+Enter to quickly fill multiple cells with a value or formula. For example, you may want to enter a bunch of 0's (zeros) in a range of cells. Or, you want to apply the same formula to a range of cells without changing the cell formats.
1. Select a range of cells
2. Enter the value or formula
3. Instead of pressing Enter afterwards, press Ctrl+Enter to fill the entire range of cells.
This is basically an alternative approach to entering a value in a single cell and then copying that cell and pasting the value or the formula. Using Ctrl+Enter does not copy formatting.
If you are using a relative reference in your formula (like =A1), then the formula in the other filles cells will be the same as if you entered the formula in just the one cell and then copied that cell to the other cells in the range. If you want the formula reference to not change when you use Ctrl+Enter, you will need to use an absolute reference (like =$A$1).
Bookmark
Categories
- arrays (1)
- charts (3)
- customizing (2)
- dashboards (1)
- data validation (1)
- drawing (1)
- Excel (1)
- formatting (3)
- formulas (4)
- functions (1)
- graphs (1)
- lookup (2)
- navigation (1)
- options (1)
- printing (2)
- shortcuts (6)
- tricks (5)
More Excel Tips
Excel Templates
Blog Archive
Subscribe
Posts
Comments
About the Author
- Jon Wittwer
- Jon Wittwer is the founder of Vertex42.com, an internet-based software development company that specializes in the creation of spreadsheet templates and financial calculators. Vertex42.com began in 2003, and since then Dr. Wittwer has created numerous templates and spreadsheets for small business, personal finance, time management, education, statistics, and engineering, almost all of which can be downloaded for free.
19 comments:
Great!!
I was looking for that for a while.
Thank you very much!
Raul.
Legend Mate! I have been going crazy trying to work this out. Thank heaps!
Nath
www.sikaudio.com
Oh !!! so simple and so helpful !!! 10x !
Really, really nice!
Very helpful, thanks so much!
I had over 110 values in which I needed I find the natural log and inverse of to plot graphs of all three things. And I really didn't want to type in =LN(n) and 1/(n) for each of the over 110 cells!
Thanks again, this saved me about an hour or so!
Yo are amazing - thanks man
Thanks man - absolutely amazing - saved me a lot of time and energy
thank you thank you thank you! i knew there was a way to do this, but i couldn't remember it and was staring at my spreadsheet for ages...i don't think i would have figured this out on my own!
Thank you so much. This is helping me a lot :)
You just saved me 6.5 hours of hating my job and continually hitting F4, enter. Thanks!
thanks! very useful.
On my excel spreadsheet I have assorted dollar amounts. I need to multiply each cell by 50%. How do I calcuate each cell at the same time?
great! thanks
how to copy texts of a all cells in col. A1 to a another col.D. in different cells ?
Thanks buddy! It's amazing!
how about doing mathematical operations for values with decimal number?
Thank You. I was looking for this answer for a long time.
Thanks a lot
helped a lot - MAX
Post a Comment