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

Goal: Fill down the name from the cells above.
Goal: Fill down the name from the cells 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.

Step 1: Link to the cell above and copy cell.
  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.
Steps 2-4: Select all blank cell.s
  1. 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.
  2. 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.
  3. Now, only the empty cells are selected.
Steps 5-6: Paste formula and convert to values.
  1. Because the original formula in cell F4 is still copied, you can simply paste it now: Press Ctrl + V on the keyboard.
  2. 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.

Fill down all empty cell with the values from above with Professor Excel Tools.
Fill down all empty cell with the values from above with Professor Excel Tools.
  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”.

Professor Excel Tools Box

This function is included in our Excel Add-In ‘Professor Excel Tools’

(No sign-up, download starts directly)

More than 35,000 users can’t be wrong.


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

Fill down cells in PivotTables by selecting the option to "Repeat all Item Labels".
Fill down cells in PivotTables by selecting the option to “Repeat all Item Labels”.
  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

You can alternatively only repeat item labels for one column in a PivotTable.
You can alternatively only repeat item labels for one column in a PivotTable.
  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

Henrik Schiffner is a freelance business consultant and software developer. He lives and works in Hamburg, Germany. Besides being an Excel enthusiast he loves photography and sports.

Leave a comment

Your email address will not be published. Required fields are marked *