'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Make Excel Chart Columns Match Legend Order
Question details
The user needs to rearrange the visual data columns in an Excel chart so they display in the exact order specified by the chart's legend.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Organizing chart data visualization to improve readability
- Observed behavior
- The chart legend is properly ordered, but the actual data series columns are plotted in a different sequence.
Verify that your chart is fully updated with the latest data and identify exactly which series columns need to be moved to match the legend sequence.
Reorder Chart Columns Using the SERIES Formula
Directly modify the plot order parameter within the series formula to accurately position each column.
Changing the legend order alone does not change the plotted series order on the chart. To align the columns with the legend, you must adjust the plot order directly via the formula bar.
Click directly on the chart column (data series) that you want to move.
Look at the Formula Bar at the top of the Excel window to find the formula, which will look similar to: =SERIES(Sheet1!$D$2,Sheet1!$E$1:$J$1,Sheet1!$E$2:$J$2,2).
Find the final number in the formula (the last argument before the closing parenthesis). This number specifies the current display order of the series.
Modify this final number to your desired position. For example, change it to '1' to make it the very first column in the chart grouping.
Press Enter to apply the change. Repeat this process for your other series until the chart columns fully match the order of your legend.
Change Order via the Select Data Source Dialog
Use the built-in chart data selection tool to visually reorder your chart's series.
Create and Manage Professional Charts Easily in WPS Office
Easily visualize your data and manage chart series orders with WPS Spreadsheet. It provides a highly intuitive interface to handle complex chart adjustments quickly, ensuring your data columns and legends are perfectly synced.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open the document containing your chart.
- 2. Access data settings: Right-click on your chart and select 'Select Data' from the pop-up menu.
- 3. Reorder the series: In the dialog box, highlight a series and use the Up/Down arrows to rearrange the display sequence.
- 4. Apply changes: Click OK to instantly apply the changes and synchronize your columns with the legend.
Frequently Asked Questions
Why did my chart columns not change when I rearranged the legend?
The legend is simply a visual key and modifying it directly does not control the actual plotting order of the data series. You must adjust the series order itself via the SERIES formula or the 'Select Data Source' dialog to change the physical column positions.
What does the last number in the SERIES formula represent?
The final argument in an Excel SERIES formula determines the exact plot order of that specific data series within the chart. Changing this number automatically moves the series forward or backward relative to the other plotted columns.
Can I change the column order by sorting my source data?
Yes, sorting or rearranging the original data columns in your worksheet will naturally update both the chart column order and the legend. However, if you are unable to modify the source data layout, adjusting the chart series manually is the most effective approach.
Will changing the series order affect my worksheet formulas?
No, altering the series order using the SERIES formula or 'Select Data' dialog only affects how the data is visually represented within the chart. Your original worksheet data and associated calculations remain completely untouched.
