Identify Trends or Out of Range Data in Excel Worksheets

There’s another type of formatting in Excel that’s different than the static formatting that’s used to visually separate data. Conditional formatting will highlight cells that meet a certain condition. One use of conditional formatting is to flag values that are high or low.

In our worksheet, there’s a table of flow rates calculated for an orifice. We can easily flag values that are greater than 20 gallons per minute. Select all of the flow rates in the column. In the Home tab, click Conditional Formatting. In the Highlight Cell Rules sub-menu choose Greater Than…:

In the box below “Format cells that are GREATER THAN:” enter in the value of 20. Click OK to accept the default formatting. Now the cells that are greater than 20 will be highlighted in red:

The conditional formatting rule that we applied overrode the existing static formatting. If we change the inputs so that the flow rates change, the conditional formatting will update automatically, always highlighting the cells that are over 20 gallons per minute.

You may modify or delete conditional formatting you’ve applied. First select the cells that you previously formatted, then go to Conditional Formatting > Manage Rules:

The Conditional Formatting Rules Manager will allow you to edit or delete existing rules as well as create new ones.

Another type of conditional formatting is called a color scale. A color scale format is useful to visually indicate trends in your data or maximum and minimum values.

Choosing the correct color scale will highlight our smallest value in green, the largest in red, and intermediate values in shades of yellow. Highlight the flow data, then go to Conditional Formatting > Color Scales > Red – Yellow – Green Color Scale (the second button). The result colors our data from green for low values, to yellow for intermediate, to red for high values:

If this were a much larger table of data and the values weren’t already ordered, this type of formatting would make it easy to spot trends in the data as well as particularly high or low values.