How to use Color Scale in Conditional Formatting

 

In this article, we’ll learn the use of Color scales option in conditional formatting. Also, answer questions like, why we use it in our reports and its impact in our report for the better understanding.

Basically Conditional formatting option is used to highlight the specific numbers, text and characters. Also, Conditional formatting has different type of highlighters in which Color scales is the one.

Color scales make the visualization of each value very clear, the shade of the color represent the values in cell.

Let’s take an example and understand:

We have data in which we have month wise amount details of Annual Premium, Total amount received and Revenue amount. Now, we want to highlight the values with the color scale.

image 1

 

Follow below given steps:

  • Select the range of amount from in B5:B16
  • Go to home tab > Click on Conditional formatting > Click on color scales
  • Select any color to highlight

image 2

 

  • Follow the above step for both ranges (C5:C16 & D5:D16)

Output:

image 3

 

Explanation: 3 Color scales calculate the 50th percentile, the cell that is highlighting with red color holds minimum (9) value, median (36) values are colored with yellow, and maximum (80) values are colored with green.

This how we can apply color scales in data.

Also, we can modify the rules according to our requirement, let’s say we want to change color formatting.

To change the color formatting follow below given steps:-

  • Select the range B5:B16
  • Click on Conditional Formatting and then click on Manage rules

image 4

 

image 5

 

  • Click on Edit rule
  • Excel launches the Edit formatting rule dialog box

image 6

 

  • Select 2 Color scale from the Format Style drop down list
  • And then select any two color of your choice
  • Click OK > Click on Apply > Click OK

image 7

 

Output:-

image 8

 

image 48

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com



Example:


Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>