6. Using Pivot Charts and Other Visualizations

A common definition of a pivot chart is a graphical representation of the data in a pivot table. Although this definition is technically correct, it somehow misses the mark on what a pivot chart truly does.

When you create a standard chart from data that is not in a pivot table, you feed the chart a range made up of individual cells holding individual pieces of data. Each cell is an individual object with its own piece of data, so your chart treats each cell as an individual data point and thus charts each one separately.

However, the data in a pivot table is part of a larger object. The pieces of data you see inside a pivot table are not individual pieces of data that occupy individual cells. Rather, they are items inside a larger pivot table object that is occupying space on your worksheet.

When you create a chart from a pivot table, you are not feeding it individual pieces of data inside individual cells; you are feeding it the entire pivot table layout. Indeed, a pivot chart is a chart that uses a PivotLayout object to view and control the data in a pivot table.

Using the PivotLayout object allows you to interactively add, remove, filter, and refresh data fields inside a pivot chart just like in a pivot table. The result of all this action is a graphical representation of the data you see in a pivot table.

The pivot table in Figure 6.1 provides for a simple view of revenue by market. The Business_Segment field in the report filter area lets you parse out revenue by line of business.

Creating a pivot chart from this data would not only allow for an instant view of the performance of each market, but would also permit you to retain the ability to filter by line of business.

To start the process, place your cursor anywhere inside the pivot table and click the Insert tab on the ribbon. On the Insert tab, you can see the Charts group displaying the various types of charts you can create. Here, you can choose the chart type you would like to use for your pivot chart. For this example, click the Column chart icon and select the first 2-D column chart, as shown in Figure 6.2.

Figure 6.3 shows the chart Excel creates after you choose a chart type.

You now have a chart that is a visual representation of your pivot table. More than that, because the pivot chart is tied to the underlying pivot table, changing the pivot table in any way changes the chart. For example, as Figure 6.4 illustrates, adding the Region field to the pivot table adds a region dimension to your chart.

In addition, filtering a business segment not only filters the pivot table, but also the pivot chart. All this behavior comes from the fact that pivot charts use the same pivot cache and pivot layout as their corresponding pivot tables. This means that if you add or remove data from the data source and refresh the pivot table, the pivot chart updates to reflect the changes.

Take a moment to think about the possibilities. You can essentially create a fairly robust interactive reporting tool on the power of one pivot table and one pivot chart—no programming necessary.

In Excel 2016, new Expand Entire Field (+) and Collapse Entire Field (−) buttons are automatically added to any pivot chart that contains nested fields. Figure 6.4 shows these buttons in the lower-right corner of the chart.

Clicking Collapse Entire Field (−) on the chart collapses the data series and aggregates the data points. For example, Figure 6.5 shows the same chart collapsed to the Region level. You can click Expand Entire Field (+) to drill back down to the Market level. These new buttons enable customers to interactively drill down or roll up the data shown in pivot charts.

In Chapter 9, “Working with and Analyzing OLAP Data,” you will find out how to create pivot charts that are completely decoupled from any pivot table.

For instance, the pivot table in Figure 6.6 is in a format that is easy to read and comprehend. The structure chosen shows Sales_Period in the column area and Region in the row area. This structure works fine in the pivot table view.

Suppose you decide to create a pivot chart from this pivot table. You would instinctively expect to see fiscal periods across the x-axis and lines of business along the y-axis. However, as you can see in Figure 6.7, the pivot chart comes out with Region on the x-axis and Sales_Period on the y-axis.

So why does the structure in the pivot table not translate to a clean pivot chart? The answer has to do with the way pivot charts handle the different areas of a pivot table.

In a pivot chart, both the x-axis and y-axis correspond to specific areas in your pivot table:

y-axis—Corresponds to the column area in a pivot table and makes up the y-axis of a pivot chart

x-axis—Corresponds to the row area in a pivot table and makes up the x-axis of a pivot chart

Given this information, look again at the pivot table in Figure 6.6. This structure says that the Sales_Period field will be treated as the y-axis because it is in the column area. Meanwhile, the Region field will be treated as the x-axis because it is in the row area.

Now suppose you were to rearrange the pivot table to show fiscal periods in the row area and lines of business in the column area, as shown in Figure 6.8.

This arrangement generates the pivot chart shown in Figure 6.9.

Over the last few versions of Excel, Microsoft introduced substantial improvements to the pivot chart functionality. Today, the pivot charts in Excel 2016 look and behave very much like standard charts. However, a few limitations persist in this version of Excel that you should keep in mind:

You still cannot use XY (scatter) charts, bubble charts, and stock charts when creating a pivot chart. This includes the new sunburst and waterfall chart types introduced in Excel 2016.

Applied trend lines can be lost when you add or remove fields in the underlying pivot table.

The chart titles in the pivot chart cannot be resized.

You do not want the overhead that comes with a pivot chart.

You want to avoid some of the formatting limitations of pivot charts.

In fact, sometimes you might create a pivot table simply to summarize and shape data in preparation for charting. In these situations, you don’t plan on keeping the source data, and you definitely don’t want a pivot cache taking up memory and file space.

The example in Figure 6.17 shows a pivot table that summarizes revenue by quarter for each product.

Keep in mind that you need this pivot table only to summarize and shape data for charting. You don’t want to keep the source data, nor do you want to keep the pivot table, with all its overhead.

The good news is, you can use a few simple techniques to create a chart from a pivot table but not end up with a pivot chart. Any one of the following four methods does the trick:

Turn the pivot table into hard values.

Delete the underlying pivot table.

Distribute a picture of the pivot table.

Use cells linked back to the pivot table as the source data for the chart.

Details about how to use each of these methods are discussed in the next sections.

To use this method, simply copy a pivot chart by right-clicking the chart itself (outside the plot area) and selecting Copy. Then open a new workbook. Right-click anywhere in the new workbook, select Paste Special, and then select the picture format you prefer. A picture of your pivot chart is placed in the new workbook.

However, if you want to retain key functionality in your pivot table, such as report filters and top 10 ranking, you can link a standard chart to your pivot table without creating a pivot chart.

In the example in Figure 6.19, a pivot table shows the top 10 markets by contracted hours, along with their total revenue. Notice that the report filter area allows you to filter by business segment so you can see the top 10 markets segment.

Suppose you want to turn this view into an XY scatter chart to be able to point out the relationship between the contracted hours and revenues.

Well, a pivot chart is definitely out because you can’t build pivot charts with XY scatter charts. The techniques outlined in methods 1, 2, and 3 are also out because those methods disable the interactivity you need.

So what’s the solution? Use the cells around the pivot table to link back to the data you need, and then chart those cells. In other words, you can build a mini data set that feeds your standard chart. This data set links back to the data items in your pivot table, so when your pivot table changes, so does your data set.

Click your cursor in a cell next to your pivot table, as demonstrated in Figure 6.20, and reference the first data item that you need to create the range you will feed to your standard chart.

Now copy the formula you just entered, and paste that formula down and across to create your complete data set. At this point, you should have a data set that looks like the one shown in Figure 6.21.

When your linked data set is complete, you can use it to create a standard chart. In this example, you are creating an XY scatter chart with this data. You could never do this with a pivot chart.

Figure 6.22 demonstrates how this solution offers the best of both worlds. You have the ability to filter out a particular business segment using the page field, and you also have all the formatting freedom of a standard chart without any of the issues related to using a pivot chart.

Suppose you want to create a report that enables your managers to see the performance of each sales period graphically. You could build a pivot chart, but you decide to use conditional formatting. In this example, you’ll go the easy route and quickly apply some data bars.

Select all the Sum of Sales_Amount2 values in the values area. After you have highlighted the revenue for each Sales_Period, click the Home tab and select Conditional Formatting in the Styles group. Then select Data Bars and select one of the Solid Fill options, as shown in Figure 6.24.

You immediately see data bars in your pivot table, along with the values in the Sum of Sales_Amount2 field. You want to hide the actual value and show only the data bar. To do this, follow these steps:

1. Click the Conditional Formatting drop-down on the Home tab, and select Manage Rules.

2. In the Rules Manager dialog, select the data bar rule you just created and select Edit Rule.

3. Place a check next to Show Bar Only (see Figure 6.25).

As you can see in Figure 6.26, you now have a set of bars that correspond to the values in your pivot table. This visualization looks like a sideways chart, doesn’t it? What’s more impressive is that as you filter the markets in the report filter area, the data bars dynamically update to correspond with the data for the selected market.

For example, to create the data bars you’ve just employed, Excel uses a predefined algorithm that takes the largest and smallest values in the selected range and calculates the condition level for each bar.

Other examples of preprogrammed scenarios include the following:

Top Nth Items

Top Nth %

Bottom Nth Items

Bottom Nth %

Above Average

Below Average

As you can see, Excel 2016 makes an effort to offer the conditions that are most commonly used in data analysis.

To see how this works, you need to begin by creating the pivot table shown in Figure 6.27.

In this scenario, you want to evaluate the relationship between total revenue and dollars per hour. The idea is that some strategically applied conditional formatting helps identify opportunities for improvement.

Place your cursor in the Sales_Amount column. Click the Home tab and select Conditional Formatting. Then select New Rule. This activates the New Formatting Rule dialog, shown in Figure 6.28. In this dialog, you need to identify the cells where the conditional formatting will be applied, specify the rule type to use, and define the details of the conditional formatting.

First, you must identify the cells where your conditional formatting will be applied. You have three choices:

Selected Cells—This selection applies conditional formatting to only the selected cells.

All Cells Showing “Sales_Amount” Values—This selection applies conditional formatting to all values in the Sales_Amount column, including all subtotals and grand totals. This selection is ideal for use in analyses using averages, percentages, or other calculations where a single conditional formatting rule makes sense for all levels of analysis.

All Cells Showing “Sales_Amount” Values for “Market”—This selection applies conditional formatting to all values in the Sales_Amount column at the Market level only. (It excludes subtotals and grand totals.) This selection is ideal for use in analyses using calculations that make sense only within the context of the level being measured.

In this example, the third selection (All Cells Showing “Sales_Amount” Values for “Market”) makes the most sense, so click that radio button, as shown in Figure 6.29.

Next, in the Select a Rule Type section, you must specify the rule type you want to use for the conditional format. You can select one of five rule types:

Format All Cells Based on Their Values—This selection enables you to apply conditional formatting based on some comparison of the actual values of the selected range. That is, the values in the selected range are measured against each other. This selection is ideal when you want to identify general anomalies in your data set.

Format Only Cells That Contain—This selection enables you to apply conditional formatting to cells that meet specific criteria you define. Keep in mind that the values in your range are not measured against each other when you use this rule type. This selection is useful when you are comparing your values against a predefined benchmark.

Format Only Top or Bottom Ranked Values—This selection enables you to apply conditional formatting to cells that are ranked in the top or bottom Nth number or percentage of all the values in the range.

Format Only Values That Are Above or Below Average—This selection enables you to apply conditional formatting to values that are mathematically above or below the average of all values in the selected range.

Use a Formula to Determine Which Cells to Format—This selection enables you to specify your own formula and evaluate each value in the selected range against that formula. If the values evaluate to true, the conditional formatting is applied. This selection comes in handy when you are applying conditions based on the results of an advanced formula or mathematical operation.

In this scenario, you want to identify problem areas using icon sets; therefore, you want to format the cells based on their values, so select Format All Cells Based on Their Values.

Finally, you need to define the details of the conditional formatting in the Edit the Rule Description section. Again, you want to identify problem areas using the slick icon sets that are offered by Excel 2016. Therefore, select Icon Sets from the Format Style drop-down box.

After selecting Icon Sets, select a style appropriate to your analysis. The style selected in Figure 6.30 is ideal in situations in which your pivot tables cannot always be viewed in color.

With this configuration, Excel applies the sign icons based on the percentile bands >=67, >=33, and <33. Keep in mind that you can change the actual percentile bands based on your needs. In this scenario, the default percentile bands are sufficient.

Click the OK button to apply the conditional formatting. As you can see in Figure 6.31, you now have icons that enable you to quickly determine where each market falls in relation to other markets in terms of revenue.

Now apply the same conditional formatting to the Dollars per Hour field. When you are done, your pivot table should look like the one shown in Figure 6.32.

Take a moment to analyze what you have here. With this view, a manager can analyze the relationship between total revenue and dollars per hour. For example, the Dallas market manager can see that he is in the bottom percentile for revenue but in the top percentile for dollars per hour. With this information, he immediately sees that his dollars per hour rates might be too high for his market. Conversely, the New York market manager can see that she is in the top percentile for revenue but in the bottom percentile for dollars per hour. This tells her that her dollars per hour rates might be too low for her market.

Remember that this in an interactive report. Each manager can view the same analysis by product by simply filtering the report filter area!