Understand Pivot Tables for Excel Dashboards and Reports

By Michael Alexander

Pivot tables in Excel are extremely useful for dashboarding and reporting. A pivot table is composed of four areas. The data you place in these areas defines both the utility and appearance of the pivot table. Take a moment to understand the function of each of these four areas.

Pivot table values area

The values area, as shown in this figure, is the large rectangular area below and to the right of the column and row headings. In the example in the figure, the values area contains a sum of the values in the Sales Amount field.

image0.jpg

The values area calculates and counts data. The data fields that you drag and drop here are typically those that you want to measure — fields, such as Sum of Revenue, Count of Units, or Average of Price.

Pivot table row area

The row area is shown in this figure. Placing a data field into the row area displays the unique values from that field down the rows of the left side of the pivot table. The row area typically has at least one field, although it’s possible to have no fields.

image1.jpg

The types of data fields that you would drop here include those that you want to group and categorize, such as Products, Names, and Locations.

Pivot table column area

The column area is composed of headings that stretch across the top of columns in the pivot table.

As you can see in this figure, the column area stretches across the top of the columns. In this example, it contains the unique list of business segments.

image2.jpg

Placing a data field into the column area displays the unique values from that field in a column-oriented perspective. The column area is ideal for creating a data matrix or showing trends over time.

Pivot table filter area

The filter area is an optional set of one or more drop-downs at the top of the pivot table. In this figure, the filter area contains the Region field, and the pivot table is set to show all regions.

image3.jpg

Placing data fields into the filter area allows you to filter the entire pivot table based on your selections. The types of data fields that you’d drop here include those that you want to isolate and focus on; for example, Region, Line of Business, and Employees.