Fill Blank Cells With Value Above

What it does

You exported a pivot table to flat data. The first row of each group has a label in column A, but every subsequent row in that group has a blank cell where the label should repeat. So now you can't filter, can't pivot, can't lookup. The macro walks down each column in your selection, remembers the last non-blank value, and pours it into every blank cell underneath it. Reset happens at the top of each new column. The result is a clean rectangular block of data where every row has its own group label.

Real-world example

Picture a senior auditor at a 200-person regional accounting firm. Her client emails her a "trial balance with class detail" exported from QuickBooks Enterprise. The export looks reasonable in Excel: column A has class names like "Eastside Office", "Westside Office", "Mobile Unit", and column B has account names. The catch is QuickBooks only writes the class name on the first row of each block. Rows 2 through 47 of the Eastside Office block leave column A blank because, visually, you can tell which class they belong to.

She needs to slice this by class to test the audit assertion. Step one in her current workflow: filter, pivot, or VLOOKUP. None of them work because column A is mostly blank. So she does the F5 > Special > Blanks > type "=A2" > Ctrl+Enter trick. It works most of the time. Sometimes she misses a column boundary and an Eastside label propagates into a Westside row, which she only catches when the audit numbers don't tie out. Then it's twenty minutes of unwinding.

With this macro she selects column A from row 2 to the last data row, runs the tool, and the macro fills 1,847 blanks in about 1 second with no risk of crossing a column boundary because each column resets independently. The popup tells her exactly how many blanks were filled, which she sanity-checks against her expected row count.

On a typical 2,000-row trial balance with class detail, expect about 8 minutes saved versus the F5-blanks workflow. The bigger win is no more "did I propagate the wrong label" sleuthing.

How to use

First time? See How to run our tools for the 30-second setup.

1. Select the column or range with blanks to fill. The macro walks each column independently, top to bottom.
2. Press Alt + F8 (Windows) or Option + F8 (Mac), pick FillBlanksWithAbove, click Run.
3. The macro fills every blank cell with the most recent non-blank value above it in the same column. The popup reports the number of cells filled.

FAQ

What if the very top row is blank?

That cell stays blank. The macro can only copy a value from above; if there's no value above the first blank, there's nothing to copy. The fill restarts at the next non-blank value it finds. So if rows 1 to 3 are blank in column A and row 4 has "Northgate", rows 5 onward will fill with "Northgate" but rows 1 to 3 remain blank.

Can it cross column boundaries?

No, that's a deliberate design choice. Each column has its own "last seen" tracker that resets when a new column starts. So if column A is "branch" and column B is "department", the macro fills branch labels down column A and department labels down column B without ever leaking a value from one column into the other.

Will it fill formula cells?

Formula cells are treated like any other cell. If a formula evaluates to empty (""), the cell is treated as blank and gets filled. If a formula evaluates to a value, that value becomes the next "last seen" value. Most of the time you're running this on plain text columns, not formula columns, so this rarely matters.

What counts as "blank"?

A cell is treated as blank if IsEmpty() returns true OR the cell contains a string that's only whitespace (spaces, tabs). So "", " ", and a truly empty cell are all treated the same. This catches the common case where a CSV import stuffed empty quotes into cells that should be empty.

Can I undo it?

Yes, Ctrl/Cmd + Z right after running. The macro can fill thousands of cells in one shot, so if you're running it on a critical file, work on a copy first. The macro runs entirely offline. No signup, no account, no data leaves your machine.