How to Sort Months in Excel Pivot Table: Easy Guide

If you’ve ever tried to sort months chronologically in an Excel Pivot Table, you may have run into issues with the months being sorted alphabetically instead (April, August, December, etc.). Luckily, there is an easy fix to sort the months in proper calendar order in your Pivot Table. This article will walk you through the steps to chronologically sort months in an Excel Pivot Table so you can easily analyze your data by month.

Why Months Get Sorted Alphabetically in Pivot Tables

When you add a field containing month names to a Pivot Table in Excel, the months will automatically get sorted in alphabetical order by default. This is because Excel treats the month names as text strings and applies an A-to-Z sort, rather than recognizing them as dates and sorting them chronologically.

While an alphabetical sort may work fine in some cases, it makes the data much harder to analyze when you want to view metrics and trends over time with the months in proper calendar order from January to December.

For example, if you have a Pivot Table summarizing monthly sales data, an alphabetical sort will place April’s numbers at the top, followed by August, December, February, and so on. This makes it very difficult to compare the sales performance from one month to the next in a meaningful way. By sorting the months chronologically instead, you can easily scan down the list from January to December to spot upward or downward trends over the course of the year.

Steps to Sort Months in Chronological Order

To get your Pivot Table to sort the months chronologically instead of alphabetically, follow these steps:

1. Create your Pivot Table as usual, adding the fields you want to summarize and analyze, including the field with the month names.
2. In the field list, click on the month field you want to sort and select “More Sort Options” from the dropdown menu.
3. In the Sort dialog box that appears, select “More Options”.
4. Under “First key sort order”, click the checkbox for “Sort by” and select “Month” from the dropdown list.
5. Click OK to close the dialogs and return to your Pivot Table.

The months in your Pivot Table should now appear in proper chronological order from January to December. Here’s a table summarizing the steps:

Additional Tip: If your month names are abbreviated (e.g. Jan, Feb, Mar), Excel should still recognize and sort them correctly using the “Sort by Month” option. However, if you run into any issues, you can modify your source data to use the full month names instead and then refresh the Pivot Table.

Sorting Months in Pivot Tables Using a Formula

An alternative approach is to use a formula to assign a numerical sort order to the months. This involves adding a helper column to your data that will map each month name to a number (e.g. 1 for January, 2 for February, etc.) Here are the steps:

1. In your source data, insert a new column next to the month names column.
2. In the new column, use a formula to assign a numerical value to each month, for example:

   =MONTH(1&A2)

Where A2 is the cell containing the month name. This formula converts the month name to a serial number that Excel recognizes as a month value.

3. Create your Pivot Table, adding the new helper column you created instead of the original month names column.
4. Right-click the helper column in the Pivot Table and select “Field Settings”.
5. In Field Settings, change the “Custom Name” to just “Month” and click OK.
6. The Pivot Table will now show the months sorted from 1 to 12. To display the month names instead of numbers, right-click any month cell and select “Group”.
7. With the grouped months selected, go to Field Settings again and change the “Name” and “Custom Name” to “Month”. Click OK.

The months should now display in proper chronological order with their names rather than numbers. Here are the steps in table form:

While this formula method takes a few more steps than using the built-in month sort option, it can be useful in cases where the built-in sort doesn’t work correctly, or if you want to apply a custom sort order to the months (e.g. fiscal year starting in April).

Troubleshooting Month Sorting Issues

If you tried the steps above and your months are still not sorting chronologically, here are a few things to double-check:

• Make sure the source data has the month names spelled correctly and consistently (e.g. not a mix of “January” and “Jan”)
• If using the formula method, verify that the formula is filled down correctly for all rows and returns a valid month number for each cell
• Check for any extra spaces, punctuation or other characters in the month name cells that could interfere with sorting
• Ensure you have followed all the steps in the correct order and clicked “OK” to apply the changes

If you’ve checked all these things and still can’t get your months to sort chronologically, try creating a new Pivot Table from scratch with just the essential columns and double-check the sorting steps as you go. If the issue persists, there may be a problem with your source data that needs to be investigated further.

Final Thoughts

By following the steps outlined above, you can easily sort the months in proper chronological order in an Excel Pivot Table. Whether you use the built-in month sorting option or create your own custom sort order with a formula, this will allow you to analyze and visualize your data by month more effectively.

Remember, the key steps are:

1. Access the Pivot Table’s sort options for the month field
2. Select to sort by “Month” rather than the default alphabetical sort
3. Alternatively, create a helper column that assigns a numeric sort order to each month
4. Use the helper column in the Pivot Table and format it to display the month names

With your months sorted from January to December, you’ll be able to scan through the data and identify patterns, trends and insights more easily. Whether you’re analyzing sales figures, website traffic, budget data or other metrics, putting the months in calendar order is an important step in creating an effective Pivot Table.

FAQs

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.