Conditional Formatting

In Excel, conditional formatting enables you to format cells in ways depending on their values through rules you can either choose from presets given to you by Excel or through your own formulas. Conditional formatting can be accessed through the Styles tab within the Home pane of Excel.

The formatting you might want to apply to cells through conditional formatting has been gone through in depth in Formatting.

There are three types of rules you can apply to a range of cells:

Highlight Cells Rules: These rules are imposed based on a cell’s relationship with one or more values, with the exception of duplicate values. 

Ex. If you’re trying to determine which teams placed top 10 within a table of competitors, use the less than rule within the data range to format those with values less than 10.

Top/Bottom Rules: These rules are imposed based on a cell’s relationship with the data itself. Cells will be formatted depending on how high they rank relative to the rest of the data.

Ex. Say you’re trying to determine the teams with that have the 12 highest scores. You can go to Top/Bottom Rules - Top 10 Items and then change the 10 to a 12 in the “Top 10 Items” box.

Custom Rules: To create a custom rule, click on More Rules… at the bottom of the drop down tab for any rule or preset in conditional formatting or click on New Rule at the bottom of the Conditional Formatting drop down tab. In New Formatting Rule, you can customize cells like you could in the other two rule types in greater depth, or you could create your own formatting formula. I will go over this in greater detail in next week's entry: Conditional Formatting Continued.

With the formatting rules based on the 'Top/Bottom Rules' (Format only top or bottom ranked values, only values that are above or below average) you can impose them into more specific scenarios. For example, you can apply formatting to cells 0-3 standard deviations above or below the average.

With the rules based on the 'Highlight Cells Rules' (Format all cells based on their values, only cells that contain, unique or duplicate values), like the rules based on Top/Bottom Rules, you be much more specific as to which cells you’ll be formatting and more in depth as to how you’ll format them. Unlike any other rule in Excel, within ‘Format all cells based on their values’, you can apply formatting in a two or three colored gradient. A three colored gradient is especially useful if you would like to apply a two colored gradient to a specific range of cells. You can just make the third color white to make the remaining cells appear unformatted. Within ‘Format all cells based on their values’, you can apply many different types of formatting; this will be gone over in a future entry.

Ex. Say you’re trying to format a table with the placements of many schools over many events and you want to pick out the teams from the schools that placed top 6 in an event. However, you want to apply this in a gradient so that first place winners will appear in a much more vibrant orange than the ones in 6th place, while also keeping the rest of the cells appear unformatted. In order to do this, select the data and go to New Rule… Click on 3-Color Scale under Format all cells based on their values and apply the boxes as such: 

.

Click OK, and you should arrive at this: 

In Excel you can also apply specially defined styles or presets you can use to apply conditional formatting to your data very quickly. 

There are three types of presets in Excel:

Data Bars: These are horizontal bars added to each cell, much like a bar graph.

Color Scales: These change the color in each cell based on its value in a two or three color gradient. For example, the Green-Yellow-Red color scale applies the highest colors as green, the average as yellow, and the lowest as red.

Icon Sets: These add a specific icon to each cell based on its value.

Example of All Three: I would not recommend you use all three haha

I went over custom rules very vaguely over this entry, in order to better grasp these more customizable formatting rules, check out Conditional Formatting Continued.