Software

Quick Tip: Fill in blank cells in Excel quick and easy

Blank cells can spell trouble. Here's an easy way to fill in those blanks and protect the validity of your data.

This tip was originally published in April 2010 but continues to be one of our most popular.

One of the first things you should do when importing foreign data into Excel or upon receiving a legacy workbook is to check for blank cells and fill them in, where appropriate. Of course, some cells are blank on purpose and should remain that way. But blanks can be troublesome, if not downright destructive. The sheet below is a good example of foreign data that as is, doesn't lend itself perfectly to a spreadsheet. In a report, repeating the company name from record to record might be distracting to the reader. Whereas in a sheet, a simple sort by a field other than the company name field would quickly orphan data - there's no way to attach a record to its company.

457.jpg

My best advice is to fill these types of blanks right away. You might consider typing the entries, but there's a quicker way:

First, select the range that contains blanks you need to fill. Don't select the column header cell - just the range that contains actual data. Using the example sheet above, the range is A2:A11.

Select Go To from the Edit menu or press [Ctrl]+G and then click the Special button. In Excel 2007, choose Go To Special from the Find and Select dropdown list in the Editing group on the Home tab.

Select Blanks.

452.jpg

When you click OK, Excel will select all the blank cells in A2:A11.

453.jpg

In the first selected blank cell (A3) enter an equal sign and point to the cell above. The cell is already selected, you don't have to actually click A3.

454.jpg

Press [Ctrl]+[Enter] and Excel will copy the respective formula to all blank cells in the selected range.

455.jpg

At this point, the range contains literal values (the original values) and formulas that repeat those literal values. To maintain order, replace the formulas with their results (the repeated literal values). Select the range (A2:A11) and choose Copy from the Edit menu. In Excel 2007, click Copy in the Clipboard group on the Home tab.

Select Paste Special from the Edit menu. Then, select Values and click OK. In Excel 2007, choose Paste Values from the Paste drop-down list in the Clipboard group on the home tab. You just replaced the formulas with literal values.

456.jpg

458.jpg

If the range contains formulas before the process, be careful: You don't want to overwrite those formulas, just the formulas you added to fill the blank cells. This technique won't work with every sheet that contains blank cells. In this case, this quick technique repeats existing data.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

4 comments
spotty186
spotty186

The blanking option must have the last cell in the range as a non-blank, otherwise Excel will not highlight the blanks at the "end" of your list. But otherwise this is handy for a lot of data.

MickThornton
MickThornton

If you use the AutoFill handle, you have to grab it at each change in your data. That would be fine for just 3 or 4 changes of data. The problem with AutoFill becomes apparent if you had a large set of data that has 100, 1,000, or more changes in data. That would require the user to have 1000 touch points using the AutoFill Handle.

I also believe that some users may just use the AutoFill Handle once or twice and overwrite all the data changes in the column.

My first thought would be to use AutoFill in many situations, but this a good solid tip for larger sets of data.  Think of it on a quarterly sales report where the change in city has blank cells (not sure why the data would be that way, but hypothetically). I'd want to be careful not to overwrite the city data and end up with all my sales from 1 city. 

fornax421
fornax421

that was overly complicated...why not just click on the cell, then click and drag the black box in the bottom right corner to fill the cells you need to? that would save about 5 steps and 10 minutes...

DAS01
DAS01

I would never leave blank cells like that (for the reason given in the article) but if one does see/receive a sheet with blanks this is a good tip.  For a small number of blank cells in a fairly small range I would just copy and paste from the cell above, but in a bigger range and especially where there is a likelihood of overlooking the blanks this is very good. 

I do not construct fancy new Excel sheets every day and so am not familiar with a lot of features and certainly would not have looked for this sort of thing.


Thanks, Susan.