Fill Blank Cells With Value Above in Excel & Google Sheets
Written by
Reviewed by
This tutorial demonstrates how to fill blank cells with the value above each in Excel and Google Sheets.
Fill Blank Cells With the Value Above
- 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.
- Then, in the Ribbon, select Home > Editing > Find & Select > Go To Special.
- Select Blanks in the Go To Special dialog box and click OK.
All the blank cells (and no populated cells) in the range are selected.
- 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 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.
- Now, replace the formulas with values in order to retain the correct value in each row, even if you sort the data or delete/insert a row.
Highlight the range containing the formulas and then, in the Ribbon, select Home > Clipboard > Copy (or press CTRL + C on the keyboard).
- Then, while keeping the range highlighted, in the Ribbon, select Home > Clipboard > Paste and then select Paste Values.
Fill Blank Cells With Value Above in Google Sheets
You cannot use CTRL + ENTER in Google Sheets. If you want to populate a cell with the value above, you can very simply enter a formula (that refers to the cell above) in the first blank cell, and then use the CTRL key to copy the formula to multiple cells.
- Click in the blank cell.
Press = (equal) and click on the cell above it.
- Press CTRL + C on the keyboard to copy the formula.
- Hold down the CTRL key, and then select the empty cells you want to fill with the value above.
- Press CTRL + V to paste the formula.
- Now, as with Excel, copy the formulas and paste them as values.
Highlight the range that contains the formulas, and then, in the Menu, select Edit > Copy (or press CTRL + C).
- Keeping the same range highlighted, in the Menu, select Edit > Paste special > Values only or press CTRL + SHIFT + V.
