ExcelInsider Team
While analyzing data, you might want to sort the fields to look for trends and find valuable insights. In Excel, sorting works a bit differently from regular tables. While Excel provides some automatic sorting methods for pivot tables, you might find manual sorting to be the right choice for you. In this article, we will show you how to sort a pivot table manually in Excel using different methods.
➤ Right-click on the row/column that you want to move.
➤ From the context menu, go to Move > Move “FieldName” to Destination.
➤ The FieldName will be the field that you are moving, and the Destination is the place you choose to move the field to.
Automatic sorting is great, but the data might need manual sorting in some cases. In this article, we will see how we can sort our pivot table manually in Excel. Try the methods below to sort your data as we did.
Manually Sorting by Manipulating Fields
To demonstrate the method, we have a pivot table here with employee attendance data. There are employee names, the departments they work in, the days they were present and absent, and the cities they live in. While we can automatically sort the rows, we can manually move them as well. Let’s sort this pivot table manually.
➤ In the default format, the employee names are at the beginning, the departments follow that, and the cities are at the end.
➤ We can drag and drop the rows to sort them in the pivot table.
➤ Now the pivot table looks like the following:
Moving the Row Labels Manually
Here, the employee names are at the top of the rows. The employee names are sorted using the data source by Excel. We can sort them automatically, but we want the employee names in an order that we like. Follow the steps below to learn how to do so:
➤ First, we must turn off automatic sorting so that our manual sorting does not change. Right-click on a cell that you are going to manually sort, an Employee in this case, and go to Sort > More Sort Options.
➤ Go to More Options in the new window.
➤ Uncheck the box that says “Sort automatically every time the report is updated” and press OK to confirm.
➤ The A4 cell contains Lucas King. Let’s say we want this to be under Jack Anderson, which is in the A7 cell now.
➤ Right-click on the A4 cell to open the context menu.
➤ Go to Move > Move “Lucas King” Down.
➤ Now, “Lucas King” has moved below “Jack Anderson”. You can play with the other options in the Move section to manually sort other data as well.
Note:
Manual sorting does not work on values. Only the rows and columns can be sorted manually. It does not work for calculated fields either.
Using Classic Pivot Table to Sort Manually
The classic pivot table gives us a bit more control over the table. It allows us to drag and drop fields, which is very useful for sorting the columns. Follow the steps below to enable classic mode and sort the table:
➤ Right-click on a cell of the table, and select PivotTable Options.
➤ Go to the Display tab, check the box that says “Classic PivotTable layout (enables dragging of fields in the grid)”, and hit OK.
➤Afterwards, the pivot table should look like the following:
➤ Now we can just drag and drop the columns manually:
➤ We can also drag and drop the fields from the PivotTable Fields section to the table.
Frequently Asked Questions
Why can’t I sort in Pivot?
Pivot table sorts values based on the fields in the row area. You cannot sort for all of the values unless you remove all but one row in the table.
How to sort values in PivotTable from largest to smallest?
Right-click on a value of the pivot table, and go to Sort > Sort Largest to Smallest.
Why is sort grayed out in PivotTable?
You probably downloaded the worksheet from the internet, and Excel is not allowing you to edit that for security reasons. You can change the file permission or click on Enable Editing in the window to enable the sort option again.
How to refresh a pivot table?
There are three ways to refresh the pivot table. First one is to right-click on any cell of the pivot table and hit Refresh. You can click on a cell of the pivot table, go to the PivotTable Analyze tab, find the Data section, and hit Refresh. You can also click on any cell of the pivot table and press Alt+F5.
Why isn’t my PivotTable sorting correctly?
The reason behind that is that a pivot table sorts not only by the values but also by the rows associated. If you remove the unnecessary rows in your pivot table, the data will be sorted properly.
Wrapping Up
In this article, we have learned the ways we can sort a pivot table manually in Excel. If you have any confusion, leave it in the comment section so that we can get back to you. Bookmark the site to find more tutorials about Microsoft Excel and Pivot Tables. We will see you in the next article.
