Color is a great visual tool in most any sheet. That’s why so many of Excel’s built-in features include color. But besides the instant visual interpretation, you might want something more functional. For instance, you might want the sum of the values that share a color. Or you might want a count by colors. In this article, I’ll show you a simple way to evaluate values by the cell’s fill color using Excel’s built-in filtering feature.
I’m using Excel 2016 (desktop) on a Windows 10 64-bit system, but filtering is available in older versions. You can work with your own data or download the demonstration .xlsx file. The browser version doesn’t support filtering by color.
Apply fill colors
You could apply a few colors to a data set manually, but let’s use a built-in conditional formatting set for this example using a basic data set:
- Select B2:B18–these are the values we’ll be evaluating.
- From the Conditional Formatting dropdown (in the Styles group on the Home tab), choose Color Scales and then choose the first option (Figure A).
That’s it! With just a few clicks, we added several colors to the data set. That’s why I chose a built-in format rather than applying the colors manually.
Figure A
A conditional scale set applies different colors to the invoice values in column B.
Create a Table object
The data set now has several colors, but as is, they’re not much help for counting or totaling the invoice values. What you might not know is that the ability to sort a Table by color is built in. For that reason, we’ll convert the data range to a Table. To do so, click anywhere inside the data. Then, click the Insert tab and then click Table in the Tables group. In the resulting dialog, check the My Table Has Headers option and click OK. At this point, we have a Table, and the invoice values have a conditional format.
SEE: Securing Windows policy (Tech Pro Research)
Add Total row
As is, we can filter by color–but now we need a way to evaluate the values visible after applying a color filter. For that purpose, let’s add a totaling row to the bottom of the Table. To do so, check Total Row in the Table Styles Options group on the contextual Design tab, as shown in Figure B. By default, this row adds a SUBTOTAL() function to the Invoice Date field. Delete the function in C19. Then, select B19 and click AutoSum in the Editing group on the Home tab to enter a function that evaluates that column. If necessary, apply the Currency format to the same cell.
Figure B
Enable the Table object’s totaling row.
You can see the results in Figure C: The total of the Invoice Amount column is $8,913. AutoSum is smart enough to know you need a SUBTOTAL() function and not a SUM() function.
Figure C
AutoSum adds a SUBTOTAL() function.
You might be wondering about the difference between SUM() and SUBTOTAL(). A SUM() function evaluates all the values in the Invoice Amount, regardless of filtering. SUBTOTAL() can evaluate all the values or only the visible values. This will make sense momentarily.
SEE: Microsoft Office Certification Training Bundle (TechRepublic Academy)
Start filtering
Now you’re ready to filter by a color and see how the totaling row responds. To apply a simple filter, click the Filter dropdown for the Invoice Amount column. Next, select Filter By Color and select yellow (Figure D). This submenu shows only a few of the colors; to see the rest, choose More Cell Colors.
Figure D
Choose a color filter.
Figure E shows the resulting filter. The SUBTOTAL() function in the Total row evaluates only the visible records. A SUM() function would continue to evaluate all the records whereas the SUBTOTAL() function evaluates only the visible values after filtering.
Figure E
The SUBTOTAL() function returns the total of only the visible records.
The built-in Color Scales conditional format updates as you modify the data set. The filter colors update as well, with no additional work on your part. It all just works!
If you want a count of the colored cells, modify the SUBTOTAL() function by changing the 109 argument to 102. Table A shows the options for this argument.
Table A
| Evaluates hidden values | Ignores hidden values | Function |
| 1 | 101 | AVERAGE() |
| 2 | 102 | COUNT() |
| 3 | 103 | COUNTA() |
| 4 | 104 | MAX() |
| 5 | 105 | MIN() |
| 6 | 106 | PRODUCT() |
| 7 | 107 | STDEV() |
| 8 | 108 | STDEVP() |
| 9 | 109 | SUM() |
| 10 | 110 | VAR() |
| 11 | 111 | VARP() |
If you’re using a menu version that doesn’t support the Table object, you can enter the appropriate SUBTOTAL() function a few rows below the corresponding column and then filter as you normally would. Upkeep is awkward if you’re adding and deleting records, because you must accommodate the totaling row, whereas the Table object does that for you.
Coming up
This simple filtering by color solution works great, but it won’t always be adequate. Stay tuned for an article that uses a VBA user-defined function to accomplish the same thing under different circumstances.
Send me your question about Office
I answer readers’ questions when I can, but there’s no guarantee. Don’t send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, “Please troubleshoot my workbook and fix what’s wrong” probably won’t get a response, but “Can you tell me why this formula isn’t returning the expected results?” might. Please mention the app and version that you’re using. I’m not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at susansalesharkins@gmail.com.
