How to Format Cells with Color Scales in Your Excel Worksheet

In an Excel worksheet, you may need to format certain cells to make them different from others. Here we will introduce the method of using the color scales to fulfill this task.

Sometimes in your Excel worksheet, you will format cells according to their values. For example, in the image below, now we need to format the cells according to the sales volume.An Example of Color Scales

And instead of formatting cells manually, now you can use the feature of color scales in Excel.

Use Color Scales

  1. Select the target area in the worksheet.
  2. And then click the button “Conditional Formatting” in the toolbar.
  3. After that, move your cursor on the option “Color Scales”. Thus, a sub menu will appear.Color Scales
  4. There are 12 pre-defined options in the sub menu. You can choose one of them. And here we also choose one in the menu.

Next you will immediately see the result in the worksheet. In the range, different cells will be filled with a different color according to their values.

Cell Format

Edit Color Scales

Instead of using the predefined color scales in the worksheet, you can also custom the scales by yourself. If you have never used the color scales in the worksheet, you can click the option “More Rules” in step 4 in the former part. On the other hand, if you have already used the color scales, you can follow the steps below and modify the settings.

  1. Put your cursor within the range. You can also select the whole area.
  2. And then click the button “Conditional formatting” in the toolbar.
  3. After that, click the option “Manage Rules” in the drop-down menu.Manage Rules
  4. In the newly pop-up window, choose the rule in the window.
  5. And then click the button “Edit Rule”. Besides, you can also directly double click the rule to edit it.Edit Rule
  6. In the “Edit Formatting Rule” window, click the small arrow in the text box of the “Minimum” in the “Type” area.
  7. Next you will see a drop-down list. There are five options available. And you can choose the option that your need.Change Type
  8. Now repeat the step 6 and 7 and change the type for the “Maximum”.
  9. Except for the option “Lowest Value” and the “Largest Value”, you need to set values for other options. Here we choose “Number” for the type. And we directly input the numbers into the two text boxes of “Values”. Besides, you can use you mouse and directly choose values from the worksheet.Change Value
  10. This is another key step in the setting. You need to set colors for the rule. First click the small arrow in the box.Set Color
  11. And then you will see the “Theme Color” menu pop up. Here you can choose one color in the menu. But if you need to set a specific color, click the option “More Color.
  12. In the “Colors” window, set the color that you need.Choose a Color
  13. And then click “OK”.
  14. Now repeat the step 10-13 and continue setting the color for the “Maximum”.
  15. When you have finished all the settings, click the button “OK” in the window.Click OK
  16. Next continue clicking “OK” in the manager window.Continue Click OK

Thus, the new setting will take effect in the target range.

New Setting

Change Format Style

In addition, sometimes there are more data in your target range, and 2-color scale is not enough for the range. Therefore, you can also choose to change the format style of the color scales. In the submenu of color scales, there are many options. And there are 8 options which are actually 3-color scale. You can choose one of them in this list.3 Color Options

But when you need to change the existing 2-color scale into 3-color scale, you can also modify it through the following steps.

  1. Repeat the first 5 steps in the previous part.
  2. Next click the small arrow in the text box of the “Format Style”.
  3. And then in the drop-down list, choose the “3-Color Scale”.3 Color Scale
  4. Thus, you can see that there is a new color option. Here you can set the type and the color for the midpoint.Midpoint
  5. When you finish the setting, click “OK” in the window.
  6. In the rules manager window, still click “OK”. Thus, the new color scale will take effect in the target range.

New Color Scale

Save Your Data from a Damaged File

When you find that your Excel corrupts, do not panic. We have diagnosed many cases and give such a conclusion: most of the lost data are actually recoverable. Therefore, you only need to take the right action and save your data from the damaged file. However, there is no universal approach for all types file corruption. You can contact us and figure out the reason behind the data disaster. Besides, you can also repair Excel corruption by our potent Excel recovery tool. Most of the errors can be fixed easily with this application.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair docx file problem and outlook repair software products. For more information visit

One response to “How to Format Cells with Color Scales in Your Excel Worksheet”