Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 11:00 AM (PST)
Microsoft Tech Community
SOLVED

Is it possible to create a 4 colour scale with number ranges rather than gradient?

Copper Contributor

I am trying to keep track of my blood pressure and want to make it easy to look at with a glance.

 

Idealy I would have it like this

under 90 is purple

90.5 to 119.5 is green

120 to 134.5 is yellow/amber

135+ is red

 

2 Replies
best response confirmed by thenewguy87 (Copper Contributor)
Solution

@thenewguy87 

Yes, you can create a custom 4-color scale in Excel based on specific number ranges. Here's how you can set it up using Conditional Formatting:

  1. Select the Range: Select the cells where you want to apply the color scale. For example, if your blood pressure values are in column A from A1 to A100, select that range.
  2. Go to Conditional Formatting: Navigate to the "Home" tab on the Excel ribbon, click on "Conditional Formatting," and choose "New Rule."
  3. Choose Format Style: In the "Select a Rule Type" window, choose "Format cells that contain."
  4. Set Conditions:
    • For the first condition (purple), select "Cell Value" in the first drop-down, choose "less than" in the second drop-down, and enter 90 in the third box. Click on the "Format" button, go to the "Fill" tab, and choose the purple color. Click "OK."
    • Repeat this process for the other conditions (green, yellow/amber, and red) with the respective ranges and colors.
  5. Order the Rules: Make sure to order the rules correctly, from the lowest range to the highest, so that Excel applies the formatting in the intended order.
  6. Apply the Rules: Click "OK" in the "New Formatting Rule" window. Now, your selected range should have the custom 4-color scale applied based on the specified number ranges.

This way, the cells will be colored according to your defined ranges, making it easy to visualize your blood pressure levels at a glance. AI was partially deployed to support the text.

 

File with Examples included.

 

My answers are voluntary and without guarantee!

 

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.

Thank you so much, that worked perfectly.
1 best response

Accepted Solutions
best response confirmed by thenewguy87 (Copper Contributor)
Solution

@thenewguy87 

Yes, you can create a custom 4-color scale in Excel based on specific number ranges. Here's how you can set it up using Conditional Formatting:

  1. Select the Range: Select the cells where you want to apply the color scale. For example, if your blood pressure values are in column A from A1 to A100, select that range.
  2. Go to Conditional Formatting: Navigate to the "Home" tab on the Excel ribbon, click on "Conditional Formatting," and choose "New Rule."
  3. Choose Format Style: In the "Select a Rule Type" window, choose "Format cells that contain."
  4. Set Conditions:
    • For the first condition (purple), select "Cell Value" in the first drop-down, choose "less than" in the second drop-down, and enter 90 in the third box. Click on the "Format" button, go to the "Fill" tab, and choose the purple color. Click "OK."
    • Repeat this process for the other conditions (green, yellow/amber, and red) with the respective ranges and colors.
  5. Order the Rules: Make sure to order the rules correctly, from the lowest range to the highest, so that Excel applies the formatting in the intended order.
  6. Apply the Rules: Click "OK" in the "New Formatting Rule" window. Now, your selected range should have the custom 4-color scale applied based on the specified number ranges.

This way, the cells will be colored according to your defined ranges, making it easy to visualize your blood pressure levels at a glance. AI was partially deployed to support the text.

 

File with Examples included.

 

My answers are voluntary and without guarantee!

 

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.

View solution in original post