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).
- Open the Conditional Formatting Rules Manager
- Show formatting rules for This Worksheet
- 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
- Highlight the cell range
- Under Home tab, in Number group press the lower right corner icon to open “Format cell” dialog
- Select “Custom”
- 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.