Chapter 30: Analyzing Data with PivotTables
3030
Using the GETPIVOTDATA function has one potential problem: the data that it retrieves must be visible. If you
modify the PivotTable so that the value used by GETPIVOTDATA is no longer visible, the formula returns an error.
If you want to prevent Excel from using the GETPIVOTDATA function when you point to PivotTable cells when creat-
ing a formula, choose PivotTable Tools Analyze ➪ PivotTable ➪ Options ➪ Generate GetPivotData. This command is
a toggle.
Creating PivotCharts
A PivotChart is a graphical representation of a data summary displayed in a PivotTable. If
you’re familiar with creating charts in Excel, you’ll have no problem creating and custom-
izing PivotCharts. All Excel charting features are available in a PivotChart.See Chapter 20, “Getting Started with Excel Charts,” and Chapter 21, “Advanced Charting Techniques,”
to learn more about charting in Excel.Excel provides several ways to create a PivotChart:■ (^) Select any cell in an existing PivotTable and then choose PivotTable Tools Analyze
➪ Tools ➪ PivotChart.
■ (^) Select any cell in an existing PivotTable and then choose Insert ➪ Charts ➪
PivotChart.
■ (^) Choose Insert ➪ Charts ➪ PivotChart ➪ PivotChart. If the active cell is not within
a PivotTable, Excel prompts you for the data source and creates a PivotChart.
■ (^) Choose Insert ➪ Charts ➪ PivotChart ➪ PivotChart & PivotTable. Excel prompts
you for the data source and creates a PivotTable and a PivotChart. This command is
available only when the active cell is not within a PivotTable.
A PivotChart example
Figure 30.25 shows part of a table that tracks daily sales by region. The Date field contains
dates for the entire year (excluding weekends), the Region field contains the region name
(Eastern, Southern, or Western), and the Sales field contains the sales amount.
This workbook, named sales by region pivot chart.xlsx, is available on this book’s web-
site at http://www.wiley.com/go/excel2019bible.