How to Fill Down Blanks in Excel (4 Quick Methods)

In this article, we will learn to fill down blanks in Excel worksheets. Sometimes, we need to download data in Excel from other sources and it may contain blank cells. To perform our operations, we need to fill down the blanks first. Otherwise, it can create some problems. You can obviously type manually into the blank cells. But it will be very tedious if you have a large dataset. Here, our discussed methods will help you to fill down the blanks very quickly.

How to Fill Down Blanks in Excel: 4 Quick Methods

1. Fill Down Blanks with ‘Go To Special’ Dialog Box

We can use the ‘Go to Special’ dialog box to fill in the blank cells very easily. To explain this method, we will use a dataset that stores the Department, Age, and Working Hour information of some employees. In our dataset, there are some blanks in the Department & Working Hour column. We will try to fill these cells very quickly.

1.1 Fill Blanks with Formula

We can fill blanks with both formulas and specific values. In this sub-method, we will talk about filling down blanks with autofill formulas.

Let’s follow the steps below to learn this method.

STEPS:

In the first place, we need to select the range of cells that contain blank cells. We have selected cells C5 to E12.

Secondly, go to the Home tab and select ‘Find & Select’.
Thirdly, select ‘Go To Special’ from the drop-down menu.

Alternatively, you can do this by pressing the F5 key from the keyboard. Press F5 and a Go To window will occur. Select ‘Special’ from there.

Now, select ‘Blanks’ from the ‘Go To Special’ window and click OK.

After that, you will see the blank cells are highlighted.

Next, go to the formula bar and type the equal sign (=). Then, select the first blank cell. In our case, Cell C6 is the first blank cell.

Finally, press Ctrl + Enter, and all the blank cells will be filled.

Note: Here, we have used formulas to fill blank cells. Copy the column where you have performed the operation and Paste Values in the same column. It will store the data as values in the dataset. If you don’t copy and paste values, you will face problems while sorting.

1.2 Fill Blanks with Specific Value

You can also enter values directly in the blank cells. The steps are given below.

STEPS:

Firstly, select the column that contains empty cells. We have selected Column C.

Now, select the blank cells using the ‘Go To Special’.
Then, type the value manually. Here, we have entered ‘Finance’ in Cell C6.

Press Ctrl + Enter to see values in the other blank cells.

You can see the blank cells hold only ‘Finance’. However, the original data contained different values in different blank cells. We can fix this problem by selecting a small set of cells.

Select a small range of cells and type the value manually. Here, we have selected Cells C6 & C7 and entered ‘Finance’ in Cell C6.

Now, press Ctrl + Enter to see the result.

You can fill in the other blank cells following the same procedure. It is very handy when you have a small dataset that doesn’t follow any pattern.

2. Excel Power Query for Filling Down Blanks

Excel Power Query gives the opportunity to fill down blanks very easily. It is one of the quickest methods. To use this method, you must convert the dataset into a table.

We will use the same dataset here. Follow the steps below.

STEPS:

First, select any cell in the dataset. We have selected Cell B4.

Secondly, go to the Insert tab and select Table.

A Create Table window will open. Make sure ‘My table has headers’ is checked. Click OK to proceed.

Now, the dataset will turn into a table like below.

Next, select any cell from the table. We have selected Cell C6 here.

Then, go to the ‘Power Query’ tab and select ‘From Table/Range’.

A Power Query Editor window will appear. It will contain our table.

Right-click the header of the columns that contain blank cells.
Select Fill and Down respectively from the drop-down menu.

Here, select ‘Close & Load’.

After that, a new sheet will appear containing a table like below with the changes we have made in the Power Query Editor.

3. Use of VBA to Fill Down Blanks in Excel

Excel VBA is another option to fill down empty cells. VBA stands for Visual Basic for Applications. It is used to automate tasks and perform various functions. Here, we will use the same dataset again.

Observe the steps to know more.

STEPS:

In the first place, go to the Developer tab and select Visual Basic.

Secondly, go to Insert and select Module.
Now, type the code in the Module and save it.

Sub Fill_Down_Blanks()
Dim x As Range
For Each x In Selection.Cells
If x.Value = "" Then
x.FillDown
End If
Next x
End Sub

After that, select the range of cells that contain blank cells.

Then, go to Macros from the Developer tab.

Select Run from the Macro.

Finally, it will fill in the blanks till the last rows like below.

4. Fill Empty Cells Using ‘Find & Replace’ Feature

Another way to fill blanks is to use the ‘Find & Replace’ feature of Excel. It is very effective for small datasets.

Follow the steps below.

STEPS:

Select the empty cells at first.

Now, press Ctrl + H from the keyboard. A Find and Replace window will occur.
Keep the ‘Find what’ field empty and type the value you want to enter in the blank cells. We have typed ‘Finance’ here.

You can also select Replace from the ‘Find & Select’ option in the Home tab.

Finally, select ‘Replace All’ to proceed.

Do this for the other blanks and you will find a dataset like below.

Read More: Applications of Excel Fill Series

Things To Remember

There are two things you need to remember when you are filling down blank cells in an Excel worksheet.

You must copy & paste only values when you are using formulas to fill cells. If you do not follow this, you may face problems while sorting the dataset.
Sometimes, the Power Query is not visible in the ribbon. You can add it from the ‘Options’ If you do not find the power query feature in the options then you need to download it from the Microsoft official site.

Download Practice Book

Download the practice book.

Filling Down Blank Cells.xlsm

Conclusion

Here, we have demonstrated 4 quick methods to fill down blanks in Excel. Method 1 is very helpful when you are working with a large dataset. I hope these methods will help you to fill empty cells swiftly. Moreover, the practice book is also added at the beginning of the article. You can download the practice book to exercise these methods. Last of all, if you have any queries or suggestions, feel free to ask in the comment section.

How to Fill Down Blanks in Excel (4 Quick Methods)

How to Fill Down Blanks in Excel: 4 Quick Methods

1. Fill Down Blanks with ‘Go To Special’ Dialog Box

1.1 Fill Blanks with Formula

1.2 Fill Blanks with Specific Value

2. Excel Power Query for Filling Down Blanks

3. Use of VBA to Fill Down Blanks in Excel

4. Fill Empty Cells Using ‘Find & Replace’ Feature

Things To Remember

Conclusion

Further Readings

Leave a reply Cancel reply