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.


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.

Add Dummy Value to Dataset to Reorder Legend without Changing Chart in Excel

  • 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.

Create Stacked Chart to Reorder Legend without Changing Chart in Excel

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.

Create Stacked Chart to Reorder Legend without Changing Chart in Excel

  • 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.

Change to Matching Color to Reorder Legend without Changing Chart in Excel

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

How to Reorder Legend without Changing Chart in Excel (with Easy Steps) 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 which might solve your issue about Legend Reordering In this article, we are going to discuss, how you can Reorder Legends without changing the chart with elaborate explanations. 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 Value 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 Reorder 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 then there should be a stacked chart created, with the given information. 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 colours, 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 to Matching Color As we prepared the chart, we need to match the colour of the data series with the dummy values Legend. ● At first, will shift the Legends on 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 colour to the same colour 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 colour is now matched up with the same data name. Step 5: Delete Top Legend Entries 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 colours. 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.

  • 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.

How to Reorder Legend without Changing Chart in Excel (with Easy Steps) 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 which might solve your issue about Legend Reordering In this article, we are going to discuss, how you can Reorder Legends without changing the chart with elaborate explanations. 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 Value 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 Reorder 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 then there should be a stacked chart created, with the given information. 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 colours, 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 to Matching Color As we prepared the chart, we need to match the colour of the data series with the dummy values Legend. ● At first, will shift the Legends on 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 colour to the same colour 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 colour is now matched up with the same data name. Step 5: Delete Top Legend Entries 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 colours. 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.

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.

Change to Matching Color to Reorder Legend without Changing Chart in Excel

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

Change to Matching Color to Reorder Legend without Changing Chart in Excel

  • 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.

Change to Matching Color to Reorder Legend without Changing Chart in Excel

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

Change to Matching Color to Reorder Legend without Changing Chart in Excel

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

Change to Matching Color to Reorder Legend without Changing Chart in Excel


πŸ’¬ 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.


Related Articles

Rubayed Razib Suprov
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo