Home > Data > Data Entry > Fill Blanks Fill Blank Cells in Excel Column Here's a quick way fill blank cells in Excel, with the value from above. After the cells are filled in, you can safely sort and filter the list, without problems. Watch my 1-minute video, and get the Excel file, and see the written steps Also see Fill Blank Cells - Macros

Fill Blank Cells From Above In this video, I show how to select and fill blank cells in an Excel report. ⚠ Be sure to watch till the end, where I change the formulas to values! ✅  To follow along, click here to get the Excel file

Step 1) Select Empty Cells

The first main step is to select all the blank cells that you want to fill.

To select the empty cells with Excel's built in Go To Special feature, follow these steps:

• Select columns A and B, that contain the blank cells.
• Use the Ribbon commands:
  • On the Excel Ribbon's Home tab, in the Editing group, click Find & Select
  • Click the Go To Special command
• OR, use keyboard shortcuts:
  • Press Ctrl + G to open the Go To window
  • Press Alt + S to open the Go To Special dialog gox

1.1) Go To Special Dialog Box

• In the Go To Special dialog box, click Blanks
  • OR, press Alt + K (the underlined letter in the Blanks option)
• Then click OK, to close the Go To Special dialog box

1.2) Blank Cells Selected

After you close the Go To Special dialog box:

• blank cells in colums A and B are selected
• the first blank cell, A3, is the active cell
• selected cells are only within the used data range, even though entire columns were selected

Step 2) Create a Simple Formula

Next, complete the following steps, to create a simple formula, that will copy the value from the closest heading above each blank cell.

Currently, blank cells in columns A and B are selected, and cell A3 is the active cell.

To build the formula, follow these steps:

• With the blank cells selected, type an equal sign, to start the formula.
• On the keyboard, press the up arrow key
  • This enters a relative reference to the cell above
  • Cell A3 is active, so it refers to cell A2, the cell directly above it

In the screen shot below, you can see the formula in cell A3, and in the formula bar:

• =A2

2.1) Enter Formula in All Selected Cells

The final step will enter that formula in all the selected blank cells

• DO NOT tap the Enter key, as you normally would, to complete a formula
• Instead, press Ctrl + Enter
  • Combining the Control and Enter keys will enter the active cell's formula in all the selected cells

Because the formula uses a relative reference, each cell's formula refers to the cell directly above it.

• This screen shot shows the formula in cell B4, which refers to cell B3
• If you check cell B3, you'll see that its formula refers to cell B2

3) Change Formulas to Values

The final step is imporant!

• ⚠ You need to change the "blank cell" formulas to values, to lock them in. If you leave formulas in the cells, they'll show incorrect data later, if you sort the list.

To change the formulas to values:

1. I use a quick mouse shortcut (video below)
2. Or, you can use Ribbon commands instead (steps below)

3.1) Mouse Shortcut Paste As Values

To quickly change formulas into values, you can use this mouse shortcut, shown in the video below. The written instructions are on the Excel Data Entry Tips page.

3.2) Use Ribbon Commands to Paste Values

Follow these steps to change the "blank cell" formulas to values

• Select the entire columns where you filled in the blanks with formulas -- columns A:B in this example
  • Note: Do not select the entire columns if other cells in those columns contain formulas that you need to keep. You don't want to accidentally change those other formulas to values.
• On the Ribbon's Home tab, click Copy, or use the keyboard shortcut - Ctrl + C
• With the columns still selected, click the drop down arrow on the Paste command
• In the Paste Values section of the drop down menu, click the Values command

3.3) Values in All Selected Cells

After you complete all the above steps, the blank cells are filled in with values, and you can safely sort or filter the list.

Optional: Hide Duplicate Headings

After you fill in the blank cells with values from above, you can use a conditional formattng trick, to hide the duplicate headings.

• This makes the list easier to read, like it was with the blank cells.
• And, you'll be able to safely sort and filter the data!

This video shows you the steps, and the written instructions are on the Conditional Formatting Examples page.

Get the Excel File ✅

• Fill Blank Cells: To see the report with blank cells, and follow along with the Fill Blanks video, you can download the sample Excel workbook file. The file is zipped, and is in xlsx format, and does not contain macros.

Get Monthly Excel Tips!

Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Sign Up.

Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!

More Data Entry Tutorials

Fill Blank Cells - Macros

Data Entry Tips

Convert Text to Numbers

Increase Numbers With Paste Special

Fill Blank Cells Excel 2003

Video Transcript If you'd like to read the video transcript, click on the green check box below. When you're finished, you can click the check box again, to hide the transcript    ✅ Click here for Transcript Fill Blank Cells Video Transcript Here is the transcript for the Fill Blank Cells video, at the top of this page. Introduction Sometimes in Excel, you'll end up with data like this, possibly exported from another system where you've got headings, but blank cells below those headings. Here we can see region and it's only listed once, and then blank to the end of the region, and the employees in each region. You might have one or multiple employees, but again, blank below those employee names. This is fine for reading the list, but if you want to work with the data, perhaps filter it or sort things, then you need to fill in these blank cells. Here's a quick way to do that. Select Blank Cells First, we'll select columns A and B where there are blanks. Then on the Ribbon's Home tab, go to Find & Select, Go To Special. In the Go To Special window, click Blanks, and then click OK. Add a Formula All the blank cells are selected now and we're going to put in a very simple formula that just says get the value from the cell above. Type an equal sign and then press the up arrow on your keyboard. You can see the formula here now has changed to A2. To fill that formula into all the selected cells, press the Ctrl key and then tap Enter. That puts that same formula into all the selected cells. Change to Values Now we want to change these formulas to values so that we can move things around without having the values change. I'm going to select columns A and B where we filled the blanks and then point to the border of what is selected. You'll see a four-headed arrow there. Press the right button on the mouse and drag slightly to the right. Then drag right back to where you started. Let go of the right mouse button, and the pop up menu has some choices. Click Copy Here As Values Only. Now everything in here is a value instead of a formula, and you can sort or filter without any problems.