Editor’s Note: This article was originally published in Aug. 2013 and the video tutorial for this article published Dec. 2018; while this program might look a little different, the steps shown in this tutorial are the same.
Conditional formatting is a popular feature and it’s powerful when combined with other features. For instance, you can’t easily sum values in a range based on a format. However, you could accomplish this using VBA. But you don’t need code; you just need a little flexibility. Today, I’ll show you how to combine Excel’s filtering feature with a function to sum formatted cells. For most users, implementing this solution will take only a few minutes!
For your convenience, a demo Excel worksheet you can use to test this Office tip is provided.
Conditional formatting setup
We’ll apply a conditional format and a filter to a single column of values. Then, we’ll add a function that updates as you filter the data by the conditional format. It’s much simpler than it sounds and doesn’t require code! This is strictly a ribbon solution – there’s no counterpart for Excel 2003 and earlier versions.
The sheet shown below uses a conditional format to highlight any value greater than 4. To set this rule, do the following:
- Select the data range, A2:A7.
- On the Home tab, click Conditional Formatting in the Styles group.
- Choose Highlight Cells Rules and then choose Greater Than.
- In the resulting dialog, enter 4. At this point, you could choose Custom Format from the With control’s dropdown. Using the default simplifies the example.
- Click OK.
To filter by the conditional format, you’ll need to add a filter to the data range as follows:
- Select the data and header cell, A1:A7.
- On the Data tab, click Filter in the Sort & Filter group. Excel adds the filter automatically.
Before we experiment with the filtering, use any method you like to add a SUM() function to A9. You need to skip a row so Excel won’t interpret the function cell as data and filter it later.
Filters
Now, let’s filter the data by the conditional format and see what happens to the SUM() function:
- From the filter dropdown, select Filter By Color.
- In the resulting submenu, choose either cell or font color.
Filtering by the conditional format was easy, but the SUM() function doesn’t reflect the data that you see. If you want a stable value that reflects the entire data set, you’re done. If you want a function that evaluates only the visible data, you need to change the function. Replace the SUM() function as follows:
- First, clear the filter by choosing Select All from the dropdown or clicking Clear on the Data tab (in the Sort & Filter group).
- Select A9 and replace the SUM() function with the following function:
After entering the SUBTOTAL() function, reapply the filter. This time, the function displays the sum of only the visible values. The function’s first argument, 9, denotes the function’s mathematical operation (sum) and SUBTOTAL() ignores all values hidden by a filter. How convenient.
Also see
- How to add a drop-down list to an Excel cell (TechRepublic)
- 10 advanced formatting tricks for Excel users (TechRepublic)
- Three tips for using Excel’s conditional formatting more efficiently (TechRepublic)
- Three easy ways to use color in an Excel worksheet (TechRepublic)
Affiliate disclosure: TechRepublic may earn a commission from the products and services featured on this page.
