Pivot tables are a great way of summarizing, analyzing, and presenting data. It’s especially helpful with large amounts of data that you want to filter, total, or sort. You could use it to review sales made by each salesperson or by region of your company, to track the amount of inventory you have on hand, and any number of other uses.

To create a pivot table, you’ll need to start with a table or range of data in which each column has a title.

Go to the Insert tab and click Pivot Table.

1

You’ll get a popup window asking you to select the range of data you want to use, and where you want the pivot table to be created. Click on the Table/Range field, then highlight the range of data you’re using by clicking and dragging your cursor over it. Click OK on the popup window to create the pivot table.

2

You’ll get a Pivot Table Field List that includes the column titles of the data you selected. You can click and drag the column titles into 4 fields to set up the pivot table – Report Filter, Column Labels, Row Labels, and Values.

3

The Values field will typically default to Sum or Count. You can change the summary function by clicking on the downward pointing triangle on the right of the title and clicking Value Field Settings. This will let you choose the average, minimum or maximum value, and other options.

value field settings 2

You can also summarize numeric fields as a percentage of the total. Right click on the field of the pivot table, select Show Values As on the dropdown menu, and pick which total you want to see the percentages of.

5

6

If you want to sort data, e.g. smallest to largest or alphabetically, click on the arrow next to the title of the column or row and select Sort A to Z or Sort Z to A.

7