Conditional formatting with formulas

The second interesting change I made in the updated library instruction spreadsheet was a type of conditional formatting that I haven’t specifically discussed before: formatting that depends on something other than the contents of the cell being formatted. I did mention it before, but at that time I hadn’t figured out how to use it, or how useful it could be.

This type of conditional formatting is available in Excel 97 and 2010, and presumably all the ones in between. It’s also available in OpenOffice Calc, but not in Google Docs.

You can specify any formula as the condition. In the example spreadsheet I used conditional formatting based on formulas to change the color of a cell based on a combination of a specified date and the date in that cell. If it was halfway through the semester and a teacher hadn’t brought in a class during the semester, then the cell for that teacher turned yellow. Later, if it’s two weeks before finals and the teacher still hasn’t brought in their class, the cell becomes red. This lets a liaison tell at a glance which teachers they may need to prod, and how urgent it is. After all, there’s no reason at all to worry if they haven’t brought their class in by the second day of the semester!

In a non-example spreadsheet I’d make both of those formats dependent on the value for =today() so it automatically updated. Since people might access that spreadsheet years from now, though, I made it depend on the value of a different cell, so people can just change the date in that cell and see how the formatting changes in the table.

A few quick notes about the process in general before I get into the program-specific instructions:

  • If you want to compare two values, you’d still start it with an equal sign even though it looks funny. For example, =a1=100, =a1>100, =a1<100, =a1=a2 =a1-100=0 are all valid.
  • If you’re not comparing two values, then anything that’s not zero is TRUE. If your formula is =a1 then any number or text in a1 except 0 would count as true. Likewise, if your formula is =a1-100 then any value except 100 in a1 would count as true.
  • If you want more than one formatted cell to check the same cell, it has to be a fixed reference. Otherwise, if you’re formatting A1:A5, and you enter =B1, then only A1 will check the value in B1. A2 will check B2, A3 will check B3, and so on.
  • If you want to compare the value in each of a group of formatted cell to a different cell, use a relative reference to the upper left cell in the formula.. For example, if you’re formatting A1:A5 and you want each cell to be formatting if it’s equal to the value in B1, then your formula would be =A1=$B$1.

To use this type of conditional formatting, you would start the same way as normal. The first step would be selecting all of the cells you want formatted. Then just follow the appropriate instructions below.

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, change the drop-down box to ‘Formula is’.
  4. Enter the formula in the box immediately to the right. Be sure to start it with an equals sign (=).
  5. Click on the Format button.
  6. In the window that appears, select the font, border, and background colors you want displayed if the condition is true.
  7. Click OK.
  8. To add additional conditions (up to 3), click the ‘Add >>’ button and repeat the steps above.
  9. Click OK.

In Excel 2010:

  1. Click on the ‘Home’ tab.
  2. In the ‘Styles’ set, click on ‘Conditional Formatting’.
  3. In the menu that appears, click on “New Rule”
  4. In the window that appears, select “Use a formula to determine which cells to format” – it’s at the bottom of the list.
  5. In the box under “Format values where this formula is true:” enter the formula would want. Remember to start it with an equal sign (=).
  6. Click on the “Format” button and select the format(s) you want the cell to have if this formula is true.
  7. Click the OK button.
  8. Click the (other) OK button.
  9. To add more conditions, repeat the process.

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, change the first drop-down box to ‘Formula is’. The second box will disappear.
  4. In the text box, enter the value you want to compare things to.
  5. 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.
  6. To add additional conditions (up to a total of 3), mark the checkbox next to the condition number, then repeat the previous steps.
  7. Click OK.