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.

In my previous post on 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 2016-2010

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:

Count blank cells using Excel's Find and Replace

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

Tips:

Excel 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 COUNTBLANK function will show you the number of empty cells, including the pseudo-blank ones.
  • With the ROWS COLUMNS COUNTA formula, 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 one of the below formulas into the formula bar.

    =COUNTBLANK(A2:A5)

    or

    =ROWS(A2:A5) * COLUMNS(A2:A5) - COUNTA(A2:A5)

  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 the Enter key.

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!

Table of contents

28 comments

  1. Hello,
    Thanks for simplifying the task. Appreciate it
    I have a scenario where I need to count number of blank cells across a data range for a specific value
    Column A has names of people, Columb B has a score or is blank.
    I would like to excel to count the number of blank cells for each of the values in column A
    Column A:
    John
    Mike
    Cindy
    Edith
    Column B:
    50
    100
    blank
    80

    Formula should give the result that Cindy has 1 (empty cell) to her name

    Thank you.

  2. Hey, I would like to get the formula for this:
    In My column A i have certain inputs say-
    A
    1

    2

    3

    4

    i want the count of the number of blank cells below my input i.e no of blank cells below 1,2,3,4 and so on.. in Column B

    Many Thanks

    • Hi! Have you tried the methods described in this blog post? If you are not satisfied, please let me know and I will try to help you.

      • I have tried the method below my comment, but its not giving me count of blank cells below my input cell but instead gives count of blank cells above my input cell.

        • Hi! To count the number of empty cells to the next non-empty cell in column A, try to enter the following formula in cell B1 and then copy it down along the column:

          =MATCH(FALSE,ISBLANK($A2:A50),0)-1

          If this is not what you wanted, please describe the problem in more detail.

  3. Hello.... Is there any way to count the number of empty cells between rows in a colunm?

    T

    K

    L

    U

    empty cells between T - K ?
    empty cells between K - L ?
    empty cells between L - U ?
    ....

    Thank You!

    • Hi! Have you tried the ways described in this blog post? If they don’t work for you, then please describe your task in detail, I’ll try to suggest a solution.

      • Hello. Thank you for your reply. Yes I have looked. The closest thing is sort of like "How to count blank rows". But what I need to do is this: I have a very large column of data. Data is in columns A B C. However, there are 2 to 15 blank rows between each row of data. I need to know how many blank rows are between each row of data and I need to be able to save the results in a separate column.

        1 A: data B: data C: data
        2
        3
        4
        5 A: data B: data C: data
        6
        7
        8 A: data B: data C: data
        9....

          • It worked!!! Thank you very much for your time. Excellent web site!

  4. Hi
    I have client information(200 clients) with 7 columns .
    I just want to delete the empty rows where all the columns are empty or without any data except the client id
    How can I do this I tried Ctrl+G(but it shows all the empty cells)
    I just want to delete where all the other cells are empty except the client id and name created
    Thanks in advance

  5. Hi, would like to ask for help. I am trying to count blank cells where in some cells are merged. When I use Countblank formula, all merged cells are not count as 1. I wanted it to be counted as 1. Thank you!

    • Hi! Merging cells is only changing their format in Excel. Two merged cells are not replaced by one. Looks like this is not possible with the standard Excel options.

  6. Hi, I would like to get formula for the following:
    data are in columns A:Z, column A is to enter date in each row, columns B:Z contain random data in certain cells, mainly are empty. The formula should calculate only empty cells from last entry in each column (B:Z) referring to the last entry in column A, so how many empty cells are between last entry in column A and last entry in column B, and so on.
    Thanks and regards.
    Leos

    • Hi! You can find the row number where the last value in a column is located by using this formula:

      =MAX(ROW(A1:A1000)*NOT(ISBLANK(A1:A1000)))

  7. Hi!

    I need to have o formula when I have 3 column in excel whith diferen number, for example

    Colum A. Colum B. Colum C

    155000 180000 3000000

    For this column I have target with % for example

    For Column A is 2%

    For Column. B is 3%

    For Column C is 4%

    The limit per Column. A is 2 % for (1 - 179999)

    The limit for Column B is 3% for (180000 - 299999)

    The limit for Column. C is 4% for (300000 - 2000000)

    How can I do this with the formul?

  8. Thanks a lot

  9. thank's alot <3

  10. Thank you so much!!

  11. this is definitely very helpful for me...
    thanks so much..

  12. tks the solution

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

    Many thanks,

    Leigh

  14. 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).

    • to count only text you can use the formulae:

      countA-count

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)