How to Create Excel Pivot Tables

If you have a large spreadsheet and find that sorting and filtering don’t give you the answers you need, try creating a pivot table! Pivot tables are a great way to summarize large amounts of data quickly. If you need to rearrange spreadsheet information, a pivot table can move data around very easily—with no retyping. Even better, pivot tables do some of the basic math for you (and without you writing a single formula!).

First, let’s look at our example spreadsheet. It lists the name of the movie, its genre, who stars in it, and the year the movie was made.

To create a summary report, let’s create a pivot table in six easy steps! If you would prefer to watch these steps in action, tune in to our Excel Pivot Tables virtual class on the MCPL360 Facebook page on Wednesday, June 3, at 1:00 p.m.!

Step 1. Make sure there are unique column headings. Sometimes, large spreadsheets will, for example, list two different sets of address information—one set for the owner and then another for the business. They could easily contain duplicate field names such as Name, Address, and Phone.

Step 2. Check for a blank column to the right of your spreadsheet and a blank row below the last row of data.

Step 3. Verify that the spreadsheet is as complete as possible.

Step 4. Insert the pivot table.

• Click on a cell in the spreadsheet.
• From the Insert tab, click the Pivot Table button.
• Double-check the Table/Range and make corrections if needed.
• While the pivot table can be added to the existing worksheet, we’ll select New Worksheet for our example.
• Click OK in the lower right.

• Just that easily, we’ve added a new sheet, and we’re ready to create a pivot table.

Pivot Table Worksheet and Field List Pane

• In cells A3:C20, there’s a “chalk outline” of where the pivot table will appear.
• On the right, is the PivotTable Fields pane.

• In the large box at the top, is a list of all the column headings, the fields, that were in the spreadsheet.

• At the bottom, there are boxes for determining what information will be in rows and columns and which fields will provide the data.

Step 5-A. Create a Pivot Table—Add Rows

• When you click the checkbox for Genre, “Genre” automatically appears in the Rows box at the bottom left of the pane.
• At the same time, the pivot table starts filling in.
• Column A now shows the genres in alphabetical order. Excel has already summarized that column of data in our spreadsheet so that each genre only appears once.
• To find out how many movies there are of a particular genre, click and drag Genre to the Values box in the lower right of the field list pane.

Here’s something to keep in mind: Adding a text field to the Values box causes Excel to count how many values there are. Adding a numeric field to the Values box causes Excel to add those values together for a total.

• Genre has text values, so it gives you a count in column B.

Step 5-B. Create a Pivot Table—Add Columns

• Columns are not required, but drag Year Made to the Columns box in the lower right.
• There are now column headings for each year.
• The pivot table now shows how many movies of each genre were released in a particular year.

Excel has added some basic formulas to the pivot table as well. Notice that there is a Grand Total by year in row 14. There is also a Grand Total for each genre in column AE. AE14 shows how many movies are in the total collection.

Step 6. Pivot the Table.

To pivot the table, move field “buttons” to other locations in the field list pane. For example:

• Drag Year Made to the Rows box below Genre.
• Drag Genre to the Columns box, keeping Year Made in the Row box.

As you can tell, you can pivot the data in a way that best shows the information you want to display.

Keep in mind that these six steps are a good start, but in some ways, just the tip of the iceberg when it comes to creating pivot tables. If you want to learn more, be sure to check out the Library’s online resources, including Universal Class, Lynda.com, Hoonuit, and LearningExpress Library, or watch our video tutorial on the MCPL360 Facebook page on Wednesday, June 3, at 1:00 p.m.!

Terri M.
Consumer Technology Specialist