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.
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.)
- 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.
- 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.
- 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.
- 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.
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.
- 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…
- 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.
- 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.
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.
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
- Conditional Formatting with 3 Color Scale in Excel Formula
- Excel Conditional Formatting Color Scale Based on Another Cell
- How to Use Excel Color Scale Based on Text
<< Go Back to Color Scales | Conditional Formatting | Learn Excel