3

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 :-)

Community
  • 1
  • 1
WonderfulWanderings
  • 31
  • 2
  • Welcome to [so]. Instead of looking at [tag:excel] you should check [tag:google-spreadsheet] and more specifically to [tag:gs-conditional-formatting]. If you don't found nothing helpful then you should show what did you tried. Please take the [tour] and checkout [ask]. – Rubén Sep 11 '16 at 20:55
  • 1
    Hi Rubén, Thank you for replying, but I already checked the Google Spreadsheet threads and didn't find an answer to my question. I can't show you what I did, because I don't know how to do this... which is why I came here to ask :-) – WonderfulWanderings Sep 13 '16 at 09:16

2 Answers2

7

This achieves your goal:

screenshot

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

Tom Ribbens
  • 210
  • 1
  • 10
1

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.

pnuts
  • 58,317
  • 11
  • 87
  • 139
  • To be specific: if e.g. you have 1 header row, your data starts at row 2, so really your first row to compare from is row "3" (because row 2 doesn't have a "previous" value), then use "D3:D" as your data range. The docs say: "Write the rule for the first row." https://support.google.com/docs/answer/78413 so you write the formula for cell D3, and the rest will follow. – hraban Mar 01 '19 at 09:03