Fill Blanks
Excel Tips and Tricks

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.

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

Data Sets
Data Sets

 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.

Using Filter
Using Filter

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.
Select Blanks
Select Blanks

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.

Put Formula
Put Formula

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)
Formula in Support Column
Formula in Support Column

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

 

Click here to download this Excel file.

 

 

 

PK
My Name is PK. I am founder of PK-AnExcelExpert.com. I have more than 10 years of experience in Data Visualization, Excel Automation and dashboard creation. Excel is my passion and I like to do always something innovative in Excel and share it with other people.
https://www.pk-anexcelexpert.com

Leave a Reply

Your email address will not be published. Required fields are marked *