Sort Dates in Pivot Table

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.

Table

Description automatically generated

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:

Graphical user interface, application

Description automatically generated

To sort it out a little bit, we have to right-click anywhere on the table and then click PivotTable Options:

Graphical user interface, application

Description automatically generated

Then we go to the tab Display and then select Classic PivotTable layout:

Graphical user interface, text, application, email

Description automatically generated

Then we will remove Subtotals by clicking on the Pivot Table and choosing Design (from Pivot Table tools) >> Subtotals >> Do Not Show Subtotals.

Graphical user interface, application

Description automatically generated

Now our table looks way better:

Table

Description automatically generated

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:

Graphical user interface, text, application

Description automatically generated

In the pop-up window that appears, we can choose the exact data that we want to be shown.

Graphical user interface, application

Description automatically generated

For our example, we will choose Days, Months, and Years.

Now our Pivot Table looks like this:

Table

Description automatically generated

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.

Graphical user interface, text, application, email

Description automatically generated

After that, we will select the Years column and choose Sort Oldest to Newest:

Graphical user interface, text, application

Description automatically generated

Finally, we got our players sorted from the oldest one to the youngest one in our Pivot Table:

Table

Description automatically generated

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.