Wondering how to change legends in Excel to make them more effective in attracting the readers’ attention? This Microsoft Excel tutorial will help you.

Your Excel graph is incomplete if you don’t add a legend to describe the lines and columns. So, you add a default legend only to find the new element that distorts the visual aesthetics of your data visualizations.

To help you restore your graph’s visual qualities without removing legends, you can change legend text in Excel, modify legend properties, and change its position too. Read this Excel tutorial until the end to learn various custom and effortless methods to customize legends in Excel.

Change Legend in Excel in Column Header

As a default action, Microsoft Excel uses the column header of the series data as the legend text. So, when you add a legend to the graph, Excel populates the legend name from the relevant worksheet data column and applies the same color coding to the legend text found in the chart’s column or line objects.

So, if you rename the Excel dataset’s column headers, the legend text will also change instantaneously.

For example, in the above dataset, I’d like to change the legend entry names as outlined below:

• Quantities to Quantities 1st Jan
• Pricing to Pricing 1st Jan

To change the legend text in Excel, I replaced the column headers of columns B and C with Quantities 1st Jan and Pricing 1st Jan.

After hitting Enter each time I replaced the column header texts, Excel updated the legend entries as well with the new texts.

Change Legend in Excel by Editing Series Label

The Series name component of an Excel chart is the legend text. Excel applies the worksheet referencing formula, like =Sheet1!$B$1, to populate the legend name as is from the column header of the input dataset.

So, when you change the column header or update the worksheet, the legend text changes automatically.

What if you want to keep the legend name static and not change it as you update the input dataset? You can’t use the method explained earlier.

The method I’ve outlined here will help you fix the legend in an Excel chart and disallow dynamic changes.

Navigate to the Excel chart you want to modify and click on a column or line object. Now, right-click, and choose the Select Data from the context menu.

You should see the Select Data Source dialog box.

Below the Legend Entries (Series) header, you should see the legend text. In this exercise, it’s Quantities.

Select that and hit the Edit button just above it.

Now, you should see the Edit Series dialog box. Click on the Series name field and delete its content.

Type in the fixed legend name you want for this series data. In this tutorial, Quantities 2. Click OK to save the modification.

Again, click OK on the Select Data Source dialog box.

Excel will replace the existing legend text with the new text you’ve entered manually.

Now, if you change the column header for the series data of the input dataset, the legend text won’t change in the chart.

Change Legend in Excel Using a Formula

You can apply a formula in the column header cells of the input dataset. When you create a chart from this data and add the legends, the legend text will change automatically as the column header changes due to the formula.

In the given dataset, I’d like to rename columns B and C to Quantities1st Jan and Pricing1st Jan. This updated data will show the current cost for the specified computer hardware and accessories on 1st Jan.

In the next working day, I’ll change the date to 2nd Jan and the changes will reflect in the above columns instantly. As I make changes to the column headers, the legend entries will also update dynamically.

So, I’ll use the CONCAT function in B1 and C1 to join texts in F1, F2, and F3.

For example, to automatically add the updated short date to the column B header, I’ll enter the following formula in the said cell and hit Enter:

=CONCAT($F$2,$F$1) 

Excel will combine both the text in F1 and F2 and place that in B1.

Similarly, I’ll use the below-mentioned formula in C1 to get the same result for the Pricing component:

=CONCAT($F$2,$F$1)

Now, the next day, I need to update the legend to include that day’s short date, like 2nd Jan.

So, I enter 2nd Jan in F1.

The CONCAT function in B1 and C1 will automatically update the column headers.

As the column headers change, legends also change dynamically.

Changing Legend Position in Excel

The default location for chart legends in Excel is at the bottom of the chart. However, you might want to change the legend position or put it directly on the chart area to save space. If you wish to make such changes, follow the instructions outlined below.

Let’s consider your chart legend is at the bottom. Click the Chart Elements option in the top right corner of the chart border. It’s the large plus key.

The Chart Elements dialog box will open.

Click on the Overflow menu arrow next to the Legend option.

You’ll see the following four alternatives for the legend position:

• Right
• Top
• Left
• Bottom

Let’s say you want the legend on the right side of the chart. Click on Right. Excel will instantly update the legend position.

There are two more options for customizing the legend position. To explore those, select More Options from Chart Elements > Legend > overflow menu.

The Format Legend navigation panel will open on the right side.

Click on the Top Right selection to move the legend to the upper right corner of the graph.

If you wish to save chart space, uncheck the checkbox for Show the legend without… checkbox in the Format Legend side panel.

You can now drag and drop the legend anywhere on the chart.

Change Legend in Excel Using Format Legend

The Format Legend menu allows you to change various visual qualities of legends in an Excel chart.

For example, if you wish to change the font color and style within the legend box or convert the legend section to a color-filled rectangle, you’ll need to use the customizations in Format Legend.

Click on the legend, right-click, and choose Format Legend from the context menu.

The Legend Options menu will open on the right side.

Click on the Fill & Line bucket icon to access customizations for Line and Border.

Alternatively, select the Effects button to access modifications for Shadow, Glow, and Soft Edges.

For instance, if you wish to give a transparent greenish tint to the legend box, go to Fill & Line, select Solid fill, and click the color picker to choose the light green shade.

Now, adjust the intensity bar for the Transparency option below Color to adjust according to your preference.

Another cool modification you can apply is using the Shadow tool in the Effects menu.

Click on Effects and expand the Shadow tree.

Select any shadow design for the legend box by clicking the Presets drop-down menu.

Find above the visual of the glow customization in the legends box.

Navigate to the Text Options menu from the Legend Options menu in the Format Legend navigation panel to change the text styles of the legend content.

Text Options has the following customization menus:

• Test Fill & Outline

• Text Effects

• Textbox

Let’s say, you’d like to add a glow effect in the legend text. Navigate to the Text Effects menu in the Format Legend navigation panel.

Click on the Glow tree to expand its options.

Hit the Presets drop-down menu and choose a glow style from the context menu.

Excel will immediately show the legend style changes in the chart object.

Change Legend in Excel Using VBA Macros

Would you like to learn how to create programmatic automation in Excel using the Excel VBA Editor? If so, this method is specifically for you. Even if you are new to Excel VBA, you can use the following VBA script to change legend text in Excel.

Before using the script, visit the following resource to find out the simple steps to convert a VBA script into a VBA macro:

📒 Read More: How To Use The VBA Code You Find Online

Now, use the following script to build a VBA macro:

Sub ChangeChartLegend()
    Dim chartName As String
    Dim numEntries As Integer
    Dim i As Integer
    Dim newLegendName As String
    
    ' Prompt for the chart object name
    chartName = InputBox("Enter the chart object name:")
    
    ' Prompt for the number of legend entries to change
    numEntries = InputBox("Enter the number of legend entries you want to change:")
    
    ' Loop through the number of entries and prompt for new legend names
    For i = 1 To numEntries
        newLegendName = InputBox("Enter the new legend name for entry " & i & ":")
        ' Change the series name, which is reflected in the legend
        ActiveSheet.ChartObjects(chartName).Chart.SeriesCollection(i).Name = newLegendName
    Next i
End Sub

Now, press Alt + F8 to launch the Macro dialog box.

Select the ChangeChartLegend macro and hit the Run button.

You’ll first see a dialog box where you need to enter the actual chart object name of the active worksheet you want to modify. For example, Chart 1.

Next, you’ll need to enter the total number of legends available in the selected chart. For example, 2.

Excel will show a dialog box where you can enter the new text for the first legend.

If you’ve entered more than one legend, you’ll get more dialog boxes.

Once you’re done entering new legend names for all the legends, Excel VBA will automatically implement the changes.

Change Legend in Excel for the Web App

Changing the legend text or other parameters in Excel for the web is a bit different than the Excel desktop app. One common feature is if you change the column header for the series data, the legend text changes in both the Excel editions.

However, to manually change the names of legends, navigate to a chart in Excel for the web app.

Click on the legend you want to change.

Click on the Legend tree expansion arrow to collapse it.

Now, you should see the Series tree. There could be multiple if there is more than one legend.

Click on a Series to expand its options.

Change the legend name in the Series Name field.

Repeat the above steps for the rest of the Series items. Hit Enter.

The changes are instantaneous.

Change Legend in Excel on macOS

The process to change the legends in Excel for Mac is quite similar to that of the Windows app. However, the interface is completely different.

If you’re on a Mac, go to your Excel chart and click on the legend entries you want to change.

Right-click and hit the Select Data option.

You should see the Select Data Source dialog.

Within the Legend entries (series) list, you should see all the legends of the chart.

Select the first entry, like Quantities, and change the text in the Name field.

You can either edit the text within the double quotes or simply delete the entire content of the Name field and type your own legend text.

Click OK to save and implement the changes.

You’ve successfully changed legends in Excel for Mac.

Conclusions

So far, you’ve learned and practiced how to change legends in Excel using various manual and automatic methods.

If the article helped you to learn a new Excel skill today, comment below. Also, if you know a better method to change Excel legend texts and styles that I might have missed mentioning, share that in your comments.