Author: Oscar Cronquist Article last updated on October 15, 2018

Color scales in conditional formatting applies a color to cells in a cell range based on their values, this lets you easily spot maximum and minimum values as well as trends.

How to build

  1. Select cell range.
  2. Go to tab "Home" if you are not already there.
  3. Press with left mouse button on the "Conditional formatting" button.
  4. Press with left mouse button on or hover over "Color scales" with the mouse pointer.
  5. Pick a predefined color scale or press with left mouse button on "More Rules" to define your own scale and colors.

How to customize Color scales

If you want to change the settings for an existing cell range follow these steps:

  1. Select the cell range.
  2. Go to tab "Home" on the ribbon.
  3. Press with left mouse button on "Conditional formatting" button.
  4. Press with left mouse button on "Manage Rules..."
  5. Press with left mouse button on "Edit Rules..."

To create a new color scale and also edit settings before creation then follow these steps:

  1. Select cells.
  2. Go to tab "Home" on the ribbon.
  3. Press with left mouse button on the "Conditional Formatting" button.
  4. Press with left mouse button on "Color scales".
  5. Press with left mouse button on "More Rules..."

The settings dialog box appears.

Here you may change the Format style to a 2-Color Scale or 3-Color Scale. The minimum and maximum drop-down lists allow you to change how min and max values are calculated:

  • Lowest/Highest
  • Number
  • Percent
  • Formula
  • Percentile

Weekly Blog EMAIL

[newsletter_signup_form id=1]
Welcome! I am Oscar and here to help you out.

Feel free to comment and ask Excel questions.

Make sure you subscribe to my newsletter so you don't miss new blog articles.

Related articles

How to use the INTERCEPT function

The INTERCEPT function returns a value representing the y-value where a line intersects the y-axis. The line is calculated using […]

How to find the smallest number excluding zeros

Column B contains numbers, the formula in cell D3 calculates the smallest value excluding zeros. Note that all numbers are […]

How to use the FORECAST function

The FORECAST function calculates a value based on existing x and y values using linear regression. Use this function to […]

How to use the LOGEST function

The LOGEST function returns an array of values representing the parameters of an exponential curve that fits your data, based […]

Identify overlapping date ranges

The formula in cell F6 returns TRUE if the date range on the same row overlaps another date range in […]

How to use the LOGNORM.DIST function

The LOGNORMDIST function calculates the lognormal distribution of argument x, based on a normally distributed ln(x) with the arguments of mean and standard_dev. This […]