How to Fill Blank Cells in Excel with the Value Above Them

Nearly everyone meets this problem the same way. A report arrives where a category is written once and then left blank for the next fifteen rows, because it looked tidier that way to whoever made it. Or someone unmerged a column of merged cells and discovered that only the top cell kept its value. Either way you now have a column with holes in it, and nothing — not sorting, not a PivotTable, not a lookup — works properly until the holes are filled.

Filling them by hand is the instinct and it is always a mistake. The whole job takes about eight seconds.

The Go To Special route, step by step

Suppose column A holds a region name in A2, blanks down to A8, another name in A9, and so on to A500.

1. Select A2:A500. Start at the first row of data, not at the header. If A2 itself is blank there is nothing above it to copy, and the fill will pull your header down into the data.
2. Press F5. Microsoft’s own shortcut list defines F5 as “displays the Go To dialog box”. Ctrl+G opens the same dialog if you prefer it.
3. Click the Special button in the bottom-left of that dialog. You can also reach this from the ribbon: Home → Editing → Find & Select → Go To Special.
4. Choose Blanks and click OK. Excel selects every empty cell in your range at once. The active cell — the white one — is the first blank, A3 in our example.
5. Type = then press the Up arrow key. Do not click anywhere; clicking destroys the multiple selection. The formula bar now reads =A2.
6. Press Ctrl+Enter. Microsoft defines this as “fill the selected cell range with the current entry”, and the key word is range: every selected blank gets the formula, each one adjusted to point at its own neighbour above.

The cascade is what makes it work. A3 becomes =A2 and picks up the region name. A4 becomes =A3, which now holds that same name. The value walks down the gap until the next real entry stops it.

Now turn the formulas into values

This step is not optional, and skipping it is the one thing that turns a tidy fix into a corrupted file.

Every filled cell currently contains a relative reference to the cell above. Sort the table, filter it, or delete a row, and those references follow the cells they were pointing at — which are no longer above them. The column quietly fills with the wrong answers.

So: select A2:A500, press Ctrl+C, then Ctrl+Alt+V to open Paste Special, choose Values and click OK. The ribbon route is Home → Paste arrow → Values. Do it straight away, while you still remember.

When you cannot touch the original column

If the source column has to stay exactly as it arrived — because it gets overwritten by the next export, or because someone else audits it — build the filled version alongside it instead. With data in column B and a header in row 1, put this in C2 and fill it down:

=IF(B2<>"",B2,C1)

It reads: if there is something in B on this row, use it; otherwise repeat whatever this helper column said one row up. The same cascade, expressed as a formula. It does need a real value in B2 on the first row, or C2 will reach up and grab your header.

The helper column recalculates when the source changes, which is exactly what you want for a column that keeps being refreshed, and exactly what you do not want if you are about to sort the sheet.

Every route compared

Route	Best for	How you start it	Repeats on new data?
Go To Special + Ctrl+Enter	A one-off clean-up of a column you own	F5 → Special → Blanks	No
Helper column with IF	Leaving the original column untouched	=IF(B2<>"",B2,C1)	Recalculates
Power Query Fill Down	A file you reimport every week	Transform → Fill → Down	Yes, on refresh
VBA macro	Many columns, or many workbooks	SpecialCells with FillDown	Yes
Ctrl+D	Deliberately copying one value down	Select, then Ctrl+D	No

Power Query deserves a mention of its own if the data keeps coming back. Load the table with Data → From Table/Range, select the column, then Transform → Fill → Down. Microsoft describes it as traversing the column to “fill any null values in the next rows until it finds a new value” — the same behaviour, saved as a step that reruns every time you hit Refresh. If the same export lands on your desk every Monday, this is the version that pays for itself.

For a column-by-column job across several sheets or files, a macro is the better fit, and the VBA FillDown approach covers the code and the one trap that catches everybody who writes it.

Ctrl+D is not the answer here

Ctrl+D looks like it should do this job, and it is worth understanding why it does not. Microsoft defines it as the Fill Down command, which copies “the contents and format of the topmost cell of a selected range into the cells below”. It does not care whether those cells below are empty. Select A2:A500 and press Ctrl+D and you will overwrite all 498 rows with whatever was in A2.

Ctrl+D is a blunt copy. Go To Special is what makes the operation blank-aware.

Where it goes wrong

“No cells were found.” Excel is telling the truth: there are no genuinely empty cells in your selection. The usual cause is cells that only look empty, covered next.

Cells that look blank but are not. A formula returning "", or a cell holding a single space, is not blank as far as Excel is concerned, and Go To Special will skip it. Diagnose it in one step: =COUNTBLANK(A2:A500) counts truly empty cells and cells holding a formula that returns "" — Microsoft documents that explicitly — while =ROWS(A2:A500)-COUNTA(A2:A500) counts only the genuinely empty ones. When the two numbers disagree, the difference is the number of empty-string impostors that Go To Special will skip. Find & Replace with an empty “Replace with” box will clear stray spaces first.

Merged cells. Merged cells cannot be filled and will refuse the operation. Unmerge first — Home → Merge & Center toggles it off — which produces exactly the column of blanks this article fixes.

You clicked something after choosing Blanks. The multiple selection is fragile. Between clicking OK and pressing Ctrl+Enter, touch nothing but the keyboard. If you lose it, start again at F5.

You sorted before pasting values. Covered above, and worth repeating because it is silent. The numbers change and nothing warns you.

Once the column is solid, the things it was blocking start working again. Lookups are the usual reason people are here in the first place: a gap in a key column makes VLOOKUP return #N/A for every row in the gap, and the same holes break the helper-column trick used for lookups on multiple criteria, where a concatenated key silently becomes a partial one.

Frequently asked questions

Sources