Mrinmoy Roy
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.
How to Apply Conditional Formatting Color Scale Per Row in Excel: 2 Methods
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.
❸ 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.
Now, look at your selected dataset. The minimum value has been highlighted with the White color. 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.
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 apply 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.
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 the Lowest Value and Highest Value are preselected. Keep this choice.
Then will see Orange color is preselected for the minimum values. The 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.
The New Rule has been applied to the highlighted cells.
The minimum value has been highlighted with the Orange color. 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.
Download Practice Workbook
You can download the Excel file from the following link and practice along with it.
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.
how do you set the conditional formatting for the set, but the scale is individualized for each row (not all rows data combined)?
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.