How to Use Conditional Formatting in Excel 2010

October 8, 2010, By Christian Cawley

The power of Microsoft Excel sits far beyond the standard everyday uses for a spreadsheet – along with the tracking of stocks, accounts, profit and loss and much, much more, you can manipulate the spreadsheet itself to show you exactly what you are looking for.

For instance, you might have a running total figure with a value that you don’t want to go beyond. Using conditional formatting in Excel 2010 you can configure your spreadsheet application to highlight this value in a number of ways.

By using conditional formatting in this way, you can quickly and easily spot the type of figures you are looking for, and save yourself time.

When to Use Conditional Formatting in Excel 2010

A typical table of values in Excel might be something like a sales total for several months. As time passes, it will be more and more difficult to keep an eye out for a particular value, say a total that is not great enough for you to pay your staff, for instance.

You might have had a month in which your business managed to break even – and by using conditional formatting, you can use this value as a reference to apply a particular visual style to other cells should they contain figures that are lower than this value.

A good example would be to make these cells display the suspect figures in bold red type, or perhaps with a highlighted background. Using conditional formatting in Excel 2010, this can be done relatively easily.

Configure Your Table to Use Conditional Formatting

Basically, we’re saying to Excel:

If the value in the any cell elsewhere on your table is less than this value, a particular visual formatting will be applied to the cell.

Say your “key” value (the one you wish to use as a reference) is in cell C4 of your table – using conditional formatting across the rest of the table you will create a reference to this cell. We can do this by selecting the range of cells we want to apply the rule to, and then on the Home tab going to Conditional Formatting > Highlight Cell Rules > Less Than.

The resulting dialogue box lets us choose the cell – select C4 here – and then we can apply a style to the cells when they contain a value that is less than that of C4. You might choose Yellow Fill with Dark Yellow Text or Light Red Fill with Dark Red Text, or you might choose Custom Format… to choose a font, border, fill and number style of your own preference.

With the conditional formatting applied in this way, it should now be much easier to see values that might cause concern. Of course, you might wish to flip it over, and flag successful figures – simply use the Conditional Formatting > Highlight Cell Rules > Greater Than item in this case. Don’t worry about adding new figures to your existing table, as it will update the formatting when appropriate.

© 2008-2012 - All rights reserved | Privacy Policy