How to Use 4 Color Scale Conditional Formatting in Excel (2 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

Use 4 Color Scale Conditional Formatting with Numbers in Excel

  • Then, under the Home tab, click the Conditional Formatting drop-down icon.
  • There, choose the New Rule.

Use 4 Color Scale Conditional Formatting with Numbers in Excel

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

Use 4 Color Scale Conditional Formatting with Numbers in Excel

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

Use 4 Color Scale Conditional Formatting with Numbers in Excel

  • Consequently, the Format Cells dialog box will appear.
  • Choose your desired color to fill the cells.
  • Press OK.

Use 4 Color Scale Conditional Formatting with Numbers in Excel

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

Use 4 Color Scale Conditional Formatting with Numbers in Excel

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

Use 4 Color Scale Conditional Formatting with Numbers in Excel

  • Hence, it’ll return the cells in blue color which falls in that condition.

Use 4 Color Scale Conditional Formatting with Numbers in Excel

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

Use 4 Color Scale Conditional Formatting with Numbers in Excel

  • In this way, you’ll get the cells in one more color.

Use 4 Color Scale Conditional Formatting with Numbers in Excel

  • Repeat the process for the last time.
  • Select greater than from the second drop-down.
  • Type 15000.
  • Choose color.
  • Then, press OK.

Use 4 Color Scale Conditional Formatting with Numbers in Excel

  • At last, you’ll see the Net Sales column in 4 different colors.

Use 4 Color Scale Conditional Formatting with Numbers in Excel

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: Conditional Formatting with 3 Color Scale in Excel Formula


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.


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.
Aung Shine
Aung Shine

Aung Shine completed his bachelor’s in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. It has been almost 2 years since he joined SOFTEKO and actively working on the ExcelDemy project. Currently he works as a Team Leader where he guides his team members to create technical content. He has published 150+ articles and reviewed 50+ articles. He has also solved various user problems before. He has interests in Data Analysis, Power Query, Advanced Excel,... Read Full Bio

2 Comments
  1. 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.

    It seems a step has been left out.

    • Hello, thanks for reaching out.

      I am not sure which step you’re referring to. If it’s the first drop-down under the Edit the Rule Description, please choose Format only cell with: Cell Value from the options. However, you should see that being selected by default.
      Kindly let us know your further queries.

      Regards,
      Aung
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo