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.


Download Practice Workbook

Download this practice workbook below.


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

Fill Down to Next Value in Excel


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.

Using Fill Handle to Fill Down to Next Value in Excel

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

Using Fill Handle to Fill Down to Next Value in Excel

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

Using Fill Handle to Fill Down to Next Value in Excel

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

Using Fill Handle to Fill Down to Next Value in Excel

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

Using Fill Handle to Fill Down to Next Value in Excel

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.

Read More: [Solved]: Fill Handle Not Working in Excel (5 Simple Solutions)


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.

Utilizing Go To Special Command to Fill Down to Next Value in Excel

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

Utilizing Go To Special Command to Fill Down to Next Value in Excel

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

Utilizing Go To Special Command to Fill Down to Next Value in Excel

Read More: How to Drag Formula and Ignore Hidden Cells in Excel (2 Examples)


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.

Applying Power Query toFill Down to Next Value in Excel

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

Applying Power Query toFill Down to Next Value in Excel

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

Applying Power Query toFill Down to Next Value in Excel

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

Applying Power Query toFill Down to Next Value in Excel

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

Applying Power Query toFill Down to Next Value in Excel

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

Applying Power Query toFill Down to Next Value in Excel

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

Read More: How to Fill Down to Last Row with Data in Excel (3 Quick Methods)


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.

Using Combined Formula Fill Down to Next Value in Excel

  • Then select cell C6 and enter the following formula.
=IF(D6="",C5,D6)

Using Combined Formula to Fill Down to Next Value in Excel

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

Using Combined Formula Fill Down to Next Value in Excel

  • 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)

Using Combined Formula Fill Down to Next Value in Excel

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

Using Combined Formula Fill Down to Next Value in Excel

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.

Read More: How to Fill Formula Down to Specific Row in Excel (7 Easy Methods)


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.

Embedding VBA Macro to Fill Down to Next Value in Excel

  • 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).

Embedding VBA Macro to Fill Down to Next Value in Excel

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

Embedding VBA Macro to Fill Down to Next Value in Excel

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

Read More: How to Use Fill Handle to Copy Formula in Excel (2 Useful Examples)


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.


Related Articles

Rubayed Razib Suprov

Rubayed Razib Suprov

Hi, I am Rubayed Razib Suprov. Graduated from the Bangladesh University of Engineering Technology in Naval Architecture and Marine Engineering department. I joined Exceldemy on March 2022. I have a keen interest in both fluid dynamics and data analysis. I try to teach people what I learned so far about the Excel tool to date, and hope I can make some positive impacts.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo