OmniDocPut Pivot Table Months in Calendar Order
Pivot table months sorting alphabetically? Learn how to put them in calendar order using real dates, a custom list, or numbered labels.
You built a pivot table from a monthly sales sheet, and the months appear as April, August, December, February and so on. The numbers are right, but the rows are in alphabetical order, which makes the report hard to read. This happens to anyone who summarizes data by month, whether the data came from a spreadsheet, a database export or a table copied out of a PDF.
The fix depends on why Excel sorts the months that way. Pivot tables sort text alphabetically by default, so if the month column holds words like "January," Excel has no reason to put them in calendar order. Once you tell Excel what the months are, or give it a sequence to follow, the rows line up correctly. This guide covers the common fixes for pivot table months not in order and when to use each one.
Why are pivot table months not in order? The short answer
Excel sorts text labels alphabetically, so you need to either give Excel real dates or teach it a month sequence. Choose the method that matches how your data is stored.
| Your situation | What to do | Why |
|---|---|---|
| Source column holds real dates | Group the date field by months | Excel sorts date groups chronologically without extra steps |
| Month names are plain text | Add a custom list of months in calendar order | Excel uses your sequence instead of the alphabet |
| You can edit the source data but not the list settings | Prefix labels with numbers, such as 01-Jan | Numbers sort in the right order and still read clearly |
| Only a few rows need changing | Drag the items into order by hand | Quick for a small, fixed report, but it can drift as data changes |
Before you start
Check these points before you change anything, because the wrong starting point is a common reason a fix fails.
- Confirm what the month column contains. Click a cell in the source column and look at the formula bar. A real date shows a date value, while text shows the month name as typed. Only real dates can be grouped by month.
- Check for mixed formats. A column that mixes "3/4/2024," "March 2024" and "2024-03" can sort unpredictably even after a fix. Make every entry use one format.
- Look at the source range. Make sure the pivot table points at the full data range, including any rows you added later. A stale range can hide new months and make the order look wrong.
- Decide whether the month should include the year. If your data covers more than one year, sorting by month name alone merges January 2024 and January 2025 into one row. Grouping by year and month keeps them apart.
- Know where the data came from. If the table was copied from a PDF, the values may be stored as text. Run OCR first if the PDF is scanned, then check the values again.
Putting pivot table months in calendar order, step by step
Start with the cleanest option, which is real dates. Use the custom list only when you cannot change the source values.
- Convert the month column to real dates. Select the column, then use your spreadsheet's text-to-date or date-format tools so each cell holds an actual date, such as 1/15/2024. Keep the day consistent, since the day does not affect the grouping.
- Refresh the source range. Make sure the pivot table's data range covers every converted row. Otherwise, some months may be missing from the report.
- Group the date field by months. In the pivot table, select a date value in the row labels and use the grouping option to group by months. If you have several years, add years to the grouping as well so each month stays tied to its year.
- Check the order. The groups should now run January through December. If they still look wrong, the column probably still contains some text entries.
- If the labels must stay as text, create a custom list. Open the workbook's list settings for custom sort orders and enter the twelve month names in calendar order. Save the list.
- Apply the custom list to the pivot field. Open the sort options for the row labels, choose a manual sort, and set the order to use your custom list. The rows will follow your calendar sequence.
- Use numbered labels as a fallback. If you cannot change list settings, rename the labels in the source, such as "01-Jan," "02-Feb" and so on. Numbered labels sort correctly by default and still read clearly.
- Recheck after new data arrives. Refresh the pivot table and look at the row order. New months added at the end can break a manual order, so re-check each time you update the report.
If your monthly figures are in a PDF, you can export the table to Excel from the OmniDoc desktop client's PDF editor, then build the pivot table from the exported sheet. Check the exported values carefully, since numbers and dates copied from a PDF sometimes arrive as text.
Check the result
Once the months are in order, confirm that the totals and the sequence are both correct before you share the report.
| Check | How to see it | If it's wrong |
|---|---|---|
| Months run January to December | Read the row labels from top to bottom | Look for text entries in the source column and convert them to dates |
| No month is missing | Compare the row count with the number of months in your data | Extend the data range and refresh the pivot table |
| Years stay separate | Look for January appearing twice or merged | Add the year to the grouping or use a year-and-month label |
| Totals match the source | Add up the source column and compare it with the grand total | Check for duplicate rows, hidden rows and blank cells in the source |
| Order holds after refresh | Refresh the pivot table and look again | Switch from a manual order to a date grouping or numbered labels |
If the months are still out of order, check whether the source column has a mix of text and dates. That mix is a common cause of a fix that looks right at first and then breaks. Also make sure you have not applied a manual sort that overrides the date grouping.
🎯 Which approach fits you?
Your choice depends mainly on whether you control the source data and how often the report changes.
- Recurring monthly reports: Use real dates with month grouping. The order holds as new rows come in, and you do not need to maintain a list.
- Shared workbooks with fixed labels: Use a custom list, since it keeps the labels as they are while fixing the sort.
- Quick one-off tables: Use numbered labels or a manual drag. These take little setup but need checking if the data changes.
❓ FAQ
1. Why does my pivot table sort months alphabetically even when they are dates?
The column probably holds text that looks like a date, not a real date value. Click a cell and check the formula bar, then convert the entries so Excel recognizes them as dates.
2. Can I keep my month names and still get calendar order?
Yes. Create a custom list with the month names in calendar order and apply it to the row labels' sort options. Your labels stay the same, and the rows follow your sequence.
3. Why did my manual sort break after I added new data?
A manual order usually places new items at the end or ignores them until you reorder. Switching to date grouping or numbered labels avoids this problem.
4. How do I handle months from more than one year?
Group by both year and month, or use labels that include the year, such as "2024-01." Otherwise, January from different years merges into one row.
📝 Summary
Pivot table months not in order usually means Excel is sorting text alphabetically. Give it real dates to group by month, or use a custom list or numbered labels when the data has to stay as text.
