Pivot Tables are one of the most useful tools in Excel that there are. However, you can always find yourself in a lot of trouble when working with them.
One of these problems is certainly sorting the data, especially if this data is related to dates.
We will cover this problem in the text below.
Sort Dates in Pivot Table
For our example, we are going to use the table with NBA players that were born in April.
All of our players are sorted alphabetically. Let’s say that we want to sort them from oldest to youngest in our Pivot Table.
Now we will create our Pivot Table by selecting our range and then going to Insert >> Tables >> Pivot Table.
We will create this table in another sheet that we will simply call that sheet „Pivot Table“.
Now we will insert columns Player Name and Date of Birth in Rows Fields.
This is our Pivot Table for now:
To sort it out a little bit, we have to right-click anywhere on the table and then click PivotTable Options:
Then we go to the tab Display and then select Classic PivotTable layout:
Then we will remove Subtotals by clicking on the Pivot Table and choosing Design (from Pivot Table tools) >> Subtotals >> Do Not Show Subtotals.
Now our table looks way better:
What we can see is that Excel automatically added Years, Quarters, and Dates of Birth to our Pivot Table. We can choose what is shown here by clicking on the Pivot Table, and then going to PivotTable Analyze (again, in Pivot Table tools) and choosing Group Field in the Group sub-tab:
In the pop-up window that appears, we can choose the exact data that we want to be shown.
For our example, we will choose Days, Months, and Years.
Now our Pivot Table looks like this:
To order these players by their age, we have to change the order of our columns, as in Pivot Tables, the first column is the one that defines the order of our rows.
If we would go on and choose the Years column, then go on to sort from oldest to newest, nothing would have happened.
That is why we have to change the order of our columns.
We will put Years first, then Months, Days, and finally names of the players.
After that, we will select the Years column and choose Sort Oldest to Newest:
Finally, we got our players sorted from the oldest one to the youngest one in our Pivot Table:
