By following the method used in the positive and

By following the method used in the Positive And Negative worksheet, you can easily break down

© 2016 Microsoft Corporation. All Rights Reserved. Page 26 2/27/2017
a company’s revenue into its cost components (including profit). See the worksheet Profitability
Waterfall and Figure 52-34.
F52xx34
FIGURE 52-34
A profitability waterfall chart.
F52xx34: This figure shows a waterfall chart that decomposes my $450,000 in revenue into its costs and profit
components.
How can I use the GETPIVOT data function and Excel’s table feature to create dynamic dashboards?

© 2016 Microsoft Corporation. All Rights Reserved. Page 27 2/27/2017
F52xx35
FIGURE 52-35
The sales data.
F52xx35: This figure shows the source sales data for your dynamic dashboards.
I now create a PivotTable by clicking the Insert tab and then PivotTable in the Tables group. In the
PivotTable Fields pane, I drag the Week field to the Rows area, Store and Category to the Columns
area, and Revenue to the Values area. This PivotTable (shown in Figure 52-36) summarizes the
weekly sales for each category in each store.
F52xx36
FIGURE 52-36
A PivotTable summarizing the category sales by store.
F52xx36: This PivotTable shows the weekly category sales summarized by store.

© 2016 Microsoft Corporation. All Rights Reserved. Page 28 2/27/2017
Now I am ready to use the GETPIVOTDATA function to extract the data needed to create the
charts I want. To begin, I create a drop-down list (see Chapter 40) in cell AG8 (still in the Model
worksheet), which can be used to select a store. Then I create check boxes (see Chapter 27) for each
category that can be used to control the range AH9:AK9. These cells control which categories appear
in my chart. Then I copy from AH11 to the range AH11:AK24 the formula
=IF(AH$9=FALSE,NA(),IFERROR(GETPIVOTDATA(“Revenue”,$I$11,”WEEK”,$AG11,”Category”,AH
$10,”Store”,$AG$8),” “)). If I select the check box for a category, this formula extracts th e weekly
sales for the category; if the category is unchecked, then an #N/A is entered in the cell. Also, if 0
sales occurred, the IFERROR portion of the formulas ensures that sales of 0 are entered. Next, I
make the range AG10:AK24 a table so that any charts based on this range will update automatically
to include the new data. Figure 52-37 shows how the source data for my chart appears if I want to