Fill Blank Cells with the Value Above in Excel

Part of the free Module 2: Fill, Auto Fill and Flash Fill · Lesson 6 of 6 · Full Excel course

Updated on 5 September 2026 · Works in Excel 365, 2021, 2019 and 2016 unless noted.

To fill blank cells with the value above in Excel, select the column, open Go To Special > Blanks, type = followed by the up arrow, and press Ctrl+Enter. Every gap is filled with the value from the cell above it. This lesson covers that technique, the other ways to fill down without dragging, and how to convert the results to values.

Table of Contents

Why blank cells appear in reports

Exports from accounting systems, pivot tables copied as values and reports typed by hand often show a category only on its first row and leave the rows beneath it blank. The layout reads well but breaks sorting, filtering, pivot tables and lookups, because those rows have no category. Filling the blanks with the value above turns the report back into a proper list. Dragging is not an option when the gaps are irregular; the Go To Special method handles thousands of gaps in one action.

How to fill blank cells with the value above

Select the range that contains the gaps, for example A2:A500. Include only the column or columns you want to fill.
Press F5 or Ctrl+G, click Special, choose Blanks and click OK. Alternatively use Home > Find & Select > Go To Special. Only the empty cells are now selected.
Without clicking anywhere, type = and press the Up arrow once. The formula bar shows a reference to the cell above the active blank, for example =A2.
Press Ctrl+Enter. The formula is entered in every selected blank at once, each one pointing at its own cell above, so the value cascades down through consecutive gaps.
Select the whole column, press Ctrl+C, then Home > Paste > Values (or Ctrl+Alt+V, V, Enter) to replace the formulas with static values. Skip this step only if the source rows will never be sorted or deleted.

The same method fills blanks with the value from the left: type = and press the Left arrow instead. To fill blanks with fixed text such as “N/A” or 0, type the text after selecting the blanks and press Ctrl+Enter without a formula.

Ways to fill down without dragging

Method	Keys or clicks	Best for	Limitation
Ctrl+D on a selection	Select from the source cell to the last row, press Ctrl+D	Any length, exact control of the end row	You must make the selection first
Double-click the fill handle	Double-click the square at the corner of the source cell	Long columns beside complete data	Stops at the first blank in the adjacent column
Ctrl+Shift+Down then Ctrl+D	From the source cell, extend to the end of the data block, fill	Keyboard-only filling of a full column	Extends only through contiguous cells in the same column
Ctrl+Shift+End then Ctrl+D	Extend from the source cell to the last used cell of the sheet, fill	Filling several columns at once to the bottom of the data	Includes every column to the right; trim with Shift+Left
Name Box	Type D2:D5000 in the Name Box, Enter, then Ctrl+D	Exact ranges on very large sheets	You need to know the last row
Ctrl+Enter	Select the range, type the formula, press Ctrl+Enter	Entering a new formula in many cells or non-adjacent cells	Enters what you type; it does not copy an existing cell
Go To Special > Blanks	Select blanks, =Up arrow, Ctrl+Enter	Irregular gaps in a category column	Produces formulas; paste as values afterwards
Excel Table calculated column	Ctrl+T, then type one formula	Data that grows over time	Requires a Table; not for filling blank text

Fill down with the keyboard on a long sheet

A sheet with 20,000 rows and a formula in E2 is filled in three keystrokes. Select E2, press Ctrl+Shift+Down to extend the selection to the last row of column E, and press Ctrl+D. If column E is empty below E2, Ctrl+Shift+Down runs to row 1,048,576; in that case select E2, hold Shift and press Ctrl+Shift+End, which stops at the last used row, then press Shift+Left until only column E is selected, and press Ctrl+D. Double-clicking the fill handle is the mouse equivalent and works as long as column D has no blanks.

Worked example: repair an exported expense report

The export lists each department once and leaves the following rows blank.

A: Department	B: Expense	C: Amount
Marketing	Advertising	12,000
(blank)	Events	4,500
(blank)	Printing	800
Operations	Rent	30,000
(blank)	Utilities	2,200
Sales	Travel	7,400

Select A2:A7 and open Go To Special > Blanks. Cells A3, A4 and A6 are selected, with A3 active.
Type =A2 (or type = and press Up) and press Ctrl+Enter. A3 shows Marketing, A4 shows Marketing because it points at A3, and A6 shows Operations.
Copy column A and paste as values. The report now sorts and pivots correctly: Marketing 17,300, Operations 32,200, Sales 7,400.

Power Query alternative for repeated imports

If the same report arrives every month, load it with Data > From Table/Range, right-click the Department column in Power Query and choose Fill > Down. Power Query treats empty cells as null and fills them in one step; refreshing the query repeats the fix on next month’s file. Power Query is worth setting up when the same import repeats; for a one-off text pattern, the Flash Fill lesson covers the quicker route.

Tips and common mistakes

Do not click after selecting blanks. A single click removes the multi-selection. Start typing straight away.
Cells with a space are not blank. Go To Special skips them. Use Find and Replace to remove stray spaces first, or use TRIM.
Paste as values before sorting. The formulas point at the cell above; sorting moves the rows and the references follow, scrambling the categories.
Merged cells block the method. Unmerge (Home > Merge & Centre) and then fill; the blanks appear where the merge was.
Select only the columns you mean to fill. Selecting the whole sheet will fill every empty cell in the data area, including number columns.
Filtered ranges are fine. Ctrl+Enter enters only into visible selected cells, so you can filter, select blanks and fill just the rows you can see.
Undo is one step. Ctrl+Z reverses the whole Ctrl+Enter fill at once.

Errors and how to fix them

Problem	Cause	Fix
“No cells were found”	The blanks contain spaces, apostrophes or formulas returning “”	Clear them with Find and Replace or Clear Contents, then repeat
Only one cell was filled	Enter was pressed instead of Ctrl+Enter	Undo, reselect the blanks and press Ctrl+Enter
Wrong value filled in the first blank	The active cell was not the first blank, or Up arrow was pressed twice	Check the formula bar shows the cell directly above the active cell
Values changed after sorting	Formulas were left in place	Undo the sort, paste the column as values, sort again
Double-click filled only part of the column	A blank in the adjacent column	Use Ctrl+Shift+Down or the Name Box and Ctrl+D
Ctrl+Shift+Down selected a million rows	The column is empty below the source cell	Use Ctrl+Shift+End and trim, or type the range in the Name Box

Practice exercise

Type five regions in column A with two or three blank rows under each, then fill the blanks with the value above and convert to values.
Put a formula in E2 beside 200 rows of data and fill it down three ways: double-click, Ctrl+Shift+Down with Ctrl+D, and the Name Box.
Insert a blank in column D halfway down and repeat the double-click to see where it stops.
Select a block of 30 cells and enter the text Pending in all of them with one Ctrl+Enter.
Load the repaired report into Power Query and use Fill Down on the region column, then refresh after adding rows.

Key takeaways

Go To Special > Blanks, =Up arrow and Ctrl+Enter fill every gap with the value above in one action.
Paste the column as values before sorting or deleting rows.
Ctrl+D on a selection, double-clicking the fill handle and Ctrl+Shift+Down are the fastest ways to fill a formula down without dragging.
Ctrl+Enter enters one formula or value in every selected cell, even non-adjacent ones.
Power Query’s Fill Down repeats the fix automatically for recurring imports.

Related lessons

Frequently asked questions

How do I fill empty cells with the value above in Excel?

Select the range with the gaps, press F5, click Special, choose Blanks and click OK. Type an equals sign, press the Up arrow so the formula points at the cell above, and press Ctrl+Enter. Every blank receives the value above it. Copy the column and paste as values so the results survive sorting.

How do I fill down in Excel without dragging?

Select the cell with the formula, press Ctrl+Shift+Down to extend the selection to the end of the data, then press Ctrl+D. Double-clicking the fill handle does the same with the mouse. On very long sheets, type the exact range in the Name Box, press Enter and then Ctrl+D.

What does Ctrl+Enter do in Excel?

Ctrl+Enter confirms an entry into every selected cell at the same time instead of only the active cell. Select the range, type a value or formula once and press Ctrl+Enter. Formulas adjust their relative references for each cell, which is why it fills blanks with the value above so neatly.

Why does Go To Special say no cells were found?

The cells that look empty contain something: a space, an apostrophe, or a formula returning an empty string. Select the column, use Find and Replace to remove spaces, or clear the contents of those cells, then run Go To Special > Blanks again.

Is there a way to fill blanks automatically every month?

Yes. Load the report into Power Query with Data > From Table/Range, right-click the column and choose Fill > Down, then Close & Load. When next month’s data replaces the source, click Refresh and the blanks are filled again with no manual steps.

Want the finished version? Ready-made Excel dashboards, trackers and VBA systems are available at NextGenTemplates.com.