Fill Blank Cells With the Value Above in Excel

To fill blank cells in Excel with the value above, you select only the blanks with Go To Special, point the first one at the cell above, and press Ctrl+Enter to fill them all at once. It takes about ten seconds on a column of any length. This guide walks through the method, how to lock the results in, and a formula option that updates by itself.

Why reports have blank cells#

Many exports and pivot-style reports show a label only on the first row of each group, and leave the rows below it blank:

That's easy to read, but hard to work with. Filter on South and you only see Lena. Sort by sales and Marcus loses his region. SUMIF and pivot tables treat the blanks as their own group. Filling the blanks with the value above fixes all of that.

How to fill blank cells with the value above#

Step 1: Select the range#

Select the column that has the gaps, from the first value to the last row of data, here A2:A6. Don't select the whole column, or Excel fills every empty row down to the bottom of the sheet.

Step 2: Select only the blanks#

Press F5 (or Ctrl+G), click Special, choose Blanks and click OK. On a Mac, press Control+G and click Special, or use Home > Find & Select > Go To Special.

Now only A3, A5 and A6 are selected. The active cell, the white one in the selection, is A3. Our guide to Go To Special explains the other options in this dialog.

Step 3: Point at the cell above#

Without clicking anywhere, type = and press ↑ (the up arrow). The active cell now shows =A2.

Step 4: Fill every blank with Ctrl+Enter#

Press Ctrl+Enter on Windows or Control+Return on a Mac. Excel puts the formula into every selected cell, and each one adjusts to point at the cell above it: A5 gets =A4, A6 gets =A5.

The result is North, North, South, South, South. If you press Enter on its own by mistake, only A3 is filled; press Ctrl+Z (Mac: ⌘Z) and repeat step 4.

Step 5: Turn the formulas into values#

The filled cells are formulas that point at the row above. If you sort the data now, they point at the wrong rows and give wrong results. Lock the results in:

1. Select A2:A6 again.
2. Copy with Ctrl+C (Mac: ⌘C).
3. Paste as values: on Windows, Ctrl+Alt+V, then V, then Enter. On a Mac, Control+⌘V, choose Values and click OK. You can also use Home > Paste > Paste Values.

Now the cells hold the text itself, and you can sort, filter and summarise safely.

Fill blanks with a fixed value instead#

Sometimes the right value for a blank is not the one above. For empty sales figures, you might want 0; for a missing category, Unknown.

Use the same steps, but at step 3 type the value instead of = and the arrow:

1. Select the range.
2. Go To Special > Blanks > OK.
3. Type 0 (or Unknown).
4. Press Ctrl+Enter (Mac: Control+Return).

No paste-as-values step is needed, because you typed a value, not a formula.

The formula method: fill blanks in a helper column#

If the data is refreshed often, or you don't want to change the original column, use a formula in a helper column. In B2, copy the first value:

=A2

In B3, take A3 if it has a value, or the filled value above if it doesn't:

=IF(ISBLANK(A3),B2,A3)

Fill B3 down to B6 with Ctrl+D (Mac: ⌘D). IF checks the test, ISBLANK(A3), and returns B2 when A3 is empty. Because B2 is the helper column itself, the value keeps passing down until a new region appears. The IS functions lesson covers ISBLANK, and our guide to fill down and fill right explains how the references move as you copy.

A shorter version, =IF(A3="",B2,A3), also treats a formula that returns empty text as blank, which ISBLANK doesn't.

When the blanks aren't really blank#

If Go To Special says "No cells were found", or skips some cells, those cells contain something you can't see. The usual causes:

• A space. Use Ctrl+H (Mac: Control+H), type a single space in Find what, leave Replace with empty, and click Replace All. Be careful: this also removes spaces inside text, so select only the column first.
• A formula that returns "". It looks empty but counts as a value. Convert the column to values first.
• An apostrophe on its own. Delete those cells' contents with Delete.

Other ways to fill blank cells#

• Power Query: for data you import again and again, Data > From Table/Range opens Power Query, where Transform > Fill > Down fills blanks every time you refresh.
• Fill a series, not a copy: if the blanks should continue a pattern, like dates or numbers, use AutoFill instead; see AutoFill series in Excel.
• Turning codes into labels: if the filled column holds short codes, the SWITCH function can turn them into readable names in the next column.

Ctrl+Enter is one of the shortcuts worth knowing by heart. It's in our Excel keyboard shortcuts cheat sheet and on the list of shortcuts that make you look fast in a test.

Practise it#

Try the helper-column formula in the exercise on this page. Then practise copying formulas down and across with Fill Down and Fill Right in a live spreadsheet, and use the IS functions and SWITCH lesson to check for empty cells with ISBLANK.