Jun
18

How to count non-empty cells in Excel 2010, 2013

This articles looks at the ways to count non-blank cells in Excel 2010-2013. Below you'll find 3 methods for counting non-blanks: see the number on the Excel Status bar, employ the Find and Replace dialog or use a special formula.

Your table may have numerous blank cells left for better visualization. On the one hand, such layout is really convenient. On the other hand, it may prevent from seeing the correct number of data rows. You may need to count all non-empty cells in Excel to see how many products are sold, or how many people take part in a conference.

Note. If a cell contains a formula that returns space between quotes (""), it's not seen as empty. I will refer to them as to blank formulas in this article.

Below you'll find 3 possibilities in Excel for counting non-blank cells.

Count option on the Excel Status bar

Excel Status bar shows a number of tools you may find helpful. Here you can see page layouts, zoom slider and basic math functions displayed for numerical values.

To see how many selected cells contain data, just look at the COUNT option on the Status bar.

Note. This option will not work if you have only one filled cell in the selected range.

See the number of selected cells that contain data on the Excel Status bar

Excel - count non-blank cells with the Find and Replace option

It's also possible to count non-empty cells with the help of the standard Excel Find and Replace dialog. This method is good if you have a large table. You'll get all values displayed on one window along with their cell addresses. In addition, you can easily navigate to any item by clicking on its name in the list.

  1. Select the range where you need to count the non-blanks and press the Ctrl + F hotkey.
  2. You will see the Find and Replace dialog box. Enter the asterisk symbol ( * ) in the Find what field.
    Enter the asterisk symbol in the Find what field
  3. Press the Options button and pick the Values or Formulas item from the Look in: drop-down list.
    • If you select Values, the tool will count all filled cells and ignore blank formulas.
    • When you pick Formulas, Find and Replace shows all cells that have values and any formulas.

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

  4. Click Find All to see the results. You'll get all the found items and their quantity on the pane.
    Get all the found items and their quantity on the add-in pane
Tip. You can now select all found items on the Find and Replace pane. You'll see all non-blank cells highlighted and it will stay after you close the window.
See the found items highlighted

Use a special Excel formula to count all non-blank cells

The third way to calculate the number of non-empty cells is to use an Excel formula. Though you will not see where the cells are, this option helps you choose what kinds of filled cells you want to count.

If you need to count all filled cells, constants, formulas, cells with spaces, you should use the formula =COUNTA().

To get the number of cells with constants and those including spaces, enter

=ROWS(L8:L11) * COLUMNS(L8:L11)-COUNTBLANK(L8:L11)

Follow these steps to apply the formulas:

  1. Select any empty cell in your sheet.
  2. Enter =counta() or =ROWS() * COLUMNS()-COUNTBLANK() to the formula bar.
  3. Then you can manually enter the range address between the brackets in your formula. Or place the mouse cursor between the brackets and highlight the necessary cell range in your table. You will see the address automatically appear in the formula.
    Place the mouse cursor between the brackets and highlight the range in your table

    With the formula =ROWS() * COLUMNS()-COUNTBLANK() you need to enter the range address 3 times.
    Enter the range address 3 times

  4. Press Enter on your keyboard.

You will see the result in the selected cell.

If you want to count only constants without cells with extra spaces, use =SUM(--(LEN(TRIM(range))>0)) Please note, this is an array formula that needs to be entered with CTR + Shift + Enter.

  1. Select any empty cell in your sheet.
  2. Enter =SUM(--(LEN(TRIM())>0)) in the formula bar.
  3. Place your mouse cursor between the brackets and select the range in your table. You will see the range address appear in the formula.
    Enter the range address 3 times
  4. Press Ctrl + Shift + Enter to see the number in the selected cell.

On the screenshot below, you can see a brief summary showing how these 3 formulas work with constants, blank formulas and extra spaces. In the test table I have a range with 4 cells selected. A2 contains a value, A3 has a formula that returns an empty string, A4 is empty and A5 has two spaces entered. Under the range, you can see the number of the found cells next to the formula I used to find them.
See how 3 different formulas work with constants, blank formulas and extra spaces

Another way to count non-blanks in Excel is using the COUNTIF formula =COUNTIF(range,"<>"&""). You will find the full details in this tutorial  - COUNTIF for non-blanks.

Now three ways of counting non-blank cells in Excel are at your disposal. Just pick the one that suites you best. It can be the Status bar, Find and Replace or a formula. Be happy and excel in Excel!

One Response to "How to count non-empty cells in Excel 2010, 2013"

  1. Tony Jest says:

    A really great explanation thanks.
    It does a wonderful job of detailing the difference between a formula that returns a blank and an empty cell.

    The next level of refinement would be to have a formula that counts non blank visible cells.

    I have a table that I use drop down filters on.
    I only want to count the visible non blank cells.
    Do you have any suggestions please?

    Subtotal and Aggregate functions are the first obvious choice, but they get confused with formulas that return blanks.

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