How to Insert a Legend in Excel Based on Cell Colors

You are working on an Excel file and have used cell background colors? In such case you might want to insert a legend or table of colors used in your sheet or workbook. Here are three methods that might work for you.

Example in this article

In this article we use the following example. You have created an analysis and highlighted some cells. Now, you want to insert an explanation, primarily for the red, yellow and green cells.

Method 1: Insert a legend manually

Yes, I know, doing things manually is kind of uncool… but often still the fastest method. When it comes to a table of colors, it might be a valid option for you.

When working with colors it is anyway recommended not to use hundreds of different colors. So, usually you would only use up to 5 or 7 different colors, right? If that is the case, try to create a legend manually.

Here is some advice, especially for larger tables than in our example: You can gain an overview of all colors in a column by inserting filters.

1. Select the whole range and click on “Filter” on the Data ribbon.
2. Then, click on the small filter icon in the heading of a column and scroll down to “Filter by Color”. Now, you can see all different colors used in the column.

Method 2: Use a VBA macro to insert a table of all background colors

Our next method to insert a legend involves a VBA macro. I have prepared a short macro that does the following:

• Insert a new worksheet called “Professor_Excel_Legend”
• On this worksheet, it creates a list of all background colors used on your currently selected

Open the VBA editor window, insert a new module and copy & paste the following code (if you need assistance with these steps, please refer to this article).

Sub createLegend()
    Dim activeSheetName, legendSheetName As String
    activeSheetName = ActiveSheet.Name
    
    legendSheetName = "Professor_Excel_Legend"
    Sheets.Add
    ActiveSheet.Name = legendSheetName
   
    Range("B3").Select
    ActiveCell.FormulaR1C1 = "Legend"
        
    Range("B4").Select
    Dim i, J, currentRow As Integer
    Dim colorFound As Boolean
    i = 0
    currentRow = 4
            
    Dim cell As Range

    For Each cell In ActiveWorkbook.Sheets(activeSheetName).UsedRange.Cells
        colorFound = False
        For J = 4 To currentRow
            If Sheets(legendSheetName).Cells(J, 2).Interior.Color = cell.Interior.Color Then
                colorFound = True
            End If
        Next
        If colorFound = False Then
            Sheets(legendSheetName).Cells(currentRow, 2).Interior.Color = cell.Interior.Color
            currentRow = currentRow + 1
        End If
                    
    Next cell

End Sub

Method 3: Use an Excel add-in to create a legend comfortably

This method is probably the fastest: Create a legend with an Excel add-in. Our add-in “Professor Excel Tools” comes with many, many features – one of them is “Table of Colors”. It creates a legend either of the current worksheet or a whole workbook at once.

Just click on the “Table of Colors” button on the Professor Excel ribbon. Now, you can specify the settings and click on Refresh / Start. Easy, right?

Image by Rudy and Peter Skitterians from Pixabay