Thursday, April 16, 2009

Quickly Fill Multiple Cells with a Value or Formula

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).

27 comments :

Raul Durand said...

Great!!
I was looking for that for a while.
Thank you very much!
Raul.

sikaudio said...

Legend Mate! I have been going crazy trying to work this out. Thank heaps!

Nath
www.sikaudio.com

LeA said...

Oh !!! so simple and so helpful !!! 10x !

Unknown said...

Really, really nice!

Anonymous said...

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!

Unknown said...

Yo are amazing - thanks man

Unknown said...

Thanks man - absolutely amazing - saved me a lot of time and energy

Anonymous said...

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!

Anonymous said...

Thank you so much. This is helping me a lot :)

Anonymous said...

You just saved me 6.5 hours of hating my job and continually hitting F4, enter. Thanks!

dimitrios_87 said...

thanks! very useful.

Anonymous said...

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?

Anonymous said...

great! thanks

Unknown said...

how to copy texts of a all cells in col. A1 to a another col.D. in different cells ?

Anonymous said...

Thanks buddy! It's amazing!

Anonymous said...

how about doing mathematical operations for values with decimal number?

Anonymous said...

Thank You. I was looking for this answer for a long time.

Kirti Raj Chahar said...

Thanks a lot

Anonymous said...

helped a lot - MAX

Anonymous said...

Hi I want to update excel rows as A1:A20 as 1, A21:A40 as 2 .... what's the way out. Is there any formula for that

Anonymous said...

To multiply all the various figures by 50% use copy and paste special. First in your document type 50% in any cell. Then copy the 50%. Then highlight all the figures to be multiplied by 50%. Choose paste special, choose multiply and then OK.
Then delete the original 50%
Have a pleasant day

Anonymous said...

Thank you. My "excel copy contents of one cell to multiple cells" brought up this page and it was exactly what I needed. Saved a lot of time and effort copying a couple of basic items across three thousand rows. Life saver!

Anonymous said...

OMG i love you :D exactly what I needed

Unknown said...

Thank you! Amazing!

Anonymous said...

Thank you, it helped me alot today

Kris Brown said...

Absolute Hero. Someone get this man a medal.

Unknown said...

is there a way to just add a ; after each separate cell and separate email without changing anything else? When I do what you suggest the ; is added but the last email is repeated within each cell.