Home>Blogs>Excel Tips and Tricks>Excel Tip : Fill the blanks cells in your data
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


    End If


End Sub


Click here to download this Excel file.




Meet PK, the founder of PK-AnExcelExpert.com! With over 15 years of experience in Data Visualization, Excel Automation, and dashboard creation. PK is a Microsoft Certified Professional who has a passion for all things in Excel. PK loves to explore new and innovative ways to use Excel and is always eager to share his knowledge with others. With an eye for detail and a commitment to excellence, PK has become a go-to expert in the world of Excel. Whether you're looking to create stunning visualizations or streamline your workflow with automation, PK has the skills and expertise to help you succeed. Join the many satisfied clients who have benefited from PK's services and see how he can take your Excel skills to the next level!

Leave a Reply