Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Getting line on line chart to display after creation from Pivot Table
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? |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Getting line on line chart to display after creation from Pivot Table
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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "dcsi_jim" wrote in message oups.com... 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? |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Getting line on line chart to display after creation from Pivot Table
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. Jon Peltier wrote: 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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "dcsi_jim" wrote in message oups.com... 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? |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Getting line on line chart to display after creation from Pivot Table
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. dcsi_jim wrote: 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. Jon Peltier wrote: 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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "dcsi_jim" wrote in message oups.com... 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? |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
Getting line on line chart to display after creation from Pivot Table
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. Interpolated is not available for selection (it's greyed out). I'm guessing this means there are no gaps. 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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "dcsi_jim" wrote in message ups.com... 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. dcsi_jim wrote: 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. Jon Peltier wrote: 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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "dcsi_jim" wrote in message oups.com... 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? |
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
Getting line on line chart to display after creation from Pivot Table
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! Jon Peltier wrote: 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. Interpolated is not available for selection (it's greyed out). I'm guessing this means there are no gaps. 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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "dcsi_jim" wrote in message ups.com... 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. dcsi_jim wrote: 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. Jon Peltier wrote: 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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "dcsi_jim" wrote in message oups.com... 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? |
#7
Posted to microsoft.public.excel.charting
|
|||
|
|||
Getting line on line chart to display after creation from Pivot Table
On Jan 15, 5:00 pm, "dcsi_jim" wrote:
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 |
#8
Posted to microsoft.public.excel.charting
|
|||
|
|||
Getting line on line chart to display after creation from Pivot Table
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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "donnyj" wrote in message ups.com... On Jan 15, 5:00 pm, "dcsi_jim" wrote: 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 |
#9
Posted to microsoft.public.excel.charting
|
|||
|
|||
Getting line on line chart to display after creation from Pivot Table
On Feb 3, 2:10 pm, "Jon Peltier"
wrote: 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 This did the trick. Many thanks! -Donny |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pivot table data display in cell | Excel Discussion (Misc queries) | |||
Why does (blank) display on pivot table | Charts and Charting in Excel | |||
pie-charting non-numeric data | Charts and Charting in Excel | |||
create space in line chart between points, linked to pivot table | Charts and Charting in Excel | |||
pivot table multi line chart | Charts and Charting in Excel |