Home » Excel Charts » How to Reorder the Legend Without Changing the Chart in Excel

How to Reorder the Legend Without Changing the Chart in Excel

Avatar photo
Written by Rubayed Razib Suprov
Last updated: Jul 29, 2024

Step 1 – Add Dummy Values to Dataset

  • Add dummy values to your dataset.
  • Suppose you have a dataset with a chart whose legend you want to reorder without altering the chart itself.

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

  • These dummy value entries should be set to zero, and the column headers must be in reverse order.
  • Here’s an example of this procedure:

Step 2 – Create Stacked Chart

  • Select the range of cells (e.g., B4:I12).
  • Go to the Insert tab and click on 2D Column Stacked Chart.

  • This will create a stacked chart with the provided information.

Create Stacked Chart to Reorder Legend without Changing Chart in Excel

Step 3 – Switch Row/Column

  • The chart we just created may not look ideal at this point.
  • To make it more usable, follow these steps:
    • Select the chart and right-click on it.
    • From the context menu, choose Select Data.

Create Stacked Chart to Reorder Legend without Changing Chart in Excel

  • In the Select Data Source window, you’ll see that the series names are listed as legend entries, and the column headers are listed as horizontal axis labels.
  • Click the Switch Row/Column button.

  • This action will swap the legend entries with the horizontal axis labels.

  • Click OK.
  • Your chart will now resemble something like the image below:

  • You’ll notice that the data chart displays only 4 layers of colors, even though there are 8 different layers.
  • The reason is simple: the dummy values we set to 0 have no significance in this stacked chart.
  • However, all 8 data legend entries are still visible.

Step 4 – Adjust Color of Legends

Match Data Series Colors with Dummy Values:

  • As we’ve prepared the chart, our next step is to ensure that the colors of the data series match those of the dummy values in the legend.
  • Shift the legends to the right side of the screen:
    • Select the chart and right-click on it.
    • From the context menu, choose Format Legend.

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

    • In the Format Legends side panel options, click on Right to position the legends on the right side.

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.

  • Select the first data series (Data 4) in the chart:
    • Right-click on it.
    • From the context menu, click on Format Data Series.
    • In the side panel, click on the color icon within the Fill & Line Options.
    • Change the legend color to match Data 4 (located at the bottom of the legend).

  • Repeat this process for the remaining legends.
  • You’ll notice that the legend entries’ colors now correspond to their respective data names.

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.

Step 5 – Remove Top Legends

  • We’ve gathered all the necessary elements to reorder the legends without affecting the chart itself.
  • Click on the legend area to select it.

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

  • Double-click on the legend entry for Data 4 to select it.

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

  • Press the Delete key to remove this entry from the chart legend.
  • Repeat the same process for the other top legends in the chart.
  • Your chart will now resemble the image below:

  • Additional Note:
    • Reordering can be done in any desired order. For instance, you can reorder the legends as 2-1-4-3.
    • To achieve this, set up the dummy values in the 3-4-1-2 order within your dataset.

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

  • Follow the steps above and you’ll achieve the desired legend order.

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

 

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

Remember these key points

  • Dummy values must be set to 0; otherwise, they may interfere with existing data.
  • Arrange the desired order in the dummy value column headers. For example, if you want to reorder as 3241, arrange the column headers in the 1423 order.

Download Practice Workbook

You can download the practice workbook from here:

Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!

Tags:

Rubayed Razib Suprov
Rubayed Razib Suprov

Rubayed Razib, holding a BSC degree in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, serves as a devoted member of the ExcelDemy project. He has contributed significantly by authoring numerous articles and showcasing proficiency in VBA. Razib efficiently automates Excel challenges using VBA macros and actively participates in the ExcelDemy forum, providing valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

About ExcelDemy.com

ExcelDemy is a place where you can learn Excel, and get solutions to your Excel & Excel VBA-related problems, Data Analysis with Excel, etc. We provide tips, how to guide, provide online training, and also provide Excel solutions to your business problems.

See Our Reviews at

trustpilot review

ExcelDemy Consulting Services

Submit Excel Task

ExcelDemy Online Training


Join ExcelDemy Forum
DMCA.com Protection Status

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo