Rubayed Razib Suprov
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.
Download Practice Workbook
Download this practice workbook below.
Step-by-Step Procedure to Reorder Legend Without Changing Chart in Excel
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.
Read More: How to Add a Data Table with Legend Keys in Excel
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 then there should be a stacked chart created, with the given information.
Read More: What Is a Chart Legend in Excel? (Detailed Analysis)
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 useable 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 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.
Read More: How to Change Legend Colors in Excel (4 Easy Ways)
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.
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. Any suggestion for the betterment of the Exceldemy community will be highly appreciable.
