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 blanks very quickly.


Download Practice Book

Download the practice book.


4 Ways to Fill Down Blanks in Excel

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

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

Fill Down Blanks with ‘Go To Special’ Dialog Box


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

Fill Down Blanks with ‘Go To Special’ Dialog Box

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

Fill Down Blanks with ‘Go To Special’ Dialog Box

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

Fill Down Blanks with ‘Go To Special’ Dialog Box

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

Fill Down Blanks with ‘Go To Special’ Dialog Box

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

Fill Down Blanks with ‘Go To Special’ Dialog Box

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

Fill Down Blanks with ‘Go To Special’ Dialog Box

You can see the blank cells holds only ‘Finance’. But 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.

Fill Down Blanks with ‘Go To Special’ Dialog Box

  • Now, press Ctrl + Enter to see the result.

Fill Down Blanks with ‘Go To Special’ Dialog Box

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

Read more: How to Use Autofill Formula in Excel


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.

Excel Power Query for Filling Down Blanks

  • Secondly, go to the Insert tab and select Table.

Excel Power Query for Filling Down Blanks

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

Excel Power Query for Filling Down Blanks

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

Excel Power Query for Filling Down Blanks

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

Excel Power Query for Filling Down Blanks

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

Excel Power Query for Filling Down Blanks

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

Excel Power Query for Filling Down Blanks

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

Excel Power Query for Filling Down Blanks

  • Here, select ‘Close & Load’.

Excel Power Query for Filling Down Blanks

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

Excel Power Query for Filling Down Blanks


Similar Readings


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.

Use of VBA to Fill Down Blanks in Excel

  • 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

Use of VBA to Fill Down Blanks in Excel

  • After that, select the range of cells that contains blank cells.

Use of VBA to Fill Down Blanks in Excel

  • Then, go to Macros from the Developer tab.

  • Select Run from the Macro.

  • Finally, it will fill the blanks like below.

Use of VBA to Fill Down Blanks in Excel

Read more: How to Use VBA AutoFill in Excel


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.

Fill Empty Cells Using ‘Find & Replace’ Feature

  • You can also select the 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.


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.

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.


Further Readings

Mursalin

Mursalin

Hi there! This is Mursalin. I am an Excel and VBA content developer as well as an electrical and electronics engineer. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I am currently working and doing research on Microsoft Excel. Here I will be posting articles related to Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo