Chapter 4: Analyzing Data with PivotTable and PivotChart Reports

Overview

After working through the exercises in the previous chapter to sort, filter, format, chart, and subtotal data, you have a good foundation on which to build when you want to analyze data in Microsoft Excel. In this chapter you will enhance and extend these skills through a data analysis technique called pivoting.

Suppose you have a large list of data; for example, figures for 2,000 sales receipt line items. You need to analyze this data to answer the following business questions:

• Which are my best selling products by volume?

• Which products generate the most revenue?

• Which sales receipts are discounted the most?

Simple sorting, filtering, or subtotaling are not good choices for answering these business questions. Sorting and filtering help you see the bottom, top, or selected sales figures, but these techniques don’t summarize the sales figures. Subtotals summarize the data, but to view a different set of subtotals for comparison, you need to repeat a lot of steps to reset the subtotals and create new subtotals. To answer these business questions, you can create PivotTable reports and PivotChart reports, interactive tables and charts that organize and summarize your data. You can use these reports to make comparisons, detect patterns, and analyze trends in a highly graphical manner. To make these analyses, you can easily move (or pivot) entire fields and records to view different data summarizations. You can look at data summarizations from one business perspective to answer a certain set of business questions, and then you can quickly change the perspective so that you’re viewing another set of data summarizations that help answer a different set of business questions.

PivotTable reports and PivotChart reports are so rich and flexible that this entire chapter is devoted to understanding how to use them for business data analysis.

Objectives

In this chapter, you will

• Understand how PivotTable reports and PivotChart reports make summarizing and analyzing large amounts of data easier.

• Learn how to create, customize, and link PivotTable reports and PivotChart reports.

• Learn how to use the PivotTable report and PivotChart report toolbars and shortcut menus.

• Learn how to filter, sort, and display the top items in a PivotTable report.

• Learn how to enhance PivotTable report and PivotChart report data analysis by creating calculated fields and calculated items.