Excel’s sorting feature is incredibly useful for organizing data in spreadsheets. With just a few clicks, you can reorder rows or columns alphabetically, numerically, or custom sort based on any criteria. However, there are some limitations to what Excel can and cannot sort.
Excel Sorting Basics
Before getting into what cannot be sorted in Excel, let’s do a quick review of how sorting works in Excel:
- Select the data you want to sort. This can be a single column, multiple columns, or a table.
- Go to the Data tab and click the Sort command.
- In the Sort dialog box, choose the column you want to sort by, and whether you want to sort ascending or descending.
- You can add additional sort columns by clicking Add Level.
- Click OK to perform the sort.
Simple enough, right? This works great for straightforward data sets. But there are some limitations.
Formulas
One major thing you cannot directly sort in Excel are cells containing formulas. If you try to sort cells with formulas, you usually get an error. That’s because the cell references in formulas don’t update when sorted.
For example, if you have formulas like =A1+B1 in row 1 and =A2+B2 in row 2, then sort rows 1 and 2, the formulas will still reference A1+B1 and A2+B2 respectively. The cell references don’t shift when sorted.
There are a couple workarounds for this:
- Select and copy just the formula cells, then use Paste Special > Values to convert formulas to values. Then sort the values.
- Use structured table references in formulas, like =Table1[@Amount]+Table1[@Price]. Table references will update when sorting the table data.
But you cannot directly sort cells containing standard cell references in formulas.
Merged Cells
Merged cells also cannot be directly sorted in Excel. When you try to sort cells that contain merged cells, you’ll get an error. This is because the sort doesn’t know how to handle the merged cells properly.
Again, there are some workarounds:
- Umerge the merged cells first before sorting.
- Copy just the visible merged cells, Paste Special > Values to unmerge, then sort.
But the act of sorting itself will not work on merged cells.
Filtered Data
If you have an Excel data set that is currently filtered to show only certain rows, you cannot sort the visible filtered rows. You’ll need to clear the filter first before sorting.
The reason is that sorting only affects the visible cells. If you sorted filtered data, it would change the sort order within the filter but would not sort the entire data set.
The proper workflow is:
- Clear any filters.
- Sort the data.
- Reapply filters after sorting.
Pivot Tables
Pivot table data cannot be directly sorted like normal Excel data. The sorting needs to be done through the pivot table fields and pivot table options.
To sort a pivot table:
- Right click any cell in the pivot table.
- Go to Sort > Sort by, then choose the field to sort by.
- You can also sort based on further fields by going to Sort > More Sort Options.
The key is that the sorting must be done through pivot table options, not regular Excel sorting.
Slicers
Slicers, which filter pivot tables, also cannot be directly sorted. The sort order of slicer items is set in the slicer options.
To sort a slicer:
- Right click the slicer.
- In the Slicer Settings dialog, go to Sort Options.
- Choose which field to sort the slicer items by, and whether to sort A-Z or Z-A.
Again, the key point is that slicers have separate controls for sorting, not the regular Excel sorting feature.
Charts and Shapes
Objects like charts, images, and shapes that are inserted into Excel cannot be directly sorted. These are not standard data range objects.
The only way to sort these types of objects is to manually drag them into place in the desired order.
Data Validation Lists
Drop down lists created through Excel data validation cannot be sorted with the normal Excel sorting tools. The items can only be manually sorted or sorted through applying proper data source sorting.
To manually sort a data validation list, you would need to:
- Go to the cells containing the list items.
- Manually edit the order of the list values.
- Go back to the data validation cell and reapply the list range.
Alternatively, if the list is based on a data table range, you can sort the source table which will cascade to the list.
But the data validation cell itself cannot be directly sorted.
Conditional Formatting Rules
Conditional formatting rules also cannot be sorted directly in Excel. The rules will remain applied to the same cells even if the data is sorted.
To properly sort conditional formatted data:
- Clear the conditional formatting rules.
- Sort the data as desired.
- Then reapply the conditional formatting.
Again, the key is that Excel provides no mechanism to sort the conditional formats themselves.
Conclusion
While Excel’s sorting feature is powerful, it does have some limitations in terms of what can be directly sorted:
- Formulas with cell references
- Merged cells
- Filtered data sets
- Pivot tables
- Slicers
- Charts, shapes, and other objects
- Data validation lists
- Conditional formatting rules
In many cases, there are workarounds to allow these data types to be sorted. But in general, Excel’s sorting capability applies specifically to standard data ranges and tables.
Being aware of these limitations can help you apply sorting appropriately in your Excel workbooks and avoid errors. With some extra steps, you can often get these other data types into a sortable format as well.
