Color Scale Per Row with Conditional Formatting in Excel

Get FREE Advanced Excel Exercises with Solutions!

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

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. 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: Excel Conditional Formatting Color Scale Based on Another Cell


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.

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

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


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.
Mrinmoy Roy
Mrinmoy Roy

Mrinmoy Roy, a dedicated professional with a BSc in Electronics and Communication Engineering from Khulna University of Engineering & Technology, Bangladesh, brings over two years of expertise to the ExcelDemy project. As a prolific contributor, he has authored around 180 articles, showcasing his deep knowledge and passion for Microsoft Excel, Data Analysis, and VBA. His unwavering commitment to continuous learning, combined with versatile skills, renders him well-suited for roles in data management and spreadsheet solutions. He has interest... Read Full Bio

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 Avatar photo
    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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo