How to easily sum values by a cell’s background color in Excel

www.office.com/setup Blogs: Adding color creates a great visual effect, but did you know that you can evaluate values by color? Use this technique to turn color into valuable information.

www.office.com/setup Blogs: 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.

SEE: Build your Excel skills with these 10 power tips(TechRepublic PDF)

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:

  1. Select B2:B18—these are the values we’ll be evaluating.
  2. 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

excelcountcolorsa.jpg - www.office.com/setup
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

excelcountcolorsb.jpg - www.office.com/setup
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

excelcountcolorsc.jpg - www.office.com/setup
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

excelcountcolorsd.jpg - www.office.com/setup
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

excelcountcolorse.jpg - www.office.com/setup
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 109argument to 102Table 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.


Also published on Medium.

%d bloggers like this: