The Case of the Mysterious Blank Cells
One of our consulting clients approached us recently with a very large and slow Excel file. While performing our initial tests for file bloat, we came across something curious.
The file contained a series of cells which at first glance appeared to be empty. But they weren’t acting like normal empty cells.
The formula bar showed no contents, but navigating with CTRL + Arrow Keys treated them as non-blank cells.
In Figure 1 below, our tests revealed that Excel assumed that these cells contained an empty string (“”). How is this possible if the formula bar is empty?
For example, it is common practice to “blank out” data when creating a dynamic subset of a list. Consider the following formula:
In this case, if the condition is not met, Excel returns an empty string which will appear as a blank cell.
A user may unknowingly copy the range of data including the blank cells and “paste as values”. When an empty string is “pasted as values”, it does not keep the [“”], but rather appears as a non-blank, empty cell.
Now that we know how these strange cells are created, how do we locate and remove them? The most dynamic method would be to build a looping macro using VBA which checks whether each cell in the worksheet is non-blank and has the value of an empty string.
However, if you are unfamiliar with VBA, you can utilize the “Go To Special” menu instead. As illustrated in Figure 2 below, the “Go To Special” menu [F5, Alt + S] locates and selects special areas in a file. Select ‘Constants’ to search for constants.
Excel will highlight all cells containing constants (i.e., non-formulas) as shown in Figure 3. You will then need to manually fix each item.
We find the fastest way is to paste over them with a regular blank cell. However, they can also be removed by selecting the cell, pressing F2 to edit the cell, and pressing Enter. This resets the cell to a regular empty cell.
These mysterious cells typically won’t break a model, but they are a nuisance and should be removed if possible.