Yes, you can manually sort columns in an Excel pivot table by dragging the field buttons (headers) left or right, or by using the Move commands on the right-click menu. This works for both row and column fields, and it overrides any automatic alphabetical or numeric sorting until you refresh or re-sort.
How Manual Sorting Works in Pivot Tables
When you create a pivot table, Excel automatically sorts row and column labels alphabetically (or numerically for numbers). Manual sorting lets you rearrange those labels in any order you want, independent of the data. The key is that you are moving the field items (the individual values like “North”, “South”, “East”) within the pivot table’s layout, not the underlying source data.
To manually sort columns:
- Click on the column header (e.g., the cell containing “North”) you want to move.
- Hover over the border of the selected cell until the cursor becomes a four-sided arrow (a move cursor).
- Hold down the left mouse button and drag the column to the desired position. Release when a thick vertical line appears where you want it.
- Alternatively, right-click the header, choose Move, and then select Move “Field Name” Left or Right (or Up/Down for row fields).
This method works in Excel for Microsoft 365, Excel 2021, 2019, and earlier versions on Windows. On Mac, the drag method works the same, but the right-click menu uses the same Move options.
Why Manual Sorting Is Useful
Automatic sorting is fine for quick reports, but manual sorting gives you control over presentation. For example, if you have quarterly columns (Q1, Q2, Q3, Q4), Excel might sort them as Q1, Q2, Q3, Q4 correctly, but if you have month names (Jan, Feb, Mar), Excel will sort them alphabetically (Apr, Aug, Dec…), which is useless. Manual sorting lets you put months in calendar order.
Another common use is placing a “Total” column at the far left instead of the default far right, or moving a specific product category to the front for a presentation. Manual sorting is also essential when you have custom categories like “High”, “Medium”, “Low” that you want in that logical order, not alphabetical (High, Low, Medium).
How to Sort Columns Automatically vs. Manually
If you prefer automatic sorting, you can use the sort buttons on the ribbon or the right-click menu. To sort a single column field:
- Right-click any cell in the column field (e.g., a month name), choose Sort, then Sort A to Z or Sort Z to A.
- For a custom sort order (like months), right-click, choose Sort > More Sort Options, then select Manual (you can then drag) or choose Ascending (A to Z) and then use the More Options to pick a custom list.
But if you want to manually reorder just a few columns, dragging is the fastest. Note that manual sorting is not the same as custom lists (like Monday, Tuesday…). Custom lists are a separate feature that you can apply via the sort options, but manual dragging always overrides them.
Common Problems and Misconceptions
Problem: Dragging doesn’t work. This usually happens because you are trying to drag a cell that is part of a grouped field or because the pivot table is in a compact layout. Make sure you click directly on the field button (the header text), not a data cell. Also, if you have more than one row or column field, you must drag the correct level.
Misconception: Manual sorting is lost after refresh. Actually, manual sorting persists across refreshes as long as the pivot table’s sort order is set to Manual. To check, right-click the field, choose Sort > More Sort Options, and see if the “Manual” option is selected. If you apply an automatic sort later, it will override manual order.
Problem: New items appear at the end. When you refresh and new data adds a new column label (e.g., a new product), it will appear at the end of the column list, not in your manually arranged position. You will need to drag it into place again. To avoid this, consider using a custom list or a calculated field, but that is more advanced.
Comparing Manual Sorting with Custom Lists
Excel has a built-in feature called Custom Lists (File > Options > Advanced > Edit Custom Lists) that lets you define a specific order (e.g., “Low”, “Medium”, “High”). You can apply a custom list to a pivot table field via the sort options. The difference is:
- Manual sorting: You physically drag each item. It is fast for a one-off report, but if you add new items later, they won’t follow the pattern.
- Custom list: You define the order once, and any new item that matches the list will be placed correctly. However, items not in the list will go to the end.
For example, if you have a custom list “Q1, Q2, Q3, Q4” and you add a new column “Q5”, it will appear after Q4 because it is not in the list. Manual sorting would require you to drag it manually. In practice, manual sorting is simpler for small tables, while custom lists are better for recurring reports.
Step-by-Step Example: Sorting Month Columns
Suppose you have a pivot table with months as columns: Apr, Aug, Dec, Feb, Jan, Jul, Jun, Mar, May, Nov, Oct, Sep (alphabetical). You want them in calendar order.
- Click on the “Jan” header (the cell that says Jan).
- Hold the left mouse button and drag it to the leftmost position. Release when the vertical line appears at the far left.
- Repeat for “Feb” (drag it next to Jan), then “Mar”, and so on. This is tedious for 12 months, so a faster way is to use the Sort option with a custom list.
- Right-click any month header, choose Sort > More Sort Options.
- Under Sort options, select Ascending (A to Z).
- Click More Options, check First key sort order, and choose Jan, Feb, Mar… from the dropdown (this is a built-in custom list). Click OK.
This will instantly put months in correct order. If you don’t have that list, you can create it manually via File > Options > Advanced > Edit Custom Lists.
Frequently Asked Questions
Why can’t I drag columns in my pivot table? This usually happens if the pivot table is in Compact Layout (the default) and you are trying to drag a data field, not a field header. Click directly on the field button (the label cell). Also, ensure you are not in Edit mode (double-clicked the cell). If you still can’t drag, try using the right-click Move option instead.
Does manual sorting affect the source data? No, manual sorting only changes the display order in the pivot table. Your original data remains untouched. The pivot table stores the sort order as part of its layout, so it is saved with the workbook.
How do I reset to automatic sorting? Right-click any item in the column field, choose Sort, then select Sort A to Z or Sort Z to A. This will override the manual order. To remove all sorting, choose More Sort Options and select Manual (which allows dragging again, but the current order stays until you drag).
Can I sort columns by values (e.g., largest total first)? Yes, but that is a different feature. Right-click a value cell in the column (like a total), choose Sort > More Sort Options, and select Ascending or Descending by the value field. This sorts the columns based on the numbers in the data area, not the labels. Manual sorting is for label order only.
Will manual sorting work after I add new data and refresh? Yes, the manual order is preserved for existing items. New items that appear after refresh will be added at the end (or according to the default sort). You will need to manually drag them into place again if you want a specific position.
Is there a keyboard shortcut to move columns? No, there is no direct keyboard shortcut for dragging. However, you can use the right-click Move option which has no shortcut either. The fastest way is still the mouse drag. On Mac, you can use the same drag method.
Final Tips for Manual Sorting
If you frequently need a custom column order, consider using a custom list (as described above) instead of manually dragging every time. Also, remember that manual sorting works independently for each field level. If you have two row fields (e.g., Region and Product), you can sort each level separately by clicking on the respective field buttons.
Finally, if you are using Excel for the web (browser), the drag-and-drop method may not work as smoothly. In Excel for the web, you can still right-click and use the Move options, but dragging is limited. For full functionality, use the desktop version of Excel.
Related
- Excel Income and Expense Tracking Spreadsheet: How to Build One
- Excel Import and Align with Existing Times
- Excel How to Ungroup Cells: Rows, Columns & Sheets
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- How to Plot a Waterfall Chart in Excel Step by StepTo plot a waterfall chart in Excel, arrange your categories in one column and the changes in value…
- Excel Clustered Column Chart with a Secondary AxisYes. In Excel for Microsoft 365 on Windows, select the chart, choose Chart Design > Change Chart Type…
- Excel Chart Stop Line Dropping to Zero: Causes and FixesTo stop an Excel line chart from dropping to zero when data is missing, select the chart and…
- Excel Pivot Table: Remove Old Filter Values After RefreshTo remove deleted items from an Excel PivotTable filter, open PivotTable Options, set Number of items to retain…
- How to Create a Traffic Light Graph in ExcelTo create a traffic light graph in Excel, put the values you want to assess in a column,…
- Agile Gantt Chart in Excel: Plan Sprints and ReleasesYes. You can make an Agile Gantt chart in Microsoft Excel by listing work items, adding start and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.