While working in a Google Sheets file, I found myself in the following situation. My sheet consists of “units”. Every unit consists of three cells above one another. I wanted all three cells to take a certain colour depending on what the value was in the first of them. I wanted to have this effect for every unit in my sheet, regardless of its position.

As an example, my particular case is that I am planning my running. For this, I have a sort of calendar in Google Sheets. Each “unit” from above represents a day in my planning. It has a cell containing the category, a cell with the distance to run that day, and a cell for extra notes. All three cells for a certain day have to have a certain colour depending on the category they are in. For example, regular runs are a light blue.

Doing this manually for every set of three cells is a pain and not future proof. If I decide to change the category (the type of run) for that day, then I manually have to change the colour as well. Instead, using conditional formatting seemed to be the way to go. “If this cell has a certain value, colour it and the two cells below it”. The point of view is different though, conditional formatting formats the cell for which the condition is defined. As such, the description of what to do is more along the lines of:

  • If the current cell has a certain value X, colour the current cell.
  • If the cell above the current cell has a certain value X, colour the current cell.
  • If the cell two above the current cell has a certain value X, colour the current cell.

The first of these is easily translated to Google Sheets. This is their regular “Text is exactly” condition. The other two are not as simple and require a custom formula. The difficulty with this formula is how to address the cell above. Relative referencing in this context seems to place A1 at the current cell, A2 at the one below it, B1 at the one to the right of the current cell, etc. Trying something like A-1 however failed to reference the cell above the current cell, which is the cell I wanted to reach.

Instead I made do with INDIRECT(). This formula takes a string like C10 and gives you the cell it represents. This allows you to dynamically refer to cells. In terms of custom conditional formatting, this also makes for absolute referencing rather than the relative referencing from above. As such, to use INDIRECT() to refer to the cell one above and the cell two above the current cell, I need to first be able to get the current column letter and row number. This enables me to reference the one above and the one two cells above by subtracting one and two respectively from the row.

To get the column name, you can use COLUMN(). This gives you the number of the current column. So column A is 1, column B is 2, column C is 3, etc. To change this into the letter representing the column, you can use your knowledge about the ASCII table. That is, adding 64 to the number you get gives you the number that represents the character you want in the ASCII table. From there, it is a matter of converting that number to its letter using Google Sheet’s CHAR() function. It should be noted this technique only works for the first 26 columns (that is, columns A to Z). Combined, this gives the following code to get the current column letter.

CHAR(COLUMN() + 64)

Getting the row is simpler, as it is already represented by a number. ROW() gives you that number. Since I want to reference the cell one or two cells above the current cell, I can get its row number using ROW()-1 and ROW()-2.

Combining the column letter and row number can be done with the & operator.

CHAR(COLUMN()+64)&(ROW()-1)

Putting that in INDIRECT() gives you the value of that cell, which you can then compare to a value of your choosing using =. The true or false result from that comparison can be used in the custom conditional formatting formula like this:

="YOURVALUEHERE"=INDIRECT(CHAR(COLUMN() + 64)&(ROW() - 1))

For example, I wanted to format the three cells as blue if the category (i.e., the value of the first of the three cells) was Endurance. To do so, I highlighted all cells and selected conditional formatting. I added the following three rules.

  1. No custom formula, use “Text is exactly”. Enter Endurance. This handles all the top cells.

  2. Add a new rule and use a custom formula. Enter

    ="Endurance"=INDIRECT(CHAR(COLUMN()+64)&(ROW()-1))

    This handles all the second cells.

  3. Add a new rule and use a custom formula. Enter

    ="Endurance"=INDIRECT(CHAR(COLUMN()+64)&(ROW()-2))

    This handles all the third cells.

While this works, it stills requires significant effort on my part. I have to add three formatting rules for every colour/category I want to use. Instead, I used another property of my particular problem: every unit starts at either row 2, row 5, row 8, row 11, etc. In other words, every unit starts at row 3n+2. As such, I can use some mathematics to reduce the above three rules to just one.

For that, consider that I want to make the following conversions in my row numbers. For rows 2, 3, and 4, I want to refer to row 2. For rows 5, 6, and 7, I want to refer to row 5. For rows 8, 9, and 10, I want to refer to row 8. In other words, for any row r, I want to refer to row floor((r-2) / 3) * 3 + 2. This can be translated to something Google Sheets understands as follows

QUOTIENT((ROW() - 2), 3) * 3 + 2

As such, I can combine those three conditional formatting rules into just the following. Highlight all the cells and select conditional formatting. Use a custom formula and enter

="Endurance"=INDIRECT(CHAR(COLUMN()+64)&(QUOTIENT((ROW()-2),3)*3+2))

Do this for each of your categories.