You are reading the article Fill Down Blank Cells Until The Next Value In Excel (3 Easy Ways) updated in September 2023 on the website Khongconthamnam.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested October 2023 Fill Down Blank Cells Until The Next Value In Excel (3 Easy Ways)
Watch Video – Fill Down in Excel
A lot of times, you may encounter a data set where only one cell is filled with data and the cells below it are blank till the next value.
Something as shown below:
While this format works for some people, the problem with this sort of data is that you cannot use it to create Pivot Tables or use it in calculations.
And this has an easy fix!
In this tutorial, I will show you how to quickly fill down cells in Excel until the next filled value.
You can easily do this using a simple Go-To special dialog box technique, VBA, or Power Query.
So let’s get started!
Suppose you have a data set as shown below and you want to fill down data in column A and column B.
In column B, the aim is to fill ‘Printer’ till the last empty cell below it, and then when ‘Scanner’ starts, then fill ‘Scanner’ in the cells below till the cells are empty.
Below the steps to use go to special to select all the blank cells and fill down using a simple formula:
Select the data in which you want to fill down (A1:D21 in our example)
Go to the ‘Home’ tab
The above steps would select all the blank cells in the data set (as shown below).
In the blank cells that are selected, you would notice that one cell is lighter than the rest of the selection. This cell is the active cell where we’re going to enter a formula.
Don’t worry about where the cell is in the selection, as our method would work in any case.
Now, below are the steps to fill down the data in the selected blank cells:
Hit the equal-to (=) key on your keyboard. This will insert an equal to sign in the active cell
Press the up arrow key. This will insert the cell reference of the cell above the active cell. For example, in our case, B3 is the active cell and when we do these two steps, it enters =B2 in the cell
Hold the Control key, and press the Enter key
The above steps would automatically insert all the blank cells with the value above them.
While this may look like too many steps, once you get the hang of this method, you’ll be able to quickly fill-down data in Excel within a few seconds.
Now, there are two more things that you need to take care of when using this method.
The first one is to make sure that you convert formulas to values (so that you have static values and things don’t mess up in case you change data in the future).
Below is a video that will show you how to quickly convert formulas to values in Excel.
In case you’re using dates in your data (as I am in the example data), you would notice that the filled-down values in the date column are numbers and not dates.
If the output in the date column are in the desired date format, you’re good and don’t need to do anything else.
But if these are not in the correct date format, you need to make a minor adjustment. While you have the right values, you just need to change the format so that it is displayed as a date in the cell.
Below are the steps to do this:
If you need to do this fill-down once in a while, I recommend you use this Go-To special and formula technique.
Although it has a few steps, it’s simple and gives you the result right there in your dataset.
But in case you have to do this quite often, then I suggest you look at the VBA and the Power Query methods covered next
You can use a really simple VBA macro code to quickly fill down cells in Excel till the last empty value.
You only need to add the VBA code to the file once and then you can easily reuse the code multiple times in the same workbook or even multiple workbooks on your system.
Below is the VBA code that will go through each cell in the selection and fill down all the cells that are blank:
Sub FillDown() For Each cell In Selection If cell = “” Then cell.FillDown End If Next End Sub
The above code uses a For loop to go through each cell in the selection.
Within the For loop, I have used an If-Then condition that checks whether the cell is empty or not.
In case the cell is empty, it is filled with the value from the cell above, and in case it is not empty, then the for loop simply ignores the cell and moves to the next one.
Now that you have the VBA code, let me show you where to put this code in Excel:
Copy-paste the VBA code into the code window
The above steps would run the VBA code and your data would be filled down.
If you want to use this VBA code again in the future, you need to save this file as a macro-enabled Excel workbook (with a .XLSM extension)
You can also add this macro to the Personal Macro Workbook and then use it in any workbook on your system.
Using Power Query is recommended when you’re using it anyway to transform your data or combine data from multiple worksheets or multiple workbooks.
As a part of your existing workflow, you can use the fill-down option in Power Query to quickly fill the blank cells.
To use Power Query, it’s recommended that your data is in an Excel Table format. If you can’t convert your data into an Excel table, you will have to create a named range for the data and then use that named range in Power Query.
Below the steps to use Power Query to fill down data till the next value:
While this method may sound a bit of an overkill, one great benefit of using Power Query is that it allows you to quickly update the resulting data in case your original data changes.
While the Power Query method works well, it’s best to use it when you’re already using Power Query in your workflow.
If you just have a dataset where you want to fill down the blank cells, then using the Go To special method or the VBA method (covered above) would be more convenient.
So these are three simple ways that you can use to fill down blank cells until the next value in Excel.
I hope you found this tutorial useful.
Other Excel tutorials you may also like:
You're reading Fill Down Blank Cells Until The Next Value In Excel (3 Easy Ways)
Update the detailed information about Fill Down Blank Cells Until The Next Value In Excel (3 Easy Ways) on the Khongconthamnam.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!