We begin with a dataset showing salesperson, order ID, order date, and amount.
The table is a proper Excel Table named “TableSales”.
Our task is to display the order amounts for each salesperson by month.
This is easily done by selecting the table and clicking Table Design (tab) -> Tools (group) -> Summarize with PivotTable and placing the pivot table on a new sheet.
Placing the Order Date field in the Rows section of the Pivot Table presents our dates in a grouped configuration where the groups are comprised of…
- Years
- Quarters
- Months
- Days (displayed with the original field name “Order date”)
The Pivot Table displays the groups in an expandable/collapsible hierarchy.
NOTE: This automatic grouping feature was introduced in Excel 2016.
If you do not wish to have these groups created, you can either click Undo (CTRL-Z) right after adding the field to the Rows section or click on a date group entry in the Pivot Table and click PivotTable Analyze (tab) -> Group -> Ungroup (or right-click a date and select Ungroup.)
If you want a grouping that differs from the default groups offered by Excel, you can click PivotTable Analyze (tab) -> Group -> Group (or right-click a date and select Group.)
The Grouping dialog box allows you to select any combination of preset groups as well as limit the data’s date range to contain records between two defined dates.
Creating Our Report
We will create our report by taking the following steps:
- Group the dates by Year and by Month.
- Place the Salesperson field in the Filters
- Place the Order Amount field in the Values
- Apply a Comma Style (no decimal places) to the values.
With this Pivot Table, we can look at sales for all salespersons or filter for a single or group of salespersons.
Featured Course
Excel Essentials for the Real World
Reformatting the Date Groups
Suppose we want to display the rows with the months in an MMM-YYYY format and dispense with the Years field from the report?
Limitations of Custom Names
Unfortunately, we are unable to manually change the displayed labels for the Months field in the report because changing a month in one year changes that same month in all years.
Plus, we don’t want to be tasked with manually changing every label currently in the report, plus any new labels that appear due to future refreshes of the data.
Let’s remove the Months field from the Rows section and replace it with the Days field.
Notice that we are missing days when sales did not occur: 7-Jan, 8-Jan, 10-Jan, etc.
Featured Course
Power Excel Bundle
Solving the Problem Using the Data Model
Starting from the beginning when our file only contains the source data, insert a Pivot Table, but before you click the OK button to create the Pivot Table framework, check the box labeled “Add this data to the Data Model”.
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.
Creating a Calendar Table in the Data Model
A calendar table is a permanent table containing a list of dates and various components of those dates.
The calendar table is a list of continuous dates with no skipped dates. It will also typically start on the first day of the (fiscal) year and end on the last day of the (fiscal) year.
Each day can then be represented in a myriad of ways:
- Years
- Quarters
- Months
- Weeks
- Days
- Julian dates
- Day Names
- Day number within the week
- Week number
- Etc…
Importing the Data into the Data Model
To open the Data Model, click Data (tab) -> Data Tools (group) -> Manage Data Model. This opens the Power Pivot for Excel window.
We see in the Power Pivot window the data from the “TableSales” table loaded as a sheet in the data model.
An advantage to importing data into the Data Model is that you can apply number formatting to the data, and it will carry over into any report you place the field within.
Creating the Calendar Table
We can quickly and easily set up the foundation of the calendar table by selecting the Order date column and clicking Design (tab) -> Calendar (group) -> Date Table -> New.
We are delivered a new sheet named Calendar that has a continuous set of dates starting from 1-Jan of the oldest transaction’s year to 31-Dec of the newest transaction’s year.
The table is also populated with a variety of common ways to display and group dates.
We are not limited to only these date descriptors. We are free to create columns that describe or group the dates in any way we desire.
The Wonders of Relationships
To allow these two tables (“TablesSales” and “Calendar”) to communicate with one another, a relationship must be created that connects the tables.
This relationship will permit a date in the “TableSales” table to ‘jump’ to the “Calendar” table and figure out what the “MMM-YYYY” equivalent is. For example, “1/1/2021” can be expressed as “Jan-2021”.
There are several ways to create a relationship that connects the two tables. A common approach would be to take the following steps:
- Switch to the Diagram View by clicking Home (tab) -> View (group) -> Diagram View.
- Left-click and hold the “Date” field in the “Calendar” table and drag it atop the “Order date” field in the “TableSales” table.
A relationship has been created between the two tables using the date fields as the connector.
Hiding Fields from the Pivot Table
We need to take steps to ensure the report writer doesn’t accidentally place the “Order date” field from the “TableSales” table into the Pivot Table report. This forces the report writer to use the date entries from the “Calendar” table and not risk creating those extra groups when using the “Order date” field.
The field can be hidden by right-clicking the “Order date” field and selecting “Hide from Client Tools”.
Returning to Excel, we see the two tables from the Data Model listed in the PivotTable Fields panel.
Expanding the “TableSales” table, we see that the original “Order date” field is hidden.
Featured Course
Master Excel Power Pivot & DAX (Beginner to Pro)
Creating Our Pivot Table Report
To create our Pivot Table report, perform the following steps:
- From the “TableSales” table, place the Salesperson field in the Filters
- Place the Order Amount field in the Values
- Expand the “Calendar” table and place the “MMM-YYYY” field in the Rows
We don’t need to apply number formatting to the aggregated values because the number formatting is defined in the Data Model and is carried to any Pivot Table that uses the “Order amount” field.
One Small Problem
If you look closely at the above image, you will notice that the month-year entries are sorted in alphabetical order instead of chronological order.
This is because Excel treats these labels as text and not true dates.
We need to help Excel understand the underlying chronology behind these labels.
Creating the Sort Column
None of our existing columns will be useful for sorting the data in a month/year format, so we will invent a column that has this type of chronological description.
We’re going to create a helper column that will ‘stamp’ each of the year/month entries with a value that can be used for sorting purposes (ex: 202101 for “Jan-2021”).
We can’t just add the year and the month together to derive this ‘stamp’. If we did, January of 2021 would read as 2022, because you’re only adding 1 to the year. February of 2021 would be 2023, and March 2021 would be 2024, etc.
A clever little formula for creating a perfect stamp (for this scenario) is to multiply the year by 100, then add the month.
Example: 2021 multiplied by 100 equals 202100. Adding the month of January to that value would yield 202101. February of 2021 would be 202102, and March of 2021 would be 202103, etc.
- Return to the Data Model (click Data (tab) -> Data Tools (group) -> Manage Data Model.)
- Switch back to the Data View (if necessary).
- Click the top of the Add Column column (far right of the table).
- Enter the following formula in the Formula Bar:
='Calendar'[Year] * 100 + 'Calendar'[Month Number]
- Rename the newly created helper column to “YYYYMM”.
- Click the “MMM-YYYY” column heading to select the field to be sorted.
- Select Home (tab) -> Sort and Filters (group) -> Sort by Column.
- In the Sort by Column dialog box, select “MMM-YYYY” on the left, and “YYYYMM” on the right.
- Click OK to close the dialog box.
If we switch back to Excel, we see the Pivot Table rows sorted in chronological order.
Discovering Dates with No Sales
If we want to see a Pivot Table report that shows days with no sales, we could try something like the following.
- Move the “MMM-YYYY” field from the Rows section to the Filters
- Filter the report to show “Amy Trefl” from “Salesperson” and “Jan-2021” from “MMM-YYY”.
- Add the “Date” field from the “Calendar” table to the Rows
The resulting Pivot Table appears as follows:
Normally, the only dates we could display for “Amy Trefl” in “Jan-2021” are the days she had sales. The pivot table will not invent days that don’t exist in the data.
Because our Data Model connects the table of sales to a table of continuous, unbroken dates, we can conceivably display every day in the model. In our example, every day in January of 2021.
To show these days, we will change a setting in the Pivot Table’s Options.
- Select a cell in the Pivot Table and click PivotTable Analyze (tab) -> Pivot Table (group) -> Options.
- In the PivotTable Options dialog box, select the Display tab and check the box for “Show items with no data on rows”.
We are presented with an unbroken list of dates for January of 2021 and we see the totals for the three days that Amy had sales.
If we wanted to see only those dates where Amy didn’t have sales, we can apply a Row filter with logic like “Value Filters Equals 0” (zero).
The report now displays only non-sales dates for Amy.
Featured Course
Master Excel Power Query – Beginner to Pro
Displaying Sales by Quarters
If we want to produce a report that shows sales on a quarter-by-quarter basis, we need to create a new helper column in the Data Model that associates each date with a specific quarter.
- Return to the Data Model (click Data (tab) -> Data Tools (group) -> Manage Data Model.)
- Click the top of the Add Column column (far right of the table).
- Enter the following formula in the Formula Bar:
=INT( ( 'Calendar'[Month Number] + 2) / 3 )
- Rename the newly-created helper column to “Quarters”.
The above formula performs the following operations:
- Add 2 to the [Month Number]
- Divide the result from step (a) by 3.
- Use the INT function to retain only the integer (i.e., whole number) portion of the result from step (b).
Examples: If we run this formula for May (5), we would get 5+2=7, then 7¸3=2 r1, then drop the remainder to yield 2 as the result.
If we run this formula for December (12), we would get 12+2=14, then 14¸3=4 r2, then drop the remainder to yield 4 as the result.
Returning to the previous Pivot Table report, perform the following steps:
- Remove the “Date” field from the Rows
- Move the “MMM-YYYY” field from the Filters section to the Rows
- Add the “Quarters” field to the Filters section below “Salesperson”.
- Filter the “Quarters” field to only display months from the 1st quarter.
We see Jan-Mar of both 2021 and 2022.
“What if we add more dates to our data set?”
If the data set driving this report increases in the date range, we can inform the Data Model of this increase by returning to the data model and clicking Design (tab) -> Calendar (group) -> Date Table -> Update Range.
Because this would require a manual adjustment any time the data source changed, we could automate this process using Power Query or DAX formulas…
… but those are for another conversation.
Practice Workbook
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
