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. Click the "Conditional formatting" button.
  4. Click or hover over "Color scales" with the mouse pointer.
  5. Pick a predefined color scale or click "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. Click "Conditional formatting" button.
  4. Click "Manage Rules..."
  5. Click "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. Click the "Conditional Formatting" button.
  4. Click "Color scales".
  5. Click "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