Charting V – PivotCharts

re: Charting V – PivotCharts

Colin Banfield — Thu, 20 Apr 2006 15:10:30 GMT

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

ADD — Thu, 20 Apr 2006 17:52:51 GMT

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

Micke Hovmöller — Fri, 21 Apr 2006 10:19:38 GMT

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

Colin Banfield — Fri, 21 Apr 2006 20:11:25 GMT

<<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

Steffen — Tue, 25 Apr 2006 13:23:57 GMT

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

David Gainer — Wed, 26 Apr 2006 07:02:02 GMT

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.