Unconditional Excellence: Conditional Formatting In Excel

One of the great leaps forward in successive versions of Microsoft Excel has been in clarifying information in immediate and attention grabbing ways. Excel's calculation tools and charting skills have steadily improved with each update to the program yet more recently other facilities have flourished alongside these more fundamental requirements that enable users to assimilate information in a more immediate and sensory fashion.

Chief amongst these features is conditional formatting. Sometimes the way in which data is presented highlights one particular aspect of the information but disregards another. The table presented may be perfectly correct in what it was created to show yet you may wish to go deeper into the data and discover additional patterns and trends that are hidden by the current format.

To take an example, imagine you have created a football league table in Excel that is formatted to calculate the number of points each team has amassed and then place each team in order from high to low on this principle. This table will give you a clear understanding of which teams are top of the league and which are battling relegation but there are other things that will not be so immediately apparent when the table is given a cursory glance. You may, for instance, want to know which team has scored the most goals as this will not necessarily be the same as which team heads the league on points. Yet in the current format of the table such statistics can easily get lost.

With conditional formatting there is a way to draw out this information whilst still retaining the original format of the table. By applying either traffic light icons or RAG (red, amber, green) status you can highlight the required fields by amount or other factors and thus create a clear, colour coded signpost to the relevant information within the overall points-based league table.

To put this conditional formatting in place first select ALL of the cells that you will want to highlight in this way before clicking on the 'conditional formatting' button on the home tab. Then within 'highlight cell rules' select 'greater than' and enter the amount that you wish (eg. 40). This is also where you can select the colour that you want to denote this particular grouping. Next, follow the same commands but select first 'between' and then 'lower than,' making sure to include the amount and colour that you require.

However, there is a slight issue in entering your requirements for the 'between' segment in that the threshold values may appear in either the colour selected for the 'between' values or in the 'greater than' or 'lower than' categories. For example, if you have selected 'greater than 40,' 'between 20 and 40' and 'lower than 20' any team that has scored either 20 or 40 goals could show up in either colour coded segment.

This is a straightforward matter to remedy. If you go to 'manage rules' within 'conditional formatting' you can edit the rule description so that a particular colour, say green, is appropriate for amounts 'greater than OR equal to' a given number. In this way you can modify each category to provide you with the most immediate and accurate visual representation of the data possible.

Attend our training courses and get more out of Excel. We’ll show you, from a consultant expertise level, how to manipulate data in Excel. From data preparation/cleaning to data formatting the professional way. We’ll cover both the science and the art of data manipulation in Excel, and share very useful keyboard shortcuts and expert tricks that will speed up your productivity in Excel.

Kindly contact us through any of the following;

Tel: 08062468296 | 08108054165

Email: [email protected]