Close

All MCPL branches will close at 5:00 p.m. on Wednesday, November 24, and will remain closed on Thursday, November 25, for Thanksgiving Day. Our Virtual Branch is available 24/7.

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

Udemy Business

Browse thousands of on-demand courses for in-demand skills.
More Info

Digital Literacy

Build your knowledge of digital tools for today's world.
More Info

Events You May Also Like

Blogs You May Also Like

Read More

One Step for One Login: Preparing for MCPL's New Website

On February 21, the Library will launch an updated version of its website.
Scaring Away Technophobia
Read More

Scaring Away Technophobia

Every skill we ever learn starts with a single decision to “go for it.” When it comes to improving
What Is DNS?
Read More

What Is DNS?

There’s a chance that you haven’t used the term “DNS” in a regular conversation unless you’re

Was this page helpful? Yes No