If you want to use a color scale in Microsoft Excel, you can do so by using the VLOOKUP function. This function can be used to lookup a color value in a range of cells. The following steps will show you how to apply a color scale based on values in Microsoft Excel:
- Open Microsoft Excel and create a new worksheet.
- Enter the following data into the worksheet:
- Select the range A1:D5 and enter the VLOOKUP function into the cell below it (A1).
- In the Function Arguments box, enter =VLOOKUP(A1,D5) and press ENTER.
- The VLOOKUP function will return a value for blue (B3). To see this value, select B3 and press F2 to open the Format Cells dialog box. In this dialog box, under Color, click on Blue and then click OK.
- To apply this color scale to all cells in the range A1:D5, select B3 and press CTRL+C (or CMD+C on Macs). This will copy B3’s color value to all other cells in this range as well (A1:D5). ..
We see color scales representing all sorts of things: temperatures, speed, ages, and even population. If you have data in Microsoft Excel that could benefit from this type of visual, it’s easier to implement than you might think.
With conditional formatting, you can apply a gradient color scale in just minutes. Excel offers two- and three-color scales with primary colors that you can select from, along with the option to pick your own unique colors.
Apply a Quick Conditional Formatting Color Scale
Microsoft Excel provides you with several conditional formatting rules for color scales that you can apply with a quick click. These include six two-color scales and six three-color scales.
Select the cells that you want to apply the formatting to by clicking and dragging through them. Then, head to the Styles section of the ribbon on the Home tab.
Click “Conditional Formatting” and move your cursor to “Color Scales.” You’ll see all 12 options in the pop-out menu.
As you hover your cursor over each one, you can see the arrangement of the colors in a screen tip. Plus, you’ll see the cells that you’ve selected highlighted with each option. This gives you a terrific way to select the color scale that best fits your data.
When you land on the scale that you want to use, simply click it. And that’s all there is to it! You’ve just applied a color scale to your data in a few clicks.
Create a Custom Conditional Formatting Color Scale
If one of the quick rules above doesn’t quite capture how you want your color scale to work, you can create a custom conditional formatting rule.
Select the cells that you want to apply the scale to, go to the Home tab, and choose “New Rule” from the Conditional Formatting drop-down list.
When the New Formatting Rule window opens, select “Format All Cells Based on Their Values” at the top.
The Edit the Rule Description section at the bottom of the window is where you’ll spend a bit of time customizing the rule. Start by choosing 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-color scale has a midpoint, whereas the two-color scale only has minimum and maximum values.
After selecting the color scale style, choose the Minimum, Maximum, and optionally, the Midpoint using the Types drop-down lists. You can pick from 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 don’t have to enter anything in the Value boxes. For all other types, including Midpoint, enter the Values in the corresponding boxes.
Finally, click the Color drop-down buttons to select your colors from the palettes. If you want to use custom colors, select “More Colors” to add them using RGB values or Hex codes.
You’ll then see a preview of your color scale at the bottom of the window. If you’re happy with the result, click “OK” to apply the conditional formatting to your cells.
The nice thing about a conditional formatting rule like this is that if you edit your data, the color scale will automatically update to accommodate the change.
For a similar way to display your Excel data, consider using the Data Bars conditional formatting rule to create a progress bar.
