Looking For The Best Vancouver and Fraser Valley IT Services?

Direct:(604) 864-_0992 | Toll Free: (877) 864-_0992

Analyze data with PivotTables in Excel

pivot-table

PivotTables are a feature in Excel that helps you pull out and see relationships between parts of very large data sets.

It is essentially a data summarization and visualization tool (Wikipedia). This tech tip will show you how to quickly create your first PivotTable from a relatively small dataset, and modify some of its parameters.

We’ll start with this small dataset.

It has a header row, four columns (Year, Author, Book, and Genre), and 67 rows of data. A header row is necessary for PivotTables.

To start, place the cursor somewhere in this dataset, such as the first cell.

Go to Insert on the Ribbon. On the immediate left, click PivotTable.

You will see this dialog box.

The entire dataset should be automatically selected. By default, the PivotTable report will be put into a New Worksheet instead of the current one.

Click OK.

A blank PivotTable section with PivotTable Fields on the right side should appear in a new worksheet.

Using the PivotTable Fields panel on the right, drag and drop the fields you want to include in this report to the areas below: Filters, Columns, Rows, and Values (e.g. the final sums of the report).

In this case, we’ll just use the Genre column in both the Columns area and the Values area.

This PivotTable report now gives us all the unique Genre labels with the counts of how many of each these genres are in the dataset.

If we add Author to the Filters area, a filter appears at the top of the report that allows us to pick specific author(s). If the selected author has multiple books in the dataset, the breakdown of the books’ genres will appear.

In this case, we’ve chosen Lois McMaster Bujold as the author to filter by, and see that she has two books, one in science fiction and one in fantasy.

What’s next?

This is a basic PivotTable. You can experiment by adding more fields to the different Filters, Columns, Rows, and Values areas. You can also try inserting a Recommended PivotTable from the Ribbon for a more ready-made summary of your data.

Changing field settings

In the areas section, you can also click the down arrows by the fields and go to Value Field Settings to select other ways of presenting the data.

For example, you can choose to express the data as a Sum, Count, Average, Max, Min, Product, and more (which is helpful for the Values area).

[

We hope this article has been helpful to you in understanding how to create basic PivotTables to analyze your data!