How to Color Scale Per Row with Conditional Formatting in Excel (2 Methods)

Dataset Overview

We’ll use the following Annual Profit Record for various companies to demonstrate these methods. In the Company column, there is a list of a few companies. The following columns have their corresponding annual profit records.


Method 1 – Using Conditional Formatting Color Scale Command

  • Choose the dataset containing numerical values.
  • Go to the Home tab in Excel.

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.

You’ll see various color palettes. Pick one (e.g., White-Red Color Scale).

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

  • Observe your dataset: the minimum value is highlighted in white, and the maximum value in red.
  • All values in between are displayed with a gradient of red and white.

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

Read More: Excel Conditional Formatting Color Scale Based on Another Cell


Method 2 – Using New Rule for Conditional Formatting Color Scale Per Row

  • Select the dataset with numerical values.
  • Navigate to the Home tab.

  • Click the Conditional Formatting drop-down in the Styles group.
  • Select New Rule.

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

  • In the New Formatting Rule dialog box:
    • Choose Format all cells based on their values.
    • Select 2-Color Scale as the format style.
    • Lowest and highest values are preselected.
    • Customize colors if desired (e.g., orange for minimum, gold for maximum).
  • Click OK to apply the new rule.

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

  • The minimum value will be highlighted in orange, the maximum in gold.
  • Intermediate values will display a gradient between these colors.

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


Download Practice Workbook

You can download the practice workbook from here:


Related Articles

<< Go Back to Conditional Formatting | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
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