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.

Preview of the color bars in Excel

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.

On the Home tab, click Conditional Formatting, New Rule

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

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.

2-color and 3-color scale setup

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.

Select the type for the color scale

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.

Choose the colors for the scale

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.

Conditional formatting of the 3-color scale in Excel

The beauty of a conditional formatting rule like this is that as you edit your data, the color gamut is automatically updated to reflect the change.

The color scale changes based on changes in data

For a similar display of your Excel Dates, you should use the Data Bar conditional formatting rule to create a progress bar.

Related Posts