Highlighting important information in an Excel report is very helpful for the end user to read the data and make decisions. However, what if you want to count how many cells have a certain color?
Excel does not have a built-in function for this, but there is a way to do it with a custom function or use combination of Table feature and SUBTOTAL function.
In real-world scenarios, you can see the color-coded cells for Tracking inventory, Monitoring project progress, and Analyzing survey results. In this article, you will learn two different methods how to count cells by color in Excel.
Use Excel Table Feature to count colored cells in Excel
First, you have to convert your data range to a table and then apply the filter to the Data range in this method.
Steps to count colored cells
- Select the range of cells, Go to Insert Tab, and Press the Table command to convert the range to a Table.
- Once it is converted into a table, you can see the Table Design Tab in the Ribbon. Select TotalRow in the Table style options.
- Click the small drop-down arrow in cell C1. Choose Grade A in the list item and then Press OK. You can see the count in Cell C12.
Use SUBTOTAL function to count colored cells in Excel
You can use the Excel Subtotal function to count colored cells in Excel. It is an alternate method to count the cells.
You have a list of other options in the SUBTOTAL function such as SUM, MAX, MIN, COUNTA, etc.,
Go to cell C15, type the formula =SUBTOTAL(3, C2:C11) and Press Enter.
You can see the count of Grade A in Cell C15.
Conclusion
You learned two easy methods to count colored cells in Excel using Table and Subtotal functions. The SUBTOTAL function can help you to Sum, Max, Min, and many other functions in the visible cells.
I hope this blog post has helped you to understand how to convert the range to Excel and count colored cells in Excel. If you have any questions or feedback, please leave a comment below.
