Let’s consider the following dataset. It contains the list of product IDs, sales dates, and sales numbers. Some of the cells have blank values, which we will fill in a few different ways.
Method 1 – Using Excel Go To Special Tool to Fill Blank Cells with the Value Above
Steps:
- Select the range of data where you want to fill the blank cells.
- Go to the Home tab and the Editing group, select the Find & Select drop-down menu and choose Go To Special.
Note: You can avoid this by pressing F5 directly from the keyboard. This will also take you to the Go To Special box.
- A dialog box named Go To Special appears.
- Choose Blanks from the Go To Special box and click OK.
- This selects all blank cells.
- Write the formula into the cell as “=D5“.
=D5
Here, D5 is the reference of the cell above, with whose value you want to fill in the blank cells.
- Press Ctrl + Enter,which fills the rest of the cells with a similar formula, using the previous cell’s reference.
Let’s convert the formulas into values.
- Select the entire dataset.
- Copy the entire dataset by pressing Ctrl + C.
- Right-click and select Paste Values (V).
- Finally, the result will look like the following picture.
The cells have been converted from having a formula (that you have applied) into a static value.
Method 2 – Applying the Find & Replace Command to Fill Blank Cells
Steps:
- Select the entire dataset.
- Go to the Home tab and the Editing group, select the Find & Select drop-down menu, and choose Find.
- A box will come up. Keep the Find what: box blank and click on Find All.
- Shift your cursor from the dialog box and place it on the worksheet, then press CTRL+A from the keyboard. This will select all the blanks.
- Click on Close to close the dialog box.
This will show the list of blanks in the selected range. For this dataset, the number of blanks found is 11.
- Press “=” from the keyboard, and an equal sign will automatically show up in the active cell.
- Write “D13” in the active cell.
=D13
- Press Ctrl+Enter.
Thus, you will find the result as shown.
Method 3 – Applying a Nested LOOKUP Formula in Excel to Fill Blank Cells
Steps:
- Select the whole data set.
- Choose Table from the Insert tab.
Note: You can also press the keyboard shortcut CTRL+T after selecting the whole data set.
- The Create Table dialog box will open up and show the selected range of data.
- Mark My table has headers checkbox and click OK.
- Your dataset will look like a table with headers having arrow icons as shown below.
- Select a random column F and copy the following formula:
=LOOKUP(ROW(B4:B14), IF(LEN(B4:B14), ROW(B4:B14)), B4:B14)
- The result will show the data of column B along with filling up the blanks with a value above that cell.
- Repeat the process for column C using the following formula:
=LOOKUP(ROW(C4:C14), IF(LEN(C4:C14), ROW(C4:C14)), C4:C14)
- The values of Dates of Sales are different from the original dataset because the Number Format is General by default.
- Change the format by selecting Short Date instead of General.
Follow the picture to find where to change.
- Copy this formula into column H:
=LOOKUP( ROW(D4:D14), IF(LEN(D4:D14),ROW(D4:D14)), D4:D14)
- This will give the following result:
This method helps to have the original dataset and forms a new table to get the desired result.
Formula Breakdown
- Here, lookup_value takes the data we want to find out. Since we have multiple rows in our data set the ROW function is working here which takes the range of the column.
- lookup_vector is using the IF function nested with the LEN function and the ROW function. Both take the range of columns to create a vector form.
- result_vector is the result values taken in the form of a vector to get the desired result.
Method 4 – Using Excel VBA to Fill Blank Cells with a Value Above
Steps:
- Select the dataset and right-click on the name of the sheet.
- Click on View Code from the Context menu.
- The VBA window will open showing the General Window on it.
- Copy the following code into it:
Code:
Sub FillCellFromAbove()For Each cell In Selection If cell.Value = "" Then cell.Value = cell.Offset(-1, 0).Value End IfNext cellEnd Sub
- Press F5 from the keyboard to run the code or click on the green arrow in the ribbon.
- The code will run, and you can see the result in the worksheet.
Download Practice Workbook
You can download the workbook from here.
Fill Blank Cells with Value Above.xlsm
Related Readings
- How to Fill Blank Cells with Value Below in Excel
- How to Fill Blank Cells with 0 in Excel
- Fill Blank Cells with Dash in Excel
- How to Fill Blank Cells with Color in Excel
- How to Fill Blank Cells with Value from Left in Excel
<< Go Back to Fill Blank Cells | Blank Cells in Excel | Excel Cells | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!