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?

Figure 1

For example, it is common practice to “blank out” data when creating a dynamic subset of a list. Consider the following formula:

=IF(condition,value,””)

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.

Figure 2

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.

Figure 3

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.

If you have a file that needs review, please reach out to our Consulting team at consulting@marqueegroup.ca.