Enter Data into Many Cells At Once

Today, I’ll show you an old trick that lets you enter data into many cells at once.

.In this example, I want to quickly fill in the blank states with the same value.

So first, I’ll select all the blanks by holding down the Ctrl key while selecting each blank cell with my mouse.

Next I’ll enter in the desired value, and then hold down the Ctrl key while pressing Enter.

Excel automatically enters my value into all the cells I have selected. Nifty!

Ok…I know what you’re thinking. I could have entered those few values in separately with very little productivity impact. True – but this trick does come in handy when you need to enter values or formulas for hundreds of cells. Let me show you a more advanced way I use this trick to save loads of time.

Sometimes I’ll turn pivot tables into hard data by doing a Copy-Past Special-Values on them. This not only leaves me with the values created by the pivot table, but also the pivot table’s data structure. As you can see in this screenshot, this is often not ideal, as the pivot table leaves behind a bunch of empty cells that will have to be filled in before the hard table is usable. So how can I fill in the empty blanks without a lot of copying and pasting?

This is where the old Ctrl+Enter trick really comes in handy!

First, I’ll select the range I’m working with, then I’ll go to the Home tab – > Find & Select -> Go to Special.

This will activate the Go To Special dialog box. From here, I choose the option for Blanks.

The result is that only the blank cells are selected. This is effectively the same as me holding down the Ctrl key while selecting all the blank cells one by one with the mouse. Only, I didn’t have to do this manually for hundreds of cells – Excel did it for me.

At this point, I can enter a simple formula that essentially copies the value from the cell above. This is done with two keystrokes:

Type an equal sign

Press the up-arrow key

The equal sign tells Excel that I’m entering a formula in the active cell, while pressing the up-arrow key points to the cell above the active cell.

Now all I have to do is hold down the Ctrl key while pressing Enter. Pressing Ctrl+Enter enters the same formula in all the selected cells.

The reason this works is because the formula I entered is a relative reference formula. So every formula that is entered essentially points to the value above it.