Returns: Book Drops Only. Holds Pick-Up: Curbside or Drive-Up Service. In-Branch: Computers, printing, fax, and copiers. Virtual Branch is available 24/7. Learn more about MCPL's pandemic response.

Back to top

How to Create Excel Pivot Tables

Published on Wed, 06/03/2020 - 08:41am
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.

Thumbnail

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.
Thumbnail
  • 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.
Thumbnail

Step 5-A. Create a Pivot TableAdd 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.
Thumbnail

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.
Thumbnail

Step 5-B. Create a Pivot TableAdd 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.

Thumbnail

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.
Thumbnail

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

 

Add new comment

Plain text

  • No HTML tags allowed.
  • Lines and paragraphs break automatically.
  • Web page addresses and email addresses turn into links automatically.

Resources You May Also Like

Events You May Also Like

Blogs You May Also Like

Lynda.com

Learning with Lynda

Mac or Windows? That is the question.
Read More
technology through the years

Back to the Future (of Technology)

As I research the latest trends in order to keep our technology programs up to date, I also come across many references to
Read More
Microsoft Word: Create a Sheet of Blank Mailing Labels

Microsoft Word: Create a Sheet of Blank Mailing Labels

Instead of using Microsoft Word’s mail merge features, you may decide to create a sheet of blank labels, so that you can t
Read More
Tracy Tries: Tex-Mex Cooking 101—Lesson 8

Tracy Tries: Tex-Mex Cooking 101—Lesson 8

Hello and welcome back to another 
Read More

Was this page helpful? Yes No