ExtremeCPA - Technology for Business

Thursday, June 23, 2005

Conditional Formatting

Conditional formatting can help you extract information from a spreadsheet visually in more powerful ways that normal "messages". For example, lets say that you are manually updating a balance sheet and you have a formula at the end that compares total assets to total liabilities + owners equity. Normally you will look to this formula and see if you need to make changes (that is, check if the number is -0- or not). Conditional formatting, however, lets you go much further by adding criteria that says "If the value of this cell does not equal -0-, then color the background yellow, the font bold and red, and put a border around the outside." Now it becomes much more clear that something needs to be done to fix the error.

To use conditional formatting, lets follow along our example. I'll assume you know how to create the balance sheet and parity check. Now all you have to do is go to the cell where the parity check is located (i.e., click on that cell with your mouse). Once the cell is the active cell (note that you should not enter the cell, just sit on top of it), go to Format | Conditional Formatting.... Now, in the "Condition 1" (you can add more for more complicated examples), you'll say "Cell Value Is" ... "not equal to" ... 0. This is the check that is performed by the conditional formatter. To change the format when this occurs, hit the "Format..." button and change to your hearts desire. Add and delete scenarios on queue. Now, the cell looks normal except when a non-zero value occurs!


Post a Comment

<< Home