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

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 to use 4 Color Scale Conditional Formatting in Excel.


Download Practice Workbook

Download the following workbook to practice by yourself.


2 Ideal Examples to Use 4 Color Scale Conditional Formatting in Excel

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. First criteria: Less than 5,000, second criteria: between 5,000 and 10,000, third criteria: between 10,000 and 15,000, and the last criteria: 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: How to Use Conditional Formatting with 5 Color Scale in Excel (2 Methods)


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. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.

Aung

Aung

I'm Aung. Recently I earned my B.Sc. Degree in Electrical and Electronic Engineering. From now on, I will be working in Microsoft Excel and other useful software, and I’ll upload articles related to them. My current goal is to write technical contents for anybody and everybody that will make the learning process of new software and features a happy journey.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo