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.
- Select the whole range and click on “Filter” on the Data ribbon.
- 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?
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.