How to Fill Blanks in Excel – 4 Methods

Method 1 – Fill Blanks using the ‘Go To Special’ Dialog Box

The sample dataset contains employees’ information: Department, Age, and Working Hours. There are blanks in the Department and Working Hours column.

1.1 Fill Blanks with Formula

STEPS:

• Select the range that contains blank cells: C5:E12.

• Go to the Home tab and select ‘Find & Select’.
• Select ‘Go To Special’.

• Alternatively, press F5 to display the Go To window. Select ‘Special’.

• Select ‘Blanks’ in ‘Go To Special’ and click OK.

• Blank cells are highlighted.

• Go to the formula bar and enter the equal sign (=).
• Select the first blank cell. Here, C6.

• Press Ctrl + Enter, and all blank cells will be filled.

Read More: How to Repeat Formula Pattern in Excel

1.2 Fill Blanks with a Specific Value

STEPS:

• Select the column that contains empty cells. Here, Column C.

• Select the blank cells using  ‘Go To Special’.
• Enter the value manually. Here,  ‘Finance’ in C6.

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

Blank cells display ‘Finance’. However, the original data contained different values in different blank cells. To fix this problem:

• Select a small range of cells and enter the value manually. Here, C6 and C7 were selected and ‘Finance’  was entered in C6.

• Press Ctrl + Enter to see the result.

You can fill in the other blank cells following the same procedure.

Read More: How to Perform Predictive AutoFill in Excel

Method 2 – Using the Excel Power Query to Fill Blanks

STEPS:

• Select any cell in the dataset. Here, B4.

• Go to the Insert tab and select Table.

• A Create Table window will open. Check ‘My table has headers’.
• Click OK.

• The dataset will turn into a table.

• Select any cell in the table. Here, C6.

• Go to the ‘Power Query’ tab and select ‘From Table/Range’.

• The Power Query Editor window will be displayed.

• Right-click the header of the columns that contain blank cells.
• Select Fill and choose Down.

• Select ‘Close & Load’.

• A new sheet will be displayed containing a table with the changes:

Method 3 – Using VBA to Fill Blanks in Excel

STEPS:

• Go to the Developer tab and select Visual Basic.

• In Insert, select Module.
• Enter 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

• Select the range that contains blank cells.

• Go to Macros in the Developer tab.

• Click Run in Macro.

• It will fill the blanks till the last row.

Method 4 – Fill Empty Cells Using the ‘Find & Replace’ Feature

STEPS:

• Select the empty cells.

• Press Ctrl + H.
• In the Find and Replace window, keep the ‘Find what’ field empty.
• Choose a value to enter in the blank cells. Here, ‘Finance’.

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

• Select ‘Replace All’.

• Follow the same steps for the other blank cells.

Read More: Applications of Excel Fill Series

Things To Remember

• If the Power Query is not visible on the ribbon, add it in ‘Options’.

Download Practice Book

Download the practice workbook.

Further Readings

• 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 Days of Week Based on Date in Excel
• How to Repeat Number Pattern in Excel
• How to Add Sequence Number by Group in Excel
• How to Enter Sequential Dates Across Multiple Sheets in Excel

<< Go Back to Excel Autofill | Learn Excel