Home DIGITAL SKILLS How to Count Blank – Empty Cells in Microsoft Excel

How to Count Blank – Empty Cells in Microsoft Excel

529
63
SHARE
How To Count Blank - Empty Cells In Microsoft Excel How to Compare Two Lists in Microsoft Excel How to Add Numbers in Microsoft Excel If you need to get the sum of two or more numbers in your spreadsheets, Microsoft Excel has multiple options for addition. We’ll show you the available ways to add in Excel, including doing it without a formula.

How to Count Blank – Empty Cells in Microsoft Excel

The only thing worse than incorrect data in your spreadsheet is missing data.

If you want to count the number of blank or empty cells in Microsoft Excel, we’ll show you two quick and easy methods.

By using a function, you can keep the number of blank cells parked in your sheet. This way, if you change your data, that count will adjust.

If you prefer to simply see a fast count of empty cells, you can use Excel’s built-in Find feature. Let’s take a look at both.

Count Blank Cells Using a Function

The COUNT function in Microsoft Excel is handy for many scenarios. So with a variation of that function, you can count empty cells easily. The function is COUNTBLANK and here’s how to use it.

Note: While the COUNTBLANK function in Google Sheets ignores cells that are empty (containing an empty string, "" ), Excel’s version of the function does not make this distinction. Thus, COUNTBLANK will return a count of both blank and empty cells.

See also  Free Download Mushroom Cloud Gaming Emulator Latest Version

Select the cell where you want to insert the function.

This is the same cell that will display the count of blank cells. Type the following formula into the cell replacing the cell range with your own and hit Enter.

=COUNTBLANK(B2:F12)

You should then see the number of empty cells in the range you selected for the formula.

Use The Countblank Function In Excel

If you want to adjust the cell range, this is also simple. Click the cell containing the function, go up to the Formula Bar, and place your cursor within the cell range.

You can manually change the cell references in the range or drag in or out on the blue box. Then, press Enter.

Edit The Countblank Function In Excel

You can also combine the COUNTBLANK function with itself to count the number of blank cells in different cell ranges of the same workbook.

Type the following formula into the cell replacing the cell ranges with your own and press Enter.

=COUNTBLANK(B2:F12)+COUNTBLANK(J2:N12)

Notice the cell ranges for each set are outlined in a different color making them easy to edit if needed.

Combine Countblank For Different Cell Ranges

And you get the total count in one cell for both sets of cell ranges.

Combined Count For Different Cell Ranges

If you use colors to differentiate data, you may also find it useful to count colored cells in your spreadsheet.

See also  Best CPM Ad Networks For Bloggers To Monetize Blog

Count Blank Cells Using the Find Feature

If you would rather not keep a formula in your sheet, but merely see a quick count of blank cells, use the Find feature.

Select the cells that include the blanks you want to find. Go to the Home tab and click the Find & Select drop-down arrow in the Editing section of the ribbon. Choose “Find.”

Click Find And Replace And Pick Find

When the Find and Replace window opens, leave the Find What box blank. Then, click “Options” to expand the section at the bottom.

Click Options

Adjust the three drop-down boxes on the left side to use the following:

  • Within: Sheet
  • Search: By Rows or By Columns (per your preference)
  • Look In: Values

Adjust The Find Feature Options

When you’re ready, click “Find All.” You’ll then see the number of cells found on the bottom left of the window.

Blank Cells Found

You’ll also see a list of those empty cells in your sheet. You can click one to go directly to it or click “Find Next” to move to each of the results in the list one at a time.

Move To Each Blank Cell

Click “Close” when you finish.

 

63 COMMENTS

  1. I relish, result in I found exactly what I used to be taking a
    look for. You’ve ended my four day long hunt! God Bless you man. Have a great day.
    Bye

  2. always i used to read smaller articles or reviews that as well clear
    their motive, and that is also happening with this article which I am reading here.

  3. I truly love your website.. Very nice colors & theme. Did you make this website yourself?
    Please reply back as I’m planning to create my
    own blog and would love to find out where you got this from or just what the theme is named.
    Thanks!

LEAVE A REPLY

Please enter your comment!
Please enter your name here