Getting line on line chart to display after creation from Pivot Table

[dcsi_jim]

I have two columns of values, one with Dates going from 04/31/2004 to
12/31/2006 (one per month), and another column with items per month.

Read another thread which seemed to imply having values stored as
number (instead or in addition to having them as a number for their
format) was important for a line chart to display properly. So, I copy
the number range (for the items per month), paste special, and select
Add. they should be stored as numbers now.

Created a Pivot table under the Data pull-down.

Clicked Chart, and it created a bar chart, which looks good.

Right-clicked, and select Line chart with markers displayed for each
data value.

The problem: the markers display properly, but NO LINE SHOWS.

My manager said he had this problem as of Dec 2006 or so, and could not
figure out why.

Any suggestions on troubleshooting?

 

[Jon Peltier]

Are there gaps in the data? By default, Excel does not plot over the gaps.
Select the chart, go to Tools menu > Options > Chart, and choose Interpolate
where it asks how to treat blank cells.

- Jon

 

[dcsi_jim]

Interpolated is not available for selection (it's greyed out). I'm
guessing this means there are no gaps.
There are no gaps in the sense that there is a y-axis value (both a
value in the pivot chart and a vertical column showing the value for
the month) for -each- month between the dates.
There are gaps in the sense there is not a value breakdown any finer
than per month, but I don't suspect Excel treats only a value per month
as there being gaps (correct me if I'm mistaken).

One strange thing I see is there are semi-random, small, black and
white squares at the top of some columns or bars (trying to use proper
terminology). The squares seems to be about 8 pixels by 8 pixels.
I count a total of 33 data items shown as columns on the chart, and 12
of these B & W squares. The white portion is usually where the squares
intersects with the top of the column - white on the bottom and black
on the top.

Don't know if this is significant or not. It might be something
indicator or property of some data points. It bothers me only some
have this.

One other note: I have at times been able to get a line showing, but
it was only for maybe 3-4 columns towards the middle of the data range.
I don't remember how I did this, but do recall there was at least one
data item missing.

Everything looks okay to me, which makes me think some setting is off,
or there's something strange going on with the data.

 

[dcsi_jim]

One more comment: it appears the B & W squares appear and change in
some fashion if I click on the columns themselves representing the data
values.
I suspect I'm not barking up the right tree spending much time talking
about these squares, but wanted to mention since I don't know what
their purpose is.

 

[Jon Peltier]

Do the squares appear when you select a series on the chart, then disappear
when you deselect the chart? If so, then they are the series selection
indicators. If there are many (more than a dozen or so) points in a series,
not all have indicators.

No, I think it means it's not available for a pivot chart. Pivot charts do
not live up to their promise.

Can you adjust the X axis? Is it a time scale axis? Try to select a
different base unit (months instead of days). Or go to Chart menu > Chart
Options > Axes tab, and choose Category instead of Time Scale or Auto.

- Jon

 

[dcsi_jim]

I tried your suggestions on adjusting the x-axis, and trying Category
instead, to no avail.
Fortunately, when I had the number and date columns showing in a sheet,
I did an Insert -> chart, and selected "Line with markers displayed for
each data value", and this worked.

I just adjusted the labels for the axis.

So, I found (with a bit of help) an alternative solution to get the
chart. Doing it the other way did not work for my manager, myself, or
on my home notebook with Excel 2003.

I suspect it's either a problem with the data, it's storage, or perhaps
a Windows or office update changed something in the past month or two.

thanks for the thoughts!

 

[donnyj]

I have two columns of values, one with Dates going from 04/31/2004 to
12/31/2006 (one per month), and another column with items per month.

Read another thread which seemed to imply having values stored as
number (instead or in addition to having them as a number for their
format) was important for aline chartto display properly. So, I copy
the number range (for the items per month), paste special, and select
Add. they should be stored as numbers now.

Created a Pivot table under the Data pull-down.

Clicked Chart, and it created a bar chart, which looks good.

Right-clicked, and selectLine chartwith markers displayed for each
data value.

Theproblem: the markers display properly, but NO LINE SHOWS.

My manager said he had thisproblemas of Dec 2006 or so, and could not
figure out why.

Any suggestions on troubleshooting?

I'm seeing the same symptom, but in a different situation:

When I create a line chart using data in merged cells, the markers
display properly, but NO LINE SHOWS!

If I move the data to cells that have not been merged, the chart
displays properly.

Has anyone seen this and found a solution?

-Donny J

 

[Jon Peltier]

Try to change how Excel deals with blank cells. In merged cells, all but the
top left cell of the merged area are considered blank. Tools menu > Options

Chart, Plot Empty Cells As... Interpolate.

- Jon

 

[donnyj]

Try to change how Excel deals with blank cells. In merged cells, all but the
top left cell of the merged area are considered blank. Tools menu > Options

- Jon

This did the trick. Many thanks! -Donny