text label in x-axis (scatter chart)

I'd like to create a scatter chart but force all the data points into
one column for each series (as poorly illustrated below if viewed using
fixed font).

  x            x
               x
  x            x

  x
  x            x


  x


Series A     Series B


There are a few issues I'm trying to work around.  First, scatter
plots seem to require a numeric value.  If I have text in the first
column of data, then the chart will spread the data points
horizontally.  If I create a numeric "label" in the first column, I
at least get the visual effect I'm looking for but then I have to
manually create a text box with the series name below each series.
This is tedious since I'd like to plot lots of series and would like
to eventually automate chart creation via a macro.  I can't find any
way to have a text label in the x-axis that references a cell from the
worksheet.

Stock charts get me pretty close but I really want to display all data
points as discrete points on the chart.

Any ideas?  Any help is much appreciated,
Andy

0
andy650 (2)
6/20/2006 12:53:41 AM
excel.charting 18370 articles. 0 followers. Follow

2 Replies
356 Views

Similar Articles

[PageSpeed] 10

Hi,

You could tackle this in two ways, you will have to decide which best 
suits you.

Use the XY scatter, plotting just the x and y values, so you get the 
layout required. Then use either of these free add-ins to link data 
labels with the text in the cells.

Rob Bovey's Chart Labeler
http://appspro.com

Or John Walkenbach's Chart Tools
http://j-walk.com

Or with your data laid out like so,
		B1: =Series1	C1: =Series2
A2: =Label1	B2: =3	C2: =4
A3: =Label2	B3: =5	C3: =2
A4: =Label3	B4: =2	C4: =6

Create a line chart with data plotted by Rows. This will give you the 2 
columns with each series consisting of 2 points. You will need to format 
each series to have a Line of none. You can change 1 series and then use 
the F4 button to repeat the changes to the currently selected item.

Cheers
Andy


andy650@gmail.com wrote:
> I'd like to create a scatter chart but force all the data points into
> one column for each series (as poorly illustrated below if viewed using
> fixed font).
> 
>   x            x
>                x
>   x            x
> 
>   x
>   x            x
> 
> 
>   x
> 
> 
> Series A     Series B
> 
> 
> There are a few issues I'm trying to work around.  First, scatter
> plots seem to require a numeric value.  If I have text in the first
> column of data, then the chart will spread the data points
> horizontally.  If I create a numeric "label" in the first column, I
> at least get the visual effect I'm looking for but then I have to
> manually create a text box with the series name below each series.
> This is tedious since I'd like to plot lots of series and would like
> to eventually automate chart creation via a macro.  I can't find any
> way to have a text label in the x-axis that references a cell from the
> worksheet.
> 
> Stock charts get me pretty close but I really want to display all data
> points as discrete points on the chart.
> 
> Any ideas?  Any help is much appreciated,
> Andy
> 

-- 

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
0
andy9699 (3616)
6/20/2006 7:43:56 AM
The second method gets me closer to what I'd like to do.  Thanks!  Now
all I have to do is write a simple macro to format all the series
identically and I'll be even closer.

Andy Pope wrote:
> Hi,
>
> You could tackle this in two ways, you will have to decide which best
> suits you.
>
> Use the XY scatter, plotting just the x and y values, so you get the
> layout required. Then use either of these free add-ins to link data
> labels with the text in the cells.
>
> Rob Bovey's Chart Labeler
> http://appspro.com
>
> Or John Walkenbach's Chart Tools
> http://j-walk.com
>
> Or with your data laid out like so,
> 		B1: =Series1	C1: =Series2
> A2: =Label1	B2: =3	C2: =4
> A3: =Label2	B3: =5	C3: =2
> A4: =Label3	B4: =2	C4: =6
>
> Create a line chart with data plotted by Rows. This will give you the 2
> columns with each series consisting of 2 points. You will need to format
> each series to have a Line of none. You can change 1 series and then use
> the F4 button to repeat the changes to the currently selected item.
>
> Cheers
> Andy
>
>
> andy650@gmail.com wrote:
> > I'd like to create a scatter chart but force all the data points into
> > one column for each series (as poorly illustrated below if viewed using
> > fixed font).
> >
> >   x            x
> >                x
> >   x            x
> >
> >   x
> >   x            x
> >
> >
> >   x
> >
> >
> > Series A     Series B
> >
> >
> > There are a few issues I'm trying to work around.  First, scatter
> > plots seem to require a numeric value.  If I have text in the first
> > column of data, then the chart will spread the data points
> > horizontally.  If I create a numeric "label" in the first column, I
> > at least get the visual effect I'm looking for but then I have to
> > manually create a text box with the series name below each series.
> > This is tedious since I'd like to plot lots of series and would like
> > to eventually automate chart creation via a macro.  I can't find any
> > way to have a text label in the x-axis that references a cell from the
> > worksheet.
> >
> > Stock charts get me pretty close but I really want to display all data
> > points as discrete points on the chart.
> >
> > Any ideas?  Any help is much appreciated,
> > Andy
> >
>
> -- 
> 
> Andy Pope, Microsoft MVP - Excel
> http://www.andypope.info

0
andy650 (2)
6/20/2006 9:48:09 PM
Reply:

Similar Artilces:

CTabCtrl and retrieving text with GetItem
I am using the following code to show a popup menu when the user right clicks on a tab in a CTabCtrl: void CTabBar::OnRButtonDown(UINT nFlags, CPoint point) { TCHITTESTINFO info; info.pt.x = point.x; info.pt.y = point.y; long tab = m_tabctrl.HitTest(&info); if (tab == -1) return; TCITEM item; item.mask = TCIF_TEXT; if (!m_tabctrl.GetItem(tab, &item)) return; ....... My next step from here was to retrieve item.pszText. However - GetItem is ALWAYS returning false. For example, I have 1 tab and I right click on it. HitTest return 0 (ind...

How do I make numbers become text?
I am trying to create a spreadsheet where numbers entered in one location become text in another. I tried the Help option, but I am still lost. Please help, and thank you. From earlier postings: How to Convert a Numeric Value into English Words http://support.microsoft.com/default.aspx?scid=KB;EN- US;140704& and: (courtesy of a cut and paste from a Tom Ogilvy post): If you want an addin that provides a worksheet function that does this, download Laurent Longre's free morefunc.xll addin found here: http://longre.free.fr/english/ It is downloaded in a zip file which also conta...

population pyramids using bar charts with a secondary axis
I have a problem that I had given up as unsolvable, but after recently learning about secondary axes, I've made encouraging progress. However, I'm stuck on the last step(s), and I'm hoping that someone will have some suggestions. My organization has been producing reports that contain population pyramids. In the past, all of their reports have been printed, so it didn't matter that in order to get the desired look, they had to use two charts slightly overlapping each other. However, we are getting to the point where we would like our charts to be available online for download....

data labels from second column
Hi Column A is list of names (Bob, Sue, etc); column B is how much they collected (58, 12, etc); Column C is the date they did it on - so record 1 says Bob collected 58 on 10/07/07, for instance. I want to create a chart with the date for the x axis, amount collected as the y axis and data labels at each point within the graph giving the collector's name. So at X=12/07/07, y=58 I want it to say Bob within the chart area. Any help much appreciated. Regards Chandler On Mon, 3 Sep 2007, in microsoft.public.excel.charting, Chandler <Chandler@discussions.microsoft.com> said: &...

How to set a icons instead of a menu text?
Hello, I'm not quiet sure this can be done , but i would like for some reasons to set icons to describe my menus. Can this be done and how? Many thanks Breadl, It is possible to owner-draw menus, youmight want to check here: http://www.codeproject.com/menu/ and here: http://www.codeguru.com/Cpp/controls/menu/ for samples. Johan Rosengren Abstrakt Mekanik AB "Bredal Jensen" <Bredal.Jensen@mimosa.com> a �crit dans le message de news:%23hH2cnyxEHA.2540@TK2MSFTNGP09.phx.gbl... > > Hello, > I'm not quiet sure this can be done , but i would like for so...

Changing text in X and Z reports
Hi, I want to change a text in X and Z reports. The original text is "Paid on Account." I want it to say "On Account." How can I do the change? I already found the XML report template but don't know where to go from there. Thank you in advance. Win open file in Frontpage or text editor. Do "find" and type in "paid on account" to get you to the section quickly. or, just roll down until you find this section =========================== Section: Grand Total Out =========================== --> <!--BOOKMARK--> <ROW...

Where is the Keep Text Formatting feature located in Word 07
I believe this Keep Text Formatting feature might be what I need, but I have been unable to locate exactly where it is located in Word 2007. I'm trying to rid a Word document sent to me of tables, text boxes, graphics and all other document formatting, while retaining the document's text content. It is unimportant to me whether the text formatting is retained or not. Thanks. Are you referring to a Keep Text Formatting feature in an earlier version of Word? I wonder whether what you're looking for is "Paste Unformatted," since you seem to be saying you _don...

Export a range to a text file
Hello need some advise on how to procede I need to be able to create a text file containg some text as well as data that is within a named range in excel and then some more text. I can handle printing to the text files using cell values etc but am unsure of the best way to print the ranges data. Is there a way or procedure to just print the range as is in csv format? As well my range will contain about 6 columns, each containg a number field (formatting of decimal places is important, some have 2 dec some 3 etc) Also the range has a max of 50 rows however will always contain lower rows of...

Step Line Charts
Does anyone know how I can create a step line chart without having to have 2 data points per x-axis point. What I want is a horizontal line until the next data point and then I want the line to go vertical. Hi Carrie, You Will need some form of extra data to get excel to create a step chart. Take a look at these methods. (http://www.andypope.info/charts/stepchart.htm) Carrie wrote: > Does anyone know how I can create a step line chart without having to have 2 data points per x-axis point. What I want is a horizontal line until the next data point and then I want the line to go vert...

Need an unbound text box and command button (Search)
I need to create an unbound text box with a command button (Search) on my form which will enable the user to find the current record for a space no. I would like the user to only have to type in the space no. My fields are: SpaceNo Current "Yes" I am not experienced at writing code. Thanks so much. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200911/1 On Wed, 25 Nov 2009 23:09:36 GMT, "Uschi via AccessMonster.com" <u25116@uwe> wrote: If you don't write code, it may be easier to instruc...

CONCATENATE text to create a formula to be evaluated
Hi, I am wanting to concatenate a set of text to create a formula. I have done so below. =CONCATENATE("=MAX('",O1,"'!A3:A65536)") The result is: =MAX('2009'!A3:A65536) ....but it treats this as a text string when I try to use INDIRECT Cell O1 contains the year minus 1. In this case O1 = 2009. All of my worksheets are named as a year e.g. 2007....2008....2009...2010. I am entering this on sheet 2010. Now the real question: How do I make another cell evaluate this string as an actual formula and spit out the highest number for ...

text in formula
Help!!! Is there a way to have a formula where a cell with text is in it, but it is not included in the formula. Like I have a random cell that appears sometimes within the range but because it is text and I do not want to include it in the formula. Is there a certain "symbol" that could be used? HELP!! Hi maureen, It depends on what the actual formula is, but *some* functions ignore text entries. You could use the ISNUMBER function to include only those entries that are numbers. Post your formula for a more detailed reply. Biff >-----Original Message----- >Help...

Linking text within Excel-- help!
I have a mailing list that I will be importing into Excel, and am trying to link the names on the list to invoices for those people on the list. Can I do this in Excel by using links? The Excel help feature only seems to describe links with figures (numbers), not text. e.g. Mr. Bill Jones, 123 Main Street, Middletown, OK 40404 (each word in its own cell in the mailing list worksheet) ....would link to a worksheet that had Bill Jones' name and address but also indicted that he purchased a $100 product on June 15 and his bill has been paid in full. My questions: 1. Each of the 300 or...

Selecting Text Box
Is there a keyboard command that one can use with the mouse to select the text box (frame) rather than trigger the Edit Text mode? When there are many objects on the page, getting the mouse to click on the right frame can be a real problem. But if the cursor is in the right box, some keyboard command (with or without the mouse) should help select the right object. Thanks! ...

Deleting a single character in a text field
In one of my tables i have a field that has a text values like '123.23123' i would like , to delete the full stop. How can i do this. I cant do it in excel as the number of records that i have is over 200000. hi, ma1000 wrote: > In one of my tables i have a field that has a text values like '123.23123' i > would like , to delete the full stop. How can i do this. I cant do it in > excel as the number of records that i have is over 200000. Create a update query, use Replace([yourField], ".", "") as new value. mfG --> stefan <-- ...

descriptive statistics
I have a survey which has some text answers and some numerical answers. I need to complete histogram and descriptive statistics on this data but I get an error message stating my "data contains non-numerice input". Does that mean all data has to be converted to nummerical format? one way to solve theproblem use count if suppose non numeric data for e.g. a.s.d.f.g etc are in A1 to A100 find out unique set by using advance filter and then use countif function "allen367" <allen367@discussions.microsoft.com> wrote in message news:E3E2CE4F-7A2E-47FF-8B6E-D88EFF52C...

Can I display the current date in a text box?
I know how to display the current date in a cell, but can I display it in a text box? And how would I do that? You would have to have some code to load it, such as Textbox1.Text = Format(Date,"dd mmm yyyy") or link the texbox to a cell with the formula =TODAY() -- HTH RP (remove nothere from the email address if mailing direct) "stephiebrady" <stephiebrady@discussions.microsoft.com> wrote in message news:C78C4C78-C12C-4A8F-9121-E377ACAE3B5B@microsoft.com... > I know how to display the current date in a cell, but can I display it in a > text box? And ...

Lines of text move when viewed in print preview and printed
I am inserting pictures with-in paragraphs of text. I have tried formating as both exact and in-line, using top/bottom. The text moves up 1-2 lines when printed or viewed in print preview. This change in text lines also happens when view is changed from 50% to 100%. I am working with two column text and the pictures are inserted into each column. How can I fix this problem? What version of Windows and Publisher are you using??? You people that think we have crystal balls or are mind readers are exasperating! -- >-----Original Message----- >What version of Windows and Publi...

Truncated "y" axis label in chart
:confused: Hi guys I have a problem with Excel, the label on the �Y� axis appears truncated even though I have resized the chart. Then, I looked the file in another computer with the same version of excel than mine as well as in another with a different version and in both look fine. Thus, I erased and re-installed excel in my computer to no avail. I have also re-sized the chart and used it both as object embedded and in separate sheet, it doesn't fix the problem. Any suggestions to fix this problem? -- flakkortin -----------------------------------------------------------------------...

more symbols in chart
I need to chart an experiment data for 28 monkeys. I wanted to use a separate symbol for each monkey. I am limited to black and white only, so I can't use multiple colors. I am experimenting on changing white, black for back ground color, fore color, to make a distinct symbol for each monkey. Does any body know how do we get more symbols on the chart? Thanks in advance for any help, sheela Jon Peltier has instructions for Custom series markers: http://www.peltiertech.com/Excel/ChartsHowTo/CustomSeriesFormatting.html sheela wrote: > I need to chart an experiment data for 28 ...

Drop Down List for Chart
I have grouped my series into several groups. How do I display a drop down list including the groups which will dynamically identify which series to plot on the chart? For example: Group A = Series 1, Series 2, Series 3 Group B = Series 4, Series 5 Group C = Series 6 Drop down list includes values: "Group A", "Group B", "Group C" When Group A is selected within the drop down, Series 1, 2 and 3 are plotted on the chart. Do you mean you have to plot data, and have the chart move week to week? if so, easiest way is to use a hlookup/vlookup "savior1&quo...

how can I show repeating values in a chart?
I would like to show modes in the form of a pie chart but am not sure how. For example the number 73 comes up 3 times in a column on my spreadsheet, how can I show that compared to the number 50 which come up 2 times in the sheet? Thanks for the help Hi, You will need to compute those values using formula or a pivot table and then chart the results. Cheers Andy Cindy wrote: > I would like to show modes in the form of a pie chart but am not sure how. > For example the number 73 comes up 3 times in a column on my spreadsheet, how > can I show that compared to the number 50 whi...

How do I assign labels to scatter charts
Hi. I'm making an x-y scatter chart but can't assign labels to the individual points. How do I do this? Many thanks in advance for your help. Tom Hi, Here is an explanation of how to link a chart text element to a cell. http://www.andypope.info/tips/tip001.htm But if you have more than a couple of data labels to do you really need to use code. This free addin will do it for you. http://www.appspro.com/Utilities/ChartLabeler.htm Cheers Andy ThomasStudd wrote: > Hi. > > I'm making an x-y scatter chart but can't assign labels to the individual > points. H...

Automatically update Value for data label
Hello I am using Excel 2003 SP2, and have some graphs which have the value (data label) for the last month. Each month new data is entered and the data label has to be deleted for the previous month and the data label for the most recent month added (it still uses the same old data - new data is only entered for the most recent month). Is there any way where the data label can automatically update with the most recent months value (as the chart updates itself automatically currently). Any ideas appreciated. Thank you in advance. Regards, Nav ...

I'm trying to display the next month in a text field
Hi there, I've gone through all the forms and can't seem to get the code straight for displaying the next month on a text field on my form. I just want the entire month name and nothing else and I have been struggling with: DateSerial(Year(Date()), Month(Date()) + 1, 1) but its showing the day and year too and thats not what I need. Thanks in advance! GM gmazza wrote: >Hi there, >I've gone through all the forms and can't seem to get the code straight for >displaying the next month on a text field on my form. >I just want the entire month name and nothing else...