Catalog

3 easy ways to count blank cells in Excel (Step-by-Step)

February 18, 2024 532 views

Assuming you're going about your data handling for data quality checks or any statistical analysis, you might want to count the blanks that are left behind. In this article, we will see how you can count blank cells in excel by utilizing Excel's built-in functions and techniques. By understanding these methods, you'll gain valuable insights into your data's completeness, enhancing your ability to perform accurate analyses and maintain data integrity. Whether you're managing large datasets or conducting routine checks, counting blank cells is a crucial step in ensuring the reliability of your data.

How to count blank cells in Excel

How to count blank cells in Excel

Method 1: Find and Replace in Excel to count blank cells

Simplify data analysis in Excel with this straightforward method to identify and count blank cells in a specific range. Enhance data accuracy and streamline your spreadsheet tasks, reducing the time spent on analysis. Customize the process using the Find and Replace dialog box according to your preferences. Let's break it down into simple steps.

Step 1: Launch Microsoft Excel and open the spreadsheet containing the data you want to analyze.

Count blank cells in Excel.

Count blank cells in Excel.

Step 2: Highlight the range of cells where you want to count blank cells. You can do this by clicking and dragging your cursor across the desired cells.

How to count blank cells in a range in Excel.

How to count blank cells in a range in Excel.

Step 3: Use the Hotkey “Ctrl + H” to open the Find and Replace dialog box.

Keyboard shortcut to find and replace blank cells in Excel

Keyboard shortcut to find and replace blank cells in Excel

Step 4: In the Find and Replace dialog box, switch to the "Replace" tab.

Find and replace blank cells in ExcelFind and replace blank cells in Excel

Find and replace blank cells in Excel

Step 5: In the "Find what" field, leave it blank. This tells Excel to find cells with no data.

Find cells with no data in Excel

Find cells with no data in Excel

Step 6: Click on "Options" if you want to customize your search. Ensure that "Look in" is set to "Values" or "Formulas" based on your preference.

Count blank cells in Excel with the condition

Count blank cells in Excel with the condition

Step 7: Click on the "Replace All" button. Excel will replace all blank cells with themselves, effectively counting them in the process.

 Replace all blank cells in Excel

Replace all blank cells in Excel

Step 8: Examine the dialog box summary to see how many replacements were made. This represents the count of blank cells in the selected range.

Find Blank cells in the selected range of Excel spreadsheet

Find Blank cells in the selected range of Excel spreadsheet

The instructions above will show you how to use Excel's Find and Replace function to quickly and effectively manage and analyze your data by counting the number of blank cells in a certain range.

Method 2: COUNTBLANK function for counting blank cells

Use the `COUNTBLANK` function in Excel to easily count blank cells in a range.

Step 1: Pick an empty cell for the result and insert the countblank function starting with a “=” sign. Here, we have selected cell E13.

=COUNTBLANK(range)

This formula instructs Excel to count the number of blank cells in the provided range.

Countblank function result cell in Excel

Countblank function result cell in Excel

Step 2: Now select the range of cells in which you want to count the blank cells. For example, if I have numbers in cells A1 to A13 and want to know how many cells are blank, I will select the range A1:A13. The formula now becomes:

=COUNTBLANK(A1:A13)

COUNTBLANK Formula to find blank cells in Excel

COUNTBLANK Formula to find blank cells in Excel

Step 3: After entering the range in the formula, press Enter. Excel will calculate the result.

How to use formula to count empty cells in Excel

How to use formula to count empty cells in Excel

This was a quick and easy way to count blank cells in Excel using the COUNTBLANK function. Depending on the needs and your unique data, change the formula's range.

Method 3: COUNTIF formula for counting empty cells

Utilize the `COUNTIF` function in Excel for counting cells based on specific conditions. Follow this concise guide for accurate results.

Step 1: Choose an empty cell and insert the countif function starting with a “=” sign. Here. I have selected cell E13.

=COUNTIF(Range, criteria)

This function directs Excel to tally cells that satisfy a criterion – in this case, counting the blank cells.

Excel Spreadsheet blank cells

Excel Spreadsheet blank cells

Step 2: Now select the range of cells in which you want to count the blank cells. For example, if I have numbers in cells A1 to A13 and want to know how many cells are blank, I will select the range A1:A13. To count empty cells, use an empty criteria (""). The formula now becomes:

=COUNTIF(A1:A13, "")

COUNTIF Formula to find blank cells in Excel

COUNTIF Formula to find blank cells in Excel

Step 3: After entering the formula, press Enter. Excel will calculate the result.

How to use formula to count empty cells in Excel

How to use formula to count empty cells in Excel

This is a simple and effective way to use the `COUNTIF` function to count empty cells in Excel. Modify the range in the formula based on your specific data set and requirements.

Best Free Alternative to Microsoft Excel - WPS Spreadsheet

WPS Spreadsheet stands out for its user-friendly design, providing a more convenient experience compared to Microsoft Excel. Its hallmark feature is seamless collaboration across platforms and compatibility with various operating systems. Offering essential MS Excel features at a more affordable price, WPS Spreadsheet ensures users find familiarity and ease of navigation. The software's frequent updates, responsive customer support, and intelligent features, like auto-filling blank cells, create a flexible and efficient spreadsheet application, making WPS Spreadsheet a reliable choice.

WPS Office

WPS Office

All three methods discussed above in the article apply to WPS Spreadsheets as well to find and count blank cells. We will discuss each method step by step below.

Trustpilotstars4.8
WPS Office- Free All-in-One Office Suite
  • Use Word, Excel, and PPT for FREE, No Ads.

  • Edit PDF files with the powerful PDF toolkit.

  • Microsoft-like interface. Easy to learn. 100% Compatibility.

  • Boost your productivity with WPS's abundant free Word, Excel, PPT, and CV templates.

5,820,008 User
avator
Algirdas Jasaitis
logo

FAQs

Q1: How to count non-blank cells in Excel?

Use the `COUNTA` function in Excel to count non-blank cells. The formula is: `=COUNTA(range)`, where "range" is the range of cells you want to count.

Q2: How to count blank rows in Excel?

To count blank rows in Excel, use the following formula “=COUNTIFS(A:A, "")”. Replace "A" with the column letter of the column you want to check for blank rows. This formula counts rows where the specified column is blank.

Master Excel, Embrace WPS for Easy Spreadsheets

As you navigate through the three easy methods of how to count blank cells in excel outlined in this guide, you'll empower yourself to efficiently manage and organize your spreadsheets. Whether you're a data enthusiast or a professional, having a comprehensive understanding of Excel functions like COUNTBLANK adds a powerful tool to your repertoire. These skills not only improve your spreadsheet efficiency but also contribute to overall productivity. And for an even more streamlined and efficient spreadsheet experience, consider exploring WPS Office, a great alternative with cross-platform compatibility. Download WPS Office Suite for free today!


15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.