This tip is provided by the Centre for Enhancing Learning through Technology (CELT). The images in this post will enlarge when clicked – use the Back button to return to the post.
The way we interpret and respond to data can be linked to how the data are presented. For example, a long list of numbers ordered largest to smallest may not be the best way for everybody to see a pattern but using Conditional Formatting we can apply other visual techniques to help make our data easier to interpret.
Using colours in this way, while thinking about preserving accessibility, can help those with a more visual preference to engage with numbers.
Here’s a very practical example that can be applied to different data in Excel.
| This is a list of items for purchase from a supplier and their cost – the list is sorted by Total Cost. But what if I want Excel to show me, for example, everything that costs £50.00 or more? |  |
| Using Conditional Formatting I can tell Excel to make all the items that cost £50.00 or more a different colour. To do this I will highlight all of the items in the Total Cost column and click on Home>Conditional Formatting. In here there are a number of pre-sets that can be applied to apply different colours but I just want to make everything that costs over £50.00 pink so I will click Manage Rules. |  |
| In Manage Rules we can ‘build’ our conditions and what Excel does with cells that meet the conditions. This may seem a little daunting at first but using the wizards in here offers a lot more flexibility over the standard pre-set rules. Having clicked Format only cells that contain; I have used the three drop-down menus to tell Excel that cells whose value is equal to or greater than 50 should be coloured pink. |
 |
| Clicking Apply and Ok; my list now looks like this. As you can see all of the items priced £50.00 or more are coloured pink. Now as it stands this may not be particularly useful as my list is already sorted by Total Cost but I can now quickly and easily see where my £50.00 threshold is. However, with the Conditional Formatting applied to the Total Cost column I can now sort my list using other columns but items costing £50.00 or above will remain pink. |
 |
| For example, my supplier needs my list sorted by part number but I still need to see which the more expensive items are. Sorting the list by Part number satisfies my supplier’s requirement while still enabling me to quickly see which of the items are costing £50.00 or more. |  |
| Finally, we can apply multiple rules and change the order in which they are applied so have a practice with some of your own data and see if you see how to use conditional formatting to highlight items that fit multiple conditions across multiple criteria. | |