Pivot table charting

[GS80]

Hi All,

I suspect i'm asking to much of the pivot table charting abilities in Excel
but here goes.

I have created a pivot table from which there is data i want to chart and
data i'd like to display just in a tabular format. I found out about the Data
table functionality which is something i believe i can make use of to get
this tabular out put in the chart area.

The problem is that i only want to display one column from the pivottable as
a chart series, the other 2 (its always a 3 column layout) i want to siplay
in the data table. Is there any way in Excel 2007 that i can do this. It
seems to tie the data source to the whole pivot table which doesn't make it
easy to alter.

My two thoughts on this were:
a) is there some way i could flatten the pivot table and then remove the
series from the chart and entries from the data table.
b) Is it possible through the Excel object model that i could delete entries
from the chart and data table .

All help is greatfully recieved on this matter.

Regards,

G

 

[Jon Peltier]

If you use a data table, it must include all data in the chart. This is only
one weakness of data tables. I prefer to embed the chart in a worksheet and
build my own table nearby.

A pivot chart includes all regular (i.e., not subtotal) data in a pivot
table, and no data from outside the pivot table. You can make a regular
chart from pivot table. Start with a blank cell outside the pivot table and
create your chart. In step 2 of the chart wizard, or from the source data
dialog ('select data' in 2007) add each series individually.

- Jon

 

[GS80]

Thanks Jon,

I'm doing a lot f this via the object model in c# and the data table did
look ideal as it sets the data directly uinder the series bars without the
need for that manipulation in the application. I'll make my own data table
though to answer that part.

As for creating a regular chart based on the pivot table. If i set the range
to be row containing values i want in my chart , it looks fine. What i'd
liekto confirm is if i dynamically change the data will the range dynamically
resize to the number of columns in the Pivot table?

Thanks for the help.

 

[Jon Peltier]

If the data is simple enough that a data table in the chart is clear and
sufficient, then the chart is probably not necessary and a table alone would
suffice. If the data is too tricky for the data table to portray nicely,
then they must be separated. The pretty alignment of the axis and the data
table columns satisfies an irrational obsession, but that doesn't justify
hosing a chart to get the table included.

I posted on my blog yesterday about using a pivot table as source data for a
regular chart:

http://peltiertech.com/WordPress/20...e-data-for-a-chart-with-a-dual-category-axis/

In general the chart will show changes to the data in the cells it is linked
to, but it will not use the resized data range after the pivot table is
updated. In the blog post I mentioned two ways to keep the chart updated
when the pivot table changes in size. The first is defining dynamic ranges
as the chart source data, which works if the number of series in the chart
remains constant. This will be covered in another blog post, probably today,
and the above post will have a link to the new one. The second method, which
works when the pivot table changes on both dimensions, is to write a VBA
procedure to update the chart. This can be linked to a worksheet event so it
happens automatically. I will describe the VBA approach in a follow-up blog
post in a few days.

- Jon