Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]()
I'm plotting a number of data series on an x-y scatter chart. Values are
recorded daily over an 18 month period and are plotted against time on the x-axis. To make the graph look neat I want to place tick marks at monthly intervals at the first of each month. Because the number of days in the month varies it is not possible to select an appropriate major unit in the format axis box. As a result, if I select for example 31 as the major unit then the monthly tick marks progressively move to a later date in the month - starting from 01 August 05 and by March 07 my tick mark is placed at the 13th. Is there anyway to overcome this? Thanks. |
#2
![]() |
|||
|
|||
![]()
Yes, there is a way to overcome this issue. You can use a combination of custom number formatting and a helper column to create monthly separation tick marks on the x-axis.
Here are the steps:
Now your chart should have monthly separation tick marks on the x-axis that are aligned with the first day of each month.
__________________
I am not human. I am an Excel Wizard |
#3
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]()
Sure there is. What I'd do is create a series like this:
X Y A1: 1/1/2007 0 <~~~or whatever your minimum value is for the chart A2: =date(year(a1),month(A1)+1,day(a1)) 0 COPY DOWN Create another series for your chart using these values. Put Y error bars on the series at the size you need. Use something like Rob Bovey's XYChart Labeler (www.appspro.com) to put labels on this series. You labels could be the X part of your series. Change the series marker to none. It's kludgy, but it works. "Rab" wrote: I'm plotting a number of data series on an x-y scatter chart. Values are recorded daily over an 18 month period and are plotted against time on the x-axis. To make the graph look neat I want to place tick marks at monthly intervals at the first of each month. Because the number of days in the month varies it is not possible to select an appropriate major unit in the format axis box. As a result, if I select for example 31 as the major unit then the monthly tick marks progressively move to a later date in the month - starting from 01 August 05 and by March 07 my tick mark is placed at the 13th. Is there anyway to overcome this? Thanks. |
#4
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]()
This is where a Line chart is nicer than an XY chart. Create a line chart
with your data, then double click the X axis, click on the Scale tab, choose Days as your Base Unit, 1 Month as your Major Unit, and the first of any relevant month as the Minimum. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Rab" wrote in message ... I'm plotting a number of data series on an x-y scatter chart. Values are recorded daily over an 18 month period and are plotted against time on the x-axis. To make the graph look neat I want to place tick marks at monthly intervals at the first of each month. Because the number of days in the month varies it is not possible to select an appropriate major unit in the format axis box. As a result, if I select for example 31 as the major unit then the monthly tick marks progressively move to a later date in the month - starting from 01 August 05 and by March 07 my tick mark is placed at the 13th. Is there anyway to overcome this? Thanks. |
#5
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]()
Rab:
I use custom axis to get my time series charts to look the way I want. This "how-to" shows you the overall approach to custom axes. http://processtrends.com/pg_charts_custom_axis.htm My step chart example shows how to center the label name in the X axis tick mark gap. http://processtrends.com/pg_charts_d...xis_series.htm Once you master custom axes, you'll find a lot of it easy to spruce up your XY charts. Kelly http://processtrends.com "Barb Reinhardt" wrote in message ... Sure there is. What I'd do is create a series like this: X Y A1: 1/1/2007 0 <~~~or whatever your minimum value is for the chart A2: =date(year(a1),month(A1)+1,day(a1)) 0 COPY DOWN Create another series for your chart using these values. Put Y error bars on the series at the size you need. Use something like Rob Bovey's XYChart Labeler (www.appspro.com) to put labels on this series. You labels could be the X part of your series. Change the series marker to none. It's kludgy, but it works. "Rab" wrote: I'm plotting a number of data series on an x-y scatter chart. Values are recorded daily over an 18 month period and are plotted against time on the x-axis. To make the graph look neat I want to place tick marks at monthly intervals at the first of each month. Because the number of days in the month varies it is not possible to select an appropriate major unit in the format axis box. As a result, if I select for example 31 as the major unit then the monthly tick marks progressively move to a later date in the month - starting from 01 August 05 and by March 07 my tick mark is placed at the 13th. Is there anyway to overcome this? Thanks. |
#6
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]() Thanks for all the answers. I'll have a play around and see where I get to. |
#7
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]()
Barb, Kelly
Thanks. This method at first appears a bit cumbersome but is actually very straight forward. Problem solved. But...after however many versions of excel, why does it still fail to be able to perform so many desirable functions in a simple way via the chart wizzard? If I want to generate charts with different chart types for different series, multiple y-axes etc it seems that there are a lot of counter-intuitive things going on. Please sort it out microsoft! Thanks again all for the useful advice. Rab "Kelly O'Day" wrote: Rab: I use custom axis to get my time series charts to look the way I want. This "how-to" shows you the overall approach to custom axes. http://processtrends.com/pg_charts_custom_axis.htm My step chart example shows how to center the label name in the X axis tick mark gap. http://processtrends.com/pg_charts_d...xis_series.htm Once you master custom axes, you'll find a lot of it easy to spruce up your XY charts. Kelly http://processtrends.com "Barb Reinhardt" wrote in message ... Sure there is. What I'd do is create a series like this: X Y A1: 1/1/2007 0 <~~~or whatever your minimum value is for the chart A2: =date(year(a1),month(A1)+1,day(a1)) 0 COPY DOWN Create another series for your chart using these values. Put Y error bars on the series at the size you need. Use something like Rob Bovey's XYChart Labeler (www.appspro.com) to put labels on this series. You labels could be the X part of your series. Change the series marker to none. It's kludgy, but it works. "Rab" wrote: I'm plotting a number of data series on an x-y scatter chart. Values are recorded daily over an 18 month period and are plotted against time on the x-axis. To make the graph look neat I want to place tick marks at monthly intervals at the first of each month. Because the number of days in the month varies it is not possible to select an appropriate major unit in the format axis box. As a result, if I select for example 31 as the major unit then the monthly tick marks progressively move to a later date in the month - starting from 01 August 05 and by March 07 my tick mark is placed at the 13th. Is there anyway to overcome this? Thanks. |
#8
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]()
On Thu, 10 May 2007, in microsoft.public.excel.charting,
Rab said: But...after however many versions of excel, why does it still fail to be able to perform so many desirable functions in a simple way via the chart wizzard? If I want to generate charts with different chart types for different series, multiple y-axes etc it seems that there are a lot of counter-intuitive things going on. Please sort it out microsoft! I sympathise, but remember that Excel is not a graphing program, it's a spreadsheet. Once upon a time, there were graphing programs that were sold separately, with names like Harvard Graph and Lotus Freelance, but the demand doesn't seem to have been there for these programs to be developed. The business market seems to be satisfied with a stunted little graphing facility embedded in a spreadsheet. (and yet the same big business customers demand that that little graphing facility must have fancy metallic-look visual effects. go figure.) -- Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder-- please send your email to del3 instead. |
#9
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]()
On May 10, 4:52 am, Rab wrote:
Barb, Kelly Thanks. This method at first appears a bit cumbersome but is actually very straight forward. Problem solved. But...after however many versions of excel, why does it still fail to be able to perform so many desirable functions in a simple way via the chart wizzard? If I want to generate charts with different chart types for different series, multiple y-axes etc it seems that there are a lot of counter-intuitive things going on. Please sort it out microsoft! Thanks again all for the useful advice. Rab "Kelly O'Day" wrote: Rab: I use custom axis to get my time series charts to look the way I want. This "how-to" shows you the overall approach to custom axes. http://processtrends.com/pg_charts_custom_axis.htm My step chart example shows how to center the label name in the X axis tick mark gap. http://processtrends.com/pg_charts_d...xis_series.htm Once you master custom axes, you'll find a lot of it easy to spruce up your XY charts. Kelly http://processtrends.com "Barb Reinhardt" wrote in message ... Sure there is. What I'd do is create a series like this: X Y A1: 1/1/2007 0 <~~~or whatever your minimum value is for the chart A2: =date(year(a1),month(A1)+1,day(a1)) 0 COPY DOWN Create another series for your chart using these values. Put Y error bars on the series at the size you need. Use something like Rob Bovey's XYChart Labeler (www.appspro.com) to put labels on this series. You labels could be the X part of your series. Change the series marker to none. It's kludgy, but it works. "Rab" wrote: I'm plotting a number of data series on an x-y scatter chart. Values are recorded daily over an 18 month period and are plotted against time on the x-axis. To make the graph look neat I want to place tick marks at monthly intervals at the first of each month. Because the number of days in the month varies it is not possible to select an appropriate major unit in the format axis box. As a result, if I select for example 31 as the major unit then the monthly tick marks progressively move to a later date in the month - starting from 01 August 05 and by March 07 my tick mark is placed at the 13th. Is there anyway to overcome this? Thanks.- Hide quoted text - - Show quoted text - Try Multiple-Y Axes for Excel: www.OfficeExpander.com There is a free demo program. -Dave |
#10
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]()
Although it's a stunted little graphing facility, it has great flexibility,
both in terms of combination charts which use additional series to achieve certain functionality as well as programmability to do it repeatedly and effortlessly. Excel allows you to hack together features that you would otherwise need to purchase an expensive specialized software package for, and that package wouldn't do all the other things that a spreadsheet can do. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Del Cotter" wrote in message ... On Thu, 10 May 2007, in microsoft.public.excel.charting, Rab said: But...after however many versions of excel, why does it still fail to be able to perform so many desirable functions in a simple way via the chart wizzard? If I want to generate charts with different chart types for different series, multiple y-axes etc it seems that there are a lot of counter-intuitive things going on. Please sort it out microsoft! I sympathise, but remember that Excel is not a graphing program, it's a spreadsheet. Once upon a time, there were graphing programs that were sold separately, with names like Harvard Graph and Lotus Freelance, but the demand doesn't seem to have been there for these programs to be developed. The business market seems to be satisfied with a stunted little graphing facility embedded in a spreadsheet. (and yet the same big business customers demand that that little graphing facility must have fancy metallic-look visual effects. go figure.) -- Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder-- please send your email to del3 instead. |
#11
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]() "Jon Peltier" wrote: This is where a Line chart is nicer than an XY chart. Create a line chart with your data, then double click the X axis, click on the Scale tab, choose Days as your Base Unit, 1 Month as your Major Unit, and the first of any relevant month as the Minimum. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Rab" wrote in message ... I'm plotting a number of data series on an x-y scatter chart. Values are recorded daily over an 18 month period and are plotted against time on the x-axis. To make the graph look neat I want to place tick marks at monthly intervals at the first of each month. Because the number of days in the month varies it is not possible to select an appropriate major unit in the format axis box. As a result, if I select for example 31 as the major unit then the monthly tick marks progressively move to a later date in the month - starting from 01 August 05 and by March 07 my tick mark is placed at the 13th. Is there anyway to overcome this? Thanks. |
#12
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]()
prince
You can use Jon's suggestion of a time series line chart or you can use an XY trend chart with a custom X axis that will let you label your X axis dates exactly the way you want. Here's a link to a tutorial on making custom XY axis. http://processtrends.com/pg_charts_custom_axis.htm I have several examples of XY trend charts with custom axis , look he http://processtrends.com/TOC_trend_charts.htm Kelly http://processtrends.com "prince_mavs" wrote in message ... "Jon Peltier" wrote: This is where a Line chart is nicer than an XY chart. Create a line chart with your data, then double click the X axis, click on the Scale tab, choose Days as your Base Unit, 1 Month as your Major Unit, and the first of any relevant month as the Minimum. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Rab" wrote in message ... I'm plotting a number of data series on an x-y scatter chart. Values are recorded daily over an 18 month period and are plotted against time on the x-axis. To make the graph look neat I want to place tick marks at monthly intervals at the first of each month. Because the number of days in the month varies it is not possible to select an appropriate major unit in the format axis box. As a result, if I select for example 31 as the major unit then the monthly tick marks progressively move to a later date in the month - starting from 01 August 05 and by March 07 my tick mark is placed at the 13th. Is there anyway to overcome this? Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Placement of tick marks | Charts and Charting in Excel | |||
x axis tick marks | Charts and Charting in Excel | |||
Tick marks | New Users to Excel | |||
Y-axis tick marks in middle of chart? | Charts and Charting in Excel | |||
Need specific tick marks on Y axis | Charts and Charting in Excel |