Google Spreadsheets custom conditional formatting based on previous cell in same column

Question

I have a list of statistics I keep for my website ordered by type of stat (in columns) and month (in rows). So I'll haven for example:

+---+-----+----------+-----------------+-----------+
|   |  A  |    B     |        C        |     D     |
+---+-----+----------+-----------------+-----------+
| 1 | /   | Sessions | Unique Visitors | Pageviews |
| 2 | Jan | 20000    | 15000           | 35000     |
| 3 | Feb | 21000    | 15500           | 36000     |
+---+-----+----------+-----------------+-----------+

I'd like to create two rules:

• one to color green each cell that has a higher value than the cell above it in the same column
• one to color red each cell that has a lower value than the cell above it in the same column.

Basically, I want to track improvements/declines from month to month. I've found this thread: Conditional Formatting based on previous cell value but it doesn't help me do what I want :/

I just want to be able to say, for example, if B4 is bigger than B3, color green, but if B4 is smaller than B3, then color red.

If anybody could let me know how to do this, I'd very much appreciate it, thanks :-)

Answer 1

This achieves your goal:

screenshot

The formula entered is: =indirect(address(row()-1;column()))

Answer 2

Clear any conditional formatting from ColumnsB:D, select B3 to the end of your range to format and Format, Conditional formatting..., Format cells if... Custom formula is and

=B3>B2

with green fill. Add another rule (same range) with Custom formula is and

=and(A3<>"",B3<B2)

with red fill and Done.