Color Scale Per Row with Conditional Formatting in Excel

In Microsoft Excel, the Color Scale uses a specific color for the minimum value and another for the maximum value. In between the minimum and maximum values, a gradient color scheme is used by mixing the colors for the minimum and maximum values. You can apply the color scale for every individual row using Conditional Formatting. In this article, I will show you 2 ways to apply the conditional formatting color scale per row in Excel. So without having any further discussion, let’s get started.


Download Practice Workbook

You can download the Excel file from the following link and practice along with it.


2 Methods to Apply Conditional Formatting Color Scale Per Row in Excel

I will use the following Annual Profit Record for various companies to show you how to apply the conditional formatting color scale per row. In the Company column, there is a list of a few companies. The following columns have their corresponding annual profit records.


1. Using Conditional Formatting Color Scale Command

In the Conditional Formatting Color Scale group, there is a bundle of color palette presets. There you will find some sample color palettes that you can use to apply the color scale per row on your dataset.

Now I will show you how to use those color palettes on your dataset.

❶ First, select the dataset having numerical values.

❷ Then go to the Home tab.

Using Color Scale to Apply Conditional Formatting Color Scale Per Row in Excel

❸ In the Styles group, click on the Conditional Formatting drop-down.

❹ From the drop-down list, select Color Scales.

After that, you will see a whole bunch of color palettes. Choose any one of them.

Here, I’m selecting the White-Red Color Scale.

Using Color Scale to Apply Conditional Formatting Color Scale Per Row in Excel

Now, look at your selected dataset. The minimum value has been highlighted with the White color. And the maximum value has been highlighted with the Red color.

All the values in between the maximum and minimum values have been highlighted with a gradient of Red and White colors.

Using Color Scale to Apply Conditional Formatting Color Scale Per Row in Excel

That’s it. This is the simple procedure to apply Excel conditional formatting color scale per row using a color scale.

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


2. Using New Rule to Apply Conditional Formatting Color Scale Per Row

Now, I will use the Excel New Rule option to create rules that applied a conditional formatting color scale per row on the dataset.

To do that,

❶ First, highlight the dataset having numerical values.

❷ Then go to the Home tab.

❸ Select the Conditional Formatting drop-down in the Styles group.

❹ From the drop-down menu select the New Rule command.

Using New Rule to Apply Conditional Formatting Color Scale Per Row in Excel

The New Formatting Rule dialog box appears.

❺ Select ‘Format all cells based on their values’.

❻ Choose ‘2-Color Scale’ beside the Format Style.

After that, in the Minimum and Maximum sections, you will see that Lowest Value and Highest Value are preselected. Keep this choice.

Then will see Orange color is preselected for the minimum values. And Gold color is preselected for the maximum values. You can choose your preferred colors.

I’m keeping this color choice.

❼ The next step is to hit the OK button to apply the new rules.

Using New Rule to Apply Conditional Formatting Color Scale Per Row in Excel

The New Rule has been applied to the highlighted cells.

The minimum value has been highlighted with the Orange color. And the maximum value has been highlighted with the Gold color.

All the values in between the maximum and minimum values have been highlighted with a gradient of Orange and Gold colors.

Thus, in Excel, you can apply a conditional formatting color scale per row.

Read More: Conditional Formatting with 3 Color Scale in Excel Formula


Practice Section

You will get an Excel sheet like the following screenshot, at the end of the provided Excel file where you can practice all the topics discussed in this article.


Conclusion

To sum up, we have discussed 2 methods to apply conditional formatting color scale per row in Excel. Please don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website ExcelDemy to explore more.


Related Articles

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

2 Comments
  1. how do you set the conditional formatting for the set, but the scale is individualized for each row (not all rows data combined)?

  2. Reply
    Naimul Hasan Arif Aug 25, 2022 at 4:52 PM

    As the scale is different for each row, you can apply conditional formatting separately on each row with different colors. I hope that’s the simplest way to do so.

Leave a reply

ExcelDemy
Logo