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.

Example: For this table you want to create a legend.
Example: For this table you want to create a legend.

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"
    ActiveSheet.Name = legendSheetName
    ActiveCell.FormulaR1C1 = "Legend"
    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
        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.

Easily insert a legend with the Excel add-in Professor Excel Tools.
Easily insert a legend with the Excel add-in Professor Excel Tools.

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?

Professor Excel Tools Box

This function is included in our Excel Add-In ‘Professor Excel Tools’

(No sign-up, download starts directly)

More than 35,000 users can’t be wrong.

Image by Rudy and Peter Skitterians from Pixabay

Henrik Schiffner is a freelance business consultant and software developer. He lives and works in Hamburg, Germany. Besides being an Excel enthusiast he loves photography and sports.

Leave a comment

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