ExcelInsider Team
Using pivot tables is an excellent way to analyze your data better. Due to how pivot tables work, the sorting system might not work as expected. Pivot tables are very different from pivot tables, and if you could not make sorting work properly, it would be very frustrating. In this article, we will learn how to fix the pivot table sorting system. We will show you various ways to fix this and ensure that you can sort your data in the pivot table properly.
➤ Remove the unneeded fields from the Rows section of the PivotTable Fields by selecting the Field and clicking Remove Field.
➤ Sort the column again by right-clicking the value field and going to Sort > Sort Largest to Smallest / Sort Smallest to Largest.
There could be a lot of reasons why the pivot table sort is not working for you. While this was one of the solutions, it might not work for you. That is why we have provided three more solutions in this article so that you can go through all of that and choose the one that works for you. Let’s begin..
Changing the Rows to Fix Sorting
To demonstrate the fix, we have a pivot table with some office items. There are categories of items, the supplier names, the stock amount, and the reorder level. The pivot table is shown in tabular layout without subtotals. Sorting the pivot table does not really work because pivot tables only sort for the rows, not for the whole table. Here is how we can fix that:
➤ Let’s do normal sorting first. To sort smallest to largest according to the stock level, click on a cell in column D.
➤ Go to the Data tab, find the Sort & Filter section, and click on the “Sort Smallest to Largest” icon.
➤ It is clear that the sorting is not working as we want. The stock numbers are all over the place. The reason is that the sorting only works for each “Rows” part of the pivot table. Let’s uncheck the Category field from the PivotTable Fields section.
➤ Now the stock levels look pretty sorted. However, there are still items in C11:C13 that show us that they aren’t. Let’s remove the Item Name as well.
➤ As we can see, the sorting function is working properly now.
Turning Off Automatic Sorting
By default, Excel automatically sorts the pivot table when it is updated. If you have a ton of data, this automatic sorting can cause issues. Follow the steps below to fix it:
➤ Right-click on the field that you want to sort. This would not work for fields that belong in the Values section. We are choosing the Category field.
➤ Select Sort > More Sort Options….
➤ In the Sort window, go to More Options…
➤ Uncheck Sort automatically every time the report is updated from the Autosort section of the new window called Mort Sort Options.
➤ Hit OK in that window, and OK again in the previous window.
Disabling Custom Lists
Data in a pivot table is usually sorted using a custom list from Microsoft. You can disable this custom list to check whether it solves the sorting for your data or not. Follow the steps below to do that:
➤ Right-click on any cell of the pivot table and select PivotTable Settings.
➤ In the new window called PivotTable Options, go to the Totals & Filters tab, and uncheck Use Custom Lists when sorting. Hit OK afterwards.
Unpivoting and Manually Sorting
If everything else fails, this can be the last resort for you to go to. We are going to unpivot the table and sort the table manually. Follow the steps properly:
➤ Select the full table range. Here, it will be A3:E16. Press Ctrl+C to copy the data
➤ Select a new cell to paste the data. We are selecting G3 for this example. Do not paste the data yet.
➤ From the Home tab, go to the Clipboard section, and select Paste > Paste Values > Values and Number Formatting.
➤ Now the new table, starting from G3, looks like the following .
➤ Right-click on a stock number, for example, J5 (Chloe Allen, 32), and go to Sort > Sort Smallest to Largest.
➤ Now the table will be properly sorted.
Frequently Asked Questions
Why is the sort greyed out in the Excel pivot table?
It is probably because you have not clicked a field row in the pivot table. A sheet can contain a pivot table and other values as well. To make sure that the sorting option works, click on a cell of the pivot table.
How to clear sorting in a pivot table?
Pivot tables can usually be sorted from smallest to largest or the opposite. Once you choose a sorting method, you cannot go back to the way it was originally sorted without any order in the beginning. You can either sort it again, but not like the default format.
How to refresh a PivotTable?
Click on any cell of the pivot table, and press Alt+F5 to refresh the pivot table. You can also right-click on the cell and hit Refresh to refresh the pivot table.
How do you clear sorting in Excel?
In a regular table, go to the Editing section of the Home tab. Find the Sort & Filter button, and click on Clear to clear the sorting of the table in Excel.
Why is the pivot table not pulling the correct data?
Unless you select the fields you want in the pivot table, it will not show data by default. If you have updated the source data after creating the pivot table, you have to refresh the pivot table to pull the new data in the pivot table.
Wrapping Up
In this article, we learned how to fix pivot table sort not working issue. In case the methods provided in this article did not solve your issue, please leave a comment below so that we can check how to fix your particular issue. If the methods did fix your issue, comment down with your feedback.
