See all How-To Articles

Fill Blank Cells With Value Above in Excel & Google Sheets

This tutorial demonstrates how to fill blank cells with the value above in Excel and Google Sheets.

fill blank cells

 

Fill Blank Cells With the Value Above

  1. The first step in filling blank cells with the value above is to select the cells you want to fill. You can do this using the Go To Special command on the Ribbon.
    First, select the range that contains the blank cells.

 

FillBlank select range

 

  1. Then, in the Ribbon, select Home > Editing > Find & Select > Go To Special.

 

FillBlank Ribbon

 

  1. Select Blanks in the Go To Special dialog box and click OK.

 

FillBlank select blanks

 

All the blank cells (and no populated cells) in the selected range are selected.

 

FillBlanks select cells

 

  1. Now, click in the formula bar (or press F2) to start entering a formula. The formula is very simple and can be achieved in three simple steps:
  • Type the = (equal) sign.
  • Click on the cell above the active cell (in this case, C3).
  • Press CTRL + ENTER to copy the formula to all the selected blank cells.

Each of the blank cells is populated with a simple formula that refers to the cell above it.
Note that you can also select the blank cells by clicking on an empty cell, and then hold down the CTRL key on the keyboard while clicking on any additional empty cells you wish to populate with the value above.

 

FillBlank formula copied

Now we need to replace the formula with a value in order to retain the correct value in each row if we were to sort the data, or delete or insert a row into the data.

  1. Highlight the range containing the formulas and then, in the Ribbon, select Home > Clipboard > Copy or press CTRL+C on the keyboard.

fill blank cells copy

Then, while keeping the range highlighted, in the Ribbon, select Home > Clipboard > Paste and then select Paste Values.

fill blank cells paste values

Fill Blank Cells With Value Above in Google Sheets

You cannot use the CTRL key in Google Sheets to copy formula but you can use it to select multiple cells. If you want to populate a cell with the value above, you can very simply do an individual formula in your blank cell that refers to the cell above it, and then use the CTRL key to copy the formula to multiple cells.

  1. Click in the blank cell.
    Press = (equal) and click on the cell above it.

 

FillBlank gs formula

 

  1. Press CTRL + C on the keyboard to copy the formula.

 

FillBlank gs copy

 

  1. Hold down the CTRL key, and then select the empty cells you want to fill with the value above.

 

FillBlank gs selected cells

 

  1. Press CTRL + V to paste the formula.

 

FillBlank gs paste

Now, as with Excel we can Copy the Formulas and paste them as values.

  1. Highlight the range that contains the formulas, and then, in the Menu, select Edit > Copy or press Ctrl+C.

fill blank cells gs copy

  1. Keeping the same range highlighted, in the Menu, select Edit > Paste special > Values only or press Ctrl+Shift+V.

fill blank cells gs paste values