If you click in an Excel cell that contains data and then press the Spacebar, it appears as though the data has been deleted and that the cell is empty. While it’s true that tapping the Spacebar does erase the contents of a cell, it leaves something behind: a blank space.
A blank space means that the cell is in fact, not empty. This can sometimes cause problems with your calculations so you’ll want to make sure you remove them.
The easiest way to do this is by using the Find and Replace dialog. You’ll want to replace the contents of any cells that contain a single blank space with nothing .
To remove a single space from your worksheet, follow these steps:
To Remove Blank Spaces from a Worksheet
- Press Ctrl + H to display the Find and Replace dialog box.
- Click the Options button.
- Click the checkbox next to Match Entire Cell Contents. THIS IS VERY IMPORTANT - if this checkbox is not checked, any cells containing spaces (such as those between words or in formulas for example) would be deleted.
- Click in the Find What box.
- Press the Spacebar once to add a single space.
- Click the Replace All button. Because we didn’t add anything in the Replace With box, all instances of a blank space are replaced with nothing or, in other words, removed.
- To ensure that you don’t have cells containing 2 blank spaces, repeat steps 1-6 above, except press the Spacebar twice in the Find What box.