How to Reorder Legend Without Changing Chart in Excel

In Excel, any kind of alteration to the default ordering of the Legends while keeping the original chart intact is quite impossible. As there isn’t any default option to do it. However, in this article, we present a workaround for this problem that might solve your issue about Legend Reordering. In this article, we are going to discuss, how you can Reorder Legend without changing the chart in Excel with elaborate explanations.

How to Reorder Legend Without Changing Chart in Excel: with Easy Steps

In the following article, we will demonstrate the way in which you can Reorder Legends in your chart, without having to alter the original shape. Although the method is indirect as there are no default options in Excel charts.

Step 1: Add Dummy Values to Dataset

In the beginning, we need to add some dummy values to the dataset.

• We have the following dataset of which chart’s Legend we will Reorder without changing the chart.

• To accomplish this, we need to add the dummy values in the dataset, which will help us in the following steps.
• The thing is, these dummy value entries must be zero. And the column headers must be the same but in reverse order.
• An example of this procedure is shown below.

Step 2: Create Stacked Chart

Now as we added the dummy values in the dataset, we can create a stacked chart out of it.

• To do this, select the range of cells B4:I12, and then from the Insert tab, click on the 2D Column Stacked Chart.

• After that, there should be a stacked chart created, with the information given.

Step 3: Switch Row/Column

Although the chart we created just now, is not in very good shape. A small tweak can turn this chart into a usable chart.

• Select the chart and right-click on it
• Then from the context menu, click on Select Data.

• Then in the Select Data Source window, you will see that the Series names are listed as the Legend Entries and the column headers are listed as the Horizontal Axis Labels.
• Now all you have to do is to click on the Switch Row/Column button.

• Doing this will switch the Legend Entries with the Horizontal Axis Labels.

• Click OK after this.
• The chart will look somewhat like the below image.
• Now you understand the chart a lot better than before.
• You can also notice that the data chart now shows only 4 layers of colors, despite having 8 different layers.
• The reason is simple, the values in the dummy values we set as 0, so none of those values actually have any significance in this stacked chart.
• But we can see all 8 data Legend entries in the chart.

Step 4: Change Color of Legends

As we prepared the chart, we need to match the color of the data series with the dummy values Legend.

• At first, will shift the Legends to the right side of the screen.
• To do this, select the chart and then right-click on it.
• From the context menu click on the Format Legend.

• Then in the Format Legends side panel options, click on the Right on the Legends.

• Then select the first data series (Data 4) in the chart and then right-click on it.
• From the context menu, click on the Format Data Series.
• From the side panel, click on the color icon in the Fill & Line Options
• Then change the legend color to the same color as Data 4 at the bottom of the Legend.

• Repeat the same process for the rest of Legends.
• Now we can see the Legends entry’s color is now matched up with the same data name.

Step 5: Delete Top Legends

Now we got all the elements necessary to Reorder Legends without altering the charts.

• Now click on the Legend area to select it.

• Then double-click on the Legend Entry Data 4 to select it.

• Right after that, press Delete to delete the entry from the chart Legend.
• After that, repeat the same process for the other top Legends in the chart.
• The chart would finally look like the below image.

• Not only in the Reorder direction but the Reorder can also be done by any order.
• For example, we can Reorder the Legends in 2-1-4-3 order.
• To do this, we the dummy values in the 3-4-1-2 direction in the dataset as shown below.

• Now repeat the above process through to Step 4.
• We will get something like the below image.

• After deleting the top Legend part (Step 5), we got the Legends in the 2-1-4-3 direction.

💬 Things to Remember

✎ Dummy values must be 0, otherwise, it can mess with the existing data.

✎ The desired direction must be put in Reorder order as the column header in the dummy value. For example, if your target is to order as 3241, put the column headers in the 1423 order.

Download Practice Workbook

Download this practice workbook below.

Conclusion

Here we Reorder the Legends in the chart while keeping the original chart unchanged. We did it by using dummy values and matching Legend entries colors.

For this problem, a workbook is available for download where you can practice these methods.

Feel free to ask any questions or feedback through the comment section.

Related Articles

• How to Create a Legend in Excel without a Chart
• How to Edit Legend in Excel
• Excel Charts with Dynamic Title and Legend Labels
• How to Rename Legend in Excel
• How to Ignore Blank Series in Legend of Excel Chart
• How to Add a Legend in Excel
• What Is a Chart Legend in Excel?
• How to Show Legend with Only Values in Excel Chart
• How to Change Legend Title in Excel

<< Go Back To Legend in Excel Chart | Excel Chart Elements | Excel Charts | Learn Excel