Skip to main content

Fill blank cells with value above or 0 in Excel - step by step guide

In Excel, it's common practice to leave cells blank when their data would simply repeat the value of the cell above them. This formatting choice often results in a cleaner and more visually appealing layout. However, if you want to sort or filter the list, these blank cells can become problematic. In such instances, you need to populate the blanks with the values from the cells above as demonstrated in the screenshot below.

In this guide, we will focus on how to fill blank cells either with the value in the cell directly above or with 0 or any other specific value. This can be incredibly useful for handling data sets that contain empty cells, ensuring that calculations and visualizations are accurate and meaningful.

Fill blank cells with value above

Fill blank cells with 0 or specific value


 Fill blank cells with value above

In this section, we will introduce four easy tricks for filling blank cells with value above in Excel.

  • Go To Special & formula: Native to Excel, but multiple steps required, need to convert the final formulas to values.
  • Kutools for Excel: Takes only 3 clicks, easy and quickly. But requires download and installation.
  • VBA code: Requires knowledge of VBA programming, possibility of bugs or errors if not coded properly.
  • Power Query: Has a steeper learning curve, applicable to Excel 2016 and later versions.

Using Go To Special feature and formula to fill blank cells with value above

With this method, you should apply the Go To Special feature to select all blank cells in the data range, then enter a very simple formula into an empty cell and then press the Ctrl + Enter keys to fill the selected blank cells with value above. Here are the steps.

Step 1: Select the blank cells

      1. Select the range of cells where you want to fill the blanks with value above.
      2. Then, click Home > Find & Select > Go To Special, see screenshot:
        Tips: You can also press F5 key to open the Go To dialog box, and then click Special button in the Go To window to display the Go To Special dialog box.
      3. In the Go To Special dialog box, choose the Blanks option and click OK.
      4. Now all blank cells within the range are selected at once.

Step2: Input formula

With the blank cells still selected, type the equals sign (=) directly. The equal sign will appear in one of the selected blank cells, such as A3 in this case. Now reference the cell above it by clicking on A2.

Step 3: Press Ctrl + Enter keys to fill all the selected blank cells

Now, press Ctrl + Enter keys together to copy the formula to all other selected cells. And all the selected blank cells are filled with the value from the cell directly above them. See screenshot:

Step 4: Convert formulas to values

Now, the selected blank cells are formulas, you should convert these formulas to values. Please select the data range which contains the formulas, then press Ctrl + C to copy the data list, and then right click the copied range, and choose Values from the Paste options section, see screenshot:

And now, the formulas are converted to static values, you can do any other operations as you need.

Note: If there are any other formulas in your selected range, these formulas will be converted to values as well.

Using Kutools for Excel with only 3 clicks

If you are looking for a quick and easy solution, Kutools for Excel’s Fill Blank Cells feature could be an excellent tool for you. With this powerful feature, you can quickly finish the following operations:

  • Fill blank cells with value above, down, left or right
  • Fill blank cells with series of linear values
  • Fill blank cells with a specific value

After downloading and installing Kutools for Excel, please do as this:

To fill blank cells with value above, please select the data range that you want to fill blank cells, and then click Kutools > Insert > Fill Blank Cells, then specify the operations in the Fill Blank Cells dialog box:

  1. Select Based on values option from the Fill with section.
  2. Choose Down in the Options section. (You can also select the Up, Right or Left to fill the blank cells with value down, left or right as you need)
  3. Click OK to apply this feature.

Result:

Now, all blank cells are populated with the value above as following screenshot shown:

Note: To apply this feature, please download and install Kutools for Excel first.

Using VBA code to fill blank cells with value above

If you are familiar with VBA code, here, we will provide a code for filling blank cells with value above. Please do with the following steps:

Step 1:Open the VBA module editor and copy the code

  1. Hold down the ALT + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window.
  2. Click Insert > Module, and paste the following code in the Module Window.
    VBA code: Insert blank cells with value above
    Sub FillBlankCellsWithValueAbove()
    'Updateby Extendoffice
        Dim WorkRng As Range
        Dim cell As Range
        On Error Resume Next
        xTitleId = "KutoolsforExcel"
        Set WorkRng = Application.Selection
        Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
        On Error GoTo 0
        If WorkRng Is Nothing Then
            MsgBox "No valid range selected.", vbExclamation, "Error"
            Exit Sub
        End If
        For Each cell In WorkRng
            If IsEmpty(cell) Then
                cell.Value = cell.Offset(-1, 0).Value
            End If
        Next cell
    End Sub
    

Step 2: Execute the code

After pasting this code, please press F5 key to run this code. In the prompt box, select the data range where you want to fill blank cells with value above. And then, click OK.

Result:

Now, all blank cells are populated with the value above as following screenshot shown:


Using Power Query to fill blank cells value above

If you are using Excel 2016 and later version, Excel provides a powerful feature – Power Query, which can help to populate each block of empty cells in the column using the value from the cell above them immediately. Here are the steps to do it:

Step 1: Get the data into Power Query

  1. Select the data range that you want to use, then click Data > From Table, see screenshot:
    Tips: In Excel 2019 and Office 365, click Data > From Table/Range.
  2. In the popped-out Create Table dialog box, click OK button to create a table, see screenshot:
  3. Now, the Power Query Editor window is displayed with the data, see screenshot:

Step 2: In the Power Query Editor window, specify the operations

  1. Click the header of the columns that you want to fill the blank cells with value above. And then, right click the selected header, choose Fill > Down, see screenshot:
    Tips: To fill blank cells in multiple columns, hold down the Ctrl or Shift key and click the column headers to select non-adjacent or adjacent columns, then right click any selected column, and then select Fill > Down as previously described.
  2. Now, all the blank cells in the selected column are filled with the value from the cell directly above them, see screenshot:

Step 3: Output the Power Query to an Excel table

  1. Then, you should output the data into your worksheet. Please click Home > Close & Load > Close & Load / Close & Load To, (here, I will click Close & Load), see screenshot:
    Tips: Click Close & Load will output the data to a new worksheet; Click Close & Load To option, the data will be output to a new sheet or current sheet you need.
  2. And then, the data will be loaded to a new worksheet, see screenshot:

Fill blank cells with 0 or specific value

If you need to fill blank cells with 0 or any other specific value, here, I will talk about two quick methods.


Using Go To Special feature to fill blank cells with 0 or specific value

Step 1: Select the blank cells

      1. Select the range of cells where you want to fill the blanks with 0 or specific value.
      2. Then, click Home > Find & Select > Go To Special, see screenshot:
        Tips: You can also press F5 key to open the Go To dialog box, and then click Special button in the Go To window to display the Go To Special dialog box.
      3. In the Go To Special dialog box, choose the Blanks option and click OK.
      4. Now all blank cells within the range are selected at once.

Step2: Input 0 or specific value

With the blank cells still selected, type 0 or other specific value you need directly, see screenshot:

Step 3: Press Ctrl + Enter keys to fill all the selected blank cells

Now, press Ctrl + Enter keys together to fill the selected blank cells with the value you typed, see screenshot:


  • Don't let blanks slow you down, Let Kutools for Excel fill them up!

    Empty cells disrupting your flow? Transform your Excel journey with Kutools. Filling all types of blank cells, perfectly meeting your needs. No blanks, just brilliance! Download Kutools for Excel Now!

    Fill blank cells with 0 or any other specific value

  • Don't let blanks slow you down, Let Kutools for Excel fill them up!

    Empty cells disrupting your flow? Transform your Excel journey with Kutools. Filling all types of blank cells, perfectly meeting your needs. No blanks, just brilliance! Download Kutools for Excel Now!

    Fill blank cells with a series of linear values

  • Don't let blanks slow you down, Let Kutools for Excel fill them up!

    Empty cells disrupting your flow? Transform your Excel journey with Kutools. Filling all types of blank cells, perfectly meeting your needs. No blanks, just brilliance! Download Kutools for Excel Now!

    Fill blank cells with value above/down/left/right


Using Find and Replace feature to fill blank cells with 0 or specific value

Excel’s Find and Replace feature also can help to fill blank cells with 0 or any other specific value, please do with the following steps:

Step 1: Select the data range and enable the Find and Replace feature

  1. Select the data range where you want to fill blank cells with a specific value.
  2. Then, click Home > Find & Select > Replace (you can also press Ctrl + H to open the Find and Replace dialog box), see screenshot:

Step 2: Specify the operations in the Find and Replace dialog box

  1. In the Find and Replace dialog box, leave the Find what text box blank, then type the specific value into the Replace with text box. And then, click Replace All button. See screenshot:
  2.  A prompt box is popped out to remind you how many cells are replaced with the new value. At last, click OK and close the Find and Replace dialog box.

Result:

Now, the blank cells in the selected range are filled with the specific value you entered, see screenshot:


This is how you can fill blank cells with value above or a specific value. If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials, please click here to access them. Thank you for reading, and we look forward to providing you with more helpful information in the future!


Related Articles:

  • Auto fill serial numbers and skip blanks
  • If you have a list of data which contains some blank cells, now, you want to insert serial numbers for the data, but skip the blank cells as below screenshot shown. In this article, I will introduce a formula for solving this task in Excel.
  • Fill blank cells with linear values
  • Do you need to fill blank cells with linear values in selections? This article is going to introduce some tips about how to fill blank cells with linear values in selections quickly.
  • Fill blank cells with dash
  • Supposing you have a range of data which contains some blank cells, and now, you want to fill each of these blank cells with the dash symbol as following screenshots shown, do you have any good ideas to fill the blank cells with dashes at once in Excel?