Color Scales

Excel provides extremely powerful & immensely useful data analysis tools under the Conditional Formatting tools. One of the best thing about these tools is that they are easy to comprehend and are at best 2-click away from being applied to your data.

We have already seen Data-Bars in a previous tutorial.

Color Scales reveal the relative position of each cell in a range (Colors are used to convey if the number is big or small with respect to all the numbers in the selected range)

To see this in action, please look at the below given table. These are marks of a class of students over 4 terms. How can excel help you in analyzing this whole table in a split-second (Yes, seriously!)? How do we know the best & worst performers? Who are the students that performed much better in second year?

Lots of numbers & little information

Color scales can help you in analyzing this complete data and answer many such questions in a quick time.

To weave the color scale magic: select all the numbers, then go to Home->Conditional Formatting->Color Scales and select one of the color schemes. For this data-set, the first option is good (giving higher numbers a greenish and lower numbers a reddish color tone)

 The steps are shown as below:

color scales in action

Please note that color scales provide other color combinations (try them out). Also, the bottom 4 default color-combinations are monochromatic (Single color with different shades) and will be more suitable for ‘Black & White’ prints.

Now coming to this output, you can now easily make inferences from the data. Like Yamaha & Jim have performed well throughout while Mark has been a poor performer. Also we can see interesting exceptions and can call them out, like Stef had a really poor Term 3, in an otherwise outstanding course. As you can see, Color scales have made it extremely easy to make sense out of all these numbers.

Output post color scales application

Most of the times color-scales & data bars will serve a lot of purpose in preliminary examination of data, before the more fancy (statsy) stuff takes place.

TutorialFiles: 

Section: