Some data series missing from stacked bar chart

[RogerC]

I have a table similar to this:
Across row 1 are header labels (A1 = 'CITY', A2 through AJ1 = Week1, Week2, Week3, etc)
Down column A are the city names (A2 through A11 = Phoenix, LosAngeles, Dallas, etc)
The data in B2 through A11 is the weekly total sales for each city.

I have checked the Source Data settings for the chart and each of it's city series. The chart appears to be pointing to all of the correct data.

Here is the problem:
For week13 (the most recent week) only the data in Rows 4, 5, and 8 are being plotted in my stacked bar chart. The data in rows 3, 6, 7, 9, 10 and 11 is missing from the chart. Even though the Source Data settings show them to be included. (These cells have actual data, not blank or 0).

The data series that are missing varies from week to week (rows 5, 7, 10 are missing from week11 chart data).

I have stared at this until I'm blue in the face, but cannot determine what is wrong. Can anyone suggest something I might look for?

 

[tbablue]

Are you inputing the data directly into the cells via a keyboard / are you importing the data from another cell, worksheet, workbook, etc.?

 

[RogerC]

tbablue,

The charted data is being auto-populated from a linked workbook. The orignal data is pasted into the source workbook from a Business Objects Report (queried from SAP data).

 

[IluvPivots]

is it possible the scale is off?

 

[klb]

Have you tried increasing the size of the chart? Have had this problem when there are too many data points.

 

[RogerC]

ILuvPivots,

Thanks for the idea, but I don't think that's the problem. For example: in the current week's bar, the values I want charted are Phoenix = 130, LosAngeles = 90, Dallas = 220, Toledo = 600, etc. The 220 is not plotted, but the 90 and 600 are. (Although the value for Dallas is plotted in the previous week's bar. )

 

[RogerC]

klb,

Thanks for the idea, but I don't think that's it either. I just tried plotting only half of my data range in the same chart... not much data really. The very same data is missing from each bar.

 

[IluvPivots]

Sometimes my number formats get all screwy when bringing something in from SAP. Occasionally numbers are seen as text.

 

[RogerC]

IluvPivots,

hmm... Good thought, but I don't think that's it either. This is the dataflow: SAP --> Business Objects Query of SAP --> Paste into Workbook 1 --> Workbook 2 (gets data via link from Workbook 1).

Workbook 1 is the total aggregate data. Workbook 2 (etc) is the Department Level data which each department needs.

I have stacked bar charts in Workbook 1 that show all data plotted correctly. (Even the same data that ends up not showing in the charts in Workbook 2).

Your photo makes me smile, even on a bad day.

 

[Jon Peltier]

Roger -

If the data comes from SAP to BO to Excel as text, it will be plotted as zero values, no matter what it looks like. You have to check for this. For example, if it is numerical data, and you have applied the general horizontal alignment, the numerical values will be right aligned. If it's left aligned under general alignment, it'[s treated as text. (A good reason not to apply too much nice formatting in a data sheet.)

Convert to numerical values by copying a blank cell (value zero), selecting the cells to change, and use Edit menu > Paste Special > Operation - Add. This forces Excel to interpret the cells as numbers.