One of the biggest misperceptions regarding PivotTables is that users cannot create calculations within a PivotTable. In fact, PivotTables do support calculations using two different methodologies: 1) Field Settings and 2) Calculated Fields and Calculated Items.
Field Settings
By default, a PivotTable sums the data added to the Values quadrant of the PivotTable Field List. However, in addition to the sum function, ten other calculations are available to users, including averages, counts, standard deviations, and variances. To cause a different calculation on a field, click the drop-down arrow next to the field in the Values quadrant of the PivotTable Field List and choose Value Field Settings from the resulting pop-up menu. This opens the Value Field Settings dialog box shown in Figure 88; there, simply choose the type of calculation to apply to the field from the Summarize by tab. To cause different calculations on the same field, add the desired field to the Values quadrant multiple times and then change the calculation on each instance of that field.
Figure 88 - Changing a Field Setting
Other means of changing the summarization technique exist, including right-clicking on an item in the field of interest and choosing the desired option from the Summarize Values By option on the contextual menu.
In addition to changing the type of calculation performed on each field, you can also change how the PivotTable displays values. Clicking on the Show values as tab in the Value Field Settings dialog box allows you to select from options such as Difference from, % of Column, and % of Row. For instance, to create a running total in a PivotTable, choose the Running Total in option as shown in Figure 89.
Figure 89 - Creating Running Totals in PivotTables
Calculated Fields and Calculated Items
In addition to changing field settings, you can employ Calculated Fields and Calculated Items to create calculations in a PivotTable. According to Microsoft Knowledge Base Article 161882,
• A Calculated Field is a user-defined field in a PivotTable that can perform calculations using the contents of other fields in the PivotTable, and
• A Calculated Item is a user-defined item in a PivotTable field that can perform calculations using the contents of other fields and items in the PivotTable.
To illustrate how to create a Calculated Field, suppose you need to calculate sales commissions due based on total sales. To create the Calculated Field, click Fields, Items, and Sets from the PivotTable Tools Analyze contextual tab from the Ribbon and choose Calculated Field… to open the Insert Calculated Field dialog box. There, enter the name for the Calculated Field and the appropriate formula as shown in Figure 90. Once created, the Calculated Field is treated the same as any other PivotTable field.
Figure 90 - Creating a Calculated Field
To illustrate how to create a Calculated Item, suppose you want to use the prior months’ data in a PivotTable to estimate future results. To use a Calculated Item to perform this calculation, click any item in the Dates field of the PivotTable, click Fields, Items, and Sets from the PivotTable Tools Analyze contextual tab from the Ribbon, and choose Calculated Item… to open the Insert Calculated Item dialog box. There, enter the name for the Calculated Item and the appropriate formula as shown in Figure 91. Notice that upon creating a Calculated Item, you can edit it directly in the PivotTable without returning to the Calculated Item dialog box.
A Dashboard Example
The example below demonstrates how a PivotTable can summarize the top five selling items from a company’s sales data. In this example, the data summarized by the PivotTable resides in SalesData, an Excel table. With the table selected, from the Table Tools, Design contextual tab, click SummarizewithPivotTable to open the Create PivotTable dialog box shown in Figure 92 and click OK to create the PivotTable on a new worksheet.
Figure 92 - PivotTable Dialog Box
In the PivotTableFieldList, add Product to the Row Labels quadrant and DollarsSold to the Values quadrant as shown in Figure 93.
Figure 93 - Initial Draft of a PivotTable
Next, click the drop-down arrow next to Row Labels, select ValueFilters, and choose Top10 to open the Top 10 Filter dialog box shown in Figure 94. Accept the default information and click OK to apply the filter.
Figure 94 – PivotTable Top 10 Filter Dialog Box
Again, click the drop-down arrow next to Row Labels. This time, select MoreSortOptions to open the Sort dialog box shown in Figure 95. Set the sort options to Descending based on SumofDollars Sold as shown.
Figure 95 - PivotTable Sort Dialog Box
Finally, applying a few conventional formatting techniques turns the PivotTable into that which is displayed in Figure 96. With the example completed, think of the speed and ease with which the PivotTable report summarizes, sorts, and filters sales data.
Figure 96 - Completed PivotTable Summarizing High Performing Products
To create a more comprehensive dashboard, copy and paste the PivotTable into columns to the right of the original PivotTable. After doing so, change the title so that it reads “Bottom Ten Sales By Product” and change the filter from a “Top 10” filter to a “Bottom 10” filter. After doing so, your dashboard should resemble the one pictured in Figure 97.
Figure 97 - Completed PivotTables Summarizing High and Low Performing Products
Finally, copy and paste both of the PivotTables into rows below their original location. Then, remove the Product field from the row quadrant and add the Customer field in its place in both of the new PivotTables. Additionally, in the two new PivotTables, reapply the filters, resort the data, and rename both of the new PivotTables to complete the Sales Summary Dashboard pictured in Figure 98. Of note, because all four of the PivotTables in the dashboard use the same data source, refreshing any of the four PivotTables will cause all of the PivotTables to refresh. Additionally, any filtering performed through Slicers or Timelines will also affect all four of the dashboard components.
PivotCharts
When is a PivotTable not a PivotTable? The answer is, “When it is a PivotChart.” A PivotChart is a graphical representation of a PivotTable with all of the analytical power of the PivotTable. PivotCharts link inextricably to PivotTables so that changing the data or organization of one automatically changes the other. Because PivotCharts are just extensions of PivotTables, they are ideal tools for accountants and other business professionals to summarize large quantities of data and to present those summaries in an easy-to-understand graphical format on an Excel-based dashboard.
You can create PivotCharts in numerous ways, but since we already have a PivotTable created, we will create our first PivotChart from that PivotTable. To do so, start by clicking in the PivotTable. Then, select PivotChart from the PivotTableTools, Analyze contextual tab of the Ribbon. Choose the chart type – columns, line, bar, etc. – and click OK to create a PivotChart similar to the one shown in Figure 99.
Figure 99 - Initial PivotChart
Having created the initial PivotChart, a little cleanup is in order. Using some of the techniques discussed earlier in this course, we can transform the PivotChart into that shown in Figure 100.
Figure 100 - Completed PivotChart Example
In our second example of working with PivotCharts, John Ward needs to convert the data from his client’s Excel-based cash disbursements journal into a dashboard format the client can understand a bit better than a traditional row-and-column financial statement. John has already built a PivotTable to summarize the data; Figure 101 presents a copy of that PivotTable.
Figure 101 - Copy of PivotTable Used to Summarize Excel-based Cash Disbursements Journal To create a PivotChart from the PivotTable, John follows the process below.
1. John positions the cursor in the PivotTable and selects PivotChart from the PivotTable Tools, Analyze contextual tab to open the Insert Chart dialog box as shown in Figure 102. In the dialog box, John chooses a Stacked Column chart with 3- D effects.
Figure 102 - Creating a PivotChart from a PivotTable
2. When John clicks OK in the Insert Chart dialog box, Excel creates the chart and displays the first draft of it on the same worksheet as the PivotTable. Figure 103 shows that draft.
3. John is not satisfied with the draft. Principally, he wants to swap the position of the rows and columns – that is, he wants to summarize the data by quarter rather than by account on the horizontal axis. Fortunately, this is an easy customization, and John accomplishes it by clicking on the Switch Row/Column icon on the PivotChart Tools, Design contextual tab. Additionally, John adds some additional formats so that the final chart resembles the one shown in Figure 104.
Figure 104 - Completed PivotChart Summarizing Cash Disbursements Journal
Now John has an easy-to-read supplement to the traditional financial statement he prepared for this client. In building this PivotChart, John noticed several interesting features of PivotCharts.
• A PivotChart inextricably links to its related PivotTable. If the PivotTable changes, so does the PivotChart; likewise, if the PivotChart changes, so does the PivotTable. This is advantageous because it ensures that the two objects always stay in sync.
• PivotCharts contain on-screen filter buttons. For instance, referring to the PivotChart shown in Figure 104, John could choose to filter the Accounts the PivotChart displays by clicking the drop-down arrow next to Account to open the filter pane as shown in Figure 105.
Figure 105 - Filtering a PivotChart in Excel
Note that applying a filter to a PivotChart will change the related PivotTable, as described above. Additionally, if John does not desire on-screen filtering capability, he can right-click on the button and choose the appropriate “hide” option from the pop-up menu.
• From a formatting perspective, a PivotChart is just like any other chart. In other words, all traditional formatting options are available. Thus, John can choose to format his chart using the same tools and options he would use if this were a “traditional” Excel chart.
From the perspective of financial reporting on Excel-based dashboards, PivotCharts are truly one of Excel’s least known and least used tools, but one that offers tremendous power and flexibility to those who learn how to work with them.
C hapter 4 -