Conditional formatting in Excel provides an easy way to highlight certain data sets within a worksheet. Though it’s not a difficult tool to use, I have been asked several times in the past by various users regarding how to correctly set up and configure conditional formatting. I thought this would be a good opportunity to illustrate a few different ways to employ conditional formatting within Microsoft Excel.
Though it varies slightly in different versions of Excel, if you’re using Excel 2013 or higher which includes the ribbon header, the option for Conditional Formatting is located on the Home tab. You may need to expand your Excel window if you do not see it immediately.
For this example I will be using a small set of data which includes a list of camping gear along with the price and a category of whether it is a Personal item or a Group item.
One of the more common types of conditional formatting involves highlighting fields that exceed a certain threshold. To begin, select all the fields that you want to include in the formatting. For example, if you want to highlight all rows where the cost is greater than 120.00 you would need to select A2 through C9. You can also select the header row and the formatting tool will ignore it or, in most cases, it won’t fit the criteria. However, if you only wanted to highlight the actual cost column where the value was greater than 120.00 you would only need to select B2 through B9.
Once the necessary rows and columns have been selected navigate to the Home tab and click on the Conditional Formatting drop-down.
In this example I want to highlight only the records in the Cost column that are greater than 120.00. From the Conditional Formatting drop-down select Highlight Cells Rules then click on Greater Than…
A new window will appear displaying the rule qualifier as well as a drop-down which allows you to select one of several predefined formats or a custom user defined format. The rules will immediately be applied as you enter the qualifier.
In this example I am using the default Light Red Fill with Dark Red Text format for all cells in the Cost column that are greater than 120.00. Another option within the drop-down on this window allows you to select a Custom format. If you click this option a new window will appear to allow you to customize nearly all aspects of the cell formatting.
Once you have entered the qualifier and selected the format click OK.
Additionally, you can also add conditional formatting based on a text string. To do this first select the cells in the data that you want to include in the formatting and click the Conditional Formatting drop-down. Next, select Text that Contains…
In the window that appears, enter the desired text and select the formatting type. In the example below I am using the Green Fill with Dark Green Text format this time and formatting the Type column where it is set to Personal.
Note that you can also apply multiple layers of conditional formatting to the same dataset. This is typical when dealing with realistic data.
If you further review the Conditional Formatting drop-down you will see a variety of options available. Another example of using this feature allows you to create a data bar within a specified column. To configure this option simply select Data Bars from the drop-down and then select the color and type desired.
In the example below first select the Cost column (cells B2 through B9) and then chose the blue option under Gradient Fill. This type of formatting allows you to see a visual percentage differential based on the highest value in the data set.
Notice that cell B2, which is the highest value in the data set, displays a data bar that extends the entire length of the cell. All other cells are filled based on their value as a percentage of the highest value. In this case 389.99 would represent 100%.
Though these are just a few examples of how to use Conditional Formatting, this tool really starts to shine when dealing with large sets of data. It provides an additional layer of organization and allows you to easily pinpoint specific data within a particular range or subset.