Preview
Customizing a Pivot Table by Applying Filtering – Practice Exercise 24
Practice Exercise 24: Customize a pivot table by applying filtering
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.
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.
If you check the box at the bottom “Select Multiple Items” (shown in FIGURE 3), again, the same problem exists. You cannot distinguish each item within those multiple items selected.
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.
Our pivot table should now look like FIGURE 4. Notice that Boston now appears next to Market. Also notice that there is a funnel icon next to Boston.
Remember: The funnel icon indicates filtering action.
STEP 5: We need to include more data in our pivot table. Let’s go to the Fields List and add the Sales field.
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.
When we added the Sales field, we also see a new field that appeared in the Columns quadrant. We should explain this field, the Values Field symbol (graphic directly below), and what it actually means when it is seen in the Rows or Columns quadrant of a Field List.
The Values field symbol (graphic directly above) is a label placeholder for data generated as a result of fields in the Values quadrant. For example, looking at FIGURE 6 below, we see the categories Desktops, Laptops, Tablets. Notice that these categories are above the labels for data generated for Sales and Sales Qty, If we swapped the 2 fields in the Columns quadrant, so that Values is on the bottom and Category on top, we would see the labels Sales and Sales Qty above the Desktop, Laptop, Tablet Categories.
STEP 6: 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.
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).
STEP 7: Let’s move our Market field from the Filter quadrant to the Row quadrant. We can now see both markets, Boston and New York, in one screen (FIGURE 7).
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.
END OF LESSON
Leave a Reply