How to Use Conditional Formatting with 5 Color Scale in Excel (2 Methods)

Get FREE Advanced Excel Exercises with Solutions!

The article will show you how to use conditional formatting with 5 color-scale in Excel. Formatting data will help you to find out or analyze your worksheet in a very convenient way. Usually, Excel has the feature of formatting by 2 or 3 color-scale. But if you have a large dataset, it will be a bit difficult to differentiate or compare the data with 2 or 3 color-scale formatting. In this article, I’ll illustrate some procedures on how to deal with this 5 color-scale formatting.


How to Use Conditional Formatting with 5 Color Scale in Excel: 2 Ways

In the dataset, we have financial information about some wrestling superstars: their Yearly Income and Net Worth. I’ll show you how to format these data with 5 color-scale in the following sections of the article.

excel conditional formatting 5 color scale


1. Using Built-In Command to Apply Conditional Formatting with 5 Color Scale

You can easily use some built-in commands for 5 color-scale conditional formatting. Here, I’ll format the Yearly Income. Let’s go through the procedure below for a better understanding.

Steps:

  • First, select the Yearly Income and then go to Conditional Formatting >> Manage Rule.

  • After that, you will see the Conditional Formatting Rules Manager Select New Rule. (Or you can follow Conditional Formatting >> New Rule.)

excel conditional formatting 5 color scale

  • Thereafter, the New Formatting Rule window will open up. Select any Rule Type of your choice. In my case, I prefer to choose ‘Format only cells that contain’.
  • Next, set a range in the ‘Edit the Rule Description’ section.
  • Later, select Format…

  • You will see the Format Cells window after that command. Select Fill and choose any color of your convenience.
  • Next, click OK.
  • This will set the cell background color.

excel conditional formatting 5 color scale

  • You need to choose a suitable Font Color so that it will be highlighted perfectly with the cell background. For that reason, select a Font and a suitable Font Color. If you want to fill the cell background with a darker color, it will be good for you to select a lighter font color.
  • In addition, you can also select other options like Font Style or effects. In this case, I just selected Bold Italic.
  • Click OK.

  • After that, the New Formatting Rule window will appear again. Just click OK.

excel conditional formatting 5 color scale

  • Thereafter, you will see that the rule has been added to the Conditional Formatting Rules Manager. This will format the cells that contain this range of values. To set another rule, click on New Rule…

  • Set a new range of money like before.

excel conditional formatting 5 color scale

  • And here I made four different additional rules to format the cells for 5 color-scale conditional formatting following a similar process in this section.
  • After setting these rules, click OK.

  • Finally, you will see the data formatted to 5 color-scale.

excel conditional formatting 5 color scale

By following the process of this section, you can easily use conditional formatting with 5 color-scale in Excel. In addition, you can use more than 5 color-scale formatting using this method if you want.

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


2. Applying Formula to Utilize Conditional Formatting with 5 Color Scale

Another way to use conditional formatting with 5 color-scale is to create 5 different rules for formatting using formulas. In this section, I’ll format the Net Worth values.

Here, I’m showing you the rules that I created. You can see that there are five different formulas for 5 color-scale conditional formatting and also the ranges that contain those formulas.

The necessary steps to make these rules are shown below.

Steps:

  • First, select the Net Worth values and then go to Conditional Formatting >> Manage Rules.

excel conditional formatting 5 color scale

  • After that, select New Rule… from the ‘Conditional Formatting Rules Manager’ window.

  • Thereafter, choose ‘Use a formula to determine which cells to format’ and type the following formula in it.

=D5

  • Next, select Format…

excel conditional formatting 5 color scale

  • In the Format Cells window, select a font color of your choice and click OK.

  • After that, select a fill color for your convenience and click OK.

excel conditional formatting 5 color scale

  • The Edit Formatting Rule will appear again. Just click OK.

  • After that, the formula will be added to the Rules Manager as the number one rule. You can edit the range for the rules after that.
  • In a similar process, I added the four other formulas and ranges that you can see in the following image one by one. You may apply your own formulas or functions. In the second rule, we used the OR function.
  • Next, click OK.

excel conditional formatting 5 color scale

Finally, we will see our data formatted to 5 color-scale.

By applying this process, you can easily use conditional formatting with 5 color-scale in Excel. In addition, you can use more than 5 color-scale formatting using this method if you want.

Read More: How to Use 4 Color Scale Conditional Formatting in Excel


Practice Section

Here, I’m giving you the dataset of this article so that you can practice these methods on your own.

excel conditional formatting 5 color scale


Download Practice Workbook


Conclusion

Suffice it to say, that you will learn some easy and effective ways of how to use conditional formatting with 5 color-scale in Excel. If you have any better methods, questions, or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles.


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.
Meraz Al Nahian
Meraz Al Nahian

Md. Meraz Al Nahian has worked with the ExcelDemy project for over 1.5 years. He wrote 140+ articles for ExcelDemy. He also solved a lot of user problems and worked on dashboards. He is interested in data analysis, advanced Excel, statistics, and dashboards. He also likes to explore various Excel and VBA applications. He completed his graduation in Electrical & Electronic Engineering from Bangladesh University of Engineering & Technology (BUET). He enjoys exploring Excel-related features to gain efficiency... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo