How to count non-empty cells in Excel

We often add blank cells in our Excel data to make it look more appealing. Though it helps in better visualization but at the same time, it stops the user from seeing the exact number of data rows. For example, if you want to calculate the total number of people who participated in a contest counting blank rows may mislead the data; therefore, it is necessary to skip the empty cells and count only the non-empty cells in Excel.

In this tutorial, we will cover interesting ways to count non-blank cells in all Excel versions (from Excel 365 to Excel 2013.) Below given are the different methods using which you can easily count non-blank cells in Excel:

1. Using the count option on the Excel status bar
2. Count non-blank cells using Excel Find and Replace tool
3. Customised Formula to count all non-empty cells

Let's get it started!

Using the count option on the Excel status bar

Status Bar is an inbuilt Excel feature that helps users easily look at the page layouts, zoom sliders, basic formulas, and various other tools. One such option present on the status bar is the Count option.

To see how many selected cells contain data (overlooking the non-empty cells), select the entire row and check the COUNT option on the Status bar.

Note. The Count option on the status bar will only work if the selected row contains at least one filled data cell, and it won't give you results for all empty cells in the selected range.

Below given are the steps to count non-empty cells using the count option:

1. Select the data row for which you want to calculate the count of non-empty cells.
2. Once selected, check the count option on the status bar.

Refer to the below screenshot

Two simple steps and you are done! Wasn't that easy. Let's see a different method if you have large data table in Excel.

Count non-blank cells using Excel Find and Replace tool

We often use the Find and Replace tool to search for any particular data in our Excel worksheet. But this tool is not limited to this feature, as you can utilize this tool to count non-empty cells as well. The Find and Replace tool is useful if you have a large data table, as it displays all the resulting values on one window along with their cell addresses. In addition, this tool allows the users to easily navigate to any value by simply pointing the cursor on its name in the data list.

Below given are the steps to count non-blank cells using Excel Find and Replace tool:

1. Select the data range for which you want to count the non-empty cells. Once done press the Ctrl + F shortcut key.
2. It will quickly open the Find and Replace dialog window. In the Find what field put the asterisk symbol ( * ).
   
3. Click on the Options button and choose the Values or Formulas item from the Look in: drop-down window. You will have the following options:

• Values: If you pick the Values option from the list, the Find and Replace will count only those cells that contains values, therefore ignoring the blank formulas.
• Formulas: If you select the Formulas options from the list, this toll will display all the cells that contain values and have any embedded formulas.

1. Once done, click on the Find All option to fetch the output. It will return all the found values and their quantity on the window pane.

Note: The Find and Replace pane allows the user to select all found values. In addition, you'll see all non-blank cells as well highlighted in the window pane and those items will stay even after you close the Find and Replace window.

Formulas are an in interesting way to count non-empty cells. You can customise different functions and incorporate them in a single formula to attain the required output. Down the tutorial will see how to get it done!

Customised Formula to count all non-empty cells

The last method to calculate the number of non-blank cells is to use an Excel formula. Since we are using formulas, this method won't show where the cells are, but it aids the users in choosing what type of filled cells they want to count.

There are three formulas that you can implement in your excel worksheet to count all non-empty cells. You can incorporate any of the below formulas as per your requirement:

Formula 1: Using COUNTA () function

This function helps the user to count all filled cells, formulas, cells containing spaces. Following are the steps to use COUNTA() function in Excel:

1. Put your cursor on any empty cell in your Excel worksheet.
2. Enter the formula =COUNTA()to the formula bar.
3. In each function between the brackets, manually enter the data range for which you want to fetch the number of cells with constants and spaces. Or you can place the mouse cursor between the brackets and highlight the necessary cell range in your table, and it will automatically display the address in the respective formula.
   The formula will become: COUNTA(B2:B10)
   
4. Press the Enter button on your keyboard.

The above formula will return the following output in the selected cell.

Formula 2: Combining ROWS(), COLUMNS() & COUNTBLANK() functions

You can use a combination of different functions to fetch the number of cells with constants and spaces.

Following are the steps to count non-blank cells using a combination of Excel Formulas:

1. Put your cursor on any empty cell in your Excel worksheet.
2. Enter the formula =ROWS() * COLUMNS()-COUNTBLANK() to the formula bar.
3. In each function between the brackets, manually enter the data range for which you want to fetch the number of cells with constants and spaces. Or you can place the mouse cursor between the brackets and highlight the necessary cell range in your table, and it will automatically display the address in the respective formula.

The formula will become: =ROWS (C3:C19) * COLUMNS (C3:C19)-COUNTBLANK (C3:C19)

1. Once done, press the Enter key.
2. The above formula will return the following output in the selected cell.

Formula 3: Using TRIM () function

You can implement the TRIM () function in your Excel worksheet if you only want to count all the constants values with extra spaces.

Note: Since its an array formula, therefore it won't work with the help of simple enter, unlike other array formulas you need to press the CTR + Shift + Enter buttons together.

1. Put your cursor on any empty cell in your Excel worksheet.
2. Type the following combination formulas in the selected cell.
   Formula applied =SUM(--(LEN(TRIM())>0)
3. Next, we will add the data range in the TRIM (). To do so, place your cursor between the TRIM () brackets and enter the data range for which you want to count all the constant values with extra spaces.
   Formula Becomes: =SUM(--(LEN(TRIM(C3: C19))>0)
   
4. Once done, press the Ctrl + Shift + Enter to see the number in the selected cell.
5. As a result, it has returned 6. Because some of the rows are empty, some contains blank formulas and one has some extra spaces.

NOTE: Another interesting method to count non-blanks in Microsoft Excel is by using the COUNTIF formula =COUNTIF (range,"<>"&"").

Points to Remember:

1. If your Excel cell contains a data formula that returns space between double quotes ("") as , it's not seen as empty.
2. Sometimes the COUNT feature is not present in the status bar. In such cases you have to manually incorporate the same.

Eureka! In your Excel worksheet, we have covered all the methods to count the non-blank cells. All you need to do is pick any method that suits your requirement. It can either be the Status bar method, the Find and Replace tool, or the formula method.