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.
- 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).
- 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.
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.
- 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.
- 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
- How to Use Excel Color Scale Based on Text
- How to Use 4 Color Scale Conditional Formatting in Excel
- How to Use Conditional Formatting with 5 Color Scale in Excel
<< Go Back to Conditional Formatting | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
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.