How to Group by Month Using PivotTable in Excel

Grouping data by month in a PivotTable is useful when you need to summarize a dataset with date values in it.

Excel’s PivotTable tool allows users to explore and summarize their data quickly. Users who require data at a monthly level can take advantage of the PivotTable to achieve their desired reports.

Let’s look at a sample dataset we can summarize into a monthly report using Excel’s PivotTable feature.

Suppose you have a dataset of orders. Each row in your dataset has a unique order ID, the date the order was made, and a product description of what was ordered. We want to know which month had the most orders. How can we accomplish this using a PivotTable?

order data table to group by month in a pivot table

We can use the Excel PivotTable feature to reorganize our dataset into different types of summaries. For example, we can determine how many orders were made at each branch or how many times a certain product was ordered.

Since we require monthly data, we can use the date field to summarize our orders by month. If the date values of your original spreadsheet are all valid dates, Excel can quickly extract the month from your dataset.

This use case is just one way a PivotTable can help explore datasets with date values. We can also group these values by day, week, quarter, or year.

Now that we know when to use the PivotTable to get monthly summaries, let’s dive into how this looks on an actual spreadsheet.

 

 

A Real Example of Grouping Data by Month in a PivotTable in Excel

Let’s take a look at a real example of a spreadsheet that groups by month Using a Pivot Table in Excel.

The PivotTable seen below gives us a quick overview of the number of monthly sales. Since each order_id is unique, we can set up a column to count the number of unique order_ids for a given month. Each month is also placed under its corresponding year.

order data displayed in a pivot table grouped by month

You can make your own copy of the spreadsheet above using the link attached below. 

If you’re ready to use a PivotTable to display monthly data in Excel, read our guide in the next section to learn how to do it yourself.

 

 

How to Group by Month in PivotTable in Excel 

This section will guide you through each step needed to start using an Excel PivotTable to organize your data by month. You’ll also learn how to organize your dataset by both month and year.

Follow these steps to start using the PivotTable feature:

  1. First, select any cell in the dataset you want to group by month using the PivotTable.
    source data for pivot table
  2. Next, look for the PivotTable option in the Insert tab.
    Add new pivot table to sheet
  3. In the new dialog box, select where you want the PivotTable to be placed. In this example, we’ll add our PivotTable to a new worksheet.
    select new worksheet
  4. You should now have a new empty PivotTable. A PivotTable panel will also appear on the right-hand side of the screen.
    Group by Month Using PivotTable in Excel
  5. In the new panel, drag the date field into the Rows area below. This will instruct Excel to group your data according to their date.
    add date as rows in pivot table in Excel
  6. Once the date field has been added, you may also add additional date fields such as Quarters and Years.
    Group by Month Using PivotTable in Excel
  7. Since we want to count the number of unique orders, we can drag the order_id field into the Values area. By default, Excel might set the new field as a summation. We can edit the field’s properties to convert it into a count aggregation instead.
    add count of order_id to values
  8. Your PivotTable should now show the number of orders per month. Users have the option of adding new labels to these fields if needed.
    Group by Month Using PivotTable in Excel
  9. In the example below, we’ve added the automatically generated Years field into our Rows area. This gives users a view of both monthly and yearly order totals.
    Group by Month Using PivotTable in Excel with additional year grouping

 

 

Frequently Asked Questions (FAQ)

    1. Why does my PivotTable refuse to group by month?
      Your original dataset may include missing or incorrectly formatted dates. Before creating a PivotTable, make sure that your date fields are formatted correctly.
    2. How do I ungroup my PivotTable report by month?
      If you need to ungroup values in your PivotTable, you can simply select on the column you want to ungroup by and click on the Ungroup option found in the PivotTable Analyze tab. In the example below, we’ve ungrouped our monthly data so that each row is now an individual date.

 

 

This step-by-step guide should be all you need to group data by month using a PivotTable in Excel. We’ve shown you how easy it is to adjust a PivotTable to match the level of granularity you need.

Excel’s PivotTable function is a powerful tool you can use to explore your dataset. With so many other Excel functions available, you can surely find one that suits your use case. 

Are you interested in learning more about what Excel can do? Subscribe to our newsletter to find out about the latest Excel guides and tutorials from us. 

Get emails from us about Excel.

Our goal this year is to create lots of rich, bite-sized tutorials for Excel users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

 

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like