99.1% Of all customers recommend us, we're so confident about our results we publish all reviews and statsView Live Stats View Reviews
How To Use Conditional Formatting In Excel
Fri 1st October 2010
Begin by creating a range of numbers and labels in Excel. This example will use a simple list of sales staff and their monthly sales figures. In column A is the sales person name and in column B is the value they have sold in the current month in pound sterling. Highlight the full data range and go to the Home tab. There is a conditional formatting tool button and when you click it several options will appear.
The first option is 'Highlight Cell Rules.' This will allow the user to format a cell that has a particular kind of value. For example a value between two set values, duplicate values, a value greater than a set value etc. In our example, the fonts can be formatted to highlight all values under a particular sales target for example. The purpose of the formatting would be so the viewer can see at a glance, the specific values they require. Just as specific values can be formatted, the next option is to format the Top/Bottom rules to change the way a value/cell in the top or bottom ten percentile appears. This might be useful when attempting to identify the consistently poor sales performers in a given month. Other functions such as above and below average can be used to find sales people who stand out in relation to the average.
Each of these options allow further rules to be set if the parameters are more specific. Say for example the user wanted to highlight all duplicate values in a list. Perhaps there is an issue over the quality of the data and several customers have been added more than once. The name range could be formatted to highlight in red all duplicate names in the list. For advanced formatting requirments a formula can be used to decide which cells are formatted based on number of set parameters. Excel attempts to give the user a list of the more common formatting solutions so even novice users can make use of the functionality, while giving advanced users the option to make elaborate formatting decisions on the data.
These types of formatting are about highlighting data to specific requirements, but the other side to conditional formatting is the Data bars, Color Scales and Icon Sets. If you highlight the range of data, choose conditional formatting and then hover over Data Bars, a range of options including gradient or solid fill will appear. Hovering over a particular option will give a preview on your live data so you can see exactly what the finished format will look like. This formatting appears like a bar graph, but each bar of the graph is in the cell itself with the relevant data. The user can immediately see the high and low bars to indicate which numbers are the highest. Once again there is a More Rules section where further tweaking can be carried out such as removing the numbers to leave a graph style and changing the colours of the bars.
As well as Data Bars there are Color Scales. This option fills the entire cell with a colour which is representative of the value. For example, if the person with the most sales will have a green cell, the person with the least sales will have a red cell and all other values will have an automatic colour somewhere in between. The better an employee is performing in relation to his/her colleagues, the greener the cell will appear. This is where a combination of formatting may be useful since it may be that some sales people are better than others, but nobody reached their target. This colour scale could compare all sales people, but an additional formatting rule could show any values in bold that did reach a target. Colours are not limited to standard green to red scales, but can be shades of one or several colours specified.
Icon sets are used in a similar way, but show a specific coloured icon as the performance indicator. A user could show green to red flags or circles, ticks and crosses or an arrow system to indicate the rating. There is even a segmented black and white circle. When considering which icons to use, it is worth looking at which ones the user will be able to look at and instantly understand. Think about the type of work the viewers do. Arrows may indicate movement to a financial group instead of a simple top and bottom indicator and this may cause confusion.
As previously noted, conditional formatting can be combined if required, although this can look overly fussy. A data bar with an icon next to it is not necessarily showing the user anything additional. Instead it can look confusing since trying to show the same thing in two different ways on top of each other can detract from the point. If more than one conditional formatting rule is combined, be sure that both show different points in a clear way. To remove the formatting choose Clear Rule and clear the rules from the selected cells.
Original article appears here:
London's widest choice in
dates, venues, and prices
On-site / Closed company:
Excel Financial Modelling
Very good training a
RB UK Commercial Ltd
PowerPoint Intermediate Advanced
Great Course! Very Helpful. Even better if you remove any aspects which are not useful to the atendees of the training once you know at the beginning what they want to work on.
For example SMART ART was not something anyone in the room wanted to work on, so maybe remove from the training for that group to save time.
I don't see how you can do better