Intermediate conditional formatting

In the course, Use conditional formatting, we covered the basics of conditional formatting.

Conditional formatting provides visual cues to help you make sense of your data.

For example, it’ll clearly show highs and lows, or other data trends based on the criteria you specify.

See the course summary at the end of this course for a link.

In this course, we’ll go over how to get that much more out of conditional formatting.

You can apply it to specific cells, tables, PivotTables, and worksheets.

For example, to quickly conditionally format these selected cells, you can use a Quick Analysis option or an option on the Conditional Formatting button in the ribbon.

But if you want more control over what type of, and when conditional formatting applies, click New Rule, select a Rule Type (such as Format all cells based on their values), select a Format Style (such as Icon Sets), select the Icon Style that you want (icon sets range from 3 to 5 icons and numerous icon styles).

I am going to leave it set to the default.

For the green icon, I set the Type of value to Number.

When I do this the Value is set to zero, overriding the previous value, which is why I do it first.

I’ll leave the operator set to greater than or equal to and set the Value to 1.

For the yellow icon, I set the Value to .5, and the red icon is automatically set to less than .5.

I click OK, and the values in the Profit column are conditionally formatted by the new rule we just created.

And I can see at a glance that Dried Pears, Walnuts and Cottonseed Oil have the highest profits.

You can also format cells that are blank or contain errors.

This time, select Format only cells that contain, select Blanks or Errors, choose the formatting you want (the options and combinations are virtually endless; I am going to fill cells that contain errors), click OK, click OK again, and the cell with an error is formatted.

For information about editing and deleting rules, see the final video of this course.

Up next, Use formulas to apply conditional formatting.