Aung Shine
We can use Excel to store important data. In Excel, it’s easier to fill those datasets with distinct colors based on the cell values. So, we can effortlessly identify our desired targets. We can apply the Conditional Formatting feature to our dataset to format the cells with colors. However, you can only choose either a 2-color or a 3-color scale. Now, if you want to apply 4-color scale Conditional Formatting, you have to follow some tricks. In this article, we’ll show you 2 ideal examples of using 4-color scale Conditional Formatting in Excel.
How to Use 4 Color Scale Conditional Formatting in Excel: 2 Ideal Examples
To illustrate, we’ll use a sample dataset as an example. For instance, in the following dataset, we have Salesman, Product, and Net Sales. Here, we’ll apply the 4-color scale Conditional Formatting to the Net Sales column. We’ll use 4 criteria for that purpose. The first criterion is less than 5,000, the second criterion: is between 5,000 and 10,000, the third criterion: is between 10,000 and 15,000, and the last criterion is greater than 15,000. Therefore, go through the examples carefully to perform the task.
1. Use 4 Color Scale Conditional Formatting with Numbers in Excel
Excel has a maximum 3-color scale by default. So to apply 4-color scale, follow the steps below.
STEPS:
- First, select the range D5:D10.
- Then, under the Home tab, click the Conditional Formatting drop-down icon.
- There, choose the New Rule.
- As a result, a dialog box will pop out.
- Click the Format Style drop-down.
- You’ll see that the 2-Color Scale and 3-Color Scale are present. But, you won’t get the 4-Color Scale.
- Now, select the second Rule Type, pointed in the red arrow.
- Next, choose less than from the second drop-down under the Edit the Rule Description.
- After that, type 5000 in the third box.
- Subsequently, press Format.
- See the below picture to understand better.
- We have to repeat these steps multiple times in the future. So, learn thoroughly.
- Consequently, the Format Cells dialog box will appear.
- Choose your desired color to fill the cells.
- Press OK.
- This will take you to the previous dialog box.
- Press OK.
- Thus, you’ll see the cell which has a value lower than 5000 in green color.
- Again, go to Conditional Formatting ➤ New Rule.
- This time, select between from the second drop-down.
- Type 5000 and 10000 in the third and fourth drop-downs respectively.
- Choose the desired color from the Format Cells dialog box.
- Afterward, press OK.
- Hence, it’ll return the cells in blue color which falls in that condition.
- Repeat the above steps for the 3rd color.
- Type 10000 and 15000 in the 3rd and 4th drop-downs.
- Select the desired color.
- Subsequently, press OK.
- In this way, you’ll get the cells in one more color.
- Repeat the process for the last time.
- Select greater than from the second drop-down.
- Type 15000.
- Choose color.
- Then, press OK.
- At last, you’ll see the Net Sales column in 4 different colors.
Read More: Excel Conditional Formatting Color Scale Based on Another Cell
2. Apply Excel 4 Color Scale Conditional Formatting on Percentage Values
In this example, we’ll work with Percentage values instead of Number values. We’ll use the below dataset which has Profit percentages. Therefore, learn the following process to carry out the operation.
STEPS:
- Firstly, choose the range D5:D10.
- Then, select New Rule from the Conditional Formatting drop-down.
- If you want to apply the 3-Color Scale, select the Percent type as shown in the marked boxes.
- To apply, the 4-Color Scale, choose the 2nd Rule Type.
- Select less than or equal to from the 2nd drop-down.
- Type 5% in the 3rd box.
- Select the color by pressing the Format.
- Next, press OK.
- Repeat the above process.
- Click greater than.
- Afterward, type 15%.
- Select the color you wish.
- Subsequently, press OK.
- Again, repeat the steps.
- Now, choose between.
- Type 6% and 10% in the marked fields.
- Click the color to fill the cells.
- Press OK.
- Finally, go through the process to apply the 4th color.
- Select between, type 11% and 15% in the demonstrated boxes.
- After choosing a color, press OK.
- Thus, it’ll return the dataset in 4 unique colors.
- Each color denotes specific criteria.
Read More: How to Use Conditional Formatting with 5 Color Scale in Excel
Download Practice Workbook
Download the following workbook to practice by yourself.
Conclusion
Henceforth, you will be able to use 4 Color Scale Conditional Formatting in Excel following the above-described examples. Keep using them and let us know if you have more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.
