Now it’s time to create our reports using standard pivot tables. A pivot table creates calculations based on criteria and conditions. For the first report, I want to get Year Month sales.
To start, I click any cell in the flat table report and go to the Insert tab and select Pivot Table. Note -the shortcut Alt + N + V can also be used to insert a Pivot table.
The following pop up screen appears after selecting Pivot Table
I selected Existing Worksheet and clicked in a cell on the new worksheet I created called Year Month Sales.
I dragged the field names into the criteria windows to create the pivot table report.
The date column is showing Sales on the daily basis, but I want to show Sales by Year and Month. Some versions separate the date automatically. My version does not, so I have to group. I right click on of the date cells in the pivot table and select grouping.
After selecting Years and Months, my pivot table updates to the correct break down for my report. Notice in the Rows that Years and Date are now populated.
Because this is a standard pivot table vs a data model pivot table I have to manually format the numbers and change the date title.