3 ways to count empty cells in Excel

If your task is to get Excel count blank cells in your worksheet, read this article to find 3 ways to accomplish it. Learn how to search and select empty cells with the Go to Special option, use Find and Replace to count blanks or enter a formula in Excel 2010-2013.

In my previous post How to count non-empty cells in Excel, I showed 3 ways to get the number of filled cells in a range. Today, you'll learn how find and count blanks in your table.

Suppose you supply goods to multiple stores. You have a worksheet in Excel with the shops' names and the quantity of items they sold. Some cells in the Items sold column are empty.
Count blank cells in excel 2010-2013

You need to make Excel count blank cells in your sheet or find and select them to see how many stores didn't provide the necessary details. Doing it manually would take too much time, so feel free to use one of the options I show in this post:

How to find empty cells in Excel with Go to Special

If you need to quickly find all blank cells in Excel, use the Go to Special functionality. Though it will not show you the number of blanks, the tool will quickly highlight all empty cells so that you can easily locate them in your sheet.

  1. Select a range with blank cells in your table and press F5 on the keyboard to get the Go To dialog box.
    Press F5 on your keyboard to get the Go To dialog box
  2. Then press the Alt + S hotkey to get the Go To Special window. On this window, select the Blanks radio button.
    Select the Blanks radio button on the Go To Special window
  3. Click Ok to see the empty cells found and highlighted in your table.
    See the empty cells highlighted in your table
Tip. You can use the Fill Color option under the Home tab to change the background of empty cells and keep the selection.
Change the background of empty cells to keep the selection

Please note that Go to Special will not find pseudo-blank cells with spaces or those with formulas that return blanks.

Excel - count blank cells using Find and Replace functionality

You can use the standard Excel Find and Replace dialog to count empty cells in your table. This tool will display the list with all blanks next to their addresses in your sheet. It also lets you navigate to any empty cell by clicking on its link in the list.

  1. Select the range where you need to count blank cells and press the Ctrl + F hotkey.
    Note. If you select one cell Find and Replace will search the entire table.
  2. Leave the Find what field empty.
    Leave the Find what field empty
  3. Press Options and select the Match entire cell contents checkbox.
    Select the Match entire cell contents checkbox
  4. Pick Formulas or Values from the Look in: drop-down list.
    • If you choose to find Values, the tool will count all empty cells including the pseudo-blank ones.
    • Select the Formulas option to search for empty cells only. You will not get cells with blank formulas or spaces.

    Pick the Values or Formulas item from the Look in: drop-down list

  5. Press the Find All button to see the results. You will get the number of blanks in the bottom-left corner.
    Get the number of blanks on the Find and Replace window
Tip. If you select the results on the add-in pane, it's possible to fill the empty cells with the same value, like 0 or the words "no information". To learn more, please check the article Fill empty cells with 0 or another specific value.

Use a special formula for counting blank cells

This part is for the formula-oriented users. Though you will not see the found items highlighted, it's possible to get the number of blanks in any cell you select to compare to the next search.

  • The formula =countblanks() will show you the number of empty cells, including the pseudo-blank ones.
  • If you enter the formula =ROWS()*COLUMNS()-COUNTA(), you'll get all truly empty cells. No values, no blank formulas.

Follow the steps below to apply them:

  1. Select any empty cell in your sheet.
  2. Enter =countblanks() or =ROWS() * COLUMNS() - COUNTA() into the formula bar.
  3. Then you can enter the range address between the brackets in your formula. Or place the mouse cursor between the brackets and manually select the necessary cell range in your sheet. You will see the address automatically appear in the formula.
  4. Press Enter
    You will get the result in the selected cell.

    On the below picture, I show the summary of how these 2 formulas work with constants and pseudo-blank cells. In my sample, I have 4 cells selected. A2 has a value, A3 has a formula that returns an empty string, A4 is empty and A5 contains two spaces. Below the range, you can see the number of the found cells next to the formula I employed.
    See how 2 different formulas work with constants and pseudo-blank cells

    You can also use the COUNTIF formula for counting empty cells in Excel, please check out this tutorial for full details - COUNTIF for blanks and non-blanks.

    Now you know how to find and count blank cells in your Excel table. Use a formula to paste the number of empty cells, turn on Find and Replace to highlight blanks, navigate to them and see their number, or choose the Go To Special feature to quickly select all blank ranges in your table. Feel free to share any other hints you may have. Be happy and excel in Excel!

4 Responses to "3 ways to count empty cells in Excel"

  1. chandra singh says:

    Hi,
    My question is how to count only text if we have given number and space and blank also are given.
    Example-
    1, 2,g,5,space,blank,kd,23
    now please count only alpha (text).

  2. Leigh Gregory-Bateman says:

    I found this article very useful as it solved an irritating problem for me.

    Many thanks,

    Leigh

  3. Kamal says:

    tks the solution

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Excel add-ins and Outlook tools - Ablebits.com
Ultimate Suite for Excel Professionals
 
 
60+ professional tools for Excel 2016-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard