Excel sheets have different orders and legend entries in their particular order. But any kind of alliteration, along with default ordering while the original data is intact, causes difficulties in reordering them. It is impossible to reorder them without the default option.
However, the series number in Excel is included to inform the user to introduce its types, numbers, and other features. The Excel legend order not changing due to the absence of the default option. A user can reorder the Legend through dummy values and matching entries colors. This is the prime norm of Excel legend reordering.
If you miss any of them, the problem may arise, and keep going. Don’t worry; below, I highlight all the topics so that users like you can fix these issues swiftly.
How to Reorder Legend Without Changing Chart in Excel?
To reorder Legend without changing charts in Excel, there are some ways to do it. I will gradually demonstrate them in depth, so you can alter them in their original shape. Again, remember that the method I describe here is indirect, as you won’t get any default option in the Excel chart.
Step 1: Add Dummy Values
At the beginning of this method, you need to add some dummy values. Here’s the process that you need to follow.
You must keep the entries and column header zero to accomplish dummy values. After this, put the headers in reverse order.
Step 2: Create A Stacked Chart
Once you finish adding dummy values, creating a stacked chart out of the dataset is a must. To do this,
- Select the range of cells around B4: l12. From the insert tab, tap on “2D column Stacked chart”.
- After that, a stacked chart will begin to be created. Put the necessary information that you want.
Step 3: Arrange the Row/ Column
As the chart you created may not be in proper shape now, try out this small tweak to make it usable.
- First, click on the chart; next, right-click on it.
- Tap on Select Data in the context menu.
- Then select the data source from your P.C. Once you click on this, you’ll be able to recognize the series’ names. The first row will be shown as the Legend Entries; next, the column headers will show as Horizontal under the Axis Labels.
- Now, select each row/column by tapping on the keyboard button.
- Doing such things will convert the legend entries into horizontal ones.
- Next, click Ok; the chart may look offensive now, and soon after, this one will look better.
- Perhaps, you can also notice that the data chart seems unfinished right now, but It’s clear hereafter. You’ll then see 4 layers of color on the chart rather than 8 variant layers.
Why does this happen? The reason is straightforward: the values you added to the chart are all 0. Therefore, none of the values have any significant role in the stacked chart. But it shows you a well-established legend entry including 8 different data. Now, go to the next step.
Step 4: Color Legends Mutation
After preparing the chart, you’ll notice the matching color that comes with dummy values.
- First, open the Excel sheet, click the shift button, and choose option legends.
- Tap on the select option and again right-click it from your mouse.
- Now, tap on the Format legend option on the context menu.
- Check out the Format legends side panel options; tap on “Right” from the legends.
Once you finish it, go back to the further steps. Here you see another option called the first data series. Click on it, and then tap on “Format Data.”
- After completing all these steps, you’ll find a side panel option; tap it, then straight to the next button. Click on the next alternative, “Fill & Line options.”
- Change the color by following the same process for data-4.
- Repeat the process once more.
- Afterward, check out the legend entry’s color, which can probably match the same data denomination.
Step 5: Erasing Top Legends Process
Once you finish doing all the steps for the recorder legends, you are free from altering charts. Next, select the entire legendary area.
Step 6: Double-Click on Legend Entry Data
Click twice on entry data 4 from the legend data sheet. After selecting this, press the Delete option. Repeat the same process for the other top legends.
I want to mention important things that reorders can be made in any order value. For instance, you can reorder through 3-1-2-4 orders.
- Use the same dummy values on the dataset in the 1-2-3-4 directions for this.
- Repeat the step-4 process once again.
- You’ll get a clear image after that.
- Delete the top legend part. You will then be able to recognize 2 to 1 and 4 to 3 directions after that. Lastly, it is now done.
Note: Remember that your dummy values must be 0. Otherwise, it can mess with your other existing data. Again, if you need any redirection, you should choose the reorder option as a header. Because you have already put the column as a header when making the chart on dummy values.
For example, suppose your focus is to reorder the chart with an included value of 3241. Then put the order as 1432/2341 and keep it as a column header.
4 Tips to Reversing Series Order in Chart Legend Not Changing the Series
There is different plotting of several series you may find in a stacked chart. Sometimes the reversing series appears on the bottom of the stack, and the last series at the top. Without changing the series, you can easily reorder the chart with VBA automatically.
Tips 1: Start with the Original Data for Stacked Chart.
Open up any real documents, then start with the original data for a stacked chart, as shown below.
Tips 2: Create Dummy Data
In this section, create some dummy data. You can add a mirror image of the original data, including all zeros. The below chart may appear like this.
| Data 1 | Data 2 | Data 3 | Data 4 |
| 10.24 | 0.62 | 10.0 | 9.94 |
| 2.21 | 15.42 | 4.18 | 18.11 |
| 0.52 | 8.03 | 5.46 | 12.61 |
| 18.75 | 0.48 | 17.57 | 2.33 |
| 7.53 | 0.52 | 1.67 | 19.12 |
| 9.26 | 5.74 | 16.20 | 13.05 |
Tips 3: Create Stacked Chart
Now, re-create the stacked chart, and reformat it so that the series will come into the same name with the same formats.
| Data 4 | Data 3 | Data 2 | Data 1 | Data 1 |
| 0 | 0 | 0 | 0 | 10.24 |
| 0 | 0 | 0 | 0 | 2.21 |
| 0 | 0 | 0 | 0 | 0.52 |
| 0 | 0 | 0 | 0 | 18.75 |
Tips 4: Individually Select & Delete the Top 4 Legend Entries
Now, select and delete the top four legend entries. Arranged the list with the bottom 4. Finally, recollect those data and decorate them sequence-wise. You may be interested for other post Why Your Microsoft Excel Not Sorting.
FAQ
How Do You Reorder Items in Legends?
1. Click on the chart > chart tools.
2. Click on Select Data under the data section.
3. In the select data source pop-up, click on the Legend Entries section.
Afterward, use the up/down arrow from the top right, then reposition the items in your desired order. For example, suppose you want to move the red bar to the top list so it can appear first in the chart.
Now, rearrange each of the items until they represent the order of the data. Select “Red & Click” from the up arrow until it is in the first position. From there, select the up arrow for the bar once again to change its position on each click.
How Do You Set a Legend Position?
Again, if you want to change its position in an axis, then strings to “upper left” > “upper right” and then “lower left” > “lower right.” Those four options will help you to specify and correspond to the corner of the axis.
What Is the Default Position of Legend in Excel?
Along with this, the Excel sheet belongs to a unique default alignment. By this, the user can conduct any ribbon, keyboard shortcuts, and format cell dialog. The default position also lets users set their custom number format.
Final Words
I discuss all possible ways to create or make an Excel legend order, not changing its organic copies. I elaborate here about two initial terms using dummy values and matching legend full colors. Meanwhile, you can use a workbook to practice on it, which is available to download.
Lastly, when doing the method, right-click any chart elements. Select the entire list before putting your data. You’ll then get the pop-up of the context menu. Click it and keep continuing to insert data in the dialog box. Practice more to acquire more excellence.