Let’s create pivot tables to answer these questions about our sales data.
What were the total sales for each sales representative?What were the top 3 States for sales?What were the total sales for each region by quarter?How many of orders were there for each product?
The great thing with pivot tables is it’s easy to answer questions like these about your data with just a few drag and drop actions. Get The Starting Workbook
Creating a pivot table to answer our questions
Let’s first make a pivot table to answer what were the total sales for each sales representative? First we will want to add the Sales Representative field to our pivot table.
Next let’s add the Total field into our pivot table.
Your pivot table should look something like this and we now have a summary of the Total field for each Sales Representative.
Creating another pivot table
The quickest way to create a new pivot table using the same Sales data is to make a copy of an already existing pivot table. We can do this by either making a copy of the sheet it’s on or by copying and pasting the pivot table to another area in our workbook.
To quickly copy a sheetHold Ctrl and then left click and drag the sheet tab over to the right or left and release.You should see a small sheet icon with a plus sign while dragging.Rename the newly copied sheet by double clicking on sheet tab.To copy the pivot tablePlace the active cell cursor inside the pivot table.Go to the Analyze tab.Go to Select then Entire PivotTable from the Actions section.Press Ctrl + C to copy the selected pivot table.Navigate to the area where you want to copy the pivot table to.Press Ctrl + V to paste the pivot table to the new area.
Removing fields from a pivot table
Now we have an exact copy of the pivot table, we can remove any fields we don’t want to use. This can be done in two different ways.
Creating a pivot table to show top 3 results
Let’s answer the question what were the top 3 States for sales? Create a pivot table with the Location field in the Rows area and the Total field in the Values area.
Add a top 3 values filter to your pivot table.
Only the States with the top 3 highest Sum of Total now appear in the pivot table.
Grouping items in a pivot table
Now let’s answer what were the total sales for each region by quarter? Create a pivot table with the Order Date field in the Rows area, the Region field in the Columns area and the Total field in the Values area. We will need to group our Order Dates into quarters. We could do this in several ways.
We will use the Group command here. Note that Excel may group these automatically by month as seen above. Grouping can be used for non-date fields also, but you will need to highlight the items to be grouped together before using the Group command. Hold Ctrl to select non-adjacent cells to be grouped together.
The pivot table will now be grouped by Order Date Quarter in the Rows.
Use a different aggregating method in the values area
So far we have only used the Sum function to summarize our Total field in the Sales data, this is the default when adding a numerical field into the Values area of a pivot table. Pivot table can summarize data in many more ways including Counts, Average, Maximum, Minimum, Standard Deviation, Variance and others. To answer the question how many of orders were there for each product we will need to summarize by Count. Create a pivot table with the Item field in the Rows area and the Item field in the Values area. Since the Item field is contains text values the aggregation type will default to Count.
You can easily change the aggregation type for any field in the Values area. Get The Completed Workbook
Here we have a Count of orders for each item in our Sales data.
