Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Pivot table charting
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 we 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 |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Pivot table charting
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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "GS80" wrote in message ... 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 we 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 |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Pivot table charting
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" wrote: 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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "GS80" wrote in message ... 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 we 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 |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Pivot table charting
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/200...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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "GS80" wrote in message ... 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" wrote: 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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "GS80" wrote in message ... 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 we 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to create pivot table from existing pivot table in excel 2007 | Excel Discussion (Misc queries) | |||
Charting 'stripes' of data from within a table? | Charts and Charting in Excel | |||
pivot charting doesn't work for me - bill - | Charts and Charting in Excel | |||
Pivot Charting x-axis control | Charts and Charting in Excel | |||
Problem with series when charting from a pivot table | Charts and Charting in Excel |