Pivot Chart with Multiple X Axis

  • Thread starter Thread starter sharonm
  • Start date Start date
S

sharonm

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!
 
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.
 
Back
Top