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:
We can fill the blanks using 4 differed tricks.
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.
- 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.
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)“
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.