Create A Legend For Conditional Formatting In Excel

How to create a legend for your customized conditional formatting options in Excel?

Are you wondering how to create a legend for your customized conditional formatting setting? Conditional formatting is almost a must-have for people working with Excel. It allows you to see the trends and patterns of data in a few clicks. 

Very often we would like to create a legend for the conditional format so people can have a quick glance of what those colors are presenting. Unfortunately, creating legends for conditional formatting is not a built-in function in Excel.

In this article, I will show you how to create legend for conditional formatting in a few steps.

Example

Let’s take this as an example.

How to create legend

Step 1: Enter the range of data

In the example, the minimum value is 0 and the maximum value is 100.

So, I will enter 0 in Cell A13.

And, enter a formula in Cell A14 and drag it down to A23 (until the cell value reach 100)

Formula in Cell A14:

=A13+10

Do I have to set the interval as 10?

Not really. You can set the interval according to how detailed you would like the legend to be. In this case, I want a more detailed legend so I chose 10. If I want a simpler legend, I would go for 5.

Step 2: Copy the range to the right

There really isn’t any tricks. Just copy it to the right. You could use Ctrl + R to do it quick.

Step 3: Apply the same conditional formatting to the range

Now we have to apply the same conditional formatting to the original range ( Cell A13 to Cell A23).

1. Open the Conditional Formatting Rules Manager
2. Show formatting rules for This Worksheet
3. Under “Applies to”, enter the cell range (A13:A23) without deleting the original range

The cell range will look like this now if you have done it properly.

Step 4: Hide the value in the range

1. Highlight the cell range
2. Under Home tab, in Number group press the lower right corner icon to open “Format cell” dialog
3. Select “Custom”
4. For “Type”, enter ;;;

Step 5: Done! Congratulations!

Step by Step Video Tutorial (50 seconds)

Do you find this article helpful? Subscribe to our newsletter to get regular Excel tips and exclusive free Excel resources.