Anyone who has worked with pivot tables has likely found it easy to filter rows or columns within the pivot table, and as you’ll see in this article, you can indeed filter any element of a pivot table that you wish.
When you click the arrow within the Row Labels or Column Labels sections, a drop-down menu lets you choose what you’d like to see. At first glance, it may appear that you cannot filter the Grand Total column or row within a pivot table.
To build a pivot table in Excel you must start with a list, as shown in Figure 1. Make sure that you have a unique title at the top of each column, and that the title is comprised of a single row. As shown in Figure 2:
Click any cell within the list.
Activate the Insert menu.
Choose the Pivot Table command.
When the new pivot table appears, click any text-based field to add it to the Rows section, such as Vendor.
Drag any text-based field to the Columns section, such as Product.
Click amount-based field to add it to the Values section.
Figure 1: Ideal data for a pivot table is in a list form such as this.
Figure 2: Transforming a list into a pivot table report only requires a handful of mouse clicks.
David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. His mantra is “Either you work Excel, or it works you.” David offers spreadsheet and database consulting services nationwide.