How to Use Excel Color Scale Based on Text (2 Suitable Examples)

You can apply color scales based on values. This is helpful if you want to rapidly recognize particular values depending on their color. Use color scales, for instance, to call attention to particular values in a list or to emphasize their significance in a document. This lesson will cover how to use Conditional Formatting to apply Excel color scale based on text values.

Color Scales and How to Add Them in Excel

Implementing a basic color scale is very easy. First, you have to select the column and add color scales from Home>>Conditional Formatting>>Color Scales and then Select any Color Scales you like.

Issues with Adding Color Scale Based on Text Values in Excel

With the method described in the previous section, we can not use color scales with texts but with numbers. Because color scales automatically determine comparison in a column with numbers but they cannot compare texts. So we have to resort to conditional formatting when it comes to text.

How to Create Color Scale Based on Text in Excel: 2 Ways

In this section, we are going to create color scales using conditional formatting. There is no default color scale for text so we have to use conditional formatting for unique text and use unique color. To do so, we are going to use the dataset given below and follow the examples I have mentioned below.

1. Color Scales Based on Unique Text

We are going to first find out unique text using the UNIQUE function and then we are going to format the cells using conditional formatting. The UNIQUE function (available in Excel 365 only) is helpful to find out all the unique values in a range. So we have to follow the steps below.

First, we have to find out the unique value from the data set by using the following formula in our dataset.

=UNIQUE(B5:B15)

Then press Shift+Enter on keyboard.

Then we are going to select the “Category” column and select Conditional Formatting.

After that, we are going to select New Rule.

Now we are going to use new rules like below.

=$F$5=$B5:$B15

Here F5 cell is a unique value “Housing” and it will change the format according to when cells($B5:$B15) text equals to that.

In the Format Cells box, we are going to select light green to fill the cell. You can opt for your own color based on your preferred color scale.

Press OK in both the Format Cells and New Formatting Rule boxes.

Finally, we got the colored cells having the text “Housing”.

Likewise, we have to create individual rules for each unique category.

After all, the whole dataset looks like a color scale based on text.

2. Color Scales Based on Text Length

It is like the previous example but we have used different rules. Here, color scaling will be according to the length of the word.

First, we are going to select the Category column and then select Conditional Formatting.

Then we are going to select New Rule from the manager.

Now we are going to give the following condition for length.

=LEN($B5:$B15)=9

We are going to select the color dark blue Fill as the formatting option. You can select anything you want.

Also, we have to give individual length formulas by again using New Formula.

Finally, we get the output color scales according to text lengths.

How to Add Color Scales Without Values in Excel

Here we are going to add color scales without values in Excel. Here, we will just use a color scale and then hide the values. This is helpful when you don’t want the numbers to jam up the visuals in the dataset and want the intensity of certain values instead.

First will add color scales in the cells.

Now we press Ctrl+1 and the Format cells will appear.
We select Number>>Custom>>Type and insert ;;; and thus it will hide all the data in the column.

Now finally we have got the color scale without data in the column.

How to Add 3-Color Scale in Conditional Formatting

In this section, we are going to add a 3-Color scale in conditional formatting. In order to do so, we have to follow the steps given below.

First, we have to select the expenses column and press Alt+O+D then Conditional Formatting Rules Manager will appear.

After that, we are going to select Format all cells based on their values and press the drop-down button to select 3-Color Scale and choose color sets as you want.

Finally, we get the 3-color scales according to values.

Frequently Asked Question

Q: Can I add color scales to text values in any version of Excel using the Color Scale feature?

A: The majority of Excel versions, including Excel 2010, 2013, 2016, and 2019, include the Color Scale feature. Nevertheless, depending on the version of Excel you are using, the precise methods for using the function may change slightly.

Q: In Excel, can I make my own unique color swatches for text values?

A: Custom color scales for text values can be made in Excel. Select “More Rules” at the bottom of the dropdown menu after choosing “Color Scales” from the Conditional Formatting menu. From this point, you can develop your own rules based on your unique requirements.

Q: Can I use color gradations on individual words rather than the entire cell?

A: The Excel Color Scale option only applies to whole cells. To apply formatting to specific text within a cell, you can utilize other Conditional Formatting options, such as Highlight Cell Rules.

Things to Remember

Based on the text values you wish to highlight, select the proper color scale. For instance, you could use a color scale that goes from red (least important) to green (most important) to draw attention to particular terms in a document.
To fit your data range, modify the minimum and maximum numbers or formulas for the color scale. By doing this, you can be certain that the color scale is used consistently throughout your data.
It’s important to keep in mind that the Color Scale feature only affects entire cells, not individual words. Consider using other Conditional Formatting options, such as Highlight Cell Rules, if you need to apply formatting to specific text within a cell.
Recognize the limitations of color gradations. They are not a replacement for in-depth data analysis, but they can be effective tools for showing trends and patterns.
Finally, keep in mind that color scales can be modified to meet your unique requirements. Never be afraid to experiment with various color settings and scales to discover the one that best suits your data.

Download Practice Workbook

You can download the Excel workbook that we used to prepare this article.

Color Scale Based on Text.xlsx

Conclusion

In conclusion, even though Excel lacks a specific “Color Scale” function for text values, it is still possible to use custom conditional formatting rules to apply a color scale to text values. Users can apply color scales that help discover patterns and trends in data by setting custom rules based on certain text values, making it simpler to study and comprehend. The ideal color scale may need some trial and error and adjustment, but the finished product may be a potent tool for improving data processing and decision-making.

<< Go Back to Color Scales | Conditional Formatting | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!