This tutorial demonstrates how to fill blank cells with the value above 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 selected 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 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.
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.
- 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 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.
- 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 we can 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.