Fill Down to Next Value in Excel (5 Easy Methods)

In many usages of Excel, we sometimes encountered random empty cells in the Excel worksheet. In most of the cases because their uppermost non-blank cells value is the same for all of the rows. So users do not bother filling up those cells with repetitive values by hand. But for clarity purposes, we need to fill down those blank cells with the next value. In this article, we are going to discuss how we can fill down to the next value in Excel efficiently.

Table of Contents hide

Download Practice Workbook

5 Easy Ways to Fill Down to Next Value in Excel

1. Using Fill Handle

2. Utilizing Go To Special Command

3. Applying Power Query

4. Using IF Function

5. Embedding VBA Macro

Conclusion

Download Practice Workbook

Download this practice workbook below.

Fill Down to Next Value.xlsm

5 Easy Ways to Fill Down to Next Value in Excel

For demonstration purposes, we are going to use the below dataset. On which the first column is the Product Name column and then serially is the company name and the Product Serial Number. There are blank cells in between the cells which are going to Fill down using various methods in the following article

1. Using Fill Handle

Fill Handle is probably the most commonly used tool in Excel. Using this tool, you can automate values and fill equations in Excel conveniently.

Steps

In the beginning, select the Fill Handle icon in the corner of cell B5 and drag it down to cell B17.

Then you will notice that the range of cells B4:B17 is now Filled with the value of cell B5.

Then select cell C5 and drag the Flash Fill icon by pressing and holding the right mouse button instead of the left mouse button. Drag the Fill Handle to cell C7.

After dragging the flash Fill Handle to C7, release the button.
After releasing the icon, you will have a new context menu will open.
From that window, click on the Copy Cells.

After that, you will notice that the range of cells C5:C7 now has Filled down with the value from cell C5.
Then you will notice that the range of cells B4:B17 is now Filled with the value of cell B5.
You can choose Flash Fill instead of Copy Cells to fill down the values as shown in the image below.

Note:

Fill Handle button could be hidden in the worksheet, you need to enable it via Advanced options in the Options menu in the File menu. In the Advanced options, click on Editing Options > Enable Fill Handle and drag-and-drop box.

2. Utilizing Go To Special Command

Go To Special method is another kind of tool to Fill down the empty cells in Excel sheets by selecting the blank cells first and then applying the formula to the blank cells.

Steps

Select the range of cells B4:D16 and then press on F5.

There will be a new window named Go To.
From that menu, click on Special.

Then there will be another window named Go To Special.
From that window, select Blanks, and click on OK.

Then you will notice that all of the blank cells in the range of cells B4:D17, now selected, with only cell C6 activated.

Then enter “=” in the activated cell C6, and then press the Up arrow on the keyboard.
This will select cell C5.
After that, click on Ctrl+Enter on the keyboard.

After pressing the button, you will notice that the blank cells are now Filled down with the next value of their upper cells.

3. Applying Power Query

Power Query is one of the most effective tools to Fill blank cells in Excel with the next value in the upper direction. Power query fetches the table data from the worksheet and then applies the fill-down command on each column.

Steps

To do this, click on the From Table/Range from the Data tab.

Then there will be a small window asking for the range of your table, which you want to add.
In that window, input $B$4:$D$16, and tick on the My table has headers box.
Click OK.

After that there will be a new window will open and in that window, you will see that our selected window is showing with all of the columns equipped with filter icons.
The empty cells are now showing as null.

Right-click on the company name column and go to Fill > Down.

Then you will notice that the company name column is now filled down with the next cell values from the upper cells.
Now again right-click on the Products Name columns and go to Fill>Down.

Next, you will see that the whole table is now filled down with the next value in the upper cells.
Now click on the Close & Load icon from the Home tab and then click on Close and Load To.

After that, there will be a new window asking for the location of the new table that we just modified in the power query.
First, click on Table.
Then select Existing Worksheet in the Where do you want to put the data? option.
Then select the location in the range box. we entered F4 in the box.
Click OK after this.

The range of cells F4:H16 is now occupied with the table that we just modified in the power query.

And this is how we fill down all the empty cells in the table by the next value in the upper direction.

4. Using IF Function

Using the IF function we are going to fill down the empty cells in the Excel Sheets with the adjacent cells in the upper direction. To use this method we need to Copy the cells multiple times on the sheet

Steps

In the beginning, the range of cells D5:D16 is copied to the range of cells C5:C16.

Then select cell C6 and enter the following formula.

=IF(D6="",C5,D6)

Then drag the cell to cell C16.
Now the range of cells C5:C16 is filled down with the next upper cell value.

Now cut the range of cells C5:C16 to the D5:D16.
Then copy the cell B5 to the C5.
Next, select the cell B5 and enter the following formula,

=IF(C6="",B5,C6)

Then drag the Fill Handle to B16 cells.
Next, you will notice that the range of cells B5:B16 is now filled down to the next upper value.

This is how we fill down to the next value in Excel using the IF function.

Note:

Using the formula method is quite easy, it doesn’t have any prior requirements like VBA. But it is a little bit time-consuming compared to the others.

5. Embedding VBA Macro

Using a simple macro can drastically reduce the time required for filling down cells with their next adjacent cell value. Then this macro can repeat multiple times when needed.

Steps

From the Developer tab, then go to the Visual Basic.

Then click Insert > Module.

In the module window, enter the following code.

Sub Down_Fill_next_value()
Dim x As Range
Dim y As Long, z As Long
Dim m As Integer, n As Integer
Set x = Selection
z = x.Columns.CountLarge
y = x.Rows.CountLarge
For n = 1 To z
  For m = 1 To y - 1
    If x.Cells(m, n) <> "" Then
      x.Cells(m, n) = x.Cells(m, n).Value
      If x.Cells(m + 1, n) = "" Then
        x.Cells(m + 1, n) = x.Cells(m, n).Value
      End If
    End If
  Next m
Next n
End Sub

Then close the window.
Select the range of data, that you need to fill with the next cell value.
After that, go to the View tab > Macros (Double click).

After clicking View Macros, select the macros that you created just now. The name here is Down_Fill_next_value. Then click Run.

After clicking OK, you will notice that the selected range of cells is now filled down with values next to their upper cells.

This is how we fill down to the next value in Excel using VBA macro.

Conclusion

To sum it up, the question “how can we Fill down to the next value in Excel” is answered here in 5 different ways. Starting from using the Fill Handle, then using the go-to special method, ended with using complex tools like Power Query and VBA Macro. Among all of the methods used here, using the Fill Handle and the Go To Special method is easier to use. The VBA process is also less time-consuming and simplistic but requires prior VBA-related knowledge. Other methods don’t have such a requirement.

For this problem, a macro-enable workbook is available for download where you can practice these methods.

Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the Exceldemy community will be highly appreciable.

Fill Down to Next Value in Excel (5 Easy Methods)

Download Practice Workbook

5 Easy Ways to Fill Down to Next Value in Excel

1. Using Fill Handle

2. Utilizing Go To Special Command

3. Applying Power Query

4. Using IF Function

5. Embedding VBA Macro

Conclusion

Related Articles

Rubayed Razib Suprov

How to Find a Named Range in Excel (4 Easy Ways)

[Fixed!] Excel MATCH Function Not Working

How to Create a Weighted Sales Pipeline in Excel

[Fixed] Excel Failed to Launch in Safe Mode (4 Easy Solutions)

Leave a reply Cancel reply