Main content

Microsoft Excel: Data Analysis Using Pivot Tables

When you use Excel as a tracking system or database, you can have a tremendous amount of data in thousands of rows and hundreds of columns. Summarizing the data and reviewing key components of the worksheet can be difficult, but PivotTables can help with this information overload. If you work in an analytical role, being able to create and manipulate PivotTables is a necessary skill.

PivotTables allow you to change the view of your data quickly and easily. They also automatically sort your data and create totals, averages and other functions, too. PivotCharts add visualizations to the data summarized into a PivotTable and can help you spot trends and patterns within the data.

Students in this half-day course will build different PivotTables using existing databases to obtain insights into account management, human resources, marketing, and more. We will use sorting and filtering to help restrict our data and use PivotTables for SUM, AVERAGE, COUNT, and PERCENTAGE functions. We will also discuss the advantages and disadvantages of the new Format as a Table feature.

Audience: We designed this course for current, experienced users of Excel who are comfortable using formatting, sorting, and filtering features.

Software: We teach this course using Microsoft Excel 2016 on our lab PCs, but much of the content should also apply to Excel 2007, 2010, and 2013 for Windows. Material learned in this course may not apply to any version of Excel for Mac.

After completing this course, participants will be able to:

  • Create custom PivotTables and edit existing PivotTables
  • Use the Format as a Table feature
  • Analyze data through PivotTables
  • Increase productivity using the Report Filter Pages
  • Produce attractive and professional reports with PivotTable styles
  • Filter data interactively through slicers
  • Apply PivotTables to a wide variety of datasets
  • Incorporate custom calculations for data items and fields