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

Get FREE Advanced Excel Exercises with Solutions!

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.

Overview of color scale based on text


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.

Basic Color Scale


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.

Dataset for color scale based on text


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.

Finding Unique Value

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

Adding Conditional formatting

  • After that, we are going to select New Rule.

Adding 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.

Giving Rules

  • 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.

Format Cells

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

Formatting cells containing housing

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

Finally for housing

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

Finall output of color scale using unique text

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

Final Dataset

Read More: Color Scale Per Row with Conditional Formatting in Excel


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.

Selecting cell formatting for length

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

Adding New Rule

  • Now we are going to give the following condition for length.
=LEN($B5:$B15)=9

Formatting Cell for length

  • 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.

After adding all condition cell length

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

Final Output of length condition


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.

Adding color scale for hiding the data later

  • 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.

Hiding Data value

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

Finally hiding data


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.

Setting up a new rule for 3 scale color

  • 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.

New Rule and Color Selection

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

Final 3 scale color scale

Read More: Conditional Formatting with 3 Color Scale in Excel Formula


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

  1. 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.
  2. 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.
  3. 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.
  4. 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.
  5. 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.


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.


Related Articles

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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Joyanta Mitra
Joyanta Mitra

Joyanta Mitra, a BSc graduate in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology, has dedicated over a year to the ExcelDemy project. Specializing in programming, he has authored and modified 60 articles, predominantly focusing on Power Query and VBA (Visual Basic for Applications). His expertise in VBA programming is evident through the substantial body of work he has contributed, showcasing a deep understanding of Excel automation, and enhancing the ExcelDemy project's resources with valuable... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo