A Quick Guide To Conditional Formatting In Excel 2007/2010

Tue 23rd August 2011

Excel has always allowed you to make cell formatting conditional on cell values or formula, but the formatting has been restricted to the regular formatting features you can apply to cells such as font and cell background colours. In Excel 2007/2010 you'll find powerful new conditional formatting with brand new formatting features. Find out all about this in this brief article.

Excel has always allowed you to make cell formatting conditional on cell values or formula, but the formatting has been restricted to the regular formatting features you can apply to cells such as font and cell background colours. In Excel 2007/2010 you'll find powerful new conditional formatting with brand new formatting features. Find out all about this in this brief article.

To apply conditional formatting in Excel 2007/2010 you normally highlight the cell or cell first and then select the conditional formatting features. So suppose you have a table of data you want to apply conditional formatting to. We'll select the cells first. Then on the Home tab click the Conditional Formatting button. You'll a choice of options. The first two, Highlight Cell Rules and Top/Bottom rules have sub menus with further options in plain English. So Highlight Cells Rules give you the choices Greater Than, Less Than and so on. If you choose one of these you're taken to a little panel which lets you set the actual value to be Greater Than or Less Than, and a suggested colour formatting which you can easily change.

If you choose one of the conditional formatting options further down the list then you'll see the new features. For example choose Data Bars and then hover over the sub menu to preview the colours available. Excel fills the cell backgrounds in proportion to the values of all the selected numbers, like a colour wash effect. So cells with low numbers are only filled a little and cells with high numbers are almost completely filled.

By the way if you want to apply another conditional formatting feature to the same cells, then the features are additive, so you can up with cells packed with quite an array of colours. You might like to remove the current scheme before applying the next. To do ensure the cells are still selected, then choose Conditional Formatting. Then towards the bottom of the menu choose Clear Rules, then Clear Rules from Selected Cells.

The next conditional formation option Colour Scales lets you apply either a three colour set or a two colour set to your cell backgrounds, with the colours being determined by the cell values. If you're interested in exploring how these values are set, ensure the cells are still highlighted, then choose Conditional Formatting, and then at the bottom the menu choose Manage Rules. This is one of the most important option in this menu because it lets you view and change conditional settings.

In the Conditional Formatting Rules Manager panel select the applied rule, then click Edit Rule. Look in the lower part of the panel which appears and you'll see you can change the colours, thresholds and formatting styles here. Before exploring the next conditional formatting option don't forget to remove the current conditional formatting.

The last conditional formatting creates perhaps the most impact. So with the cells still selected choose Conditional Formatting, Icon Sets. In the sub panel you can hover over the options to preview the effects. When you find one you like the look of, for example traffic lights, apply it with a left click. Now the icons are applied as cell backgrounds. And as there are three traffic lights the appropriate light is applied to cells depending on whether their values are in the lower, middle or upper third of the values of all the cells. Again if you want to see how the traffic lights are configured, just choose Conditional Formatting, Manage Rules, select the rule and click Edit Rule. Then you'll see what makes the traffic light change from Red to Amber to Green.

Once you've become accustomed to choosing Conditional Formatting, you may find you use the Manage Rules option more and more. This is because you can add new rules, edit any rule or delete rules all from this panel. You can also add more than one rule, and easily change the colour or icon schemes each rule applies, and you can also create conditional formatting rules based on formula rather than on cell values.

You can also copy conditional formatting applied to one group of cells to another separate group using the format painter. Suppose you've applied the traffic light icons to a set of cells. To copy the conditional formatting just select any cell in this set, click the format painter, and then in one action highlight the other set of cells with completely different numbers. The second set of traffic lights will display depending on the values in this other set of data.

Interested in learning more about the main new and extremely useful features in Excel 2007/2010? A really effective way is to attend a hands on training course. So over a short time you can gains lots more skills in using Excel.

Author is a freelance copywriter. For more information on excel training, please visit https://www.stl-training.co.uk

Original article appears here:
https://www.stl-training.co.uk/article-1872-guide-conditional-formatting-in-excel-2007-and-2010.html