February 2017 Excel Tip of the Month - Conditional Formatting
By Aaron Cooper, Partner, Brown Smith Wallace
One of the most underused, yet effective, tools for analyzing data in Excel, is conditional formatting. This tool provides a way to visualize data and is easily accessible from the Home menu tab. Conditional formatting allows you to automatically apply formatting, such as colors, icons, and data bars to cells, based on the cell value, by creating a conditional formatting rule. For example: If the value is greater than $1,000, color the cell red with a horizontal arrow. This rule allows you to quickly locate which cells contain values greater than $1,000.
Here’s a step-by-step tutorial:
- Select the range of cells you want to highlight.
- From the Home tab, click Conditional Formatting. A drop down menu will provide the options to choose: “Greater Than…,” “Less Than…,” “Between…,” “Equal To…,” “Text That Contains…,” “A Date Occurring…,” and “Duplicate Values.” To highlight cells that are greater than a value, select “Greater Than…”
- Next, enter the minimum value you would like and select the formatting style and highlight colors you would like used. Then click OK.
- This results in Excel highlighting all of the cells that are greater than your given value.
Using Data bars and color scales within Conditional Formatting add the ability to have significant additional functionality and value by showing relative progress of a set of data. Conditional Formatting is particularly useful for dashboards in this regard.
Aaron is a partner in the Brown Smith Wallace Insurance Advisory Services practice. He is a regular content contributor to IASA national and the St. Louis chapter. You can reach Aaron at email@example.com.
Return to February eInterpreter