Skip to content
Dynamic Conditional Formatting
Welcome
The Demos
The Formula
Defining Colors
Meet Jono
More
Share
Explore
Defining Colors

icon picker
Hex Values

This example uses custom hex values for Conditional Formatting.
Probably the most useful implementation is using Hex Values as color stops. There are millions of colors to choose from, and tons of online tools to help you find the color you’re looking for and get its hex value. I think the most useful tool I use is
Coolors
⁠
but I’ve recently really enjoyed
Happy Hues
⁠
too.

Here’s an example

_GRADIENT(
thisRow.[Row ID],
1, "#ff8906",
5, "#e53170",
10, "#a7a9be"
)

Where are these colors from?

These colors are taken from past Pantone Color of the Year winners.
Color of the Year
⁠
is an annual event held every December, where Pantone selects a color (or colors) that represent current global trends in color. I’ve included the year that color was selected just for fun.
You can use any hex value as a color stop, meaning there are
16.7 million colors
⁠
available to you.
Just make sure you ALWAYS include the # at the beginning of the hex value.

Generator

Let’s generate the Conditional Formatting formula for you:
Copy the below Custom Hex Values table across to your doc.
In your target table, add a new LOOKUP column and choose Custom Hex Values as the table to look up to.
Name your new column Color LU.
Click the Generate Formula button below
Open your target table’s Options panel, click Conditional Format
Click Add Rule and select the Formula option. Paste the code in there. You can see a video guide at the bottom of
The Formula
⁠

* Note if you make any changes to the Custom Hex Values table, you’ll need to regenerate the Conditional Format formula, and update it on the destination table.
1
Clear
Generate Formula
_GRADIENT(
thisRow.[Color LU].[Row ID],
1⁠
, "#BF1932",
2⁠
, "#DD4124",
3⁠
, "#FF6F61",
4⁠
, "#F0C05A",
5⁠
, "#88B04B",
6⁠
, "#009473",
7⁠
, "#45B5AA",
8⁠
, "#91A8D0",
9⁠
, "#0F4C81",
10⁠
, "#F7CAC9",
11⁠
, "#C74375",
12⁠
, "#AD5E99",
13⁠
, "#DECDBE",
14⁠
, "#964F4C",
15⁠
, "#939597"
)
No results from filter
⁠

⁠
Custom Hex Values
0
Name
Hex Value
Preview
Row ID
Visually Distinct
Year Selected
1
True Red
#bf1932
■
1
2002
2
Tangerine Tango
#dd4124
■
2
2012
3
Living Coral
#ff6f61
■
3
2019
4
Mimosa
#f0c05a
■
4
2009
5
Greenery
#88b04b
■
5
2017
6
Emerald
#009473
■
6
2013
7
Turquoise
#45b5aa
■
7
2010
8
Serenity
#91a8d0
■
8
2016
9
Classic Blue
#0f4c81
■
9
2020
10
Rose Quartz
#f7cac9
■
10
2016
11
Fushcia Rose
#c74375
■
11
2001
12
Radiant
#ad5e99
■
12
2014
13
Sand Dollar
#decdbe
■
13
2006
14
Marsala
#964f4c
■
14
2015
15
Ultimate Grey
#939597
■
15
2021
There are no rows in this table
⁠



Want to print your doc?
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.