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.
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.
- 10 advanced formatting tricks for Excel users
- Three tips for using Excel's conditional formatting more efficiently
- Three easy ways to use color in an Excel worksheet
Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.