For some reason in Microsoft Excel, it creates a legend in the wrong order for stacked bar charts. However, if you want to reorder the legend to match the colour sequence in your stacked bars, this short blog explains how. It’s a quick fix to change the graph legend order, with no complicated workarounds or coding required!
“Order is one of the needs of life which, when it is satisfied, produces a real happiness.” – Maria Montesson
Here’s my 5-minute explainer video, including how you can make a staked bar chart in Excel. Otherwise you can read on for the simple steps and screenshots…
Help! My Excel Legend Order Doesn’t Match the Bars
Categorised data over time makes an ideal candidate for stacked bar charts. For example, sales by product type AND by month/year. Stacked bars are a really great way of showing more data in one chart, without making it overly complex to read.
You’ll see in my image below, for demonstration purposes I’ve used colours by year and created a ‘100% Stacked Column‘ chart. The legend by default is placed below the graph by Excel.
You may then be tempted to drag the legend over to the right and resize, to try and align your categories with the order of the stacked bars. But then this happens, where there’s a mismatch of the legend to the column order on your graph:
How to Invert Legend in Excel
Here’s how to easily fix it and match your legend with the bar chart category order in three easy steps. There’s no complex workarounds or coding required, it’s just one small button hidden away in the format options…
1: Select the legend and from the ‘Format‘ tab of the ribbon choose ‘Format Selection‘.
2: In the ‘Format Legend‘ dialogue box and under the ‘Legend Position‘, choose ‘Right‘.
3: Now in correct order, resize your chart legend to preference.
And that’s it, your chart legend now matches the stacked columns. This means your reader can more easily and intuitively understand the information you are presenting, a boon for accessibility of your content! Of course, you can also apply this simple hack to stacked bars charts, which are the ones going across the chart left to right.
I hope you’ve found this Excel explainer helpful and it saves you from headaches, frustration, and wasting time wondering why the legends don’t match. Let me know in the comments how much time you saved!
Kind Regards, Adrian
For more free support in Excel, PowerPoint, Word, and more, please subscribe to my EATO YouTube channel for more. Liking the content? Please like and share with others who may also find it useful. If so inclined, supporting my work helps keep free content flowing. Want fantastic, time-saving templates for your own business? Then please get in touch to arrange a bespoke quote. Finally, please feel free to leave your feedback below, including what tutorials or templates you’d like created.
