STEP 3A: The “Create Pivot Table” dialog box appears (FIGURE 2). The default radio-button choices will be used. You want the radio button choice “Select a table or range” selected, which is the default choice. Excel automatically captures the table range (Sheet1!$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. In this case, you should see “XYZ_Sales”
|
FIGURE 2
|
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. |
FIGURE 3
|
FIGURE 4
|
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 STEP 8: Check the Sales Qty box at the top. Our pivot table should look like the graphic below in FIGURE 6. |
FIGURE 5
|
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: |
FIGURE 7
|
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)”. Select the OK button. |
FIGURE 9
|
FIGURE 10
|
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. |
FIGURE 12
|
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.
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. |
FIGURE 16
|
FIGURE 17
|