Excel Tip : Fill the blanks cells in your data

Many times we have zig-zag data which has to be formatted in proper manner to create some report or dashboard. In this article you will learn how we can fill the blanks in our data.

Table of Contents

Below is the data in which blanks cells have to be filled:

We can fill the blanks using 4 differed tricks.

1-Using Filter:

We can fill the blanks using filers. First filter the blanks cells and put the formula in first visible cell. Like if first visible cell is “A3” then put the formula “=A2” on cell “A3”. Fill down the formula.

2-Select Blanks:

Select the all the cells and press Ctrl+G.
Click on “Special” button.
Select the “Blanks” radio button.
Click on OK button.

Blanks cells will be selected. Now put the formula like if active cell is B3 then put the formula “=B2” and press “Control + Enter”. Formula will be filled in all blank cells.

3-Using Formula:

We can take the support column can put the formula like given in below image. Then fill down the formula can copy and paste on column A and B.

Formula on E2 = “=IF(A2=””,E1,A2)“
Formula on F2 = “=IF(B2=””,F1,B2)“

4-Using VBA:

We can use small VBA code as given below. Then we can select the range and run this macro.

Option Explicit

Sub Fill_Down()

Dim rng As Range

For Each rng In Selection

    If rng.Value = "" Then

        rng.FillDown

    End If

Next

End Sub