ExcelInsider Team
When working with datasets in Excel, it’s common to have blank cells in columns where the previous row holds the relevant value. For example, in logs, inventory lists, or form outputs. We can repeat the value manually but it can be time consuming particularly for large datasets.
To fill blank cells with value above, follow these steps:
➤ Click on a new empty cell like D2
➤ Use this formula =IF(ISBLANK(A2),D1,A2) and click Enter
➤ Use the fill handle to copy the formula to the bottom.
In this article, we’ll explore 4 methods to fill blank cells with the value above in Excel. We will use methods like Go To Special, Power Query, formulas, and VBA macros.
Using Go To Special Tool
This method is very useful for quickly filling blank cells in a column with the value from the cell above it. For example, think of a dataset where the same category, label, or identifier applies to multiple rows but only appears once at the beginning of each group. We have taken a dataset of the sales department where product categories aren’t filled in all the rows. We will apply this method to automatically fill up those unfilled cells by taking the above cell data.
Steps:
➤ Select the range where you want to fill the blanks. For this example, select cells A2:A11 (assuming A1 has the column header “Product Category”).
➤ Press Ctrl + G to open the Go To window. Click on Special
➤ In the dialog box, choose Blanks and click OK. This will highlight only the blank cells in your selection.
➤ Without clicking anywhere else, press the equal sign =. You’ll see it appear in the first blank cell.
➤ Now press the Up Arrow key (↑). Excel will reference the cell directly above (e.g., it will display =A2 in cell A3).
➤ Hold down Ctrl and then press Enter. Excel fills all selected blank cells with the respective value from the cell above using formulas.
Note:
Make sure not to click anywhere else after selecting blanks. Otherwise, the selection will be lost.
Implementing VBA Macro
This method uses VBA (Visual Basic for Applications) to automatically fill blank cells in a column with the value above. It is very useful for large datasets where manually selecting and filling cells is inefficient.
Steps:
➤ Open your Excel workbook. Make sure it is in table format. To make it a table press Ctrl + T .
➤ Press Alt + F11 to open the VBA Editor.
➤ In the VBA editor, go to Insert > Module to create a new module.
➤ Copy and paste the following code into the module:
Sub FillBlankCellsWithAboveValue()
Dim rng As Range
Dim col As Range
Dim ws As Worksheet
Set ws = ActiveSheet
Set col = ws.Range("A2:A11") ' Adjust range as needed
For Each rng In col
If rng.Value = "" Then
rng.FillDown
End If
Next rng
End Sub
➤ Close the VBA editor and return to Excel. Press Alt + F8 , choose FillBlankCellsWithAboveValue, and click Run.
➤ All blank cells in column A (or the range you specified) should now be filled with the value from above.
Note:
Make sure to adjust Range(“A2:A11”) in the VBA code to match the actual range of your data.
Applying Power Query Fill Down Method
In this method we will use Power Query to automatically fill blank cells in a column with the value from above. It is effective when you are dealing with regularly updated datasets such as product inventories or sales logs. The process is dynamic and adjusts automatically upon refresh.
Steps:
➤ Select any cell inside your dataset (e.g., A1:C11) and go to the Data tab on the Excel ribbon. Click From Table/Range.
➤ In the Create Table dialog box, confirm the data range and check “My table has headers” if applicable. Click OK.
➤ Power Query Editor will open. Locate the column with blank cells (e.g., “Category“). Right-click on the column header and choose Fill > Down.
➤ Once filled, click Close & Load in the top-left of the Power Query editor to load the modified data back to Excel.
➤ Your data will now appear in a new worksheet or table with the previously blank cells filled with the value from above.
Note:
➧ You can refresh this query anytime using Data → Refresh All, and it will apply the Fill Down to new blank cells.
➧ Power Query won’t modify the original data directly. It will create a linked version of it.
Using Formula in New Column
This method uses a helper column and a simple formula to fill down blank cells with the value above. It is perfect for structured data where you want to preserve the original column and compute the filled values in a separate one.
Steps:
➤ Open your Excel sheet that contains blank cells in a column, such as “Delivery Location” in column A. Place your cursor in cell D1 (or any free column header) and name it something like “Filled Location”.
➤ In cell D2, type the following formula:
=IF(ISBLANK(A2),D1,A2)
This formula checks if cell A2 is blank. If it is, it uses the value from the cell above in column D; otherwise, it uses the current value in A2.
➤ Press Enter, and you’ll see the formula output either the current value from A2 or the value from D1, depending on whether A2 is blank.
➤ Click on cell D2, and then double-click the fill handle (small square at the bottom-right corner of the cell) to copy the formula down through the range that matches your dataset.
➤ Now column D will show all blank rows filled with the correct value from above.
Note:
Works well for static datasets. If your dataset updates frequently, use Power Query instead for dynamic fill-down.
Frequently Asked Questions (FAQs)
How to fill in blank cells in Excel with value above?
Use Go To Special > Blanks, then enter a formula like =A2 referring to the cell above and press Ctrl + Enter .
How do you insert blank cells above?
Right-click on a cell > Insert > Shift cells down. This will insert a new blank cell above.
How do I fill blank cells with value from another column?
Use =IF(A2=””,B2,A2) to pull values from Column B when Column A is blank.
Concluding Words
As you have already known, filling blank cells with the value above is a common data cleaning step in Excel. Depending on your needs, you can choose from four of these effective methods I have described through the article-
- Go To Special + Ctrl + Enter (Quick Manual Method)
- Power Query Fill Down (Dynamic and repeatable)
- Formula in New Column (Simple and non-destructive)
- VBA Macro (Automated for large data)
Choose the method that best fits your data volume and complexity. And let me know what you think about these guidelines.
