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!
Method 1 – Fill Down Using Go To Special + Formula
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: 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: 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.
Convert Formulas to Values
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.
Change the Date Format
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
Method 2 – Fill Down Using a Simple VBA Code
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: 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: 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) Another thing you can do is add this macro to your Quick Access Toolbar, which is always visible and you’ll have access to this macro with a single click (in the workbook where you have the code in the backend). So, the next time you have data where you need to fill-down, all you need to do is make the selection and then click on the macro button in the Quick Access Toolbar. You can also add this macro to the Personal Macro Workbook and then use it in any workbook on your system. Click here to read more about personal macro workbooks and how to add code to them.
Method 3 – Fill Down Using Power Query
Power Query has an inbuilt functionality that allows you to fill-down with a single click. 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 I have the data set that I have converted into an Excel table already. You can do that by selecting the dataset, going to the Insert tab, and then clicking on the Table icon. 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. For example, if you add more records to the original data, you don’t need to do all the above again. You can simply right-click on the resulting table and then click on Refresh. 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:
How to use Fill Handle in ExcelHow to Quickly Fill Numbers in Cells without Dragging10 Excel Data Entry Tips You Can’t Afford to MissHow to Highlight Blank Cells in ExcelHow to Use Flash Fill in ExcelHow to Edit Cells in Excel?
