Download the practice workbook 👉 HERE and follow along.
Want to see your data organized by month or year in Excel? Pivot Tables make this super simple!
Whether you’re tracking sales, expenses, or project timelines, grouping dates in a Pivot Table helps you analyze trends faster.
Let’s walk through how to do this step by step.
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:
- Select your data.
- Go to Insert > Table and check “My table has headers.”
- Name your table (e.g., “TableSales”).
Step 2: Insert a Pivot Table
- Click anywhere in your table.
- Go to Table Design > Summarize with PivotTable.
- Choose to place your Pivot Table on a new worksheet.
Step 3: Add Fields to Your Pivot Table
Now, build your Pivot Table:
- Drag the Order Date field to the Rows section.
- Drag the Salesperson field to the Filters section.
- 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:
- Click on any value in the Pivot Table.
- Go to Home > Number Group > Comma Style.
- 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:
- Right-click any date in the Rows section.
- Select Group, or go to PivotTable Analyze > Group > Group.
- Choose the start and end points for your custom group. For example, you might group data by months or quarters only.
Featured Course
Excel Essentials for the Real World
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:
- Remove the default “Months” field from the Rows section of the Pivot Table.
- Replace it with the Days field.
- 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
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:
- Insert a new Pivot Table.
- 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
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:
- From your TableSales table, drag Salesperson to the Filters section and Order Amount to the Values section.
- From your Calendar table, drag MMM-YYYY to the Rows section.
- The values will already have consistent formatting, thanks to the Data Model!
Featured Course
Excel Pivot Tables & Dashboards
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
- Use the Salesperson filter to select a specific person, like “Amy Trefl.”
- 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:
- Right-click on any date in the Rows section.
- Select Filter > Value Filters.
- 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.
Featured Course
Master Excel Power Query – Beginner to Pro
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:
- Open the Data Model:
- Go to Data > Data Tools > Manage Data Model.
- Add a new column:
- Click the Add Column header (on the far right of the table).
- Enter the following formula in the Formula Bar:
=INT( ( 'Calendar'[Month Number] + 2) / 3 )
- Rename the column to Quarters.
Step 2: What Does the Formula Do?
Here’s how the formula works:
- Add 2 to the month number (e.g., May = 5 → 5 + 2 = 7).
- Divide by 3 to determine the quarter (e.g., 7 ÷ 3 = 2.33).
- Round down using the
INTfunction 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:
- Remove the “Date” field from the Rows section.
- Move the MMM-YYYY field from Filters to Rows.
- 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):
- Open the dropdown in the Quarters filter.
- 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:
- Open the Data Model:
- Go to Data > Manage Data Model.
- 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
Want to take your Excel skills to the next level? Practice everything you’ve learned with our free downloadable workbook!
Why Download the Workbook?
- Learn by doing: Apply techniques like group dates in pivot tables, creating calendar tables, and sorting data by quarters directly in Excel.
- Build confidence: Tackle real-world scenarios with guided exercises.
- Save time: Skip the setup—our workbook is ready to use!
Get Started Now!
Click the link to download the workbook and start mastering Excel with practical examples. It’s completely free and designed for learners of all levels.
Featured Bundle
Black Belt Excel Bundle
Leila Gharani
I’ve spent over 20 years helping businesses use data to improve their results. I've worked as an economist and a consultant. I spent 12 years in corporate roles across finance, operations, and IT—managing SAP and Oracle projects.
As a 7-time Microsoft MVP, I have deep knowledge of tools like Excel and Power BI.
I love making complex tech topics easy to understand. There’s nothing better than helping someone realize they can do it themselves. I’m always learning new things too and finding better ways to help others succeed.
