Selecting only blank cells

This is one of the questions colleagues at work are often asking me. The need arises usually when you are importing data from the mainframe to Excel.
Many times you are getting something like this:

If you want to produce a Pivot Table from this data, you will have to fill the blank cells in column A:
  • values in cells A3 and A4 must be "Peter"
  • values in cells A6 and A7 will must be "Paul"
  • values in cells A9 and A10 must be "Mary"

This can be done manually in our example. But suppose you have, let's say, 2,000 records in your table. In that case you can take advantage from the "Go To - Special" feature in Excel.


First select the whole relevant range (the column range, in our case A1:A10). Then press "Ctrl + G" or F5. You will get this:

Hit the "Special" button and mark "Blanks"

and hit "OK"

Now enter "=" (the equal sign) in cell A3 (the first blank cell in the selected range), , click cell A2 (the cell above the first blank cell), and hit "Ctrl + Enter". You will get this:

This technique can be useful in many other cases. For example, if you want to delete a whole line when some cell is blank (like in our example), you can select all the blank cells using F5, then press the right button in the mouse, from the pop-up menu select "Delete --> Entire Row" and press "OK". All the rows with a blank cell in the selected range will be deleted.

No comments: