Sort Pivot Table by Month

So far, you have a good idea of how powerful Pivot Tables are and how useful they can be in conducting daily activities.

There are a lot of ways to sort our data in the Pivot Table, and in the text below, we will show how to sort the data in the table by month.

Sorting the Pivot Table by Month

For our example, we will create a table of banking loans with the names of banking officers, loan amounts, durations, and dates of approval.

We will create the Pivot Table out of this data to find the number of loans that were approved by each banking officer and to see the dates of approval.

Our Pivot Table will look like this:

We have placed banking officers’ names and dates of approval into the Rows field, and we have put the sum of loans into the Values field.

As seen, our data is arranged in alphabetical order, but the referral point is a column of banking officers. Now, to have our data arranged by month, we have to make our months column first in the Pivot Table.

Notice that Excel has automatically added a Months column, although the date was inputted in month/day/year format.

We will place the date of approval in the first column and remove the banking officer column. We will be left with this table:

All we have to do now is to click on the downward arrow and click Sort Oldest to Newest:

Now, our table looks like this:

We will add banking officers again, beneath the month in the Row fields, and finally, have our table set:

You can also define your custom lists of monthly orders by going to File >> Options >> Advanced. Then you scroll to the General section where you will find Edit Custom Lists.

When you click on it, all predefined custom lists in Excel that you have at your disposal.

You can simply click add and define your list of months, whichever you like.

To input your list as a valid one for Pivot Table, you have to click on the dropdown arrow, and select More Sort Options.

You then have to select More Options:

Then unclick Sort automatically every time the report is updated and in the first key sort order dropdown, select the item from the list you created:

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