Combine data in one bar for a PIVOT bar graph

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Scott - 23 Oct 2006 21:12 GMT

My COLUMN Headings are as follows:

Month-Day-Date-Sales-Discounts-Net, where Day is the day of the week, Mon.
Tues. etc.

I want my Page tabs in the Pivot Chart to be Month and Day.

My Chart Type is Stacked Column.

My Y Axes would be dollars, and my X axes would be the Date.

I would like to show Total Sales, with the amount of the discounts shown on
the same "bar" with different color.

One of the issues, I believe, is the fact that Excel only has 255 columns,
where there are 365 days in the year. I believe this because I tried to make
Sales a Series field, and received an error message.

Reply to this Message

Jon Peltier - 23 Oct 2006 22:51 GMT

If you need more than 256 columns, could you reorient the pivot table to use
256 rows instead? This will make for a rather crowded chart, won't it?
Working with dates, it will be better to use a single date column. You can
format a date to show just the day of the week (number format "ddd" for Mon,
Tues, etc., or "dddd" for Monday, Tuesday, etc.) or to include the day with
the date ("ddd dd.mmm.yy" for Mon 23 Oct 06).

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

> My COLUMN Headings are as follows:
>
[quoted text clipped - 15 lines]
> make
> Sales a Series field, and received an error message.

Reply to this Message

Scott - 24 Oct 2006 14:09 GMT

Yes, it does make for a crowded chart.

The two items I put in the "PAGE" field are Day and Month. I would never
have "All" selected in these two fields at the same time, hence the "crowded"
chart would be okay. The purpose of the graph is to show trends for days of
the week. So I would only be looking at say "Tuesday" or "Friday" when
reviewing the report. In other words, I want to evaluate all Tuesdays during
the year, or a particular month. Using only one cell to represent the date
as you suggested would defeat this purpose, I believe.

I do have the data listed by column, there are no row headings.

Thanks.Scott.

> If you need more than 256 columns, could you reorient the pivot table to use
> 256 rows instead? This will make for a rather crowded chart, won't it?
[quoted text clipped - 29 lines]
> > make
> > Sales a Series field, and received an error message.

Reply to this Message