Скачать или смотреть How to Conditionally Format Text in Google Sheets for Unique Colors

Learn how to conditionally color alphanumeric text in Google Sheets using unique formulas for dynamic formatting.
---
This video is based on the question https://stackoverflow.com/q/74786168/ asked by the user 'Inetquestion' ( https://stackoverflow.com/u/1773021/ ) and on the answer https://stackoverflow.com/a/74799375/ provided by the user 'doubleunary' ( https://stackoverflow.com/u/13045193/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: How do you conditionally format arbitrary text in google sheets?

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Conditionally Format Text in Google Sheets for Unique Colors

If you’ve ever dealt with datasets containing alphanumeric values in Google Sheets, you may have wondered how to highlight or color those text strings based on their content. Unlike numbers, color scales and formatting options for text can feel limited, especially when you don't know the possible string values in advance. In this post, we’ll show you a powerful formula that allows you to conditionally format arbitrary text so that similar or identical strings share the same color. Let’s dive in!

Understanding the Problem

In a typical scenario, you might be looking to color an entire column based on the alphanumeric text it contains. For instance, if you have the following data:

Apples

Bananas

Apples

Oranges

Bananas

You may want all instances of "Apples" to have one color, "Bananas" a different color, and "Oranges" a third distinct color. However, the challenge arises when you don’t know all possible strings ahead of time. That's where our solution comes in.

The Solution: Using a Unique Formula

To accomplish this color-coding dynamically, we will use a combination of functions in Google Sheets. Follow these steps:

Step 1: Preparing Your Data Column

Assume that your text strings are located in column A, starting from A2. We will create a formula that converts these strings into unique numerical values.

Step 2: Enter the Formula

In a free column (for example, column B), enter the following formula in row 2:

[[See Video to Reveal this Text or Code Snippet]]

Step 3: Understanding the Formula

Let’s break down what this formula does:

arrayformula: It enables the formula to work over a range of cells rather than a single cell.

lambda and map: These allow us to apply a custom function to each item in the data array.

if and countif: These check if the occurrences of each text string are less than 2. If so, we skip it to avoid color coding unique texts.

mod and code: These functions convert characters to numbers and generate a unique number for each distinct text string.

Step 4: Applying Color Scales

Once you've applied the formula, you’ll notice that your new column (let’s say column B) contains unique numerical values corresponding to the text strings in column A. Now, it’s time to apply color formatting:

Highlight the range in column B.

Go to Format Conditional formatting.

Select Color scale from the options available.

Adjust your color choices as desired.

Step 5: Result

After applying the color scale, similar text strings in column A will now share the same color coding based on their unique numerical representation from column B. This method turns an unorganized text list into a visually digestible format where duplicates stand out.

Conclusion

By utilizing a straightforward array formula and color scales, you can efficiently manage and visualize alphanumeric text in Google Sheets. This eliminates guesswork, transforming your data into an effortlessly readable format where similar strings are color-coded. With just a few steps and a simple formula, you can enhance your data presentation and analysis significantly.

Now that you know how to conditionally format arbitrary text in Google Sheets, give it a try on your own dataset and see the magic unfold!