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 Using Go To Special feature and formula Using Kutools for Excel with only 3 clicks Using VBA code Using Power Query	Fill blank cells with 0 or specific value Using Go To Special feature Using Find and Replace feature

 Fill blank cells with value above

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

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.

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.
4. 

Result:

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

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:

Amazing! Using Efficient Tabs in Excel Like Chrome, Edge, Firefox and Safari!
Save 50% of your time, and reduce thousands of mouse clicks for you every day!

---

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:

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. Thank you for reading, and we look forward to providing you with more helpful information in the future!

Related Articles: