98.8% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsPrevious article Next article Excel articles
Unconditional Excellence: Conditional Formatting In Excel
Mon 13th February 2012
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. You can learn this and much more by participating in the advanced course in Excel.
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.
Author is a freelance copywriter. For more information on excel training course london, please visit https://www.stl-training.co.uk
Original article appears here:
https://www.stl-training.co.uk/article-2041-unconditional-excellence-conditional-formatting-in-excel.html
London's widest choice in
dates, venues, and prices
Public Schedule:
On-site / Closed company:
TestimonialsMODUS UTILITIES LIMITED
Managing Director John Cahill Project Intermediate Excellent instruction from the trainer. He made the subject very interesting and easy to follow. Open Energi
Projects And Operations Administrator Alex Barnard Excel Introduction Course was perfectly paced and presented. Really helpful and patient instructor. I'd used excel previously but this has improved my overall confidence. I now won't need to ask people how to do things as much! Networkology
Technical Director Douglas Goodridge Finance for Non-Financial Managers It was a perfectly packaged course for a single day of content. Richard was extremely knowledgeable and enthusiastic about everything we spoke about and was able to tailor content at times to our individual scenarios and questions. Very useful. |
PUBLICATION GUIDELINES