Conditional Formatting Continued

To create a custom rule within Conditional Formatting, go to the Styles Tab in the Home pane within Excel. Hover over any of the drop down menus for Highlight Cells Rules through Icon Sets and click on More Rules or click on New Rule at the bottom of the Conditional Formatting Menu.

This entry will assume you’ve already gone over anything in Conditional Formatting.

Within ‘New Formatting Rule’, there are six different categories that either execute what we’ve gone over in the last entry or something completely new: Format all cells based on their values, Format only cells that contain, Format only top or bottom ranked values, Format only cells that are above or below average, Format only unique or duplicate values, Use a formula to determine which cells to format.

Format all cells based on their values:

There are six different types of values you can add to customize the ‘Format all cells based on their values’ rules:

Lowest/Maximum Value: Chooses the lowest or highest values within the selected set of data. 

Number: Exactly what you think it is; it’s just a number.

Percent/Percentile: These two perform the same function except for the difference in that percent is calculated based on the range of the set while percentile is determined within the order of values. Percentile examines the set of values contained in the selected range, orders them, and uses their position within the range of values values to determine the percentile.

Formula: A formula that returns some value, which will be set as the referenced value for the formatting rule (you cannot use relative references in formula values). Use this if you want the data to update automatically.

There are four different format styles one might use within ‘Format all cells based on their values.’

2-Color Scale: Within ‘2-Color Scale’, formatting can be applied such that the background colors of the formatted cells may end up on a color gradient depending on its value relative to the minimum and maximum values. 

Ex. Say you want to highlight the cells that might’ve placed top 6 in a red-yellow gradient. You can do this by going to ‘2-Color Scale’ and selecting 1 or ‘Lowest Value’ under ‘Minimum’ and 6 under ‘Maximum.’

3-Color Scale: This does the same thing as 2-Color Scale except you can cause the gradient to appear as three colors, with the ‘Midpoint’ being it’s separate value that you can customize as you could the ‘Minimum’ and ‘Maximum’. 

Ex. Say you want to do the same thing as the last example but you just want to have the people that placed top 6 colored in a gradient. To do this you can do the same thing you would’ve done in ‘2-Color Scale’ except for the third value you should make it the value right after the midpoint, which would cause the data that doesn’t fall within 1-6 to be formatted with a white background.

Data Bar: This is basically a menu to do what you might’ve done with the data bar preset except with more customizability in the form of formatting or ‘Minimum’ and ‘Maximum’ values. By default those values are set to the lowest and highest values within the selected range. However, wiithin the Data Bar menu, you can customize the aforementioned min/max values to any value mentioned in the section prior. You can also format the bar appearance from it’s border to color and to whether or not it appears as a gradient. 

Within ‘Negative Value and Axis…’ you can also format the negative data bar in various ways as shown in the image on the left. 

Ex. Say you have a datatable with the number of apples gained and lost by day of the week for 3 locations. In order to better visualize their relationship with the individual relationships with the overall data, you can use data bars by percentiles. To do so, go to ‘Data Bar’ and select ‘Percentile’ under ‘Minimum’ and ‘Maximum’. Enter 0 for each. If you enter anything else (n) the data bars that appear will be in accordance to its relationship with the nth percentile that you enter. Here, a bar that goes all the way to the left will be the 0th percentile and the one that goes all the way to the right will be the one that reaches the 100th percentile. Click OK.

Icon Sets: Like ‘Data Bars’, this basically does what it’s preset did while providing for greater customization. Like the other subsections, the Value ‘Types’ range from Number, Percent, Formula, to Percentile. Here, you are provided a wider range of icons and free reign over the values a cell has to reach to display those icons.

Ex. Say you want to further format the data mentioned in the past section, except you want the cells that are in the bottom 25th percentile to display an icon and you want similar results for the 25th - 50th, 50th - 75th, and 75th - 100th percentile. You can do this buy going to Icon Sets and clicking on any four icon set within Icon Style. Change each Value Type to Percentile and click OK. 

Format only cells that contain: 

This allows you to format cells in any way you’d like, as described in Formatting, for cells with Cell Values, Specific Text, or Dates that match some sort of relationship you mention in the menu. You can also format cells with Blanks, No Blanks, Errors, or No Errors. 

Format only top or bottom ranked values: 

This is very straightforward. It allows you to format any cell whose values rank in the top or buttom n or nth percent of the selected range.

Format only values that are above or below average:

This is also very straightforward. It allows you to format any cell whose values lie above or below or on the average within a specified number of standard deviations from 0 - 3.

Format only unique or duplicate values: 

This is also very really straightforward. It formats cells whose value appear more than once or exactly once.

Use a formula to determine which cells to format: 

The cells will be formatted when the imposed formula evaluates to TRUE for that specific cell. When creating formulas pay close attention to the reference type, this has been discussed in Cell References. You can put any cell reference into the formula and Excel will refer to the individual cells as long as the reference is relative. 

Use this rule to format entire rows based on cell values (you can do this by making the reference in the formula mixed, as such =$B3="asdf", and the rows will be formatted where there is text containing asdf).

When you create the formula, make sure the relative reference you select is in the top left corner of the selected range. Excel evaluates the table going from left to right from the top left corner of the table. If the cell reference you put in the formula is somewhere other than the top left, it will use that value even though the formatting should start in the top left, but because these two references are off shifted, the cells will be formatted according to where the relative reference has been shifted, even if these two references don’t line up.

Ex. Say you want to impose a formula so that the values in the cells will be bold and underlined as long as their values are positive, you can do as follows. The following data will be the same as from the examples from ‘Data Bars’ and ‘Icons’.

Type the following formula into the textbox and format the preview as such:

You will end up with this:

However, if you make the relative reference within the formula start from a row below the top left corner, the formatting will be offset a row up because formatting starts from the top left, even though the start point from where the formula is evaluated is offset a row down.