Data clean-up techniques in Excel: Filling blank cells

Sometimes you will end up with a worksheet that will look something like the one shown in the following Figure.

Filling Blank Cells in Excel.

Filling Blank Cells in Excel. This data contains gaps in the Sales Representative column.

Filling blank cells with data is common. In our example, an entry in column A applies to several rows of data. This type of data arrangement is easy to understand. For example, you can find easily Jane’s Jan, Feb and March months’ sales. But if you sort this type of list, the blank cells will mess things up, and you will not be able to tell who sold what. See this figure sorted according to Amount.

Filling blank cells in Excel

When data is sorted, you can’t find out the sales representative as there were gaps in this column.

We shall explain here x ways to solve this problem.

Download this file to work with filling blank cells in Excel

Solution 1:

If your data is small, you can enter the missing cell values manually or by using this commands: Home ➪ Editing ➪ Fill ➪ Down (or the Ctrl+D shortcut).

For example, select cell A6 and choose Home ➪ Editing ➪ Fill ➪ Down or press CTRL+ D, A6 will show “Jane”. Then select cell A7 and press CTRL+ D or choose Home ➪ Editing ➪ Fill ➪ Down. Cell A7 will be filled with “Jane”. Fill the rest blank cells in this way.

Filling blank cells in Excel

Filling Blank cells manually. Press CTRL+ D or choose Home ➪ Editing ➪ Fill ➪ Down

Solution 2

If your data is large, it is not possible to fill blanks manually. Here’s a better way:

Step 1: Select the range that has the blank cells (A5:A16, in our example).

Step 2: Choose Home ➪ Editing ➪ Find & Select ➪ Go to Special. The “Go to Special” dialog box will appear.

Step 3: Select the “Blanks” option and click OK. This action selects the blank cells in the cell range A5:A16.

Filling Blank Cells in Excel

Go to Special Dialog Box. Choose “Blanks” and then press OK.

Step 4: In the formula bar, type an equal sign (=) and then the cell address of the first cell with data in the column (=A5, in our example), and press Ctrl+Enter. You will find the following result.

Filling blank cells in Excel

The Blank cells are filled with data as you expected.

Step 5: If you select the recent filled blank cells, they will show “Formula” in the formula bar rather than data. But we want data in those cells. See the following figure.

Filling blank cells in Excel

If you click the recently filled cells, they will show formula rather than data. We need data in those cells.

Step 6: Reselect the original range and press CTRL+ C to copy the selection.

Step 7: Choose Home ➪ Clipboard ➪ Paste ➪ Paste Values to convert the formulas to values.

After you complete these steps, the gaps are filled in with the correct information.

Happy Excelling 🙂

Further Readings




Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy:

We will be happy to hear your thoughts

Leave a reply