How to Fill Blanks in Excel - 4 Methods

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

Use the ‘Go to Special’ dialog box to fill the blank cells.

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

Fill Down Blanks with ‘Go To Special’ Dialog Box

1.1 Fill Blanks with Formula

Use autofill formulas.

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.

Note: Copy the column in which the operation was performed and Paste Values in the same column. It will store data as values.

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.

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

Use Excel VBA.

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

Fill blanks using the ‘Find & Replace’ feature of Excel.

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.

Filling Down Blank Cells.xlsm

How to Fill Blanks in Excel – 4 Methods

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

1.1 Fill Blanks with Formula

1.2 Fill Blanks with a Specific Value

Method 2 – Using the Excel Power Query to Fill Blanks

Method 3 – Using VBA to Fill Blanks in Excel

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

Things To Remember

Further Readings

Leave a reply Cancel reply