Microsoft Office Tutorials and References
In Depth Information
Chapter 4: Chartless Visualization Techniques
Use a Formula to Determine Which Cells to Format: Evaluates values based on a
formula you specify. If a particular value evaluates to true, then the conditional formatting is
applied to that cell.
This selection is typically used when applying conditions based the results of an
advanced formula or mathematical operation.
You can use Data Bars, Color Scales, and icon sets only with the Format All Cells Based
on Their Values rule.
2. Ensure that the Format All Cells Based on Their Values rule is selected; then use the Format
Style drop-down menu to switch to icon sets.
3. Click the Icon Style drop-down menu to select your desired icon set.
Figure 4-16: Select the Format All Cells Based on Their Values rule; then use the Format Style
dropdown menu to switch to icon sets.
4. In the Type drop-down boxes, change both types to Formula.
5. In each Value box, enter =Average($C$2:$C$22) .
This tells Excel that the value in each cell must be greater than the average of the entire
dataset in order to get the Check icon.
At this point, your dialog box will look similar to the one in Figure 4-17.
6. Click OK to apply your conditional formatting.
It’s worth taking some time to understand how this conditional formatting rule works. Excel will
assess every cell in your target range to see if its contents match the logic in each Value box in order
(top box first):
➤ If a cell contains a number or text that evaluates true to the first Value box, the first icon is
applied, and Excel moves on to the next cell in your range.
➤ If not, Excel continues down each Value box until one of them evaluates to true.
➤ If the cell being assessed doesn’t fit any of the logic placed in the Value boxes, Excel
automatically tags that cell with the last icon.