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 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.
Read More: How to Use Autofill Formula in Excel
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.
Similar Readings:
- AutoFill Cell Based on Another Cell in Excel
- How to AutoFill from List in Excel
- How to Create a Custom AutoFill List in Excel
- How to Repeat Formula Pattern in Excel
- How to Apply AutoFill Shortcut in Excel
- How to AutoFill Sequential Letters 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.
- 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.
Similar Readings:
- How to AutoFill Numbers in Excel
- How to Autofill Numbers in Excel Without Dragging
- How to AutoFill Ascending Numbers in Excel
- How to AutoFill Numbers in Excel with Filter
- How to Auto Number Cells in Excel
- How to Auto Number or Renumber after Filter in Excel
- Applications of Excel Fill Series
- [Fix] Excel Fill Series Not Working
- Fix: Excel Autofill Not Working
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 like below.
Read more: Excel Formulas to Fill Down Sequence Numbers Skip Hidden Rows
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.
Similar Readings
- How to Auto Generate Number Sequence in Excel
- How to Add Sequence Number by Group in Excel
- How to Repeat Number Pattern in Excel
- How to Autofill Dates in ExcelÂ
- How to Enter Sequential Dates Across Multiple Sheets in Excel
- How to Autofill Dates in Excel Without Dragging
- How to Auto Populate from Another Worksheet in Excel
- How to Perform Predictive AutoFill in Excel
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.
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
- How to Autofill Days of Week Based on Date in Excel
- How to AutoFill Months in Excel
- How to Create Automatic Rolling Months in Excel
- How to Increment Month by 1 in Excel
- How to Autofill a Column in Excel
- How to Fill Column in Excel with Same Value
- How to Fill Down to Last Row with Data in Excel
- How to AutoFill Formula When Inserting Rows in Excel
- Filling a Certain Number of Rows in Excel Automatically
- [Fixed!] AutoFill Formula Is Not Working in Excel Table
- How to Turn Off AutoFill in Excel
- [Fixed!] Auto Fill Options Not Showing in Excel
- [Solved:] Excel Double Click AutoFill Not Working
- Excel VBA: Autofill Method of Range Class Failed
- How to Use VBA AutoFill in Excel
- AutoFill Formula to Last Row with Excel VBA
- Drag Number Increase Not Working in Excel