Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Axis with 2 labels
Hi
I am doing a line chart and would like two labels for the x axis accompanying the same data point. The problem I am having is the formatting of the labels. I would like to fit them in both with the same orientation (vertical). But only one label goes vertical when I do this. There is a column for one label (date), and another for ID (which is a #). thanks |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Axis with 2 labels
On Tue, 1 May 2007, in microsoft.public.excel.charting,
BillO said: I am doing a line chart and would like two labels for the x axis accompanying the same data point. The problem I am having is the formatting of the labels. I would like to fit them in both with the same orientation (vertical). But only one label goes vertical when I do this. There is a column for one label (date), and another for ID (which is a #). Yes, that's a problem with having two labels on the X axis. Are you prepared to go radical and abandon the Microsoft-provided axis facility? If so, you can create your own customised X axis with all the properties you want, by creating a data series, labelling it, and formatting it to look like an axis. For two labels you either want a specially-formatted label, or more likely two series. Have a look at the "Arbitrary axis" examples on Jon Peltier's "Chart Axes and Axis Tricks" page: http://peltiertech.com/Excel/Charts/axes.html#ArbAxis Tushar Mehta's "Flexible log scale" tutorial is about Y axes, but the principles are the same, and it may help you if you get stuck. http://www.tushar-mehta.com/excel/ne...ble_log_scale/ -- 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. |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Axis with 2 labels
Another option would be to concatenate your ID numbers and dates together
into one X-Axis range as opposed to two. For example, given your ID numbers in column A and your dates in column B, add a formula similar to the one below into column C: =B1&" | "&TEXT(A1,"dd/mm/yy") Then reference your X-Axis off of column C. -- John Mansfield http://cellmatrix.net "BillO" wrote: Hi I am doing a line chart and would like two labels for the x axis accompanying the same data point. The problem I am having is the formatting of the labels. I would like to fit them in both with the same orientation (vertical). But only one label goes vertical when I do this. There is a column for one label (date), and another for ID (which is a #). thanks |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Axis with 2 labels
BillO:
Del's arbitrary axis suggestion is a good one. Here a link to my how-to tutorial on custom axes. http://processtrends.com/pg_charts_custom_axis.htm. I have a two label y axis example at this link. http://processtrends.com/toc_chart_d..._Axis_ Labels While it takes a little patience to master custom axes, once you do you can start making Excel do things you never thought possible. Kelly http://processtrends.com "Del Cotter" wrote in message ... On Tue, 1 May 2007, in microsoft.public.excel.charting, BillO said: I am doing a line chart and would like two labels for the x axis accompanying the same data point. The problem I am having is the formatting of the labels. I would like to fit them in both with the same orientation (vertical). But only one label goes vertical when I do this. There is a column for one label (date), and another for ID (which is a #). Yes, that's a problem with having two labels on the X axis. Are you prepared to go radical and abandon the Microsoft-provided axis facility? If so, you can create your own customised X axis with all the properties you want, by creating a data series, labelling it, and formatting it to look like an axis. For two labels you either want a specially-formatted label, or more likely two series. Have a look at the "Arbitrary axis" examples on Jon Peltier's "Chart Axes and Axis Tricks" page: http://peltiertech.com/Excel/Charts/axes.html#ArbAxis Tushar Mehta's "Flexible log scale" tutorial is about Y axes, but the principles are the same, and it may help you if you get stuck. http://www.tushar-mehta.com/excel/ne...ble_log_scale/ -- 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. |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
Axis with 2 labels
On Tue, 1 May 2007, in microsoft.public.excel.charting,
Kelly O'Day said: While it takes a little patience to master custom axes, once you do you can start making Excel do things you never thought possible. If I was designing a charting module for spreadsheets today, I would make axes be just another kind of data series, with marker types available for correct appearance and a chart wizard for worry-free setup by non-expert users. This actually mirrors the way I was taught graphing as a child at school: the "x-axis" was always referred as the line obeying the equation y=0, and the "y axis" was always called x=0. The idea was to encourage children to understand that there is nothing special or magic about those parts of graph space, and especially nothing particularly magic about the origin point (0,0). (although it mainly confused and annoyed me at the time because I got the language mixed up: was x=0 the x axis? :-) I have had colleagues freak out when they see me do axes that aren't on the zero line, especially when I do the custom axis trick of not having the two axes joined at one corner, but each floating free with a gap of white space. They understand better when I call them "scale bars" and compare them to the scale bars on a map, with the graph space analogous to a map area. -- 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. |
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
Axis with 2 labels
Del -
I guess my own idea of "axes" intuitively incorporates your "scale bars", which is why I've been able to easily incorporate all kinds of axis effects in my work. (Also I learned computer charting on an HP plotter, using HPGL commands to move a pen around the page. I had to do all my own determinations of scales, tick and label location, etc.) Other people aren't so intuitive with this, which is why it sometimes takes an hour or a dozen emails to get the idea across. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Del Cotter" wrote in message ... On Tue, 1 May 2007, in microsoft.public.excel.charting, Kelly O'Day said: While it takes a little patience to master custom axes, once you do you can start making Excel do things you never thought possible. If I was designing a charting module for spreadsheets today, I would make axes be just another kind of data series, with marker types available for correct appearance and a chart wizard for worry-free setup by non-expert users. This actually mirrors the way I was taught graphing as a child at school: the "x-axis" was always referred as the line obeying the equation y=0, and the "y axis" was always called x=0. The idea was to encourage children to understand that there is nothing special or magic about those parts of graph space, and especially nothing particularly magic about the origin point (0,0). (although it mainly confused and annoyed me at the time because I got the language mixed up: was x=0 the x axis? :-) I have had colleagues freak out when they see me do axes that aren't on the zero line, especially when I do the custom axis trick of not having the two axes joined at one corner, but each floating free with a gap of white space. They understand better when I call them "scale bars" and compare them to the scale bars on a map, with the graph space analogous to a map area. -- 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. |
#7
Posted to microsoft.public.excel.charting
|
|||
|
|||
Axis with 2 labels
Del:
We think alike. If I were redoing Excel's charting tool, I'd drop the XY (Scatter) Chart - Line Chart terminology. To me, all 2 D charts are XY charts; scatter and line charts are just special forms of XY charts. How many questions show up on the Chart forum because users want to have a "line" chart with numeric values for X and Y. ....Kelly "Del Cotter" wrote in message ... On Tue, 1 May 2007, in microsoft.public.excel.charting, Kelly O'Day said: While it takes a little patience to master custom axes, once you do you can start making Excel do things you never thought possible. If I was designing a charting module for spreadsheets today, I would make axes be just another kind of data series, with marker types available for correct appearance and a chart wizard for worry-free setup by non-expert users. This actually mirrors the way I was taught graphing as a child at school: the "x-axis" was always referred as the line obeying the equation y=0, and the "y axis" was always called x=0. The idea was to encourage children to understand that there is nothing special or magic about those parts of graph space, and especially nothing particularly magic about the origin point (0,0). (although it mainly confused and annoyed me at the time because I got the language mixed up: was x=0 the x axis? :-) I have had colleagues freak out when they see me do axes that aren't on the zero line, especially when I do the custom axis trick of not having the two axes joined at one corner, but each floating free with a gap of white space. They understand better when I call them "scale bars" and compare them to the scale bars on a map, with the graph space analogous to a map area. -- 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. |
#8
Posted to microsoft.public.excel.charting
|
|||
|
|||
Axis with 2 labels
I've made this suggestion, along with a more powerful bunch of OM around the
Axis object, but it would entail too large a revision to the charting engine. Unfortunately 2007 has gone in the other direction. I have a tutorial on my web site showing how to use line and XY series in conjunction to get a nice stock chart with ticks for open and close instead of the candlestick: http://peltiertech.com/Excel/ChartsH...artTricks.html This technique works nicely in Excel versions 2003, 2002, 2000, 97, and probably earlier. However, it falls down in 2007, because an XY series cannot coexist with a line series on the same axis. You would have to use the secondary axes for the XY chart, which makes it unavailable for other effects you could add to such a chart. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Kelly O'Day" wrote in message ... Del: We think alike. If I were redoing Excel's charting tool, I'd drop the XY (Scatter) Chart - Line Chart terminology. To me, all 2 D charts are XY charts; scatter and line charts are just special forms of XY charts. How many questions show up on the Chart forum because users want to have a "line" chart with numeric values for X and Y. ...Kelly "Del Cotter" wrote in message ... On Tue, 1 May 2007, in microsoft.public.excel.charting, Kelly O'Day said: While it takes a little patience to master custom axes, once you do you can start making Excel do things you never thought possible. If I was designing a charting module for spreadsheets today, I would make axes be just another kind of data series, with marker types available for correct appearance and a chart wizard for worry-free setup by non-expert users. This actually mirrors the way I was taught graphing as a child at school: the "x-axis" was always referred as the line obeying the equation y=0, and the "y axis" was always called x=0. The idea was to encourage children to understand that there is nothing special or magic about those parts of graph space, and especially nothing particularly magic about the origin point (0,0). (although it mainly confused and annoyed me at the time because I got the language mixed up: was x=0 the x axis? :-) I have had colleagues freak out when they see me do axes that aren't on the zero line, especially when I do the custom axis trick of not having the two axes joined at one corner, but each floating free with a gap of white space. They understand better when I call them "scale bars" and compare them to the scale bars on a map, with the graph space analogous to a map area. -- 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
|
|||
|
|||
Re-imagining charting software (was Axis with 2 labels)
On Wed, 2 May 2007, in microsoft.public.excel.charting,
Kelly O'Day said: If I were redoing Excel's charting tool, I'd drop the XY (Scatter) Chart - Line Chart terminology. To me, all 2 D charts are XY charts; scatter and line charts are just special forms of XY charts. How many questions show up on the Chart forum because users want to have a "line" chart with numeric values for X and Y. Yes, there is an important difference between the Scatter chart and the Line chart, but it's a difference whose value should not be held in the "Chart type" field, but in the "Axis type" field. The Format Axis dialogue box should offer the types "Nominal", "Ordinal", and "Interval" as named by S. S. Stevens in the 1940s. "Nominal" is like Excel's "Category" "Ordinal" is like Excel's "Time-scale" (and therein lies an interesting bug/feature/property that's the key to a favourite trick of mine for quick and dirty step charts) "Interval" is the scale type Excel calls "Category" when the chart type is Scatter. But the chart type is an inappropriate way of controlling the difference, a bad early design choice by Microsoft that's now frozen in. -- 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. |
#10
Posted to microsoft.public.excel.charting
|
|||
|
|||
Re-imagining charting software (was Axis with 2 labels)
On Wed, 2 May 2007, in microsoft.public.excel.charting,
Del Cotter said: as named by S. S. Stevens in the 1940s. "Nominal" is like Excel's "Category" "Ordinal" is like Excel's "Time-scale" "Interval" is the scale type Excel calls "Category" when the chart type is Scatter. Oh, and I forgot to note that in Excel today, the Y axis is always an interval scale; nominal and ordinal are not offered at all. Which leads to all those kludges involving horizontal bar chart combinations or custom-labelled dummy series, to provide labels up the left side. In my dream Turbo Excel Charts, the X axis would offer N, O, and I types, *and so would the Y*. P.S. But like I said, in my dream software there would be no axes really, in the sense of there being something different from data series. P.P.S. Any minute now, some smart alec is going to recommend R :-) -- 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
|
|||
|
|||
Re-imagining charting software (was Axis with 2 labels)
(and therein lies an interesting bug/feature/property that's the key to a
favourite trick of mine for quick and dirty step charts) Ooh, tell us more! - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Del Cotter" wrote in message ... On Wed, 2 May 2007, in microsoft.public.excel.charting, Kelly O'Day said: If I were redoing Excel's charting tool, I'd drop the XY (Scatter) Chart - Line Chart terminology. To me, all 2 D charts are XY charts; scatter and line charts are just special forms of XY charts. How many questions show up on the Chart forum because users want to have a "line" chart with numeric values for X and Y. Yes, there is an important difference between the Scatter chart and the Line chart, but it's a difference whose value should not be held in the "Chart type" field, but in the "Axis type" field. The Format Axis dialogue box should offer the types "Nominal", "Ordinal", and "Interval" as named by S. S. Stevens in the 1940s. "Nominal" is like Excel's "Category" "Ordinal" is like Excel's "Time-scale" (and therein lies an interesting bug/feature/property that's the key to a favourite trick of mine for quick and dirty step charts) "Interval" is the scale type Excel calls "Category" when the chart type is Scatter. But the chart type is an inappropriate way of controlling the difference, a bad early design choice by Microsoft that's now frozen in. -- 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. |
#12
Posted to microsoft.public.excel.charting
|
|||
|
|||
Del's Quick & Dirty Step Chart (was Re-imagining charting software)
On Wed, 2 May 2007, in microsoft.public.excel.charting,
Jon Peltier said: "Del Cotter" wrote "Ordinal" is like Excel's "Time-scale" (and therein lies an interesting bug/feature/property that's the key to a favourite trick of mine for quick and dirty step charts) Ooh, tell us more! (This works for me using Excel 97 under Windows XP) Consider the following range Series1 1 4 2 2 3 8 4 4 5 7 6 9 7 5 8 6 Where the label "Series1" occupies the cell B2, the X range is A2..A9 and the Y range is B2..B9. If you quickly use the Chart Wizard to create a scatter chart with lines and markers, the Source Data will look like this: X Values: =Sheet1!$A$2:$A$9 Y Values: =Sheet1!$B$2:$B$9 So far, nothing surprising. Now if you go in and manually edit the range (first remembering that F2 key!) so it looks like this: X Values: =Sheet1!$A$2:$A$9,Sheet1!$A$2:$A$9 Y Values: =Sheet1!$B$2:$B$9,Sheet1!$B$2:$B$9 (i.e. the same ranges copied twice and separated by a comma) nothing changes on the scatter graph, except that there is now a line zipping back to the beginning: the data is just being drawn twice. Now go in and manually edit the range again so it looks like this: X Values: =(Sheet1!$A$3:$A$9,Sheet1!$A$2:$A$9) Y Values: =(Sheet1!$B$2:$B$8,Sheet1!$B$2:$B$9) (i.e. the X range first part has lost its first row cell, and the Y range first part has lost its last row cell) the scatter chart now looks like it has double vision: The line is drawn across the chart, zips back, and draws itself again displaced one place to the left. Now change the chart type to Line, and change the X axis to Time-scale, and see what you get. !! How does it work? Whereas the line in a scatter chart is drawn strictly in the order the data points appear in the range, the line in a Time-scale Line chart is drawn from left to right in the order the points appear on the time-scale! I expect somebody who knows VBA could turn this into a macro. -- 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. |
#13
Posted to microsoft.public.excel.charting
|
|||
|
|||
Del's Quick & Dirty Step Chart (was Re-imagining charting software)
I wondered if that was your trick. A fellow named Michel Gerday sent me a
file in which he used names to get this effect in line charts: http://peltiertech.com/Excel/ChartsHowTo/StepChart.html Pretty neat, unfortunately in breaks down if the data is in an Excel 2003 list, because the list will incorrectly modify the definition of the names if the size of the list changes. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Del Cotter" wrote in message ... On Wed, 2 May 2007, in microsoft.public.excel.charting, Jon Peltier said: "Del Cotter" wrote "Ordinal" is like Excel's "Time-scale" (and therein lies an interesting bug/feature/property that's the key to a favourite trick of mine for quick and dirty step charts) Ooh, tell us more! (This works for me using Excel 97 under Windows XP) Consider the following range Series1 1 4 2 2 3 8 4 4 5 7 6 9 7 5 8 6 Where the label "Series1" occupies the cell B2, the X range is A2..A9 and the Y range is B2..B9. If you quickly use the Chart Wizard to create a scatter chart with lines and markers, the Source Data will look like this: X Values: =Sheet1!$A$2:$A$9 Y Values: =Sheet1!$B$2:$B$9 So far, nothing surprising. Now if you go in and manually edit the range (first remembering that F2 key!) so it looks like this: X Values: =Sheet1!$A$2:$A$9,Sheet1!$A$2:$A$9 Y Values: =Sheet1!$B$2:$B$9,Sheet1!$B$2:$B$9 (i.e. the same ranges copied twice and separated by a comma) nothing changes on the scatter graph, except that there is now a line zipping back to the beginning: the data is just being drawn twice. Now go in and manually edit the range again so it looks like this: X Values: =(Sheet1!$A$3:$A$9,Sheet1!$A$2:$A$9) Y Values: =(Sheet1!$B$2:$B$8,Sheet1!$B$2:$B$9) (i.e. the X range first part has lost its first row cell, and the Y range first part has lost its last row cell) the scatter chart now looks like it has double vision: The line is drawn across the chart, zips back, and draws itself again displaced one place to the left. Now change the chart type to Line, and change the X axis to Time-scale, and see what you get. !! How does it work? Whereas the line in a scatter chart is drawn strictly in the order the data points appear in the range, the line in a Time-scale Line chart is drawn from left to right in the order the points appear on the time-scale! I expect somebody who knows VBA could turn this into a macro. -- 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. |
#14
Posted to microsoft.public.excel.charting
|
|||
|
|||
Del's Quick & Dirty Step Chart (was Re-imagining charting software)
On Thu, 3 May 2007, in microsoft.public.excel.charting,
Jon Peltier said: Pretty neat, unfortunately in breaks down if the data is in an Excel 2003 list, because the list will incorrectly modify the definition of the names if the size of the list changes. If you can't do it with names in XL2003, can you do it with OFFSET? -- 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. |
#15
Posted to microsoft.public.excel.charting
|
|||
|
|||
Del's Quick & Dirty Step Chart (was Re-imagining charting software)
You can do it with names if you don't use lists. I presume one could define
names in a different clever way to overcome the problem. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Del Cotter" wrote in message ... On Thu, 3 May 2007, in microsoft.public.excel.charting, Jon Peltier said: Pretty neat, unfortunately in breaks down if the data is in an Excel 2003 list, because the list will incorrectly modify the definition of the names if the size of the list changes. If you can't do it with names in XL2003, can you do it with OFFSET? -- 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Y-axis labels | Excel Discussion (Misc queries) | |||
X - axis labels | Charts and Charting in Excel | |||
labels on the x-axis | Charts and Charting in Excel | |||
how to put x axis values in the chart instead of x axis labels | Charts and Charting in Excel | |||
x Axis labels | Charts and Charting in Excel |