Dynamic Conditional Formatting

Explore

The Formula

This info has just been discovered, so this page is a write-up of everything I know so far. As we find out new info together, I’ll update this doc.

Syntax. How to use the formula.

The _GRADIENT formula requires a Number column followed by a seemingly unlimited number of Color Stops. Color stops are comprised of a Number and a Color. The row’s formatting is determined by comparing the row’s Number column with the Color Stop’s you’ve defined.

_GRADIENT(

thisRow.[Lightness],

1, "#000000",

10, "#FFFFFF"

)

Color

Lightness

There are no rows in this table

⁠

In this example, we color the row based on a Number column called Lightness.

- If the row’s lightness is 1, it’ll be fully black.

- If the row’s lightness is 10, it’ll be full white.

- And anything in between will be an appropriate shade of gray.

Defining Colors. Three ways to define colors.

There are three ways to define the color of Color Stops (as far as I know, there could be more). I go into more detail about each on their respective pages.

⁠

Hex Values⁠

⁠

HTML Colors⁠

⁠

Coda Colors⁠

⁠

Color Stops.

These examples explain how to setup Color Stops. There doesn’t seem to be a limit to the number of color stops supported, though I’m sure there may be a performance impact when having too many.

Example One

In this example, we setup three color stops:

1 Light Pink

5 Lavender

10 White

We then use the Table’s Position column to determine the row’s format. In this example, the color stop’s color is being defined by a Hex Value.

_GRADIENT(

thisRow.[Position],

1, "#ffb6c1",

⁠ ⁠ ⁠

⁠

5, "#b6c1ff",

⁠ ⁠ ⁠

⁠

10, "#ffffff"

⁠ ⁠ ⁠

⁠

)

Position

Boys

Girls

Liam

Olivia

Noah

Emma

Oliver

Ava

Elijah

Charlotte

William

Sophia

James

Amelia

Benjamin

Isabella

Lucas

Mia

Henry

Evelyn

Alexander

Harper

There are no rows in this table

⁠

Example Two

In this example, I want to show you want happens when a row’s Number column is out of range. We’ll adjust the numbers in the table’s Position column so that some fall out of the range we specified for the color stops. The table’s _GRADIENT() formula remains exactly the same as in Example One.

Position

Boys

Girls

Out of Range

-10

Noah

Emma

-5

Benjamin

Isabella

Liam

Olivia

Oliver

Ava

4.5

Lucas

Mia

William

Sophia

John

Joan

Henry

Evelyn

Alexander

Harper

Elijah

Charlotte

James

Amelia

There are no rows in this table

⁠

You’ll see that when a row’s Number column is out of range, it applies the closest color stop’s color. This is also how Color Scales work when you set a Min or Max value.

Rows after our highest color stop (10) are white, and rows before the lowest color stop (1) are pink.

⁠

Applying the Formula.

Where do I write this formula?

Applying the _GRADIENT formula is pretty easy, and because it’s just a single copy-paste, applying it to multiple tables is really quick.

Open up the Table’s Options

Select Conditional Format

Click on Add Rule and select FORMULA

Paste the formula into the editor window! Note that you can still apply additional Rules to the table’s conditional formatting if you want.

Note that when I was copy-pasting formulas into the editor, I’d have to: Right-click in the editor and select “Paste as Text” to get it to work.

Here’s a video:

⁠

Using this doc’s generators

On each page within

Defining Colors⁠

, you’ll find a Generator. This is just a handy way to generate the conditional format without having to type it out line by line.

⁠

Important Caveat

HTML Named Colors Don’t Transition

If you define colors using HTML Named Colors, they don’t seem to transition when the row’s number column is between two color stops. in the example below, we’ve used HTML Named Colors instead of Hex Values, and rows seem to just “Round Down” to the color stop lower than their Position column.

Here’s the formula:

_GRADIENT(

thisRow.Position,

1, "LightPink",

5, "Lavender",

10, "White"

)

Position

Boys

Girls