To sort an Excel column chart by value, sort the chart’s source data: select the whole data range, choose Data > Sort, sort by the value column, and choose Largest to Smallest or Smallest to Largest. The chart will follow the new row order, keeping each category paired with its value.
Why sorting the source data works
A standard column chart displays categories in the order they appear in its source data. Excel does not generally provide a separate sort-by-value command for an ordinary chart, so the dependable method is to reorder the source rows. If a chart lists products and sales, sorting the source rows by sales also changes the order of the product columns in the chart.
This changes the arrangement, not the values or the chart type. It is useful when you want to make the largest or smallest values easy to spot, compare categories from high to low, or make a report easier to scan. The chart remains linked to its source cells, so changes to the values can update the plotted heights; however, Excel will not necessarily re-sort the rows automatically whenever a value changes.
Sort the chart’s data in Excel for Windows
For a simple list with one category column and one value column, use the Sort dialog so Excel sorts complete rows together:
- Identify the full range used by the chart, including the category labels and every series you want to keep aligned. For example, a table might have Product in column A and Sales in column B.
- Select a cell in the data range, or select the range itself. Avoid selecting only the numbers in the value column.
- Choose Data > Sort. If the first row contains headings, select My data has headers.
- In Sort by, choose the heading for the value you want to rank, such as Sales.
- For Order, choose Largest to Smallest for descending order or Smallest to Largest for ascending order, then select OK.
The chart should redraw with the categories in the sorted order. If Excel asks whether to expand the selection when you have selected only one column, choose Expand the selection. That keeps each label attached to its corresponding values. Sorting just the numbers can leave the labels behind and create a chart that looks plausible but reports the wrong category-value pairings.
Use a table for a chart that is easier to maintain
If the source data will grow, format it as an Excel table before making or maintaining the chart. Select a cell in the data and choose Insert > Table, confirm the range and whether it has headers, then sort from the filter arrow in the value column. Choose Sort Largest to Smallest or Sort Smallest to Largest. The table sorts complete records, and a chart based on the table is generally easier to extend as you add rows.
Tables also make it clearer which cells belong together: the header names identify the fields, and the filter arrows provide sorting and filtering controls. Check the chart’s data source if newly added rows do not appear. The chart may refer to a fixed range instead of the table, or the new row may not have been added to the table. Adding a row immediately below a table usually extends it, but verify the table boundary and chart source when a report does not update as expected.
Sort charts with multiple data series
For a chart with several series, such as sales for three regions, sort the complete source range by the series you want to use as the ranking measure. Every row must move together. Excel then presents each category in the new order while preserving the values from the other series in that category’s row. The other series do not independently determine the category order.
For example, if a chart compares North, South, and West sales for each product, sorting by North sales ranks products according to that series. The South and West columns remain beside their original products, but their own values may not appear in descending order. If your goal is to rank by a combined measure, calculate or provide that measure in the source data, then sort by its column. Make the chart’s purpose clear in its title or labels so readers understand what determines the ranking.
PivotCharts and PivotTables have their own sorting controls
A PivotChart is controlled by its associated PivotTable, so sort the PivotTable field rather than trying to rearrange the chart’s source cells. In the PivotTable, open the dropdown for the row-label field, choose More Sort Options if needed, and set an ascending or descending sort by the value field you want to rank. The PivotChart follows the PivotTable’s category order.
Menu wording can vary with the selected field and Excel build. If the dropdown does not offer the value field you expect, first confirm that the measure is in the PivotTable’s Values area and that it is summarized as intended. A PivotChart can also be affected by filters, slicers, and refreshes. After refreshing the PivotTable, check whether its sort order remains suitable; a refresh can change the underlying values and therefore the meaning of a manual ranking.
Formula alternative for a separately sorted list
If you want to preserve the original order, create a second, sorted copy of the data and build the chart from that copy. In Microsoft 365 or Excel 2021 and later, the SORT function can return rows ordered by a chosen column. With product names in A2:A6 and sales values in B2:B6, enter this formula in an empty cell such as D2:
=SORT(A2:B6,2,-1)
The formula sorts the two-column range by its second column in descending order. For example, if the rows are Pear 12, Apple 30, Plum 18, Orange 25, and Grape 9, the returned order is Apple 30, Orange 25, Plum 18, Pear 12, and Grape 9. Select the sorted results as the chart’s source. Because this is a spilling formula, leave enough empty cells below and to the right for the results; existing content in the spill area causes a #SPILL! error.
This approach keeps the original data intact, but it is not always the simplest option for a chart that must expand as new records arrive. A fixed range in the formula will not include rows added outside A2:B6. Use an Excel table and an appropriate expanded source, or update the formula range when the data grows. SORT is available in Microsoft 365 and Excel 2021 or later; older Excel versions can use ordinary sorting or other approaches, but should not be assumed to support this function.
Ascending, descending, and chart direction
Largest to Smallest puts the highest value first in the worksheet data. For a vertical column chart, that usually places the first category at the left and later categories to its right. Smallest to Largest does the opposite. Choose the order that best supports the comparison: descending is common for rankings, while ascending can be helpful when the lowest values are the focus.
A horizontal bar chart can look different because its category axis is vertical. Excel may display the first source category at the bottom rather than at the top, depending on the axis settings. If the data is correctly sorted but the visual order seems reversed, right-click the category axis, choose Format Axis, and review Categories in reverse order. This setting changes the display direction; it does not sort the source data or alter the values.
Common sorting problems and how to fix them
- Labels no longer match values: Undo the sort immediately if possible, then select and sort the complete data range. Never sort an isolated value column when its neighboring columns contain the matching categories or series.
- The chart did not change: Confirm that you sorted the cells used by the chart. Select the chart and review Chart Design > Select Data to check its data range. A chart may point to a different range or to a separate summary table.
- Numbers appear in an unexpected order: Some entries may be stored as text, contain spaces, or mix numbers with text. Check the source cells and convert values that should be numeric before sorting. Text such as 100 can sort differently from the numeric value 100.
- Headers were sorted into the data: In the Sort dialog, make sure My data has headers is selected. If the heading was moved, undo the sort and repeat it with the correct header setting.
- Equal values appear in an arbitrary-looking order: A sort by one field does not establish a meaningful order among ties. Add a secondary sort level, such as sorting by Sales from largest to smallest and then Product from A to Z.
- New categories are missing: Check whether the chart source range includes them and whether they are inside the source table. A chart based on a fixed cell range may not automatically include data entered beyond that range.
Excel for Mac and Excel for the web
In Excel for Mac, the same basic approach applies: select the complete source data and use Data > Sort, then choose the value field and sort order. Ribbon layout and dialog details can differ from Windows, but the key is to sort the full rows and identify headers correctly. In Excel for the web, use the sort commands on the Data tab or the dropdown in a table or filtered column. Then check that the chart uses the sorted range. Exact controls can vary with the current web interface, but the chart still reflects the order of its source categories.
Frequently Asked Questions
Can I sort a column chart without changing the original data?
Yes. Make a separate sorted copy or use a SORT formula in an empty area, then set the chart’s source to those results. The original list remains in its initial order.
Can I sort the chart itself from highest to lowest?
For a standard chart, sort the source data; the chart follows that category order. For a PivotChart, sort the associated PivotTable. Chart filters can hide or show categories, but they are not a general replacement for sorting the source ranking.
Will the chart automatically re-sort when a value changes?
Usually not when the chart is based on a manually sorted range. The values may update while the categories stay in their existing row order. A SORT-based source can recalculate its returned order when values change, provided its formula range includes those values.
How do I sort a chart with two or more value columns?
Sort the entire source range by the one value column that should control the ranking. All other series stay attached to their categories, but they will not each be sorted independently.
Why does my chart look upside down after sorting?
That is most often an axis-display setting on a horizontal bar chart. Review Format Axis and Categories in reverse order. A vertical column chart generally follows the source category order from left to right.
Related
- Excel Power Query Merge Table Types and When to Use Them
- Excel Power Query: Enable Background Refresh
- Excel Power Query: Delete Duplicate Rows
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- BI with Excel Power Pivot: Models, Reports, and LimitsYes—Excel Power Pivot can support business intelligence (BI) by combining data from multiple sources into a relational data…
- Excel Gantt Chart Schedule: Create and Track ProjectsAn Excel Gantt chart schedule is a task list paired with a date-based timeline, where colored cells show…
- Excel Pivot Table Filter: Count Greater Than a ValueTo show only PivotTable items whose count exceeds a number, apply a value filter: open the dropdown for…
- Excel Pivot Chart: Group Dates by Month?Yes. In Excel for Microsoft 365 on Windows, group dates in the PivotTable behind the PivotChart: right-click a…
- Power Pivot Troubleshooting: Fix Common Excel IssuesTo troubleshoot Power Pivot in Excel for Microsoft 365 on Windows, first confirm the Power Pivot add-in is…
- Dynamic Pie Chart in Excel: Build One That UpdatesA dynamic pie chart in Excel updates when its source data changes. For a chart that grows as…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.