How to Use Color Scales in Excel (Conditional Formatting)

How to Use Color Scales in Excel (Conditional Formatting)

In today’s tutorial, we’re gonna talk about how to use colour scales in Excel. Thanks to colour scales, you’ll be able to design a data table with a clear overview of the maximum, the minimum as well as the middle values.

So, let’s get into it!

See the video tutorial and transcription below:

See this video on YouTube:
https://www.youtube.com/watch?v=fj6VtUi2s9s

There are a few ways to use Excel graphical features to make your data presentations attractive. In previous tutorials, we went through various kinds of graphs, charts and data bars among others.

Today, we’ll have a closer look at ‘Color Scales’.

To use this function, you’ll need to select the data range to which you want to apply the colour scales, first.

How to Use Color Scales in Excel (Conditional Formatting) - select range of the table with heatmap

Then go to the ‘Home’ tab, find ‘Styles’ and click on ‘Conditional Formatting’. There are several useful functions hidden here, in the ‘Conditional Formatting’ section. All of them come quite handy when you need to present your data in a clear way.

How to Use Color Scales in Excel (Conditional Formatting) - option of conditional formatting

We’ll go for ‘Color Scales’ now and in the window that just appeared, we can select from two types of colour scales.

The first one contains a scale of three colours, the other one just two.

Select the first one: Green – Yellow – Red.

How to Use Color Scales in Excel (Conditional Formatting) - green and red color scale

Excel immediately applies a scale in these colours on the selected cells. Red colour marks the minimum, green colour the maximum and the yellow marks the middle values.

Thanks to the different shades of the colours, we’re able to find our way through the set of data and spot quickly whether particular values are closer to the minimum or rather the maximum of the range.

How to Use Color Scales in Excel (Conditional Formatting) - create heat map in Excel

We’re gonna move on now and see how we can edit the colour scale or, if necessary, how we can remove it.

Let’s start with the first case, which is editing the colour scale.

Click on any cell containing the selected data with the colour scale formatting.

How to Use Color Scales in Excel (Conditional Formatting) - heatmap in Excel

Then click on ‘Conditional Formatting’ again and select ‘Manage Rules’.

How to Use Color Scales in Excel (Conditional Formatting) - manage rules while conditional formatting

Select ‘Edit Rule’ in the window that just appeared.

How to Use Color Scales in Excel (Conditional Formatting) - edit rule while creating heatmap

There’s a number of options for editing colour scales here. We’re gonna go only through the basic ones now.

In this part, you can still make up your mind whether you want to change to the two-colour or stick with the three-colour scale.

How to Use Color Scales in Excel (Conditional Formatting) - format cells bassed on their value

Below, you can pick specific colours based on what suits you best. In this case, we’ll choose this sharp red for the minimum and intense green for the maximum of the range.

How to Use Color Scales in Excel (Conditional Formatting) - pick the colour to heatmap

Confirm with OK, then click on OK again.

How to Use Color Scales in Excel (Conditional Formatting) - how to adjust colour in heatmap

Excel will adjust the scale according to the selected settings.

How to Use Color Scales in Excel (Conditional Formatting) - settings of the heatmap in excel

If you need to remove the colour scale, select ‘Clear Rules’ in the section ‘Conditional Formatting’, then click on ‘Clear Rules from Entire Sheet’, and that’s it!

How to Use Color Scales in Excel (Conditional Formatting) - clear rules from sheet in Excel

The colour scale has been removed from the table.

How to Use Color Scales in Excel (Conditional Formatting) - remove conditional formatting from sheet in excel

If you found this video useful, you can watch more tutorials by EasyClick Academy and learn about the possibilities with graphical data representation in Excel. Check out the links to the videos below!

If you found this tutorial helpful, give us a like and watch other video tutorials by EasyClick Academy. Learn how to use Excel in a quick and easy way!

Is this your first time on EasyClick? We’ll be more than happy to welcome you in our online community. Hit that Subscribe button and join the EasyClickers!

Thanks for watching and I’ll see you in the next tutorial!

Comments

Add comment

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

Your personal information will only be used for the purpose of processing this comment.