Excel charting - stackable bar chart 2

[JPimp]

I am trying to make this in to a stackable bar chart, but I am at a loss here.

DATE flexlate CountOfWO_BASE
0108 ontime 22
0108 overdue 10
0208 ontime 28
0208 overdue 9
0308 ontime 17
0308 overdue 21
0408 ontime 25
0408 overdue 14
0508 ontime 32
0508 overdue 11
0608 ontime 52
0608 overdue 6
0708 ontime 40
0708 overdue 9
0808 ontime 50
0908 ontime 93
0908 overdue 11
1008 ontime 75
1008 overdue 16


As you can see for each month, i.e. 0108, 0208, etc, I have 2 values, one for ontime, and one for overdue, when I try to make this stackable bar chart, it still creates 1 column for ontime and one for overdue for each month, what do I need to do in order for this to work?

Thanks,

Kai-What?

 

[xlbo]

Date ontime overdue
0108 22 10
0208 28 9

etc etc

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[AnotherHiggins]

The header "CountOfWO_BASE" has me wondering.... Where is this data coming from? Are you doing a Pivot Table to get to this point?

I ask because the fastest way to get from where you are to where you want to go is with a Pivot Chart.


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[JPimp]

The data is coming from an Access/Oracle Query, so I am just doing a simple data import from the Query.

Kai-What?

 

[AnotherHiggins]

To stack the columns, Excel wants to see two different series. Currently, you just have one series - column C.

Two ways come to mind to do what you want. I'll start with what might be considered the more 'basic' solution, but it will likely be a pain for you to do this....

[tab]Option 1 - restructure your data (as xlbo noted above)
The Chart wizard would understand what you want stacked if your data looked like this:
[tt]
DATE OnTime OverDue
0108 22 10
0208 28 9
0308 17 21
[/tt]

[tab]Option 2 - Pivot Chart
This will actually go really quickly and can serve as a good introduction to Pivot Tables & Pivot Charts.
[ul][li]Select a cell somewhere in your data table[/li]
[li]Go to Data > PivotTable and PivotChart Report[/li]
[li]In the bottom section of the dialog box, select PivotChart Report (with PivotTable report)[/li]
[li]Select Next[/li]
[li]Excel will try to select the entire data table - if it is incorrect, you can select the table here[/li]
[li]Select Next[/li]
[li]Select Layout[/li]
[li]Click and Pull Date into the area labeled Row[/li]
[li]Click and Pull flexlate into the area labeled Column[/li]
[li]Click and Pull CountOfWO_BASE into the area labeled Data[/li]
[li]Select Next[/li]
[li]Select OK[/li]
[li]Select Finish[/li][/ul]
You now have an interactive Pivot Chart. You (or the user) can filter right there on the chart.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[JPimp]

Thanks for your help everyone, I will try to change the data structure first in the qry, and if that fails, go with the pivot table.

Kai-What?

 

[SkipVought]

I do this quite often. In fact, I'm working on a chart project right now, using the following technique. I actually have source data in multiple tables (sheets), all date phased.

First, I name all my data ranges with the table heading values.

Second, I set up my chart data for my x-axis, to be able to display as either week or month buckets.

Third, I set up chart data for each series that I want to plot. FYI, I often do MRP charts, displaying On-Hand, Past Due, Demand (displayed negative), Supply in work and Supply unreleased on the primary axis and Part Shortage, Part Surplus in the secondary axis, as the net of the data on the primary axis.

Fourth, I use the SUMPRODUCT function, so I can plot data for a particular Part that I have selected. So I usually end up with about 100 rows and less than a dozen columns, which calculates pretty quickly.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]