If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
P & V chart: Secondary X axis gradient (again)
It did not work. There may be some trick with it but I
couldn't find it either. OK let me explain a bit more. Category data is one year date, yet, it is not 365 days. Some skipps here and there throughout, therefore, there is a space among columns accordingly. I am trying to fill the space out by creating secondary X axis. (I hope I am doing right.) By doing so, I can successfully fill the space by making a bit of change (making weight thiker, etc.) But I don't know how to make the area (that is a plot area without secondary X axis) gradient. Thanks. -----Original Message----- Richard - An off-topic word to the wise. I know it's only a hotmail account, but if you don't do something to disguise it, you'll be fighting off mountains of spam in your inbox. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ wrote: right click on the 'volume' data series and select 'format data series'. On the 'Patterns' tab, click 'fill effects'. from there you can pick your gradient colours. Richard -----Original Message----- I created a typical price&volume graph. Price/XY, Volume/Column. I put Secondary Category (X), Category in order not to break columns. Q. How could I change Volume's color (on Secondary X axis) to gradient (two colors)? . . |
#2
|
|||
|
|||
P & V chart: Secondary X axis gradient (again)
I'm not really sure I understand what you're doing. If you have time
based data with gaps, and you don't want the gaps to show, you can change to a category axis. Right click on the chart, choose Chart Options from the pop up menu, and on the Axes tab, change the X Axis from Automatic or Time Scale to Category. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ wlotto wrote: It did not work. There may be some trick with it but I couldn't find it either. OK let me explain a bit more. Category data is one year date, yet, it is not 365 days. Some skipps here and there throughout, therefore, there is a space among columns accordingly. I am trying to fill the space out by creating secondary X axis. (I hope I am doing right.) By doing so, I can successfully fill the space by making a bit of change (making weight thiker, etc.) But I don't know how to make the area (that is a plot area without secondary X axis) gradient. Thanks. -----Original Message----- Richard - An off-topic word to the wise. I know it's only a hotmail account, but if you don't do something to disguise it, you'll be fighting off mountains of spam in your inbox. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ wrote: right click on the 'volume' data series and select 'format data series'. On the 'Patterns' tab, click 'fill effects'. from there you can pick your gradient colours. Richard -----Original Message----- I created a typical price&volume graph. Price/XY, Volume/Column. I put Secondary Category (X), Category in order not to break columns. Q. How could I change Volume's color (on Secondary X axis) to gradient (two colors)? . . |
#3
|
|||
|
|||
P & V chart: Secondary X axis gradient (again)
'Category' of Value(X)Axis (Primary axis) is dimmed (both
Automatic and Timescale are also dimmed). I can select Category for Secondary Axis (Category(X) axis). When I select this, every problem happens. Why selections for Value(X) axis are dimmed? thanks. -----Original Message----- I'm not really sure I understand what you're doing. If you have time based data with gaps, and you don't want the gaps to show, you can change to a category axis. Right click on the chart, choose Chart Options from the pop up menu, and on the Axes tab, change the X Axis from Automatic or Time Scale to Category. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ wlotto wrote: It did not work. There may be some trick with it but I couldn't find it either. OK let me explain a bit more. Category data is one year date, yet, it is not 365 days. Some skipps here and there throughout, therefore, there is a space among columns accordingly. I am trying to fill the space out by creating secondary X axis. (I hope I am doing right.) By doing so, I can successfully fill the space by making a bit of change (making weight thiker, etc.) But I don't know how to make the area (that is a plot area without secondary X axis) gradient. Thanks. -----Original Message----- Richard - An off-topic word to the wise. I know it's only a hotmail account, but if you don't do something to disguise it, you'll be fighting off mountains of spam in your inbox. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ wrote: right click on the 'volume' data series and select 'format data series'. On the 'Patterns' tab, click 'fill effects'. from there you can pick your gradient colours. Richard -----Original Message----- I created a typical price&volume graph. Price/XY, Volume/Column. I put Secondary Category (X), Category in order not to break columns. Q. How could I change Volume's color (on Secondary X axis) to gradient (two colors)? . . . |
#4
|
|||
|
|||
P & V chart: Secondary X axis gradient (again)
I'm going to start over.
In your first post, you said you want a chart with volume as columns and price as XY. This will also work with the price on a Line chart. If the data is arranged like this, it will be easy to make the chart: Date Volume Close 3/2/2004 14044549 98.24 3/3/2004 13637071 107.55 3/4/2004 11787985 95.96 1. Select the data and make either a Line or a Scatter chart. 2. Right click on the Volume series, select Chart Type from the pop up menu, and choose the Stacked Column option. 3. (see NOTE below) Double click on the X axis, Scale tab, check Value Axis Crosses at Maximum Category. 4. Double click on the Price series, click on the Axis tab, and choose Secondary. NOTE: If you make the Volume series the secondary series, it will make formatting the X axis more difficult. If you want volume on the right axis, and price on the left, follow all steps. If you want volume on the left and price on the right, skip step 3. If you follow these steps, whether you choose Line or Scatter for your Price chart type, the chart will be substantially the same. Don't let the name of the chart type fool you. Either type series can be formatted to look the same: with or without markers, with or without connecting lines. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ wlotto wrote: 'Category' of Value(X)Axis (Primary axis) is dimmed (both Automatic and Timescale are also dimmed). I can select Category for Secondary Axis (Category(X) axis). When I select this, every problem happens. Why selections for Value(X) axis are dimmed? thanks. -----Original Message----- I'm not really sure I understand what you're doing. If you have time based data with gaps, and you don't want the gaps to show, you can change to a category axis. Right click on the chart, choose Chart Options from the pop up menu, and on the Axes tab, change the X Axis from Automatic or Time Scale to Category. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ wlotto wrote: It did not work. There may be some trick with it but I couldn't find it either. OK let me explain a bit more. Category data is one year date, yet, it is not 365 days. Some skipps here and there throughout, therefore, there is a space among columns accordingly. I am trying to fill the space out by creating secondary X axis. (I hope I am doing right.) By doing so, I can successfully fill the space by making a bit of change (making weight thiker, etc.) But I don't know how to make the area (that is a plot area without secondary X axis) gradient. Thanks. -----Original Message----- Richard - An off-topic word to the wise. I know it's only a hotmail account, but if you don't do something to disguise it, you'll be fighting off mountains of spam in your inbox. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ wrote: right click on the 'volume' data series and select 'format data series'. On the 'Patterns' tab, click 'fill effects'. from there you can pick your gradient colours. Richard -----Original Message----- I created a typical price&volume graph. Price/XY, Volume/Column. I put Secondary Category (X), Category in order not to break columns. Q. How could I change Volume's color (on Secondary X axis) to gradient (two colors)? . . . |
#5
|
|||
|
|||
P & V chart: Secondary X axis gradient (again)
Data is like this. Some dates are skipped. One day or
sometimes one week (Real data covers for a couple of years.) What I have to do is: 1. Close - left Y. Volume - right Y. 2. Beginning date and ending date have to be shown exactly at the ends of X axis. 3. Skipped dates cannot have spaced. Has to be shown continuously. 4. Change the background (only plot area) to two color gradient. *BTW, why stacked column? Not a clustered column? Date Volume Close 3/2/04 14044549 98.24 3/3/04 13637071 107.55 3/5/04 10899971 98.30 3/6/04 9771689 97.16 3/7/04 11143407 96.02 3/9/04 9886843 93.74 3/10/04 8758561 117.6033333 3/11/04 10130279 116.4633333 3/12/04 9001997 115.3233333 3/14/04 6745433 113.0433333 -----Original Message----- I'm going to start over. In your first post, you said you want a chart with volume as columns and price as XY. This will also work with the price on a Line chart. If the data is arranged like this, it will be easy to make the chart: Date Volume Close 3/2/2004 14044549 98.24 3/3/2004 13637071 107.55 3/4/2004 11787985 95.96 1. Select the data and make either a Line or a Scatter chart. 2. Right click on the Volume series, select Chart Type from the pop up menu, and choose the Stacked Column option. 3. (see NOTE below) Double click on the X axis, Scale tab, check Value Axis Crosses at Maximum Category. 4. Double click on the Price series, click on the Axis tab, and choose Secondary. NOTE: If you make the Volume series the secondary series, it will make formatting the X axis more difficult. If you want volume on the right axis, and price on the left, follow all steps. If you want volume on the left and price on the right, skip step 3. If you follow these steps, whether you choose Line or Scatter for your Price chart type, the chart will be substantially the same. Don't let the name of the chart type fool you. Either type series can be formatted to look the same: with or without markers, with or without connecting lines. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ wlotto wrote: 'Category' of Value(X)Axis (Primary axis) is dimmed (both Automatic and Timescale are also dimmed). I can select Category for Secondary Axis (Category(X) axis). When I select this, every problem happens. Why selections for Value(X) axis are dimmed? thanks. -----Original Message----- I'm not really sure I understand what you're doing. If you have time based data with gaps, and you don't want the gaps to show, you can change to a category axis. Right click on the chart, choose Chart Options from the pop up menu, and on the Axes tab, change the X Axis from Automatic or Time Scale to Category. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ wlotto wrote: It did not work. There may be some trick with it but I couldn't find it either. OK let me explain a bit more. Category data is one year date, yet, it is not 365 days. Some skipps here and there throughout, therefore, there is a space among columns accordingly. I am trying to fill the space out by creating secondary X axis. (I hope I am doing right.) By doing so, I can successfully fill the space by making a bit of change (making weight thiker, etc.) But I don't know how to make the area (that is a plot area without secondary X axis) gradient. Thanks. -----Original Message----- Richard - An off-topic word to the wise. I know it's only a hotmail account, but if you don't do something to disguise it, you'll be fighting off mountains of spam in your inbox. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ wrote: right click on the 'volume' data series and select 'format data series'. On the 'Patterns' tab, click 'fill effects'. from there you can pick your gradient colours. Richard -----Original Message----- I created a typical price&volume graph. Price/XY, Volume/Column. I put Secondary Category (X), Category in order not to break columns. Q. How could I change Volume's color (on Secondary X axis) to gradient (two colors)? . . . . |
#6
|
|||
|
|||
P & V chart: Secondary X axis gradient (again)
Here's how to deal with your constraints.
#1 was covered in the NOTE under my previous process. #2,3: Make the initial chart as a Line chart. Double click the X axis, and uncheck the Value Axis Crosses Between Categories box, to put the end dates on the extreme ends of the axis. #4: Double click on the plot area, between plotted columns and markers. Click the Fill Effects button under the Area color palette. On the Gradient tab, select the options that produce the gradient you desire. And with only one column series, it doesn't matter whether you choose clustered or stacked. In fact, I'd intended to write clustered. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ wlotto wrote: Data is like this. Some dates are skipped. One day or sometimes one week (Real data covers for a couple of years.) What I have to do is: 1. Close - left Y. Volume - right Y. 2. Beginning date and ending date have to be shown exactly at the ends of X axis. 3. Skipped dates cannot have spaced. Has to be shown continuously. 4. Change the background (only plot area) to two color gradient. *BTW, why stacked column? Not a clustered column? Date Volume Close 3/2/04 14044549 98.24 3/3/04 13637071 107.55 3/5/04 10899971 98.30 3/6/04 9771689 97.16 3/7/04 11143407 96.02 3/9/04 9886843 93.74 3/10/04 8758561 117.6033333 3/11/04 10130279 116.4633333 3/12/04 9001997 115.3233333 3/14/04 6745433 113.0433333 -----Original Message----- I'm going to start over. In your first post, you said you want a chart with volume as columns and price as XY. This will also work with the price on a Line chart. If the data is arranged like this, it will be easy to make the chart: Date Volume Close 3/2/2004 14044549 98.24 3/3/2004 13637071 107.55 3/4/2004 11787985 95.96 1. Select the data and make either a Line or a Scatter chart. 2. Right click on the Volume series, select Chart Type from the pop up menu, and choose the Stacked Column option. 3. (see NOTE below) Double click on the X axis, Scale tab, check Value Axis Crosses at Maximum Category. 4. Double click on the Price series, click on the Axis tab, and choose Secondary. NOTE: If you make the Volume series the secondary series, it will make formatting the X axis more difficult. If you want volume on the right axis, and price on the left, follow all steps. If you want volume on the left and price on the right, skip step 3. If you follow these steps, whether you choose Line or Scatter for your Price chart type, the chart will be substantially the same. Don't let the name of the chart type fool you. Either type series can be formatted to look the same: with or without markers, with or without connecting lines. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ wlotto wrote: 'Category' of Value(X)Axis (Primary axis) is dimmed (both Automatic and Timescale are also dimmed). I can select Category for Secondary Axis (Category(X) axis). When I select this, every problem happens. Why selections for Value(X) axis are dimmed? thanks. -----Original Message----- I'm not really sure I understand what you're doing. If you have time based data with gaps, and you don't want the gaps to show, you can change to a category axis. Right click on the chart, choose Chart Options from the pop up menu, and on the Axes tab, change the X Axis from Automatic or Time Scale to Category. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ wlotto wrote: It did not work. There may be some trick with it but I couldn't find it either. OK let me explain a bit more. Category data is one year date, yet, it is not 365 days. Some skipps here and there throughout, therefore, there is a space among columns accordingly. I am trying to fill the space out by creating secondary X axis. (I hope I am doing right.) By doing so, I can successfully fill the space by making a bit of change (making weight thiker, etc.) But I don't know how to make the area (that is a plot area without secondary X axis) gradient. Thanks. -----Original Message----- Richard - An off-topic word to the wise. I know it's only a hotmail account, but if you don't do something to disguise it, you'll be fighting off mountains of spam in your inbox. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ wrote: right click on the 'volume' data series and select 'format data series'. On the 'Patterns' tab, click 'fill effects'. from there you can pick your gradient colours. Richard -----Original Message----- I created a typical price&volume graph. Price/XY, Volume/Column. I put Secondary Category (X), Category in order not to break columns. Q. How could I change Volume's color (on Secondary X axis) to gradient (two colors)? . . . . |
#7
|
|||
|
|||
P & V chart: Secondary X axis gradient (again)
It worked almost all but,
1. The data is covering over two years and does not have data point every single day. It skipps like 1, 4, or 7... days, or sometimes no skipps at all. Skipped dates come out to be space on columns. (eg. 500 data points in two years.) 2. When I change 'Chart Optionselect Category' in order to fill the space between columns, the ending date changes to some obscure date, not the last date of data. I don't know how to scale it. Scale tub shows no longer usual format. 2. Any other way with XY schatters start, not with two lines? thanks. -----Original Message----- Here's how to deal with your constraints. #1 was covered in the NOTE under my previous process. #2,3: Make the initial chart as a Line chart. Double click the X axis, and uncheck the Value Axis Crosses Between Categories box, to put the end dates on the extreme ends of the axis. #4: Double click on the plot area, between plotted columns and markers. Click the Fill Effects button under the Area color palette. On the Gradient tab, select the options that produce the gradient you desire. And with only one column series, it doesn't matter whether you choose clustered or stacked. In fact, I'd intended to write clustered. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ wlotto wrote: Data is like this. Some dates are skipped. One day or sometimes one week (Real data covers for a couple of years.) What I have to do is: 1. Close - left Y. Volume - right Y. 2. Beginning date and ending date have to be shown exactly at the ends of X axis. 3. Skipped dates cannot have spaced. Has to be shown continuously. 4. Change the background (only plot area) to two color gradient. *BTW, why stacked column? Not a clustered column? Date Volume Close 3/2/04 14044549 98.24 3/3/04 13637071 107.55 3/5/04 10899971 98.30 3/6/04 9771689 97.16 3/7/04 11143407 96.02 3/9/04 9886843 93.74 3/10/04 8758561 117.6033333 3/11/04 10130279 116.4633333 3/12/04 9001997 115.3233333 3/14/04 6745433 113.0433333 -----Original Message----- I'm going to start over. In your first post, you said you want a chart with volume as columns and price as XY. This will also work with the price on a Line chart. If the data is arranged like this, it will be easy to make the chart: Date Volume Close 3/2/2004 14044549 98.24 3/3/2004 13637071 107.55 3/4/2004 11787985 95.96 1. Select the data and make either a Line or a Scatter chart. 2. Right click on the Volume series, select Chart Type from the pop up menu, and choose the Stacked Column option. 3. (see NOTE below) Double click on the X axis, Scale tab, check Value Axis Crosses at Maximum Category. 4. Double click on the Price series, click on the Axis tab, and choose Secondary. NOTE: If you make the Volume series the secondary series, it will make formatting the X axis more difficult. If you want volume on the right axis, and price on the left, follow all steps. If you want volume on the left and price on the right, skip step 3. If you follow these steps, whether you choose Line or Scatter for your Price chart type, the chart will be substantially the same. Don't let the name of the chart type fool you. Either type series can be formatted to look the same: with or without markers, with or without connecting lines. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ wlotto wrote: 'Category' of Value(X)Axis (Primary axis) is dimmed (both Automatic and Timescale are also dimmed). I can select Category for Secondary Axis (Category(X) axis). When I select this, every problem happens. Why selections for Value(X) axis are dimmed? thanks. -----Original Message----- I'm not really sure I understand what you're doing. If you have time based data with gaps, and you don't want the gaps to show, you can change to a category axis. Right click on the chart, choose Chart Options from the pop up menu, and on the Axes tab, change the X Axis from Automatic or Time Scale to Category. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ wlotto wrote: It did not work. There may be some trick with it but I couldn't find it either. OK let me explain a bit more. Category data is one year date, yet, it is not 365 days. Some skipps here and there throughout, therefore, there is a space among columns accordingly. I am trying to fill the space out by creating secondary X axis. (I hope I am doing right.) By doing so, I can successfully fill the space by making a bit of change (making weight thiker, etc.) But I don't know how to make the area (that is a plot area without secondary X axis) gradient. Thanks. -----Original Message----- Richard - An off-topic word to the wise. I know it's only a hotmail account, but if you don't do something to disguise it, you'll be fighting off mountains of spam in your inbox. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ wrote: right click on the 'volume' data series and select 'format data series'. On the 'Patterns' tab, click 'fill effects'. from there you can pick your gradient colours. Richard -----Original Message----- I created a typical price&volume graph. Price/XY, Volume/Column. I put Secondary Category (X), Category in order not to break columns. Q. How could I change Volume's color (on Secondary X axis) to gradient (two colors)? . . . . . |
#8
|
|||
|
|||
P & V chart: Secondary X axis gradient (again)
1. If you use a line chart, you can eliminate the skips by changing to a
category axis. Right click on the chart, choose Chart Options from the pop up menu, and on the Axes tab, change the X Axis from Automatic or Time Scale to Category. 2. A category axis will only go as far as the data goes. Does your X data extend down further in the worksheet than your Y data? 3. Double click the series, and on the Patterns tab, set the line style to None. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ wlotto wrote: It worked almost all but, 1. The data is covering over two years and does not have data point every single day. It skipps like 1, 4, or 7... days, or sometimes no skipps at all. Skipped dates come out to be space on columns. (eg. 500 data points in two years.) 2. When I change 'Chart Optionselect Category' in order to fill the space between columns, the ending date changes to some obscure date, not the last date of data. I don't know how to scale it. Scale tub shows no longer usual format. 2. Any other way with XY schatters start, not with two lines? thanks. -----Original Message----- Here's how to deal with your constraints. #1 was covered in the NOTE under my previous process. #2,3: Make the initial chart as a Line chart. Double click the X axis, and uncheck the Value Axis Crosses Between Categories box, to put the end dates on the extreme ends of the axis. #4: Double click on the plot area, between plotted columns and markers. Click the Fill Effects button under the Area color palette. On the Gradient tab, select the options that produce the gradient you desire. And with only one column series, it doesn't matter whether you choose clustered or stacked. In fact, I'd intended to write clustered. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ wlotto wrote: Data is like this. Some dates are skipped. One day or sometimes one week (Real data covers for a couple of years.) What I have to do is: 1. Close - left Y. Volume - right Y. 2. Beginning date and ending date have to be shown exactly at the ends of X axis. 3. Skipped dates cannot have spaced. Has to be shown continuously. 4. Change the background (only plot area) to two color gradient. *BTW, why stacked column? Not a clustered column? Date Volume Close 3/2/04 14044549 98.24 3/3/04 13637071 107.55 3/5/04 10899971 98.30 3/6/04 9771689 97.16 3/7/04 11143407 96.02 3/9/04 9886843 93.74 3/10/04 8758561 117.6033333 3/11/04 10130279 116.4633333 3/12/04 9001997 115.3233333 3/14/04 6745433 113.0433333 -----Original Message----- I'm going to start over. In your first post, you said you want a chart with volume as columns and price as XY. This will also work with the price on a Line chart. If the data is arranged like this, it will be easy to make the chart: Date Volume Close 3/2/2004 14044549 98.24 3/3/2004 13637071 107.55 3/4/2004 11787985 95.96 1. Select the data and make either a Line or a Scatter chart. 2. Right click on the Volume series, select Chart Type from the pop up menu, and choose the Stacked Column option. 3. (see NOTE below) Double click on the X axis, Scale tab, check Value Axis Crosses at Maximum Category. 4. Double click on the Price series, click on the Axis tab, and choose Secondary. NOTE: If you make the Volume series the secondary series, it will make formatting the X axis more difficult. If you want volume on the right axis, and price on the left, follow all steps. If you want volume on the left and price on the right, skip step 3. If you follow these steps, whether you choose Line or Scatter for your Price chart type, the chart will be substantially the same. Don't let the name of the chart type fool you. Either type series can be formatted to look the same: with or without markers, with or without connecting lines. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ wlotto wrote: 'Category' of Value(X)Axis (Primary axis) is dimmed (both Automatic and Timescale are also dimmed). I can select Category for Secondary Axis (Category(X) axis). When I select this, every problem happens. Why selections for Value(X) axis are dimmed? thanks. -----Original Message----- I'm not really sure I understand what you're doing. If you have time based data with gaps, and you don't want the gaps to show, you can change to a category axis. Right click on the chart, choose Chart Options from the pop up menu, and on the Axes tab, change the X Axis from Automatic or Time Scale to Category. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ wlotto wrote: It did not work. There may be some trick with it but I couldn't find it either. OK let me explain a bit more. Category data is one year date, yet, it is not 365 days. Some skipps here and there throughout, therefore, there is a space among columns accordingly. I am trying to fill the space out by creating secondary X axis. (I hope I am doing right.) By doing so, I can successfully fill the space by making a bit of change (making weight thiker, etc.) But I don't know how to make the area (that is a plot area without secondary X axis) gradient. Thanks. -----Original Message----- Richard - An off-topic word to the wise. I know it's only a hotmail account, but if you don't do something to disguise it, you'll be fighting off mountains of spam in your inbox. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ wrote: right click on the 'volume' data series and select 'format data series'. On the 'Patterns' tab, click 'fill effects'. from there you can pick your gradient colours. Richard -----Original Message----- I created a typical price&volume graph. Price/XY, Volume/Column. I put Secondary Category (X), Category in order not to break columns. Q. How could I change Volume's color (on Secondary X axis) to gradient (two colors)? . . . . . |
#9
|
|||
|
|||
P & V chart: Secondary X axis gradient (again)
Yes, it eliminated skipps. However, as I said, dates get
screwed up at the same time!! I need to show the first and last dates on the ends on X axis. (eg. if the data spans like 3/14/00-2/12/04, the left most has to be 3/14/00. the right most has to be 2/12/04! When I change from Auto to Category, the ending date changes to, say, 1/20/04 or the best was 2/11/04. Isn't any way with XY chart instead of Line? -----Original Message----- 1. If you use a line chart, you can eliminate the skips by changing to a category axis. Right click on the chart, choose Chart Options from the pop up menu, and on the Axes tab, change the X Axis from Automatic or Time Scale to Category. 2. A category axis will only go as far as the data goes. Does your X data extend down further in the worksheet than your Y data? 3. Double click the series, and on the Patterns tab, set the line style to None. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ wlotto wrote: It worked almost all but, 1. The data is covering over two years and does not have data point every single day. It skipps like 1, 4, or 7... days, or sometimes no skipps at all. Skipped dates come out to be space on columns. (eg. 500 data points in two years.) 2. When I change 'Chart Optionselect Category' in order to fill the space between columns, the ending date changes to some obscure date, not the last date of data. I don't know how to scale it. Scale tub shows no longer usual format. 2. Any other way with XY schatters start, not with two lines? thanks. -----Original Message----- Here's how to deal with your constraints. #1 was covered in the NOTE under my previous process. #2,3: Make the initial chart as a Line chart. Double click the X axis, and uncheck the Value Axis Crosses Between Categories box, to put the end dates on the extreme ends of the axis. #4: Double click on the plot area, between plotted columns and markers. Click the Fill Effects button under the Area color palette. On the Gradient tab, select the options that produce the gradient you desire. And with only one column series, it doesn't matter whether you choose clustered or stacked. In fact, I'd intended to write clustered. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ wlotto wrote: Data is like this. Some dates are skipped. One day or sometimes one week (Real data covers for a couple of years.) What I have to do is: 1. Close - left Y. Volume - right Y. 2. Beginning date and ending date have to be shown exactly at the ends of X axis. 3. Skipped dates cannot have spaced. Has to be shown continuously. 4. Change the background (only plot area) to two color gradient. *BTW, why stacked column? Not a clustered column? Date Volume Close 3/2/04 14044549 98.24 3/3/04 13637071 107.55 3/5/04 10899971 98.30 3/6/04 9771689 97.16 3/7/04 11143407 96.02 3/9/04 9886843 93.74 3/10/04 8758561 117.6033333 3/11/04 10130279 116.4633333 3/12/04 9001997 115.3233333 3/14/04 6745433 113.0433333 -----Original Message----- I'm going to start over. In your first post, you said you want a chart with volume as columns and price as XY. This will also work with the price on a Line chart. If the data is arranged like this, it will be easy to make the chart: Date Volume Close 3/2/2004 14044549 98.24 3/3/2004 13637071 107.55 3/4/2004 11787985 95.96 1. Select the data and make either a Line or a Scatter chart. 2. Right click on the Volume series, select Chart Type from the pop up menu, and choose the Stacked Column option. 3. (see NOTE below) Double click on the X axis, Scale tab, check Value Axis Crosses at Maximum Category. 4. Double click on the Price series, click on the Axis tab, and choose Secondary. NOTE: If you make the Volume series the secondary series, it will make formatting the X axis more difficult. If you want volume on the right axis, and price on the left, follow all steps. If you want volume on the left and price on the right, skip step 3. If you follow these steps, whether you choose Line or Scatter for your Price chart type, the chart will be substantially the same. Don't let the name of the chart type fool you. Either type series can be formatted to look the same: with or without markers, with or without connecting lines. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ wlotto wrote: 'Category' of Value(X)Axis (Primary axis) is dimmed (both Automatic and Timescale are also dimmed). I can select Category for Secondary Axis (Category (X) axis). When I select this, every problem happens. Why selections for Value(X) axis are dimmed? thanks. -----Original Message----- I'm not really sure I understand what you're doing. If you have time based data with gaps, and you don't want the gaps to show, you can change to a category axis. Right click on the chart, choose Chart Options from the pop up menu, and on the Axes tab, change the X Axis from Automatic or Time Scale to Category. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ wlotto wrote: It did not work. There may be some trick with it but I couldn't find it either. OK let me explain a bit more. Category data is one year date, yet, it is not 365 days. Some skipps here and there throughout, therefore, there is a space among columns accordingly. I am trying to fill the space out by creating secondary X axis. (I hope I am doing right.) By doing so, I can successfully fill the space by making a bit of change (making weight thiker, etc.) But I don't know how to make the area (that is a plot area without secondary X axis) gradient. Thanks. -----Original Message----- Richard - An off-topic word to the wise. I know it's only a hotmail account, but if you don't do something to disguise it, you'll be fighting off mountains of spam in your inbox. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ wrote: right click on the 'volume' data series and select 'format data series'. On the 'Patterns' tab, click 'fill effects'. from there you can pick your gradient colours. Richard -----Original Message----- I created a typical price&volume graph. Price/XY, Volume/Column. I put Secondary Category (X), Category in order not to break columns. Q. How could I change Volume's color (on Secondary X axis) to gradient (two colors)? . . . . . . |
#10
|
|||
|
|||
P & V chart: Secondary X axis gradient (again)
If you have a lot of points, Excel will only give you some of the
labels. If it skips every second point, you have a 50% chance that your desired last value will appear (1,2,3,4,5 becomes 1,3,5). Even in an XY scatter chart, you can coerce Excel to starting and stopping an axis where you want, but if the ending date falls between major ticks, it will not be shown. You could hide the regular axis labels, and add your own labels, using the Arbitrary Axis Scale technique from my web site: http://peltiertech.com/Excel/Charts/ArbitraryAxis.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ wlotto wrote: Yes, it eliminated skipps. However, as I said, dates get screwed up at the same time!! I need to show the first and last dates on the ends on X axis. (eg. if the data spans like 3/14/00-2/12/04, the left most has to be 3/14/00. the right most has to be 2/12/04! When I change from Auto to Category, the ending date changes to, say, 1/20/04 or the best was 2/11/04. Isn't any way with XY chart instead of Line? -----Original Message----- 1. If you use a line chart, you can eliminate the skips by changing to a category axis. Right click on the chart, choose Chart Options from the pop up menu, and on the Axes tab, change the X Axis from Automatic or Time Scale to Category. 2. A category axis will only go as far as the data goes. Does your X data extend down further in the worksheet than your Y data? 3. Double click the series, and on the Patterns tab, set the line style to None. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ wlotto wrote: It worked almost all but, 1. The data is covering over two years and does not have data point every single day. It skipps like 1, 4, or 7... days, or sometimes no skipps at all. Skipped dates come out to be space on columns. (eg. 500 data points in two years.) 2. When I change 'Chart Optionselect Category' in order to fill the space between columns, the ending date changes to some obscure date, not the last date of data. I don't know how to scale it. Scale tub shows no longer usual format. 2. Any other way with XY schatters start, not with two lines? thanks. -----Original Message----- Here's how to deal with your constraints. #1 was covered in the NOTE under my previous process. #2,3: Make the initial chart as a Line chart. Double click the X axis, and uncheck the Value Axis Crosses Between Categories box, to put the end dates on the extreme ends of the axis. #4: Double click on the plot area, between plotted columns and markers. Click the Fill Effects button under the Area color palette. On the Gradient tab, select the options that produce the gradient you desire. And with only one column series, it doesn't matter whether you choose clustered or stacked. In fact, I'd intended to write clustered. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ wlotto wrote: Data is like this. Some dates are skipped. One day or sometimes one week (Real data covers for a couple of years.) What I have to do is: 1. Close - left Y. Volume - right Y. 2. Beginning date and ending date have to be shown exactly at the ends of X axis. 3. Skipped dates cannot have spaced. Has to be shown continuously. 4. Change the background (only plot area) to two color gradient. *BTW, why stacked column? Not a clustered column? Date Volume Close 3/2/04 14044549 98.24 3/3/04 13637071 107.55 3/5/04 10899971 98.30 3/6/04 9771689 97.16 3/7/04 11143407 96.02 3/9/04 9886843 93.74 3/10/04 8758561 117.6033333 3/11/04 10130279 116.4633333 3/12/04 9001997 115.3233333 3/14/04 6745433 113.0433333 -----Original Message----- I'm going to start over. In your first post, you said you want a chart with volume as columns and price as XY. This will also work with the price on a Line chart. If the data is arranged like this, it will be easy to make the chart: Date Volume Close 3/2/2004 14044549 98.24 3/3/2004 13637071 107.55 3/4/2004 11787985 95.96 1. Select the data and make either a Line or a Scatter chart. 2. Right click on the Volume series, select Chart Type from the pop up menu, and choose the Stacked Column option. 3. (see NOTE below) Double click on the X axis, Scale tab, check Value Axis Crosses at Maximum Category. 4. Double click on the Price series, click on the Axis tab, and choose Secondary. NOTE: If you make the Volume series the secondary series, it will make formatting the X axis more difficult. If you want volume on the right axis, and price on the left, follow all steps. If you want volume on the left and price on the right, skip step 3. If you follow these steps, whether you choose Line or Scatter for your Price chart type, the chart will be substantially the same. Don't let the name of the chart type fool you. Either type series can be formatted to look the same: with or without markers, with or without connecting lines. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ wlotto wrote: 'Category' of Value(X)Axis (Primary axis) is dimmed (both Automatic and Timescale are also dimmed). I can select Category for Secondary Axis (Category (X) axis). When I select this, every problem happens. Why selections for Value(X) axis are dimmed? thanks. -----Original Message----- I'm not really sure I understand what you're doing. If you have time based data with gaps, and you don't want the gaps to show, you can change to a category axis. Right click on the chart, choose Chart Options from the pop up menu, and on the Axes tab, change the X Axis from Automatic or Time Scale to Category. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ wlotto wrote: It did not work. There may be some trick with it but I couldn't find it either. OK let me explain a bit more. Category data is one year date, yet, it is not 365 days. Some skipps here and there throughout, therefore, there is a space among columns accordingly. I am trying to fill the space out by creating secondary X axis. (I hope I am doing right.) By doing so, I can successfully fill the space by making a bit of change (making weight thiker, etc.) But I don't know how to make the area (that is a plot area without secondary X axis) gradient. Thanks. -----Original Message----- Richard - An off-topic word to the wise. I know it's only a hotmail account, but if you don't do something to disguise it, you'll be fighting off mountains of spam in your inbox. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ wrote: right click on the 'volume' data series and select 'format data series'. On the 'Patterns' tab, click 'fill effects'. from there you can pick your gradient colours. Richard -----Original Message----- I created a typical price&volume graph. Price/XY, Volume/Column. I put Secondary Category (X), Category in order not to break columns. Q. How could I change Volume's color (on Secondary X axis) to gradient (two colors)? . . . . . . |
Thread Tools | |
Display Modes | |
|
|