Welcome to MSDN Blogs Sign in | Join | Help

Charting V – PivotCharts

Whenever we talk to users about PivotCharts, the first request we hear is that they behave more like regular charts.  In previous versions, PivotCharts had very limited layout and formatting options. In addition, if you refreshed the PivotTable that the PivotChart was based upon, the PivotChart would lose whatever formatting it had. We heard from many users that they would often just create regular charts instead of PivotCharts, since could be problematic  As PivotTables and charts changed in Excel 2007, we made sure PivotCharts changed along with them – with a key goal being that they became more consistent.

Formatting

PivotCharts in Excel 2007 can have all the same formatting as regular charts, including all the layouts and styles talked about in previous posts.  You can move and resize chart elements, or change formatting of individual data points.  One of the few restrictions that we did not have time to address in this version is the one on chart types – you still can’t create scatter, bubble, or stock PivotCharts.

If you refresh the data for your PivotChart, the chart updates and the formatting does not change.  Any new series or data points will be formatted to match the style or series as appropriate.  As a result, you can easily make PivotCharts for presentation or publication.

Furthermore, PivotChart will remember formatting across pivots.  If you set the colour of the series or a particular data point to red, then change the pivot so your data is no longer showing, the red band is gone as well. If you bring the data back into view, the red colour returns.

PivotTable Field List

PivotCharts use the same field list as PivotTables.  Just as with PivotTables, you can click the checkboxes for the fields you are interested in, and they will be reasonably laid out as a PivotChart.  You can also drag the fields around from region to region to pivot the PivotChart.  The field list also provides access to field settings such as how to summarize the data.

PivotChart Filter Pane

The PivotChart filter pane is a new task pane that enables you to filter PivotCharts.  In previous versions, filters were accessed from buttons in the PivotChart, but removed these so they didn’t affect the layout of the PivotChart.  The filter pane has the same filter capabilities as for PivotTables.  See filtering in PivotTables for details.  Below is a shot of the PivotChart field list and Filter task pane.


(Click to enlarge)

Published Wednesday, April 19, 2006 11:57 PM by David Gainer
Filed under:

Comments

# re: Charting V – PivotCharts

Thursday, April 20, 2006 8:10 AM by Colin Banfield
David, the changes to PivotCharts are certainly welcome.  However, there is one issue that I have with existing PivotCharts that you may or may not have addressed in the new version.  Because of the linkage between the PivotTable and the PivotChart, you can't arrange the fields in the Chart independently of the table.  For example, if I want to show total quantity and total sales in the PivotTable but only total sales in the PivotChart, the only way to do this is to create a copy of the PivotTable and create the chart from the copy.  Creating a regular chart from the PivotTable is a lot of hassle if you want to keep your chart refreshed when the PivotTable is refreshed.  There should be an *option* to select and arrange the fields in the Chart independently of the table.

Another thing, *when* you're creating a PivotChart, why not provide the option to specify a location instead of having the user go through the additional step of changing the location *after* the chart is created?  When I'm creating a PivotChart, very often I'd like it to be on the same worksheet as the PivotTable.

# re: Charting V – PivotCharts

Thursday, April 20, 2006 10:52 AM by ADD
Thank you this blog and for the time and effort in keeping us informed of the changes in Excel.  It has helped a lot.

That said, I'm really amazed at all the time and effort that has gone into making the building of these charts pretty.  You say there is a new chart engine.  Has anything changed with regards to the actually charts themselves?  More charts?

How about things like being able to resize the data label boxes?  Having custom contents in the data labels?

I know this is a series on charting so I'm hoping there is more indepth information coming about the charting engine.

Thanks

# re: Charting V – PivotCharts

Friday, April 21, 2006 3:19 AM by Micke Hovmöller
I would just like to raise my voice in support for Colin's suggestion that the chart can be placed on the same sheet as the pivot table.

This is important for two reasons (one of which may be obsolete by now, however). The first, and most important reason is that I want to work with the table and chart interactively and side by side, so that I can see at all times what the final result will be. It is usually easier to tweak the table than the chart and then I don't want to have to switch to another sheet to see the reuslts.

The other reason is that the charts that fill their own sheets will almost be guaranteed to have formatting issues when copied to Powerpoint. This may be solved now, so maybe this isn't important anymore.

In practice, I never, ever, copied a pivot  chart to Powerpoint becuase they uniformly looked terrible. If this is properly addressed you will have one very very satisifed customer! (I know you write that it is fixed, but I didn't see any examples and, well, seeing is believeing...)

# re: Charting V – PivotCharts

Friday, April 21, 2006 1:11 PM by Colin Banfield
<<I would just like to raise my voice in support for Colin's suggestion that the chart can be placed on the same sheet as the pivot table.<<

Micke, you can change the PivotChart location by selecting Chart-->Location on the PivotChart menu.  My point is that the user should be provided with the option before the PivotChart is created.

# re: Charting V – PivotCharts

Tuesday, April 25, 2006 6:23 AM by Steffen
Please give us at least a way to switch rows/columns in a pivot chart  independent of the table. To see how important that is just do the following:
- Create a PivotTable with a stacked chart (say: stacked bar) from AdventureWorksDW AS SalesSummary
- Check any measure (say SalesOrderAmount)
- Check Calendar Year
- Check sales region
Now look at the table:
Makes sense: Date in columns, sales regions in rows
But, Chart: makes no sense: Sales regions are the individual bar, stacked by year! But most people want to have years as bars, stacked by sales region. No way to do this without rearranging the table

Regards,
Steffen

# re: Charting V – PivotCharts

Wednesday, April 26, 2006 12:02 AM by David Gainer
Hi folks - thanks for the comments.

Colin - the linkage between PivotChart and PivotTableis something we would like to do, but did not get to this release.  WRT the location, the new default is to insert the PivotChart on the same sheet as a PivotTable.  The PivotChart can be moved to a different sheet by clicking on the “Move Chart” button on the Chart Design tab.  In the next release, we will reassess providing a UI option to set the location on creation.

ADD - there are no new chart types in Excel 2007 and you can’t resize the data label text boxes.  We know these are features people are interested in, and we will keep this in mind for next version.  As for data labels, custom content can be added by setting the data label to a formula.  This is the same functionality that was in Excel 2003 and earlier versions.

Micke - the formatting of a chart copied from Excel to PowerPoint will now carry over seamlessly.

Steffen - I hear you.  We will look at this for next version.






New Comments to this post are disabled
 
Page view tracker