Home > Structure > Worksheets > Blank Cells

Excel Blank Cells Not Blank

Debra Dalgleish - Contextures

Do you have worksheet cells that look blank, but the Excel COUNTA function counts them? See how to fix that problem, so the cells are really blank, and the formula counts are correct.

excel counts cells that look blank

1) Problem: Excel Cells Not Really Blank

In some Excel worksheets, some cells might look blank, but Excel formulas count them as containing data. Why does that happen?

In the video and notes below, I show how you can fix those "blank" cells, so your formulas and navigation shortcuts work correctly!

2) Video: Fix Cells That Look Blank

In this short video, I show how to fix cells that look blank, but aren't really blank. If you run into this problem frequently, I also show how to record and run a macro, that will help you fix those cell quickly.

Video Timeline

  • 0:00 COUNTA Function ignores blank cells
  • 0:34 Problem with blank cells
  • 1:14 Record macro while fixing cells
  • 2:55 Edit and test macro

3) Why Cells Look Blank

Here are 3 reasons that worksheet cells can look blank/empty:

  1. Not Blank: Cells contain a formula, or data that's visible in the formula bar. For example:
    • cell has a formula that returns an empty string
    • cell has a zero, and display zeros option is turned off
    • cell has a value hidden by formatting
  2. Not Blank: Cells contain a hidden empty string
  3. Blank: Cells are truly empty -- won't be counted by COUNTA function

3.1) Not Blank - Formula / Hidden Data

Some cells look empty, but if you click on the cell to select it, you can see a formula or value in the Formula Bar.

In the sections below, I've described 3 situations that cause this type of "empty" cells on a worksheet.

3.1.1) Empty String Formula

For example, in the screen shot below, the formulas in column C return an empty string (""), if the value in column B is not an odd number:

  • =IF(ISODD(B3),1,"")

In each row with an even number, the cell in column C looks empty.

However, when you click on the "empty" cell, the formula appears in the formula bar.

formula returns empty string

3.1.2) Display Zeros Turned Off

On some sheets, the Display Zeros setting might be turned off, in Excel Options.

If cells on that sheet contain a zero, or a formula that returns a zero, the cell will look empty. However, when you click on the "empty" cell, the cell contents appear in the formula bar.

formula returns empty string

3.1.3) Formatting Hides Values

For some cells, there could be cell, font or number formatting that hides the contents. For example, in the screen shot below:

  • In column C, there is a custom number format: 0;0;;@ (negative;zero;text). This format shows positive and negative numbers, but not zeros. positive;
  • And in column F, conditional formatting applies white font and white fill, if the cell contains a zero.

formula returns empty string

3.2) Not Blank - Hidden Empty String

Sometimes cells look blank, and you can't see anything in the formula bar. However, the COUNTA function still counts those "empty" cells!

  • These "empty" cells contain a hidden apostrophe, and that's why Excel counts them.

In the sections below, I show 2 situations that cause this type of "empty" cells on a worksheet:

  • Imported data
  • Excel data that was pasted as values

After that, I show:

  • How you can change an Excel setting, to see the hidden apostrophes
  • How you can fix the cells with hidden data, to make them really empty

3.2.1) Cause: Imported Data

If you import data to Excel, such as query results from Microsoft Access, some cells might look empty, but Excel treats them as containing empty strings.

  • This happens if the records in Access contained null values or zero-length strings.
Imported Data Example

In the example shown below, there is a small table, copied from Access, with 3 cells – that appear blank - C4, C5 and C7.

Excel Count empty strings

In the Access database, those cells might have had a zero length string or a null value.

  • If you click on one of the “blank” cells, nothing shows in the Formula Bar.
  • However, the COUNTA formula in cell E2 is counting those cells, as if they contained data.

The "empty" cells contain a hidden apostrophe, and that's why Excel counts them

3.2.2) Cause: Pasted Excel Data

In some cases, the problem "empty" cells are created within Excel, if cells are copied, and pasted as values.

The example below shows how this situation can occur.

Pasted Data Example - Part 1

First, follow the steps below, to create cells that look blank, because they contains formulas that return empty strings.

  • In column A, enter the numbers 1 to 10
  • In column B, enter this formula: =IF(ISODD(A2),1,””)
    • The formula checks the number in column A.
    • If it is odd, the IF function returns a 1.
    • If it is not odd, IF returns an empty string (“”)
  • The rows with 2, 4, 6, 8 and 10 look blank, because the formula returns an empty string (“”) in those cells

Excel Count empty strings

  • However, the formula bar shows the IF function formula if you click on any of those cells, so it’s easy to understand why the cells are not really blank.
Pasted Data Example - Part 2

Next, follow these steps to create the blank cells problem:

  • In the example that you created, select all the cells with formulas
  • Copy the cells, and then Paste As Values, in the same location

Now there is a blank cell problem:

  • Cells for 2, 4, 6, 8 and 10 look empty
  • Excel still counts them in the COUNTA formula
  • If you click on any of the empty string cells, the formula bar shows nothing.

So, you can create the same issue by pasting Excel formulas as values, if some of the cells contain an empty string

Excel Count empty strings

4) Show Blank Cell Hidden Contents

To see apostrophes in cells with hidden content, follow the steps below, to turn on an Excel option:

  • First, on the Excel Ribbon, click the File tab
  • At the left, scroll down, and click Options
  • In the Category list at the left, click Advanced
  • Scroll down to the end of the Advanced options, and look for the Lotus Compatibility section
  • Add a check mark to Transition Navigation Keys

Excel Count empty strings

  • Finally, click the OK button, to close the Options window

To test the setting change:

  • Select a cell that looks blank, and then check the Formula bar
  • You should see an apostrophe there.

Excel Count empty strings

5) Fix Blank Cells - 3 Ways

In the sections below, there are 3 ways you can fix cells that look blank, but aren't really blank. All 3 solutions are quick and easy, so use the method that you prefer.

I usually use the Text to Columns method.

5.1) Fix Blank Excel Cells – Text to Columns

Here's a quick way to fix cells that look blank, but aren't. This was suggested by Ed Ferraro, in my Contextures blog comments.

Follow the steps below, to fix the problem with Excel's Text to Columns feature:

  • First, select the range of cells that you want to fix
  • Next, on the Excel Ribbon, click the Data tab
  • Click the Text to Columns command
  • In Step 1, select Delimited, then click the Finish button

That removes the hidden content, and the cells are really empty.

5.2) Fix Blank Excel Cells – Filter

Here is another quick solution, posted by Gobish, in the Contextures blog comments.

  • In the column heading cell, click the arrow, to see the filter options
  • Remove the check mark from (Select All)
  • Add a check mark to (Blanks)
  • Click OK

Excel Count empty strings

  • Select all the visible blank cells, and press the Delete key to clear them
  • To remove the filter, click the arrow in the heading cell, and click the Clear Filter command

That technique also solves the problem, and the cells are really empty.

5.3) Fix Blank Excel Cells – Find and Replace

To fix the problem, so the “blank” cells are recognized as blank, you can use the Find and Replace command, in two steps.

    

Note: You can record a macro while you fix the cells, and run that macro later, if you encounter a similar problem. See my macro code, below.

Step 1 to Fix Blank Cells

  • Select all the cells
  • Press Ctrl + H to open the Find and Replace window
  • Leave the Find What box empty
  • In the Replace With box,type a string that is not in the data, such as “$$$$”
  • Click the Replace All button, to Replace all the blanks with $$$$

Excel Count empty strings

Step 2 to Fix Blank Cells

  • Next, put “$$$$” in the Find What box
  • Press the Tab key, to go the the Replace With box
  • With the $$$$ text selected, press the Delete key, to clear the Replace With box
  • Click the Replace All button, to replace all the $$$$ entries with nothing

This technique also solves the problem, and the cells are really empty.

Excel Count empty strings

In addition to the COUNTA problem, there is a navigation problem with these cells that look empty.

  • Select the first cell in the column with the blank cells
  • Press the End key, and then the Down arrow
  • Normally, Excel goes down to the last cell that contains text
  • In this case though, Excel “sees” something in all the cells, and goes to the last cell in our list (C8)

7) Macro: Fix Blank Cells

Here is an Excel macro that fixes the blank cells, by using the Find and Replace command. It works on the selected cells in the active worksheet.

Store the macro code on a regular worksheet module.

Sub FixBlankCells()
' Make blank cells from database really blank
    With Selection
        .Replace What:="", _
            Replacement:="$", _
            LookAt:=xlPart, _
            SearchOrder:=xlByRows, _
            MatchCase:=False, _
            SearchFormat:=False, _
            ReplaceFormat:=False
        .Replace What:="$", _
            Replacement:="", _
            LookAt:=xlPart, _
            SearchOrder:=xlByRows, _
            MatchCase:=False, _
            SearchFormat:=False, _
            ReplaceFormat:=False
    End With
End Sub

8) Download the Sample File

To work with the sample data, and run the macro, you can download the Fix Blank Cells in Excel sample workbook. The file is in xlsm format, zipped, and contains macros. You’ll have to enable macros if you want to test the Fix Blank Cells code in the sample file.

More Ways to Count in Excel

7 Ways to Count in Excel

Count If Greater Than

Count Filtered Rows

Count in Date Range

Count with Criteria

 

 

Last updated: May 23, 2024 3:44 PM