How to Edit Legend in Excel

This guide will cover off the basics of working with a legend in Excel charts. We will run through how to add a legend, rename them, and how to change the look and feel of them.

We will cover:

• How to add a legend to a chart
• How to manually edit the legend name
• Using headers to automatically update the legend
• Editing the legend format

Excel provides us with a lot of very simple ways to visualise our data with only a few clicks. If we have a basic set of data that is clean and organised enough, the built in options for bar charts, line charts and so on will work very well.

Once the charts are complete, we need to ensure that the data makes sense, and is all labelled correctly. This is where information such as the legend can be particularly important, especially when working with more than one variable.

Let’s look at an example. The below represents two different soccer players and the number of goals they have scored in each game over the course of a week:

Our raw data set is as follows:

Given the simple nature of this data we are going to create a bar chart, which is represented for this example below:

The chart itself is nice and clean, however there isn’t really any information on who each player is.

How to add a legend to a chart in Excel

In a lot of cases the easiest way to make sure the legend is included by default is to simply highlight and select all data and the headers in a single selection, and then select the type of chart to insert. As the data was already all selected Excel is smart enough to understand which cells represent the headers of each data series.

If we do need to add the legend then simply click on the chart itself, and under the Chart Design ribbon, select Add Chart Element, and then under Legend choose the position. In this case we have selected it to appear to the right of the bar chart:

If the data we are working with to build the chart has no headers, or we have created the visualisation in a more manual way by inserting a chart and then selecting each data point manually without including the headers, then Excel will typically create default names for the data sets such as Series 1, Series 2 and so on. See below:

This isn’t really helpful as it also provides zero context in to what the chart is trying to visualise and represent.

Luckily, updating these legend names is quite simple, and there are a couple of approaches we can take, depending on whether or not the data set has headers.

How to manually update the legend name

This approach is a bit manual, but it also provides us with more control over how the series / data sets are labelled in the legend, and is not dependant on headers within the data itself.

To manually edit the legend right click on the chart, select Select Data, and then on the pop up box that appears, click Edit under the Legend Entries (Series) side of the window:

In the next box that appears, simply enter how you would like to the name to appear under Series Name. Given the players name in our example for column A (Series1) is James, we are going with that.

For this example, make sure that you do not edit the series values.

The result will be as follows:

To update the other colour bar, repeat the process for Series2.

While this does allow for a fair bit of freedom in the labels, there is room for error here, especially working with a data set that has more columns as it could lead to accidently typing in the labels in the wrong order which effectively makes the data useless.

This leads us into the second method for editing the legend.

Using headers to automatically update the legend

If the data set in Excel has headers for each column that are clearly labelled, then this approach is a fair bit easier to work with.

To automatically update the legend labels, we need to tell Excel that the overall dataset is actually a bit bigger than what it is reading – specifically that there is another row above including headers.

First, we need to go back into the Select Data box as we did in the previous approach. As you can see from the below screenshot with data included, we are looking at row 3 onwards, even though the headers are in row 2:

Updating this is very simple – in the Chart data range field highlighted at the top of the box, update the overall range. In this example it is as simple as taking that 3 under the B3 potion, and replacing it with a 2.

Then just press OK and the chart will be updated! As you can see from the below the series labels are selected in red, indicating that these are the legend names:

The other benefit of this approach, is that if our data changes in any way, for example the header name, then it will automatically be represented in the chart without needing to remember to update it too.

Editing the legend format

Finally, let’s take a quick look at the formatting of the legend in Excel.

To open up the format panel, either double click on the legend itself, or right click and select Format Legend.

This brings up a panel with a range of options:

From here, we can choose the location of the legend itself, and update the look and feel of things such as text colour, borders, fill and shadows. See below an example of adding a range of these features:

If you combine these formatting options with the wider chart design panels it is possible to make some very visually appealing charts in Excel!

This sums up our guide on how to edit legend in Excel. For more tips and tutorials on using Excels built in functions be sure to check out our Excel Tips page