AppId is over the quota
This post is brought to you by Kevin Donovan, a Program Manager on the Office Graphics & Visualizations team.
PivotCharts allow for charting-based data exploration and summarization in a way that no other Office visualization can. But they also offer power and flexibility that are largely unknown to many standard chart users, unless they work across businesses where PivotCharts play a significant role within their business intelligence efforts. Like PivotTables, PivotCharts work really well on aggregate data, which is why a lot of casual Excel analysts don't often encounter the need to use them. I'll show you some of the reasons and ways to use PivotCharts to explore your data.
I've borrowed a nicely-sized data set from my Power Map colleagues to illustrate the point. The data set examines power generation plants, by type, and across different states in the U.S. I like this data set because it illustrates the type of data structure best suited to PivotCharts, which aggregate values based on repeating categories of data, allowing you to slice it in interesting and insightful ways.
If you've ever inserted a chart in Excel, you may have used the incredibly valuable Recommended Charts feature in Office 2013.
The algorithm behind that feature looks at your data and decides what chart type best matches the selection. It's an awesome feature because it means that customers don't have to agonize over the best chart for their data. Moreover, the recommendation engine evaluates whether PivotCharts fit the shape of the data. When the recommendation engine sees a bunch of repetitive rows or columns, the chart suggestions will include PivotCharts because it's likely that you want to see that data aggregated in some meaningful way. As you can see from its repetitive values, our sample set lends itself to a PivotChart:
You'll know when the recommendation engine suggests a PivotChart because you will see a little pivot icon in the upper-right corner of the preview thumbnail:
Given the size and shape of this data, you likely wouldn't want to visualize it with a standard chart. If you did, it would be very difficult to read because the chart attempts to plot all of the data in the table, like this:
But if I apply a PivotChart at the same data set, I get something much more useful. That's because PivotCharts allow you to visualize the aggregated data, which is automatically grouped when you create the PivotChart, according to different "pivots" (often called "dimensions"), such as date, company, country, and state. Thus, a PivotChart on the same data set yields a chart like this:
Think of a PivotChart as the visual equivalent of a PivotTable. Compare the prior chart with the one that follows. The two use the same data set. However, in order to generate the one below, I used the Field List to select additional pivot points. Once they're selected, I used the Field Well to move them around. In my Axis category, I have State first (in this case I filtered out everything except the Western most states) and Energy Description second. This creates a hierarchy on the x-axis, first by State, then by Energy Description.
The first thing to notice are the on-chart meta-data elements. These are the tell-tale elements that distinguish PivotCharts from standard charts. One of those elements appears in the upper-left corner of the chart. It allows users to see the values (typically called a "measure") on the y-axis. If I had two measures in my data set, I would see two of these badges on the chart.
The other element unique to PivotCharts is an on-chart filtering option. This piece is one of the most valuable elements on the PivotChart because you can filter the PivotChart in really robust ways. It's always located on the lower-left corner of the chart and contains the same set of functions found in PivotTables.
Because PivotCharts often operate on large, hierarchical, and generally complex data sets, they also contain some enhanced filtering options. For example, as long as the PivotChart connects to a PivotTable, users can reduce the uninteresting data in their charts very quickly, filtering on values, sorting, and searching.
You'll notice that these are the same, powerful filtering options that you find on PivotTables. The options become very handy when your charts get too unwieldy or when you only care about a subset of the data. For example, if you only want to see types of energy whose megawatt value is over 100,000, you could easily create that chart:
In Excel 2013, we added the ability for you to create PivotCharts against a data model in your spreadsheet without creating a corresponding table. We call this decoupled PivotCharts. For these charts, we create the PivotTable in memory and use that data to drive the chart. This allows you to create a really clean surface of charts without the clutter of all of the accompanying tables. Plus, you get the Field List and Field Well that come with all PivotTables and PivotCharts. I can insert a new PivotChart directly against the data model contained in the workbook without having to select a PivotTable first. To do this, simply click the PivotChart button in the Charts group of the Ribbon, and choose Use an external data source.
Here, I can see an aggregate of megawatts, by energy type, by date.
In short, we created PivotCharts to visualize hierarchical data sets, and they work really well for those situations where data can be aggregated and sliced by some dimension that allows users to gain new insights on their data. Most of those scenarios involve business intelligence because they integrate nicely with in-memory models and OLAP data sets, which tend to prevail within businesses. But there's no reason why anyone analyzing the types of data I've described wouldn't benefit from the power and flexibility of PivotCharts.
I hope that helps clear up some of the mysteries of PivotCharts. Let us know what you think in the comments below!
-- Kevin Donovan, Program Manager, Office Graphics and Visualizations
For more on Excel charts:
Power Map for Excel earns new name with significant updates to 3D visualizations and storytelling
Telling a story with charts in Excel 2013
Drilling around in your Excel PivotTables and PivotCharts
No comments:
Post a Comment