How to Group Dates in Excel Pivot Tables (+ Custom Formats and Fiscal Periods)

Get the PivotTable Date Grouping Workbook

Practice grouping dates by month, quarter, and year. Includes the calendar table and sort helper already built, so you can follow the tutorial without setting up the Data Model from scratch.

How to Group Dates by Months in a Pivot Table

Step 1: Start with Your Data

Make sure your dataset is ready. For this example, we’re using a table with these columns:

• Salesperson
• Order ID
• Order Date
• Order Amount

Turn your dataset into a proper Excel Table for easy management:

1. Select your data.
2. Go to Insert > Table and check “My table has headers.”
3. Name your table (e.g., “TableSales”).

Step 2: Insert a Pivot Table

1. Click anywhere in your table.
2. Go to Table Design > Summarize with PivotTable.
3. Choose to place your Pivot Table on a new worksheet.

Step 3: Add Fields to Your Pivot Table

Now, build your Pivot Table:

1. Drag the Order Date field to the Rows section.
2. Drag the Salesperson field to the Filters section.
3. Drag the Order Amount field to the Values section.

💡 Tip: To see sales for a specific salesperson, use the Filter dropdown at the top.

Step 4: Group Dates by Month and Year

After dragging the Order date into the rows, Excel automatically groups your dates into:

• Years
• Quarters
• Months
• Days

💡 Tip: Want to undo the date grouping? Right-click a date and select Ungroup.

Step 5: Format Your Pivot Table

To make your table easier to read:

1. Click on any value in the Pivot Table.
2. Go to Home > Number Group > Comma Style.
3. Remove decimals if needed.

Bonus Tip: Customize Your Date Grouping

If Excel’s default groups don’t suit your needs, you can create your own:

1. Right-click any date in the Rows section.
2. Select Group, or go to PivotTable Analyze > Group > Group.
3. Choose the start and end points for your custom group. For example, you might group data by months or quarters only.

For 10 more Pivot Table techniques (drill-down, calculated fields, Timeline Slicers, Copilot), see our advanced Pivot Table tips.

How to Reformat and Sort Dates in Excel Pivot Tables

Want to display your Pivot Table dates in a clean, custom format like “MMM-YYYY” (e.g., Jan-2021)?

Or sort your rows chronologically instead of alphabetically?

We’ll show you how to fix common issues like skipped dates, unsorted month-year labels, and grouping errors—all using Excel’s Data Model.

Step 1: Start with the Right Date Format

By default, Excel groups dates in Pivot Tables into Years, Quarters, Months, and Days. But if you want a custom format like “MMM-YYYY,” here’s what to do:

1. Remove the default “Months” field from the Rows section of the Pivot Table.
2. Replace it with the Days field.
3. Notice the raw date display—don’t worry! We’ll fix this in the next steps.

💡Notice that we are missing days when sales did not occur. For example, 7-Jan, 8-Jan, etc.

Featured Bundle

Power Excel Bundle

10x your productivity in Excel 💪 by mastering Excel’s Power Tools in ONE convenient (cost savings) bundle. Learnings apply to Power BI as well.

Learn More

Step 2: Use the Data Model for Advanced Formatting

The Data Model allows you to manage continuous date ranges and create custom formats for Pivot Tables. To start:

1. Insert a new Pivot Table.
2. Check the box labeled “Add this data to the Data Model” before clicking OK.

Now, let’s create a calendar table to handle your custom formatting.

Step 3: Create a Calendar Table in the Data Model

A calendar table is a special table containing continuous dates and additional fields for grouping and formatting. Here’s how to set it up:

• Open the Data Model: Go to Data > Manage Data Model.

• Create a new calendar table: Select your Order Date column, then go to Design > Calendar > Date Table > New.

• Create a new calendar table: Select your Order Date column, then go to Design > Calendar > Date Table > New.
• The calendar table will include a continuous range of dates (e.g., 1-Jan-2021 to 31-Dec-2021) and fields like:
  • Years
  • Months
  • Day Names
  • Week Numbers

Feel free to add your own custom fields later!

Before you start dragging fields into the Pivot Table sections, we’re going to go into the Data Model and create a special table that will support our needed date formatting.

Step 4: Build a Relationship Between Tables

To connect your data to the calendar table:

• Switch to Diagram View: Go to Home > View > Diagram View.

• Drag the Date field from the calendar table to the Order Date field in your sales data table.

This creates a relationship, enabling your Pivot Table to reference custom date formats like “MMM-YYYY.”

Featured Course

Excel Data Modeling with Power Pivot & DAX

For Excel users whose data is spread over more than one table. Connect them properly, handle millions of rows, write the calculations pivot tables can’t. Same skills run Power BI.

Learn More

Step 5: Sort Month-Year Labels Chronologically

Excel treats “MMM-YYYY” (e.g., Jan-2021) as text, which can result in alphabetical sorting. To fix this:

1. Add a helper column in the Data Model to create a numeric “stamp” for sorting:

• Go to the calendar table and click Add Column.
• Use this formula in the formula bar:

='Calendar'[Year] * 100 + 'Calendar'[Month Number]

• Rename the column to YYYYMM.

2. Sort the “MMM-YYYY” column:

• Select Home > Sort by Column.

• In the dialog, choose “MMM-YYYY” as the field to sort and “YYYYMM” as the sorting key.

3. Return to Excel, and your Pivot Table will now display rows sorted chronologically!

Step 6: Hide Unnecessary Fields

To avoid confusion, hide the original Order Date field from the Pivot Table:

• Right-click Order Date in the Data Model.
• Select Hide from Client Tools.

Back in Excel, only the calendar table’s fields will appear in the Pivot Table field list.

Step 7: Create Your Final Pivot Table

With everything set up, build your Pivot Table:

1. From your TableSales table, drag Salesperson to the Filters section and Order Amount to the Values section.
2. From your Calendar table, drag MMM-YYYY to the Rows section.
3. The values will already have consistent formatting, thanks to the Data Model!

Featured Course

Excel Pivot Table Mastery: Basics to Dashboards

Use pivot tables to summarize thousands of rows in seconds and build dashboards that update themselves. No manual sorting and no complicated formulas.

Learn More

How to Show Missing Dates in Excel Pivot Tables

Want to display all dates in your Pivot Table, even those without data? For example, you might want to see all days in January, including those with no sales.

This is especially helpful for spotting gaps or analyzing trends. Here’s how to do it step by step.

Step 1: Set Up Your Pivot Table

Start by creating a Pivot Table that includes:

• The Salesperson field in the Filters section.
• The MMM-YYYY field (e.g., Jan-2021) in the Filters section.

• The Date field from the Calendar table in the Rows section.

Step 2: Filter the Data

1. Use the Salesperson filter to select a specific person, like “Amy Trefl.”
2. Use the MMM-YYYY filter to select the month and year you want to analyze, such as “Jan-2021.”

Step 3: Enable Missing Dates in the Pivot Table

By default, Pivot Tables only show dates where data exists. To display all dates, including those with no sales:

• Select any cell in your Pivot Table.
• Go to PivotTable Analyze > Options (in the Pivot Table group).

• In the PivotTable Options dialog box:
  • Select the Display tab.
  • Check the box for Show items with no data on rows.
  • Click OK.

Now, your Pivot Table will show an unbroken list of dates for January 2021. You’ll see sales data on the days Amy made sales and blank cells for days without sales.

Step 4: Highlight Non-Sales Days

If you want to focus only on dates where Amy had no sales:

1. Right-click on any date in the Rows section.
2. Select Filter > Value Filters.
3. In the dialog box, set the condition to Equals 0 (zero).

Your Pivot Table will now display only the dates where no sales occurred for Amy in January 2021.

If you need to combine data from multiple sheets before building the Pivot Table, see our guide on creating a Pivot Table from multiple sheets.

How to Display Sales by Quarter in Excel Pivot Tables

Want to analyze your sales quarter by quarter? Pivot Tables make it simple to group data and filter for specific time periods, such as Q1 or Q4.

In this guide, you’ll learn how to create a helper column for quarters using the Data Model and use it to build a quarterly sales report.

Step 1: Add a Helper Column for Quarters

To group your data by quarters, we’ll create a helper column in the Data Model. Follow these steps:

1. Open the Data Model:
   • Go to Data > Data Tools > Manage Data Model.
2. Add a new column:
   • Click the Add Column header (on the far right of the table).
3. Enter the following formula in the Formula Bar:

=INT( ( 'Calendar'[Month Number] + 2) / 3 )

4. Rename the column to Quarters.

Step 2: What Does the Formula Do?

Here’s how the formula works:

1. Add 2 to the month number (e.g., May = 5 → 5 + 2 = 7).
2. Divide by 3 to determine the quarter (e.g., 7 ÷ 3 = 2.33).
3. Round down using the INT function to keep only the whole number (e.g., 2.33 → 2).

Examples:

• May (5): 5 + 2 = 7 → 7 ÷ 3 = 2.33 → Quarter 2.
• December (12): 12 + 2 = 14 → 14 ÷ 3 = 4.67 → Quarter 4.

This formula assigns each month to the correct quarter:

• Q1: January – March
• Q2: April – June
• Q3: July – September
• Q4: October – December

Step 3: Build Your Quarterly Sales Report

Now that your helper column is ready, let’s update the Pivot Table:

1. Remove the “Date” field from the Rows section.
2. Move the MMM-YYYY field from Filters to Rows.
3. Add the Quarters field to the Filters section, beneath Salesperson.

Step 4: Filter by a Specific Quarter

To focus on a specific quarter (e.g., Q1):

1. Open the dropdown in the Quarters filter.
2. Select 1st Quarter.

The Pivot Table will now display sales for January, February, and March across all years in the dataset (e.g., 2021 and 2022).

How to Add New Dates to the Calendar Table

Have you ever added new dates to your dataset only to find that your Pivot Table doesn’t update?

This happens because the Data Model doesn’t automatically recognize changes to the date range.

But don’t worry! Here’s how to fix it and keep your reports accurate.

Step 1: Manually Update the Date Range in the Data Model

If your dataset now includes additional dates, follow these steps to inform the Data Model:

1. Open the Data Model:
   • Go to Data > Manage Data Model.
2. Update the calendar table:
   • Go to Design > Calendar > Date Table > Update Range.

This will expand the date range in your calendar table to include the new dates. Your Pivot Table will now recognize and display the updated information.

Step 2: Automate the Process

Manually updating the date range every time your dataset changes can be tedious. To save time, you can automate this process using either Power Query or DAX formulas.

• Power Query:
  Power Query can dynamically create or update a calendar table based on the range of dates in your dataset. When your data source changes, simply refresh the query, and the date range will automatically update.
• DAX Formulas:
  With DAX, you can create a calendar table that adjusts automatically based on the minimum and maximum dates in your data. This ensures your calendar table always stays in sync with your dataset.

Want to learn how to use these powerful tools? Check out our courses on Power Query and Power Pivot with DAX.

Download the Workbook

Featured Bundle

Black Belt Excel Bundle

This Excel Black Belt Package includes EIGHT of our Popular Courses. You’ll learn high-value, in-depth Excel skills that solve real problems.

Learn More

LinkedIn Twitter YouTube

Leila Gharani

Founder of XelPlus and ten-time Microsoft MVP. Leila helps over 500,000 professionals master Excel, Power BI, and data automation through practical, real-world training.

More About Leila Join 500,000+ professionals in our courses