We’re still using data file, FIRST RANGE TO TABLE EXERCISE, using worksheet tab XYZ COMPUTER SALES.
Let’s move some of our fields in the quadrants. STEP 1: Click and drag the Market field over to the Filters quadrant. STEP 2: Click and drag the Month field over to the Rows quadrant. STEP 3: Click and drag the Category field over to the Columns quadrant. When finished with these steps, our quadrants should look like FIGURE 1. |
FIGURE 1
|
Our pivot table should now look like FIGURE 2.
The problem with the Filter quadrant is that anything placed in it with “All” as the default, fails to differentiate the separate components. For example in FIGURE 2, there were 167 sales for Jan. You don’t know how many of those were for Boston and how many were for New York. |
FIGURE 2
|
STEP 4: 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)” (FIGURE 3). Click on that downward pointing arrow. From the drop-down menu, select Boston. Select the OK button. |
FIGURE 3
|
On the Field List, check the box for the Sales field. The Sales field is automatically added to the Values quadrant.
The pivot table now has the Sales Total data in it (shown as Sum of Sales). Our pivot table now looks like FIGURE 5 below.
From the Fields List, 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 below (FIGURE 6).
The field list order in a quadrant affects the sort order on the Pivot Table. The order of the fields in a quadrant determines how the data will sort. Fields at the top sort first, followed by the field below it.
In the Rows quadrant (FIGURE 9) notice that the Year field is at the top. Locate where the year is shown in the pivot table; it is at the top (FIGURE 8). In the Rows quadrant, below the Year 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 market(s) are shown below the month in the pivot table, just like they are shown in the Rows quadrant. |
FIGURE 8
|
FIGURE 9
|