Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 21
Default Pivot Chart with Multiple X Axis

I am new to Pivot Charts so I am hoping someone can help me with this. I have
a bar chart something like the following. I am using Excel 2003.



Amount


Cat 1 Cat 2 Cat 3 Cat 1 Cat 2 Cat 3
2007 2008

What I need to know is how do I get the bars for each year together with no
spaces. In other words, I would have a group or set of 3 bars and then a
space and then another set of 3. Also, if possible, make each Cat a different
color.

is this possible?

Thanks in advance!


  #2   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 3,346
Default Pivot Chart with Multiple X Axis

Hi,

First warning none of this is a good idea in 2003, and some not even in 2007.

A. This can be done rather easily with a regular chart, however, on a pivot
chart it may be doable but its probably going to take a lot of work at least
for the part that puts a space between the bars for the two years. One
workaround that's not so hard is to copy the pivot table and move to a new
location and choose Paste Special, Values. In this case if you insert a
blank row between one year and the next (assuming a vertical layout) you will
get a blank between the two year.

B. To spread the bars out is no problem,
1. double-click the series and on the Options tab set the Gap width to 0.

C. To format each category a different color because it is really a single
series means you must manually select each bar a color it. single click the
series, then single click the data bar. Then double click each bar one at a
time and on the Patterns tab change the Area color.
Problem - when you refresh the pivot table or pivot chart Excel will remove
your custom colors which means to get this to be efficient you must write
code that formats the chart after every refresh.
If you use the solution mentioned in A above, you will be able to color the
bars any color and have them retain their formatting.

But the solution in A means that you no longer have a pivot chart and are
not linked to the pivot table. You could use the =GETPIVOTDATA function to
build the non-pivot chart data area and retain the links. But it would still
not be a pivot chart in fact.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"sharonm" wrote:

I am new to Pivot Charts so I am hoping someone can help me with this. I have
a bar chart something like the following. I am using Excel 2003.



Amount


Cat 1 Cat 2 Cat 3 Cat 1 Cat 2 Cat 3
2007 2008

What I need to know is how do I get the bars for each year together with no
spaces. In other words, I would have a group or set of 3 bars and then a
space and then another set of 3. Also, if possible, make each Cat a different
color.

is this possible?

Thanks in advance!


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Pivot Chart Axis Labels Matthew Charts and Charting in Excel 3 December 23rd 08 06:59 AM
Pivot chart - switch axis JICDB Charts and Charting in Excel 1 September 16th 08 07:49 AM
Pivot Chart Y Axis Neil Charts and Charting in Excel 3 July 13th 07 06:22 PM
chart from pivot data does not update x-axis bar chart values - bug jason gers Excel Discussion (Misc queries) 0 April 3rd 07 06:34 PM
Pivot Table, Line Chart with Secondary Y axis, Page Field selection destroys Y2 axis [email protected] Charts and Charting in Excel 0 July 13th 05 09:30 PM


All times are GMT +1. The time now is 07:43 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"