99.1% Of all customers recommend us, we're so confident about our results we publish all reviews and statsView Live Stats View Reviews
Conditional Formatting in Excel 2007
Tue 23rd June 2009
An example might be to set the font colour of figures in a table to be green for numbers over zero and red for negative values - this makes it easy to see at a glance which numbers are more than and less than zero. Changing the cell values may then change the font colour.
In Excel 2003 to apply conditional formatting to highlighted cells, you choose Format, Conditional Formatting and then set up to three separate conditions and resulting formats. Alternatively, you can choose Format, Cells then in the Number tab click on Custom and add the required conditional formatting symbols. But there are easier ways to do this in Excel 2007.
Excel 2007 has a host of further conditional formatting features which are easy to apply with instant visual impact. You can see how these effects work with a simple example. With Excel 2007 open, add the numbers 1 to 10 in separate cells down one column. Now highlight these cells.
In the Home tab, in the Styles group, click on the Conditional Formatting command to reveal the various options. The top two options Highlight Cell Rules and Top/Bottom Rules let you set up different numerical conditions to control the formatting - much easier than in previous Excel versions but with the same results.
Try this by hovering over Top/Bottom Rules, then click on Above Average. In the Above Average dialogue box choose the required formatting, and click OK to finish. Then click off the highlighted cells to see the results. Try changing some of the cell values and the formatting changes accordingly. To remove the conditional formatting, highlight the cells, click on Conditional Formatting, hover over Clear Rules, then click Clear Rules from Selected Cells.
The really impressive conditional formatting effects though are in the Data Bars, Color Scales and Icon Sets options. Highlight the same cells again, click Conditional Formatting, hover over Data Bars and then hover over one of the Data Bars options. Excel's new Live Preview feature lets you see how the cells look with this formatting. To apply the formatting click on the preferred option, then click off the cells to remove the highlight. Now you see the Data Bar conditional formatting applied to the cells.
You will see that the Data Bars are coloured in proportion to the cell values - Excel detects the highest and lowest values in the range of selected cells and arranges the size of the coloured bar accordingly. If you try changing some cell values you can see the amount of colour in the bars changing.
Before trying the next type of conditional formatting its best to clear the current formatting To do so, highlight the cells, click on Conditional Formatting, hover over Clear Rules and choose Clear Rules from Selected Cells. If you don't do this, and apply further conditional formatting, the results are cumulative but can be a little confusing!
Again highlight the cells, click Conditional Formatting and this time choose Color Scales. Hover over one of the options and you can see Excel applies a range of coloured backgrounds to the highlighted cells. If you click on More Rules rather than on one of the Color Scales options you can see that you can change which colours are chosen and whether there are two colours or three colours as the basis. Now move the mouse away and the original formatting returns. If necessary clear the existing conditional formatting.
The last conditional formatting option to explore is Icon Sets. Once again highlight the cells, click on Conditional Formatting and this time hover over Icon Sets, then click to select one of the options, for example the traffic lights. Now the cells have a particular traffic light colour, depending on the cell values. Try changing the cell values to get the traffic lights to change colour. As there are three traffic lights, Excel assigns the first traffic light colour to the cells in the lower third of the values in the highlighted cells, then next traffic light colour to cells in the middle third and the third colour to cells in the upper third.
Once you've got the idea behind how this new conditional formatting works, you can customise settings. For example if you apply the traffic lights Icon Sets, then with the cells still highlighted click on Conditional Formatting, and choose Manage Rules, you can change the settings.
In the Conditional Formatting Rules Manager, click the Edit Rule... button which takes you to the Edit Formatting Rule box. You can see in the lower part of the box how the cell values are set for each traffic light colour. You can change the switchover value for each traffic light if you wish. To return to the spreadsheet click Cancel, then Close.
If you applied conditional formatting over a range of cells in Excel 2007, you can copy the same conditional formatting to another range of cells using the Format Painter button. To do this, first click into any of the cells with the required conditional formatting. Then click the Format Painter button (in Home tab, far left). Then in one action highlight the other range of cells. The conditional formatting is now applied to this second range of cells.
For more examples of using Excel 2007 conditional formatting and to explore some of the other formatting innovations, you may find a training course helpful. Conditional formatting in Excel has never been easier.
Original article appears here:
London's widest choice in
dates, venues, and prices
On-site / Closed company:
Generator Group LLP
PowerPoint Intermediate Advanced
I was very satisfied with the course content and delivery and feel that I have a lot to take away.
Excellent Account Management
A good day of teamwork and sharing ideas. However the agenda was very long and therefore felt unrealistic. True enough, we lost structure and focus towards the end (I.e team and personal goals would have been great at the start of the day). Feels like training that is more relevant to big corporates than smaller businesses. Some of the group work was really useful though.
HCC International Insurance Co PLC
I cant think of anything to say, found today extremely helpful.