TrueJournals

Tag: coding

Conditional Formatting in Google Docs

by on Jun.05, 2012, under technology

Recently, I was working on a project which involved generating aspreadsheetof a bunch of changes I was making to some information. As part of this, I wanted to color code the rows of the spreadsheet: green would mean OK, yellow would mean a change, and red would mean a problem. I could do all this formatting manually… but I decided to find a way to make Excel do it for me. Enter conditional formatting!

Conditional formatting in Excel is very, veryrobust. For this project, I needed to change a row’s background color and font color based on three possibilities:

  1. If Column E has any text, the row should be red.
  2. If Column A and Column D contain different values (case-insensitive), the row should be yellow.
  3. Otherwise, the row should be green.

Unfortunately for me, Excel’s conditional formatting works on a per-cell basis. However, Excel does provide the ability to choose conditional formats based on a formula! After some fiddling with Excel functions, I was able to generate formulas to check different cells in the row, then apply the conditional formatting based on that. I ended up with these three formulas, executed in this order, and corresponding with the above three conditions:

  1. =AND(CELL(“contents”, INDIRECT(CONCATENATE(“E”, ROW())))>0,ROW()>1)
  2. =AND(CELL(“contents”, INDIRECT(CONCATENATE(“A”, ROW())))<>CELL(“contents”, INDIRECT(CONCATENATE(“D”, ROW()))), ROW()>1)
  3. =AND(CELL(“contents”, INDIRECT(CONCATENATE(“E”, ROW())))=0, CELL(“contents”, INDIRECT(CONCATENATE(“A”, ROW())))>0)

Wow! First, notice all of these start with an AND function. AND evaluates the conditions given, performs a logical AND on all of them, and returns a boolean true or false based on that. Explanation of the conditions:

  1. Two conditions:
    • CELL(“contents”, INDIRECT(CONCATENATE(“E”, ROW())))>0 checks for text in Column E. If there is nothing in the cell, the value is 0.
    • ROW()>1 checks if this cell is NOT in the first row. The first row is my header row, and I don’t need that one color coded.
  2. Two conditions:
    • CELL(“contents”, INDIRECT(CONCATENATE(“A”, ROW())))<>CELL(“contents”, INDIRECT(CONCATENATE(“D”, ROW()))) checks if the contents of the cell in Column A and the contents of the cell in Column D are different. The <> operator is “not equals”, and Excel seems to automatically do a case-insensitive comparison.
    • ROW()>1 checks if this cell is NOT in the first row.
  3. Two conditions:
    • CELL(“contents”, INDIRECT(CONCATENATE(“E”, ROW())))=0 ensures that Column E is empty. (Again, the value is 0 if there is nothing in the cell)
    • CELL(“contents”, INDIRECT(CONCATENATE(“A”, ROW())))>0 ensures that there is some value in Column A. If there’s no value in Column A, there’s no point in any highlighting.

Alright… So, with all those conditions set up, text is automatically highlighted based on what values are entered. This made editing the spreadsheet really easy: simply type in values, and the highlighting is automatic. If I go back and change something later, the highlighting changes without me needing to do anything. (continue reading…)

7 Comments :, more...

Looking for something?

Use the form below to search the site:

Still not finding what you're looking for? Drop a comment on a post or contact us so we can take care of it!

Visit our friends!

A few highly recommended friends...