Tuesday, October 19, 2004

Conditional Formatting in Excel

The conditional formatting feature of Excel has some pretty wide uses.

One of the main reasons I use it is for error checking.
It can be very useful to scan a large selection of data and look for a coloured cell out of place.

An example may be that you are processing a large selection of numbers in cells, and negative values need to be located or maybe you are looking for numbers over $1000 etc.
Select 1 cell in the range and choose Format - Conditional Formatting from the menu.

Use the first option Cell Value Is and then select the comparison phrase in the next drop down box, Eg less than. Now enter the value in the input box at the end. Eg 0. (In this example we will locate all cells where the value is less than 0 or a negative value.)
Now click the format button and select the pattern tab and choose red.
Click OK twice.
Now copy the cell and then select the entire range and Edit - Paste Special As - Formats.
All cells with negative values should now be coloured red. click on any of these cells and make the value positive and see the red disappear.

Tomorrow I shall discuss some more advanced uses of Conditional Formatting.


Posted by: Jethro | 11:59 PM |