How to Fill Down Empty Cells With Values From Above

You work with a table or PivotTable and have an issue: Only the first item is filled. The cells underneath are blank, indicating that the first cell should repeat. Here is how to easily fill down the first filled cell for all blank cells underneath.

Example: Fill down empty cells with values from above

Let’s take a look at the simple example first. In the following sections, we explore then the solutions.

In our example, you have sales persons in the first column. The problem: The name is only written in the first row. The following 11 cells underneath are empty. You want to fill down the name from the first cell as indicated with the arrows.

Manually fill down values from above

You could of course just use copy and paste to copy the values down. But what, if you deal with hundreds or thousands of rows?

There is a better way.

1. Go to any cell outside your table. Write = and refer to the cell above. So, in cell F4, write =F3. Press enter and copy the cell by pressing Ctrl + C on the keyboard.

2. Select the cells in the first column, no matter if there are values or if they are blank. Select them down until you reach the last row in your table.
3. Open the Go to special window: On the Home ribbon, click on Find & Select and then on “Go to special”. Alternatively, press Ctrl + G on the keyboard and then on “Special”. Select “Blanks” and click on OK.
4. Now, only the empty cells are selected.

5. Because the original formula in cell F4 is still copied, you can simply paste it now: Press Ctrl + V on the keyboard.
6. This last step is optional: You can now convert the pasted formulas to values. In order to do that copy the whole first column and open the Paste Special window (press Ctrl + Alt + V on the keyboard). Select Values and confirm with OK.

Shortcut: Speed up the process to fill down values in empty cell

Our Excel add-in Professor Excel Tools can speed up this process significantly: As you can see in the following animation, it only takes 9 seconds.

1. Again, type =F3 into cell F4 (or similar in any other cell outside your table to fill). Copy cell F4.
2. Select all cells in the column. Then, select all blank cells by clicking on “Select all blank cells” from the “Selection Tools” on the Professor Excel ribbon.
3. Paste by pressing Ctrl + V on the keyboard.
4. If you like, you can convert all pasted formulas to values. Just click on “Change reference” on the Professor Excel ribbon and then on “Values”.

Special case: PivotTables

With PivotTables, it’s even easier to fill all blank cells down.

Option 1: Fill down all columns at once in a PivotTable

1. Select any cell within your PivotTable.
2. Go to the Design ribbon, click on the small arrow in the corner of the Report Layout button and then on “Repeat all item labels”.

Option 2: Repeat items in one column only

1. Right click on a cell within the column you want to repeat the cell above.
2. Click on Field Settings.
3. Go to the Layout & Print tab and set the checkmark at “Repeat item labels”.

Image by Son Hoa Nguyen from Pixabay