Conditional formatting

Conditional Formatting

Conditional formatting is a feature that has a variety of uses. Mainly surrounding different ways of displaying data corresponding to the value found within the cell or range of data. This can help you visualise the data faster than reading off the values using colour schemes and icon sets to show the quantities or differences between values more effectively than numbers would at a glance. 

Conditional formatting drop down menu can be found in the home tab:

in this drop down menu you will find a bunch of preset designs using:

• Highlight cells - Allows you to highlight cells that have a greater than or less than value, between a certain value or equal to a value or text or date matches as well. This will formatting the cell with criteria met.
• Top/Bottom rules - This will automatically highlight the highest or lowest, or both simultaneously in a range of data.
• Data bars - A scaling bar that increases across the width of a cell as the value increases, this is useful for: Seeing comparatively a cells value in relation to other cells in the column or row. 
• Colour scales - A colour scale based on the value found in the cell. There are multiple different colour scales available moving from low to high values. There is also the option for 2 colour or 3 colour scales.
• Icon sets- Multiple icon sets to show changes up, down or remained the same

An image of the conditional formatting menu: 

Highlight Cell Rules

This option highlight cells with the following criteria:

• Greater than or less than (a separate option for either)
• Between (set a high and a low value)
• Equal to (select an exact value, must be an integer) 
• Text that Contains (if you're looking for a word in a range, instead of a numerical value)
• A date occurring (highlights a singular date, unfortunately not available as a preset for a date range - see custom rules/new rule later in this guide)
• Duplicate values - (see if there is more than one of the same value in the selected range)

Here is an example of the greater than rule being used:

Top/Bottom Rules

This option highlight cells with the following criteria:

• Top 10 items (highlights the highest 10 values in the data range)
• Top 10% (highlights the top 10% of values, although with a small sample it'll round down the to nearest integer, so with only 15 values, it'll only highlight one value, at 20 values, it'd highlight two)
• Bottom 10 items (Highlights the lowest 10 values in the data range)
• Bottom 10% (highlights the lowest 10% of values, although with a small sample it'll round down the to nearest integer, so with only 15 values, it'll only highlight one value, at 20 values, it'd highlight two)
• Above average (Highlights any values above the mean average of the data range)
• Below average (Highlights any values below the mean average of the data range)

Here is an example of the greater than rule being used:

Data Bars

This option can be used to display a bar within the cell, underneath the value. This bar will be proportionally scaled to match the corresponding value. This is useful if you have a large list of values and you wish to see how a value compares with other values, not necessarily found on screen. The largest value in the data set will have the bar fully across the cell, the lowest value will have almost no bar and the values in-between will have an appropriate length bar to match the value. This then allows you to see how one value relates to the other values in the data array without having to scroll up or down to view the rest of the values.

here is an example of the data bars in use: 

Colour Scales

Colour scales are used in much the same way that data bars are. However instead of showing what is effectively a percentage bar, this option will scale a colour of a cell to correspond to a value. This is much easier and identifying higher or lower numbers at a glance than data bars. Also for the advanced users, there is the option to change the values at which the colour scale changes. i.e. you could use the scale to highlight values that are above or below a certain value and gradient colour the in-between values.

here is an example of colour scale in use:

Icon Sets

Although there are multiple icon sets, if for example we take the three icon set, Excel will assign icons by dividing the values of the data array into thirds. The first icon will then be applied to any values with are within the top third of numbers.

Explanation for 3 icon sets:  Assuming we made an array of numbers 0-100 and applied this format to it: Excel calculates the 67th percent and 33rd percent.  A green arrow will show for values equal to or greater than 64.31. A yellow arrow will show for values less than 64.31 and equal to or greater than 32.69. A red arrow will show for values less than 32.69.

Here is an example of the icon sets in use: 

Custom Rules

However if you would like to create a custom rule or modify one of the above types of conditional formatting. We can change the formatting on a specified range at any point, even retrospectively. Therefore you can edit one of the existing presets to meet your needs. This can include letters, words, numbers and values above or below a certain criteria and anything that you can define effectively.

Use the "New Rule" button at the bottom of the conditional formatting to begin creating a rule:

First highlight the area you wish to apply the conditional formatting on. Once highlighted select the "new rule" button and we can immediately start adding the criteria for this. Don't worry if you want to modify the range to apply this across as we can modify this later if it's not big enough.

Once we click 'new rule' you will see the following menu:

Here you can select a rule type that:

• Formats all cells based on their value

You'll then find the following options within this category. 

• Format only cells that contain:

• Format only top and or bottom ranked values

• Format only values that are above or below average

• Format only unique or duplicate values

• use a formula to determine which cells to format

more detail - Format all cells based on the values

• Formats all cells based on their value: 

You'll then find the following options within this category. 

note: Using icon sets is intended to be applied to a range that shows value changes periodically, that way you can easily track positive, negative and values that remained the same easily. The first example shows the icon set checking to see where a value sits (above, below or equal to a value set). Where as the second example (value, then change) shown on the right below shows the best use for 'icon sets' as it has been applied to the column tracking the change in the value. 

here is an example of the 4 options within the "Format all cells based on the values" option. Followed by a better use of the Icon sets:  

Icon set:

It is worth noting that the value in the cell that icon sets have been applied to can have an option to 'only show icon' meaning you will just see if the change is: positive, negitive or unchanged without being overwhelmed by figures:

More detail - Format only cells that contain:

This option contains more specific conditional formating for finding values that meet specific criteria. This is opposed to the previous category in which most of the tools are used to simply visualise data clearer. 

As an example let's look at somthing like an attendance tracker: in this example we add non-attendance as a certain letter, coresponding to what reason they had the day off. S: Sickness and H: Holiday . 

As you can see, after adding a few days into the attendance tracker it becomes difficult to see easily what each character is in each cell esspecially once it starts to become full. So if we used conditional formatting we can get the cell's format to change according to what it finds in that cell: 

• In this example I used "specific text" "containing" "h" or "S" and made it so the fill of the cell to add colour corresponding to it's contents. (H's turn green and S's to yellow)

After adding the conditional formatting you can see that the values have changed their respective fill colours, which makes it much easier to indentify the value in the cell. however we can use more tools within this feature to accomplish the following:

if you have no idea what type of conditional formatting you should be using and/or you can't get it to work, send an email to ITskills@chi.ac.uk if you'd like to book a training session.