How to count non-empty cells in ExcelWe 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:
Let's get it started! Using the count option on the Excel status barStatus 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:
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 toolWe 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:
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 cellsThe 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:
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:
The formula will become: =ROWS (C3:C19) * COLUMNS (C3:C19)-COUNTBLANK (C3:C19)
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.
NOTE: Another interesting method to count non-blanks in Microsoft Excel is by using the COUNTIF formula =COUNTIF (range,"<>"&"").Points to Remember:
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. |