PivotTable Month Groups

Jeff Lenning | May 1, 2015 | Comments Off

There are often several ways in Excel to accomplish any goal. For example, when building reports, we can use formulas or PivotTable. When building a formula-based report, we typically set up the report labels as desired and then write Excel formulas to compute the amount values and subtotals. This is a terrific approach when we have specific and rigid report structure requirements, such as preparing financial statements. However, reports without such strict layouts can often be built with the PivotTable feature instead of with formulas. When we build a PivotTable report, we’re able to take advantage of the robust capabilities of the feature, including its ability to automatically group transactions by month. Let’s check it out.

PivotTable Basics

This feature creates a PivotTable report by summarizing a data table. Let’s say we’ve exported some transactions from our accounting system. The data contains TransID, AcctNum, Account, Date and Amount columns (Figure 1).

Figure 1

We’d like to view a summary report by account with monthly columns. To create the desired PivotTable report from these transactions, we select any cell in the data table and click the following icon:

Insert > PivotTable

In the resulting Create PivotTable dialog box, we elect to insert the report into a new worksheet and click OK. To define the report structure, we use the PivotTable Fields panel to insert the Account field into the rows area, the Amount field into the values area and the Date field into the columns area. We then clean up a few cosmetic items, such as applying a number format, turning off report headers and changing the value field header to Monthly Expenses. The resulting report is shown in Figure 2.

Figure 2

We’re close to the report we envisioned; however, we have one column for each date rather than for each month. The good news is that it’s super easy to have Excel group the date columns by month.

Month Groups

There are many cool things about PivotTable reports, and one of the coolest is the ability to group date fields. To do so, we right-click any report cell that contains a date, and select Group. This opens the Grouping dialog box where we instruct Excel how to group the date field. In our case, we want to group by month, so we select the Months option and click OK. Bam! The updated report is shown below in Figure 3.

Figure 3

And the crowd goes wild! I told you it was cool! And that, my friend, is the basic idea of grouping a PivotTable date field. But, before we wrap up, let me ask you a question: If the data had spanned multiple years, how would our PivotTable display the columns? Would it place all January transactions, regardless of year, into a single January column? Or, would it break the years apart so that each year got its own set of monthly columns? What do you think?

The data in our source table did span multiple years, and our PivotTable report displayed all January transactions, regardless of year, in a single January column (I’ll wait here a moment while you auditors out there download the sample file and confirm). So, how do we tell Excel to create separate monthly columns for each year? We simply create year groups.

Year Groups

If we open the Grouping dialog box again, we’ll be able to group by year, and within each year, by month. To do so, we select both Months and Years. This will
cause Excel to create separate monthly columns for each year. When you group by month, it is a good habit to also group by year.

Conclusion

PivotTables have many powerful capabilities, and grouping date fields is just one example. If you’d like to practice, please download the sample file and feel
free to check out the additional resources listed below.