Can I automatically conditionally format entire column with different background colors based on contents?

Question

A common situation I encounter when working with a sheet is to have data such as this (in CSV format):

-, A, B
1, ColA, ColB
2, one, value1
3, two, value2
4, three, value1
5, four, value2
6, five, value3

etc.

To help understand the data at a glance, I would like to be able to conditionally format the values in ColB to have a different background color based on their contents - e.g. value1 would become red, value2 would become blue, value3 would become green etc.

It is straightforward to do this manually via 3+ conditional formats, but I would like to do this via a generic formula that can be copied into any spreadsheet, and would format the contents of each cell based on its contents without adjusting. Ideally a wide spread of colors would be used for the different values, and so with 3 distinct values in the column these would be colored something like red, green, and blue; with 6 distinct values it would be something like these plus yellow, purple, and orange; with 100 distinct values these would be colored with 100 colors spread around the color wheel etc.

Is something like this possible, and if so how can this be done?

Answer 1

Proof of Concept

This solution is front-end loaded. It requires the initial setup of a named range and one rule for each color. Once set up, rules can be reused in other sheets with minimal hassle and moved around to change the range.

Prep Required

Named Range

• Name is arbitrary but would require a lot of effort to change later
• the range can be easily changed in the future by simply editing the Named Range
  e.g. B:B could become C4:C500, or F:F, etc.

Name	Range	Notes
_data	B:B	- This is the data you want to color

Conditional Formatting

• uses a Custom Formula
• 1 rule for each color you want applied
• each rule (color) increments the row in the final INDEX function by 1

# Basic Syntax

=AND(INDEX(INDIRECT("_data",ROW()))<>"",
     INDEX(INDIRECT("_data",ROW()))=
       INDEX(SORT(UNIQUE(INDIRECT("_data"))),1))

# Rule 2

=AND(INDEX(INDIRECT("_data",ROW()))<>"",
     INDEX(INDIRECT("_data",ROW()))=
       INDEX(SORT(UNIQUE(INDIRECT("_data"))),2))

# Rule 3

=AND(INDEX(INDIRECT("_data",ROW()))<>"",
     INDEX(INDIRECT("_data",ROW()))=
       INDEX(SORT(UNIQUE(INDIRECT("_data"))),3))

# Rule 4

=AND(INDEX(INDIRECT("_data",ROW()))<>"",
     INDEX(INDIRECT("_data",ROW()))=
       INDEX(SORT(UNIQUE(INDIRECT("_data"))),4))

# and so on

10 Rule/Color Example

Example Moving Rules

To show the solution is portable, I'll move the rules from B:B to a different data set in C20:C100. It is a simple matter to adapt this to another sheet in your spreadsheet or even copy the sheet to a completely different spreadsheet, then copy over the formatting and delete the original sheet.

1. I select and copy the existing data without formatting to C20 to simulate an entirely new set of data
2. Copy the Conditional Formatting from a cell in the old range and paste it to the new range C20:C100. Rules automatically update to include the new range however they will not be applied properly, when at all.
3. Remove conditional formatting from old range by either selecting the range and then Format menu > Clear formatting (CTRL+\) or alternatively copy a cell with no conditional formatting then Paste Special > Conditional formatting only to the old range
4. Update Named Range _data to match new range C20:C100
5. Trigger a rule update by using _data in some way. For example, enter =_data in any cell For some reason Sheets will stubbornly refuse to acknowledge the update without this trigger.

Step 1: New Range

 

Step 2: Copy Formatting

 

Step 3: Conditional Formatting cleared from old range
             Not yet working properly on new range

 

Step 4 & 5: _data updated and refresh triggered