6. Using Pivot Charts and Other Visualizations

What Is a Pivot Chart...Really?

When sharing your analyses with others, you find there is no way to get around the fact that people want charts. Pivot tables are nice, but they leave so many annoying numbers that take time to absorb. On the other hand, charts enable users to make a split-second determination about what your data actually reveals. Charts offer instant gratification by enabling users to immediately see relationships, point out differences, and observe trends.

The bottom line is that managers want to absorb data as fast as possible, and nothing delivers that capability faster than a chart. This is where pivot charts come into play. While pivot tables offer the analytical, pivot charts offer the visual.

A common definition of a pivot chart is a graphical representation of the data in your 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, charting each one separately.

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

When you create a chart from your pivot table, you are not feeding it individual pieces of data inside individual cells. Instead, you are feeding the pivot chart the entire pivot table layout. Therefore, a true definition of a pivot chart is a chart that uses a PivotLayout Object to view and control the data in your pivot table.

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

Creating Your First Pivot Chart

With all the complexity behind the make-up of a pivot chart, you might have the impression that it is difficult to create a pivot chart. The reality is that it is quite a straightforward task.

The pivot table in Figure 6.1 shows how straightforward it is to create a pivot chart. This pivot table 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.

Figure 6.1 This basic pivot table shows revenue by market and enables filtering by line of business.

Creating a pivot chart from this data not only provides an instant view of the performance of each market, but it also permits 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 Application 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 this example, click the Column chart icon and select the first 2-D column chart, as shown in Figure 6.2.

Figure 6.2 Select the chart type you want to use.

As you can see in Figure 6.3, choosing the chart type causes a chart to appear.

Figure 6.3 Excel creates your pivot chart on the same sheet as your pivot table.

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, adding the Region field to the pivot table adds a region dimension to your chart, as shown in Figure 6.4.

Figure 6.4 The pivot chart displays the same fields your underlying pivot table displays.

In addition, selecting a Business Segment from the page field filters not only the pivot table, but also the pivot chart. All this behavior occurs because 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 your data source and then refresh your pivot table, your pivot chart updates to reflect the changes.

Take a moment and think about the possibilities. Essentially, you can create a fairly robust interactive reporting tool based on the power of one pivot table and one pivot chart—no programming necessary.

Keeping Pivot Chart Rules in Mind

As with other aspects of pivot table technology, pivot charts come with their own set of rules and limitations. The following sections provide an understanding of the boundaries and restrictions of pivot charts.

How Changes in Underlying Pivot Tables Affect Pivot Charts

The primary rule you should be cognizant of is that your pivot chart is merely an extension of your pivot table. If you refresh, move a field, add a field, remove a field, hide a data item, show a data item, or apply a filter, your pivot chart reflects your changes.

Placement of Data Fields in a Pivot Table Might Not Be Suited for a Pivot Chart

One common mistake people make when using pivot charts is assuming that Excel places the values in the column area of the pivot table in the x-axis of the pivot chart. For instance, the pivot table in Figure 6.5 is in a format that is easy to read and comprehend. The structure chosen shows Sales Periods in the column area and the Region in the row area. This structure works fine in the pivot table view.

Figure 6.5 The placement of your data fields works for a 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.6, your pivot chart comes out with Region in the x-axis and Sales Period in the y-axis.

Figure 6.6 Creating a pivot chart from your nicely structured pivot table does not yield the results you were expecting.

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

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

• X-axis—Corresponds to the row area in your pivot table and makes up the x-axis of your pivot chart.

• Y-axis—Corresponds to the column area in your pivot table and makes up the y-axis of your pivot chart.

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

Now suppose you 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.7.

Figure 6.7 This format makes for slightly more difficult reading in a pivot table view but enables your pivot chart to give you the effect you are looking for.

This arrangement generates the pivot table shown in Figure 6.8.

Figure 6.8 With the new arrangement in your pivot table, you get a pivot chart that makes sense.

A Few Formatting Limitations Still Exist in Excel 2010

With legacy versions of Excel, many users avoided using pivot charts because of the formatting limitations that came with them. These limitations included the following:

• Inability to resize or move key components of the pivot chart

• Loss of formatting when underlying pivot tables were changed

• Inability to use certain chart types

These limitations led to pivot charts being viewed by most users as too clunky and impractical to use.

Beginning with Excel 2007, Microsoft introduced substantial improvements to the pivot chart functionality. For example, it gave users the ability to format almost every property and component of a pivot chart. In addition, pivot charts in Excel 2007 no longer lost their formatting when the underlying pivot table changes. Pivot charts were also placed on the same worksheet as the source pivot table.

Excel 2010 continues to offer the functionality introduced in Excel 2007, with a few additions such as pivot field buttons and slicers. Overall, the look and feel of pivot charts in Excel 2010 is like that of standard charts, which makes them a more viable reporting option.

However, a few limitations persist in Excel 2010 that you should keep in mind:

• You still cannot use XY (scatter) charts, bubble charts, or stock charts when creating a pivot chart.

• Applied trend lines are often lost when adding or removing fields in the underlying pivot table.

• The chart titles in the pivot chart cannot be resized.

Examining Alternatives to Using Pivot Charts

There are generally two reasons why you would need an alternative to using pivot charts.

• 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 your data in preparation for charting. In these situations, you usually do not plan to keep your source data, and you definitely do not want a pivot cache taking up memory and file space.

In the example in Figure 6.18, you can see a pivot table that summarizes revenue by quarter for each product.

Figure 6.18 This pivot table was created to summarize and chart revenue by quarter for each product.

The idea in Figure 6.18 is that you created this pivot table only to summarize and shape your data for charting. However, you do not want to keep the source data, and you do not want to keep the pivot table with all its overhead. The problem is if you try to create a chart using the data in the pivot table, you inevitably create a pivot chart. This effectively means you have all the overhead of the pivot table looming in the background. This could be problematic if you do not want to share your source data with end users or if you do not want to inundate them with unnecessarily large files.

The good news is there are a few simple techniques that enable you to create a chart from a pivot table, but not end up with a pivot chart. Any one of the following methods does the trick.

Method 1: Turn the Pivot Table into Hard Values

After you have created and structured your pivot table appropriately, select the entire pivot table and copy it. Then, select Paste Values from the Insert tab, as shown in Figure 6.19.

Figure 6.19 The Paste Values functionality is useful when you want to create hard-coded values from pivot tables.

This action essentially deletes your pivot table, leaving you with the last values that were displayed in the pivot table. These values can subsequently be used to create a standard chart.

Method 2: Delete the Underlying Pivot Table

If you have already created your pivot chart, you can turn it into a standard chart by deleting the underlying pivot table. To do this, select the entire pivot table and press the Delete key. Keep in mind that unlike Method 1, with this method you are not left with any of the values that made up the source data for the chart. In other words, if anyone asks for the data that feeds the chart, you do not have it.

Method 3: Distribute a Picture of the Pivot Chart

It might seem strange to distribute pictures of pivot chart, but this is an entirely viable method of distributing your analysis without a lot of overhead. In addition to small file sizes, you also get the added benefit of controlling what your clients get to see.

To use this method, copy the pivot chart by right-clicking the chart itself outside of the plot area, select Copy, and 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.

Method 4: Use Cells Linked to the Pivot Table as the Source Data

Many Excel users shy away from using pivot charts solely based on the formatting restrictions and issues they encounter when working with them. Often these users give up the functionality of a pivot table to avoid the limitations of pivot charts.

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

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

Figure 6.20 This pivot table enables you to filter by business segment to see the top 10 markets by total contracted hours and revenue.

Suppose you want to turn this view into an XY scatter chart to point out the relationship between the contracted hours and revenues. You need to keep the functionality of filtering out 10 records by model number. However, you want to avoid the inability to create XY.

In this case, a pivot chart is definitely out because you cannot build pivot charts with certain chart types such as 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 is the solution?

The answer is to 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.

To do this, click your cursor in a cell next to your pivot table, as shown in Figure 6.21. Reference the first data item that you need to create the range you will feed your standard chart.

Figure 6.21 Start your linked data set by referencing the first data item you need to capture.

Next, 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 similar to Figure 6.22.

Figure 6.22 Copy the formula and paste it down and across to create your complete data set.

After 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 (see Figure 6.23). You could never do this with a pivot chart.

Figure 6.23 Use your completed linked data set to create a standard chart.

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

Figure 6.24 This solution enables you to continue using the functionality of your pivot table without any of the formatting limitations you would have with a pivot chart.

Using Conditional Formatting with Pivot Tables

One of the most impressive new features of Excel 2010 is the conditional formatting functionality. In legacy versions of Excel, conditional formatting simply enabled you to change the color or formatting of a value or a range of cells dynamically based on a set of conditions you defined.

In Excel 2010, conditional formatting includes a more robust set of visualizations including data bars, color scales, and icon sets. These new visualizations enable users to build dashboard-style reporting that goes far beyond the traditional red, yellow, and green designations. What’s more, conditional formatting has been extended to integrate with pivot tables. This means that in Excel 2010, conditional formatting is applied to a pivot table’s structure, not just the cells it occupies.

In this section, you learn how to leverage the magic combination of pivot tables and conditional formatting to create interactive visualizations that serve as an alternative to pivot charts.

To start the first example, create the pivot table shown in Figure 6.25.

Figure 6.25 Create this pivot table.

Say you want to create a report that allows 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, take the easy route and apply some data bars.

First, select all the Sales_Amount values in the values area. After you have highlighted the revenue for each Sales_Period, click the Home tab, and then select Conditional Formatting in the Styles group to data bars, as shown in Figure 6.26.

Figure 6.26 Apply data bars to the values in your pivot table.

As shown in Figure 6.27, you now have a set of bars that correspond to the values in your pivot table. This visualization looks similar to a sideways chart. What is 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.

Figure 6.27 You have applied conditional data bars with just three easy clicks!

Notice that you did not have to trudge through a dialog box to define the condition levels. This occurs because Excel 2010 has a handful of preprogrammed scenarios that can be leveraged when you want to spend less time configuring conditional formatting and more time analyzing data. For example, to create the data bars you just employed, Excel uses a predefined algorithm that takes the largest and smallest values in the selected range and calculates the condition levels for each bar.

Other examples of preprogrammed scenarios include:

• Top Nth Items

• Top Nth %

• Bottom Nth Items

• Bottom Nth %

• Above Average

• Below Average

Excel 2010 makes an effort to offer the conditions that are most commonly used in data analysis.

It is important to note that you are not limited to these preprogrammed scenarios. You can still create your own custom conditions. To help illustrate this, create the pivot table shown in Figure 6.28.

Figure 6.28 This pivot table shows Sales_Amount, Contracted Hours, and a calculated field that calculates Dollars Per Hour.

In the scenario shown in Figure 6.28, 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.

To do this, begin by placing 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 box, as shown in Figure 6.29.

Figure 6.29 The New Formatting Rule dialog box.

The objective in this dialog box is 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—Applies conditional formatting to only the selected cells.

• All Cells Showing “Sales_Amount” Values—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 in which you are 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”—Applies conditional formatting to all values in the Sales_Amount column at the Market level only, except the subtotals and grand totals. This selection is ideal for use in analyses where you are using calculations that make sense only within the context of the level being measured.

In this example, select the All Cells Showing “Sales_Amount” Values for “Market” radio button because it makes the most sense, as shown in Figure 6.30.

Figure 6.30 Select the All Cells Showing “Sales_Amount” Values for “Market” radio button.

Next, in the Select a Rule Type section, specify the rule type you want to use for the conditional format by selecting one of the following five rule types:

• Format All Cells Based on Their Values—Enables you to apply conditional formatting based on some comparison of the actual values of the selected range. In other words, 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—Enables you to apply conditional formatting to those 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—Enables you to apply conditional formatting to those cells that are ranked in the top or bottom Nth number or percent of all the values in the range.

• Format Only Values That Are Above or Below the Average—Enables you to apply conditional formatting to those 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—Enables you to specify your own formula and evaluate each value in the selected range against that formula. If the values evaluate as true, then 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. With this in mind, format the 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 new icon sets that are offered by Excel 2010. In that light, 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.31 is ideal in situations in which your pivot tables cannot always be viewed in color.

Figure 6.31 Select Icon Sets from the Format Style drop-down.

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

Click OK to apply the conditional formatting. As you can see in Figure 6.32, you now have icons that enable you to determine where each market falls in relation to other markets as it pertains to revenue.

Figure 6.32 You have applied your first custom conditional formatting!

Next, apply the same conditional formatting to the Dollars Per Hour field. When you are done, your pivot table should look similar to the one shown in Figure 6.33.

Figure 6.33 You have successfully created an interactive visualization.

Take a moment to analyze the report you have created. 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.

Next Steps

In the next chapter, you learn how to bring together disparate data sources into one pivot table. You also create a pivot table from multiple data sets, and you learn the basics of creating pivot tables from other pivot tables.