Conditional Formatting

Another useful formatting tool is conditional formatting. That means telling the spreadsheet that if a certain condition is true, then it should format the cell in a certain way.

I most commonly use it to make zeros disappear. By telling it to change the font color to match the background whenever the value in the cell is zero, I can make the non-zero entries in a table stand out much more clearly.

Other common uses might include using it to automatically highlight missing values, statistical outliers, or probable data entry errors. It can also identify things like the tipping point in debt payments or savings.

When using conditional formatting you can usually set the background, the font color/size/style/type, and/or the borders around the cells. There may be a limit on the number of conditional rules that can be set up, or it might be limited only by your patience in setting them up. If the rules conflict, then it can get complex. I’ll discuss that next week.

The conditional rules can be things like whether the value in the cell is greater than, less than, or equal to a certain value, or whether a cell with text contains a certain string. Some can also compare it to an average value, or check whether it’s a duplicate value, or the top or bottom 10%, etc.

To find the conditional formatting options for your spreadsheet program, you’ll need to start by selecting the cell or cells you want to format. After that, here’s what you do:

In Excel 97:

  1. Click on the ‘Format’ menu header, or type Alt-o
  2. In the menu that appears, click on “Conditional Formatting…” or type ‘d’.
  3. In the window that appears, leave the first drop-down box as ‘Cell value is’. There’s also the option of ‘Formula is’, but I can’t get it to behave in what I would consider a logical fashion.
  4. Use the second drop-down box to select what sort of comparison you want to make — greater than, less than, equal to, between, etc.
  5. In the text box(es), enter the value you want to compare things to.
  6. Click on the Format button.
  7. In the window that appears, select the font, border, and background colors you want displayed if the condition is true.
  8. Click OK.
  9. To add additional conditions (up to 3), click the ‘Add >>’ button and repeat the steps above.
  10. Click OK.

In Excel 2007:

  1. Click on the ‘Home’ tab.
  2. In the ‘Styles’ set, click on ‘Conditional Formatting’.
  3. In the menu that appears, select what sort of condition you want, then the specific condition you want.
  4. In the window that appears, select the type of formatting that you want to appear when the condition is true.
  5. Click the OK button.
  6. To add more conditions, repeat the process.

Note If you want a set of numbers colored on a rainbow spectrum or gradient, Excel 2007 lets you do that easily.

In Google Spreadsheet:

  1. Click on the ‘Format’ header.
  2. In the menu that appears, click on ‘Conditional formatting…’
  3. In the window that appears, select what condition you want from the drop-down menu, then specify the value for the condition in the box next to it.
  4. Click on the box to the right of the word ‘Text:’ to select the color that the text should be if the condition is true, or leave it blank if the color shouldn’t change.
  5. Click on the box to the right of the word ‘Background:’ to select the color that the background of the cell should be if the condition is true, or leave it blank if the color shouldn’t change.
  6. To add more conditions, click the link for ‘+ Add another rule’, the repeat the previous steps.
  7. Click the ‘Save Rules’ button.

In OpenOffice Calc:

  1. Click on the ‘Format’ menu header, or type Alt-o
  2. In the menu that appears, click on “Conditional Formatting…” or type ‘o’.
  3. In the window that appears, leave the first drop-down box as ‘Cell value is’. You can change it to ‘Formula is’, but in my version of the program the formula option is buggy.
  4. Use the second drop-down box to select what sort of comparison you want to make — greater than, less than, equal to, etc.
  5. In the text box, enter the value you want to compare things to.
  6. In the drop-down menu on the next line, select the cell style you want displayed if the condition is true, or click on ‘New Style…’ to define a new one.
  7. To add additional conditions (up to a total of 3), mark the checkbox next to the condition number, then repeat the previous steps.
  8. Click OK.

In Microsoft Works Spreadsheet: No conditional formatting options.