How To Apply A Color Scale Based On Values in Microsoft Excel

We see colored bars that represent all sorts of things: temperatures, speed, age, and even population. If you have data in Microsoft Excel that could benefit from this type of visual is easier to implement than you might think.

With conditional formatting, you can apply a gradient scale in minutes. Excel offers two- and three-tone scales with primary colors to choose from, along with the option to pick your own unique colors.

Apply a color scale for quick conditional formatting

Microsoft Excel gives you several conditional color bar formatting rules that you can apply with a quick click. This includes six two-color scales and six three-color scales.

Select the cells you want to apply the formatting to by clicking and dragging through them. Then go to the Styles section of the ribbon on the Home tab.

Click on “Conditional Formatting” and move the cursor to “Color Bars”. You will see all 12 options in the pop-out menu.

If you move the mouse pointer over each one, you can see the arrangement of the colors in a screen tip. Also, the cells you selected will be highlighted with each option. This gives you an excellent opportunity to choose the color gamut that best suits your data.

When you land on the scale you want to use, just click on it. And that’s it! You have just applied a color scale to your data in just a few clicks.

Create a custom color gamut for conditional formatting

If any of the quick rules above doesn’t quite capture how you want your color gamut to work, you can create a custom conditional formatting rule.

Select the cells you want to apply scaling to, go to the Home tab and choose New Rule from the Conditional Formatting drop-down list.

When the New Formatting Rule window opens, at the top select Format All Cells Based On Their Values.

In the Edit Rule Description section at the bottom of the window, you will spend some time customizing the rule. First, choose 2-color scale or 3-color scale from the Format Style drop-down list.

The main difference between these two styles is that the three-tone scale has a midpoint, while the two-tone scale has only minimum and maximum values.

After choosing the color gamut style, select the minimum, maximum, and optionally midpoint from the Types drop-down lists. You can choose between lowest / highest value, number, percent, formula or percentile.

The Lowest Value and Highest Value types are based on the data in your selected range of cells, so you do not need to enter anything in the Value fields. For all other types, including Midpoint, enter values in the appropriate fields.

Finally, click the Color dropdown buttons to choose your colors from the palettes. To use custom colors, select More Colors to add them with RGB values or Hex codes.

You will then see a preview of your color scale at the bottom of the window. When you’re satisfied with the result, click OK to apply the conditional formatting to your cells.