You can think of conditional formatting as automatic formatting that is triggered by conditions that you define. For example, you can use conditional formatting to automatically change the color of cells that contain values greater than or less than certain values. Conditional formatting is a great way to visually highlight important information in a worksheet. Let’s take a look. A common use case of conditional formatting is to highlight values in a set of data. Here we have a table that shows monthly sales figures for a group of sales people. The monthly sales quota is $5000, so let’s use conditional formatting to highlight monthly sales numbers that are below that value. Conditional formatting is applied with rules. First, select the cells you’d like to format. In our sales table, that’s all the monthly sales figures, excluding totals. Then, open the Conditional Formatting menu, which appears in the Styles group on the home tab of the ribbon. There are many conditional format presets in this menu. For this example, we’ll use a preset in the Highlight Cells Rules section. We’d like to highlight values below a certain threshold, so we’ll choose the “Less Than” rule to start. This type of rule requires two inputs: the criteria, or condition, used to trigger the rule, and the format to apply when that condition is met. Because our threshold is $5000, we’ll use 5000 for the condition. For the format, we can choose from several pre-built options, or we can define a custom format. For now, let’s choose the “Light Red Fill with Dark Red Text” preset. Once we click OK, the rule is created and we see the formatting applied to cells that meet the conditions we defined. Conditional formatting is fully automatic. If we change a value in the table to be lower than our threshold of $5000, the rule that we created is triggered and the formatting is automatically applied.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
