Conditional Formatting in Excel

Tired of manually formatting Excel spreadsheets? Conditional formatting in Excel can automate this process for you. Let’s walk through what conditional formatting is, the different options available, and how to use it as part of an analysis.

What is Conditional Formatting?

Conditional formatting changes the appearance of a cell or its contents based on the conditions that you specify. This is an included feature of all modern versions of Excel, as well as other spreadsheet programs such as Smartsheet and Google Sheets.

The conditions can be applied to a single cell or a range of cells. In addition, the condition itself can be either formula-based or an absolute condition.

Types of Conditional Formatting

Highlight cell rules identify values above, below, or matching a specified condition, and adjust the formatting. Top/bottom rules can identify top #, bottom #, or percentiles, and also adjust the formatting.

Data bars, color scales, and icon sets are three different sets of formatting that you can use to create stoplights, dashboards, or visual analyses of the numbers in your spreadsheet.

Clear rules allow you to clear the rules in a selected range of cells. Alternatively, manage rules lets you adjust an existing conditional format or program to your format.

Example: Using Conditional Formatting to Create a Stoplight Chart

Step 1: Layout a data set to apply conditional formatting

For this analysis, we will use a dynamic index. Conditional formatting is a great pairing with a dynamic index because the format can update as you change the index view. This way, end-users can adjust the view to the period of time they’d like to see while maintaining the visual aspect.

Step 2: Design the condition

First, select the conditional formatting you want to use. In this case, we will select stoplights as shown below.

Next, you need to manage the rule based on the conditions you want to trigger the format. We want anything below zero to be red, close to zero to be yellow, and fully profitable to be green.

Step 3: Test the conditions

Finally, we want to test the conditions to make sure we set it up properly. In these three images, we will show the green, yellow, and red conditions being triggered. With that, the file is ready to go.

Green Condition

Yellow Condition

Red Condition

Have any questions on conditional formatting in Excel? Are there other topics you would like us to cover? Leave a comment below and let us know! Make sure to subscribe to our Newsletter to receive exclusive financial news right to your inbox.