Close

The Red Bridge Branch will be closed until further notice for maintenance. All due dates and holds will be extended through the closure. Our virtual branch is available 24/7.

In-Branch: Expanded hours, technology services, browsing, and small meeting rooms. Returns: At branch desks, and all book drops. Holds Pick-Up: In-Branch or curbside or drive-up service. View current services and how the Library is keeping customers and staff safe.

Back to top

How to Create Excel Pivot Tables

How to Create Excel Pivot Tables

June 3, 2020

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.

View All Blogs

Read Similar Blogs:
Technology
Self-Development

Resources You May Also Like

Computer Science

Stay up-to-date with the world of technology.
More Info

Applied Science & Technology Index Retrospective

Search applied science resources from 1913 to 1983.
More Info

MUZZY Online

Build your language skills with kid-friendly MUZZY.
More Info

Events You May Also Like

Blogs You May Also Like

gardening

Whip Those Pre-Winter Chores with MCPL!

As summer winds down, I’m sure like me, your thoughts turn to back-to-school events, pumpkin spice everything,
Read More
pull the plug

Unplug to Power Up

As a Consumer Technology Specialist for the Library, it’s my job to get people excited about learning te
Read More
Mirroring Your Phone to a Computer

Mirroring Your Phone to a Computer

Do you ever wish you could show people your phone screen while video chatting?
Read More
Windows 7

Warning: Windows 7 Support Is Ending!

As of January 14, Microsoft will no longer support the Windows 7 Operating System.
Read More

Was this page helpful? Yes No