Preview
Creating a Pivot Table Using the Standard Method; Practice Exercise 3
Practice Exercise 3: Creating a Pivot Table Using the Standard Method
Continue to use data file: RANGE TO TABLE TO PIVOT TABLE2
STEP 1: On the newly created table, click mouse on any cell of your table. This ensures that Excel captures the range of the table.
STEP 2: On the Ribbon select the Insert tab, go to the Tables section and select Pivot Table icon as shown in FIGURE 1.
STEP 3A: The “PivotTable from table or range” dialog box appears (FIGURE 2). The default radio-button choices will be used. Excel automatically captures the table range (A$1:$J$109) and enters it into the Table/Range window. If you previously gave the table a name, Excel would extract that name and enter it automatically into the Table/Range window. Since we assigned a table name previously, we should see the name, “XYZ_SALES”.
STEP 3B: For the “Choose where you want the PivotTable to be placed”, in most cases you can just leave it with the default selection, “New Worksheet”
STEP 3C: At the bottom, the checkbox, “Add this data to the Data Model”, would be checked if we were adding another table for inclusion in the pivot table. We are not, so that box is left unchecked.
Select the OK button.
STEP 4: Here is the initial result you see from the previous step.
As you can see from the right graphic (FIGURE 3), you are directed to create a pivot table. “To build a report, choose fields from the Pivot Table Field list.”
The pivot table Field List is shown on the far-right graphic (FIGURE 4). The Field list contains all of the fields from the data table that you used to create the pivot table.
A pivot table will appear only when you begin checking the boxes of the various fields in the Pivot Table Fields box. The checked boxes will be the fields displayed in the pivot table.
STEP 5: (FIGURE 5) The Category field will go into the Rows quadrant. Check the Category box. Notice that the Category field is now in the Rows quadrant.
Let’s say your boss wants to see the data by market. We need to put the Market field in the Columns quadrant. Since Excel would automatically place the Market field in the Rows quadrant, we will have to manually move the Market field down into the Columns quadrant.
REMEMBER: Fields that contain text will automatically be placed in the Rows quadrant.
STEP 6: Click and drag the Market field down into the Columns quadrant.
We also want to be able to see a pivot table for each month, so the Month field will be used in our Filter quadrant.
STEP 7: Click and drag the Month field down into the Filters quadrant
We’re measuring our Sales Qty and Sales Total fields so that will be important. Sales Qty is data so let’s start with it. Since Sales Qty is data, it goes into the Values quadrant.
STEP 8: Check the Sales Qty box at the top.
Notice that the Sales Qty field automatically goes into the Values quadrant.
Remember: Fields that contain numbers will automatically be placed in the Values quadrant.
Our pivot table should look like the graphic below in FIGURE 6.
Let’s move some of our fields in the quadrants. For Steps 9, 10, and 11, refer to FIGURE 7.
STEP 9: Click and drag the Market field over to the Filters quadrant.
STEP 10: Click and drag the Month field over to the Rows quadrant.
STEP 11: Click and drag the Category field over to the Columns quadrant.
Our pivot table should look like the one below in FIGURE 8:
STEP 12: The problem with the Filter quadrant is anything placed in it with “All” as the default, fails to differentiate the separate components. In this case, both markets (New York & Boston) are displayed together. You do not know how many sales occurred for each market (FIGURE 9).
If you check the box at the bottom “Select Multiple Items”, again, the same problem exists (FIGURE 10). You cannot distinguish each item within those multiple items selected.
To distinguish the numbers for each market (Boston and New York) separately, let’s filter our pivot table for only Boston.
To select the filter, up at the top of the pivot table there is a downward pointing arrow to the right of “Market (All)”.
Click on the downward pointing arrow. From the drop-down menu (bottom-right graphic) select Boston.
Select the OK button.
The pivot table report for the Boston market is below (FIGURE 11). Notice that Boston now appears next to Market. Also notice that there is a funnel icon next to Boston. The funnel indicates filtering action.
STEP 13: We need to include more data in our pivot table. Let’s add the Sales field.
On the Field List, check the box for the Sales field. Notice that the Sales field is automatically added to the Values quadrant (Sum of Sales). The pivot table now has the Sales data in it. Your pivot table should now look like FIGURE 13 below. If the category labels are not correctly positioned, read the note below.
NOTE: You will also see an extra field, “Values” in the Columns quadrant of the Field List, shown in FIGURE 12. The Values field should be ABOVE the Category field. If it is not, click and drag the Values field so it is above the Category field.
The Values field is used for positioning the labels for the Values quadrant. It’s position in the Columns quadrant determines where the labels will be positioned, i.e. Sum of Sales, Sum of Sales Qty.
STEP 14: Our pivot table is looking good but if you look at the data table, you’ll notice we have two years of data; 2014 and 2015. We need to be able to see which year of data is being viewed on our pivot table.
Click and drag the Year field down to the Rows quadrant.
Place the Years field so that it is the top field in the Rows quadrant. That way, the data is sorted by year instead of by month.
The pivot table now has the 2014 and 2015 years visible on our pivot table, as shown in FIGURE 14.
STEP 15: Let’s move our Market field from the Filter quadrant to the Row quadrant.
With the Market filtering removed, we can now see both markets, Boston and New York, in one screen, as shown in FIGURE 15.
Field List Order Affects Sort Order on a Pivot Table
The order of the fields in the Rows quadrant determines how the data will sort (FIGURE 16). In the Rows field list (FIGURE 17) notice that the Year field is up at the top.
Locate where the year is shown in the pivot table; it is at the top. Below the Years field is the Month field. Again, notice where the month is shown in the pivot table; it is below the year. You can see that the markets are shown below the month in the pivot table, just like they are shown in the Rows field list.
END OF LESSON
Leave a Reply