X axis values on a line graph

In an excel spreadsheet, I have a series of data. Each "batch" of data has 24 
cells, but there are only 2 cells out of each 24 whose values I want to plot 
on a line graph on a separate sheet in the same file. The first of these 
contains a weekly date range and the second contains a percentage. For 
example:

4/7/08-4/13/08    44%
3/31/08-4/6/08    39%
3/24/08-3/30/08   51%

Notice that the data is going backwards in time. There are currently 16
batches of data, going back weekly to the beginning of 2008. Each week,
a new batch of data will be added at the top of the sheet.

I have created the line graph, and the Y axis is just fine, correctly 
showing 10%, 20%, etc. going upwards from zero, and the values are plotted 
correctly. I'm having trouble with the X axis, though. I did figure out how 
to get the data to reverse order, so that week 1 in January starts at the 
left and moves to the right until it reaches the 16th week in April. However, 
I can't figure out how to get the correct labels on the X axis. Right now 
they are simply numbers (16, 15, 14,
13, etc. ending at 1) What I want the labels to say are the date ranges. For 
instance, the first X axis label should be 12/31/07-1/6/08, the second should 
be 1/7/08-1/13/08, etc., until the 16th label, which should be 
4/17/08-4/13/08. In other words, the labels on the X axis, moving from left 
to right, should be the values in the following cells from the datasheet: 
A110, A103, A96, etc. through A10 and finally A3.

I can't figure out how to "tell" Excel to go to those cells to get the 
labels for the X axis. I have tried under "Format Axis" but there  doesn't 
seem to be anywhere for me to enter cell references.

Any help would be appreciated.

Marlene
0
Marlene (15)
4/23/2008 6:23:03 PM
excel.charting 18370 articles. 0 followers. Follow

1 Replies
750 Views

Similar Articles

[PageSpeed] 17

Make sure it's a line chart, not an XY chart. XY charts cannot display 
nonnumeric axis labels.

Excel 97-2003:
Right click the chart, choose Source Data, click on the Series tab. Click in 
the Category Labels box, and select the range with the date labels.

Excel 2007:
Right click the chart, choose Select Data, click on the Edit button under 
the Category (X) Axis Labels, then delete any references in the box and 
select the range with the date labels.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Marlene" <Marlene@discussions.microsoft.com> wrote in message 
news:7778B1E9-5C44-4313-918C-0EA7283754DD@microsoft.com...
> In an excel spreadsheet, I have a series of data. Each "batch" of data has 
> 24
> cells, but there are only 2 cells out of each 24 whose values I want to 
> plot
> on a line graph on a separate sheet in the same file. The first of these
> contains a weekly date range and the second contains a percentage. For
> example:
>
> 4/7/08-4/13/08    44%
> 3/31/08-4/6/08    39%
> 3/24/08-3/30/08   51%
>
> Notice that the data is going backwards in time. There are currently 16
> batches of data, going back weekly to the beginning of 2008. Each week,
> a new batch of data will be added at the top of the sheet.
>
> I have created the line graph, and the Y axis is just fine, correctly
> showing 10%, 20%, etc. going upwards from zero, and the values are plotted
> correctly. I'm having trouble with the X axis, though. I did figure out 
> how
> to get the data to reverse order, so that week 1 in January starts at the
> left and moves to the right until it reaches the 16th week in April. 
> However,
> I can't figure out how to get the correct labels on the X axis. Right now
> they are simply numbers (16, 15, 14,
> 13, etc. ending at 1) What I want the labels to say are the date ranges. 
> For
> instance, the first X axis label should be 12/31/07-1/6/08, the second 
> should
> be 1/7/08-1/13/08, etc., until the 16th label, which should be
> 4/17/08-4/13/08. In other words, the labels on the X axis, moving from 
> left
> to right, should be the values in the following cells from the datasheet:
> A110, A103, A96, etc. through A10 and finally A3.
>
> I can't figure out how to "tell" Excel to go to those cells to get the
> labels for the X axis. I have tried under "Format Axis" but there  doesn't
> seem to be anywhere for me to enter cell references.
>
> Any help would be appreciated.
>
> Marlene 


0
jonxlmvpNO (4558)
4/23/2008 9:00:00 PM
Reply:

Similar Artilces:

X-Axis Frustration
Should be a simple graph - but!!! A typical plot that I just can't make work would be for the following simple data for various aircraft: X-Axis values (cruise speed - mph) - 315 325 355 520 and 555 Corresponding Y-Axis Values (mpg) - 0.4 1.11 0.52 0.25 and 0.11 Couple of questions: 1) How do I get even increments of cruise speed on the x-axis, say 0 to 600, in even 100 increments, with data points properly spaced? 2) What is the real difference between Chart Options of Category and Automatic? Dave By making an XY chart not a Line...

Cell Values Not There ???
I'm using VBA code in Excel 2007. I am having a strange problem. The cells have content. Some of them, but not all of them, are selected from Data Validation lists (drawn from a named range). However, when I try to access their values in VBA, it says they are empty!'' For example, the simple code: Debug.Print "Cell value is: " & ActiveSheet.Cells(2, 7).Value results in the output: Cell value is: I even tried the following code, to make sure it was not a problem with the ActiveSheet reference: ThisWorkbook.Sheets("Form").Cells(2, 7)....

How do I format a field based on the value
I have a form that is populated from a query using several tables. In the detail section I have the following fields: Category Code qty PO number ... .... ... The qty is populated in the query based on whether or not a PO number is available. If the PO number is not available then it comes from table a, if it is available then it comes from table b. On the form, I have my fields color-coded for easy reading. I would like to be able to color the border and column heading based on where this value comes from. If table A, then it is blue, if table b then it...

How to Populate .msg File Used in Command Line
I have a .msg file I saved as a boilerplate for Calendar requests that will be programmatically started in an application I am developing. I want to be able to modify the Calendar request's date, time, duration, and message contents based on the user's inputs prior to opening the .msg file. After the user verifies the contents, he/she will click the <Send> button. Currently, from the command line, I can run "c:\program files\microsoft office\office12\outlook.exe" /f "c:\p_review\PR.msg" The Calendar request opens with the boilerplate. Ho...

Justifying text of an axis
How do you justify the text of an axis in an excel chart? On Mon, 21 Apr 2008, in microsoft.public.excel.charting, mdicke <mdicke@discussions.microsoft.com> said: >How do you justify the text of an axis in an excel chart? You can't, it's fixed by the axis. The best you can do is select a monospaced font and use a custom number format with ???? (question marks). -- Del Cotter NB Personal replies to this post will send email to del@branta.demon.co.uk, which goes to a spam folder-- please send your email to del3 instead. ...

Order of X-axis
Dearest Wizards, Col A has dates, in order but with most recent toward top. Col D has data to be graphed. What I want is for the resulting graph to have the most recent date on the RIGHT side of the graph (on the X-Axis) and the earliest dates on the LEFT. The order on the spreadsheet really can't be changed to accommodate this. TIA for any help you can give. Sam Hi, Double click the X axis and on the scale tab of the format dialog check, Dates in Reverse order To keep the Y axis labels on the left also check Value (Y) axis crosses at maximum value Cheers Andy -- Andy Pope,...

One line is killing my program!
The following line works, where it specifies the first 4 characters of two area codes (403, and (587. .Range("C2:C" & q).FormulaR1C1 = _ "=MID(R[0]C[-2],INDEX(FIND({""(403"",""(587""},R[0]C[-2]),MATCH(TRUE,ISNUMBER(FIND({""(403"",""(587""},R[0]C[-2])),0)),14)" I need to substitute two variables - areaCode1 and areaCode2 to replace (403 and (587. I think I've done so, but the following doesn't work: .Range("C2:C" & q).FormulaR1C1 ...

2 existing spreadsheets show 1st line rather than header row to sort ...
(XL2003) This is odd, I haven't seen this ever before. I was working on spreadsheets yesterday for a mail merge. Two had headers rows. I cleaned up both documents, put print area and then tried to sort. In both cases, what came up in the sort was the first row below the header where one always sees the actual titles of the header row when one selects the Header Row radio button under "My List Has" in the "Sort" box. I fiddled and fiddled a bit and managed to get one of the workbooks to display the sort properly by the header row titles - without figuring out what ...

Max Value
I am trying to use Conditional Formatting to find the Max value in a Row using =Max(B6:AC6), having that cell Highlight to a different color but the =Max is highlighting them all. Help phuser wrote: >I am trying to use Conditional Formatting to find the Max value in a Row >using =Max(B6:AC6), having that cell Highlight to a different color but the >=Max is highlighting them all. > There may be other ways, but what I do is create a column, or in your case cell, (can be hidden, or way out of sight) having the formula =Max(B6:AC6). Then use the value of that cell as the co...

Xpath with multiple values..
Is is possible to use XPath with multiple values like in SQL Queries (AND Clause).For example: If I have xml: <rows> <row code=1/> <row code=2/> <row code=3/> <row code=5/> <row code=6/> <row code=7/> <row code=100/> </rows> Now I want only to select rows 1,2,3 an 100 XPath("rows/row[@code='1' and @code='2' and @code='3' or @code='100']"). I tryed like this above, but I got nothing. Any examples. Regards. Try using ORs all the way. An AND situation is impossible here...

How to fill cell with two combobox values?
I am looking for VB code for command button that fills two combo box values and a toggle value (Y/N) behind each other in one cell. Bart Excel 2003 ...

How to clean 0x0E value from file when using XSL?
I have some code that cleans up an xml file before running xsl transformation on it due to what I call bad characters in the data. I have as an example: str = Replace(str, "&#x19;", ".") str = Replace(str, "&#x18;", ".") In there as filters to strip out characters XSL doesn't like and replace it with periods. I am now getting an exception of a hex value of 0x0E however can't figure out what the string replace value should be for this one. From what I can tell, it's some kind of "shift out" character whatever that is. A...

How do I assign a set of values to a selection from a drop list?
Hi. I am trying to assign a set of values, in separate fields ,to a each selection from a drop-down list in Excel (using data validation). What I am trying to do is very smilar to, say, to selecting a SKU from a list and having the product description, unit price etc. fill into their corresponding fields automatically. For example, I pick SKU "11111" from a drop-down list to fill the SKU field, then, automatically, "5-inch widget" comes up in the product description field and "$5.00" shows up in the unit price field. Please help. Mike Along with the d...

Junk E-mail folder
Is it possible to configure the Junk E-mail folder to automatically and permanently delete items older than 30 days? Thanks. You can use the AutoArchiving to do that. Right click on the folder, choose Properties and go to the AutoArchive tab "Jaycee" <jaycee131973@hotmail.com> wrote in message news:%232IGo%238NGHA.2300@TK2MSFTNGP15.phx.gbl... > Is it possible to configure the Junk E-mail folder to automatically and > permanently delete items older than 30 days? ...

how do I set up spread sheet for demographical data to get graph.
I need to set up a spread sheet to be able to get age, sex,service usage numbers, site utilisation, presenting issue e.g., drug and alcohol, region, percentage of use base on population e.g, 80 staff DoHi, Do you want to show these results graphically, or in the form of some sort of output table? Dave url:http://www.ureader.com/msg/10355820.aspx How are you intending to get the data or enter the data into this sheet? How many worksheets or tables will you need? What is the final display options: Examples, Reports, Charts, Pivot = table? And lastly who are the end users, and how do they...

Line Chart #9
Hi, I have a line chart for month on month data Jan'07 Feb'07 Mar'07 Apr'07 May'07 Jun'08 A 93 99 120 111 85 90 This is the Eg data. Is there a way to show difference in values. For Eg : Jan - Feb there is a +6 difference. Then there is a +11 difference... etc Is there a way i can show this difference on the graph automatically? Other than manually inserting a text box?? Thanks Rajula Please don't multi-post. Cross-post if you must. -- David Biddulph "Rajula" <Rajula@discussions.microsoft.com> wrote in message news:2FBF5AFF-6629-470C-B...

How do I get non-sequential dates on X-axis
I am trying to make a chart that tracks progress each week from Monday to Friday. i.e. June 5 to June 9, then June 12 - June 16. This goes on the X-axis. Each time I try and chart the series without the dates it works OK. As soon as I add the date, Excel automatically fills in the weekend days. I have looked at every option that I can think of and prefs but can't get it to just show the dates that I actually want to chart. Can someone tell me how I can force Excel not to show dates for which there is no data and there is no labels etc? Thanks in advance Using Excel 2004 for Mac...

Stopping the Over-Receipt of Purchase Line Items
Without code, does anybody know of a way to prevent a user from overreceiving a purchase order? The message appears that says the item has been fully received. But, the user is still able to keep going. Thanks -- Charles Allen, MVP Charles, I checked and I don't think you can accomplish this without a customization. Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com The Dynamics GP Blogster at http://dynamicsgpblogster.blogspot.com "Charles Allen" wrote: > Without code, does anybody know of a way to...

X-Y Graph with Months, Not Date on Axis
I have a data set with dates in column A and values in column B. I have this plotted as an X-Y graph. I can change the major units on the X (date) axis, but only by integers. I would like for it to display months, rather than equally spaced dates. The closest I can do, is to use 30 days for the major unit on the X-axis. Is it possible to have each month displayed rather than specific date in 30 day increments? Bill Hi, Try the following approach; I think that it would work. If the lowest date in your data is not on the first of a month, create a dummy date which is the first of the low...

Need help with some excel graphs
Been trying for three days to get this sorted! I have created some graphs that automatically update on a monthly basis - problem is the future months are now shown as Zero on the graph which doesn't look so good. Is there any way of disabling this or having a formula that places a value in another cell. Any help on this would be great... Hi, 1.. On your graph select the Y axis, 2.. then right click to get the menu 3.. in format axis, select the "Scale" 4.. Put a value in the maximum. -- JP jp@solutionsvba.com http://www.solutionsvba.com "RuudsRightFoot&quo...

How do you serialize a blank value in XML to GP using eConnect?
Hi, I'm trying to save a customer address with a blank ADDRESS2 field to our Great Plains database using eConnect 10 for GP 10. ADDRESS2 has a value in the database, but the user wants to set it to blank (delete it). I update the object, then serialize it, and pass it eConnect. The problem is that the XmlSerializer class serializes only non-empty fields in the eConnect.Serialization.taCreateCustomerAddress_ItemsTaCreateCustomerAddress object to XML. And fields not present in the XML are not overwritten in the database (explained here http://msdn.microsoft.com/en-us/library/bb625...

Displaying values vs formulas
I'm having another brain-dead moment and I hope someone can resuscitate! I'm putting formulas in various cells on a worksheet and some of them are displaying the results and some are just showing in the cell as the formula. How can I get them all to display results. I swear that all the cells are formatted the same but the displays are different. What am I doing wrong? Thanks for any help. Gary Vocks The cells displaying the formula itself and not the results are probably formatted as Text. A quick way to find out if this is so, is to select a cell that displays the formula, the...

Accumulating Values in a Pivot table
Hi all. Does anybody of you know, how I can show in a Pivot table accumulated values (example)in each row: Day Amount Accum. Value 1 50 50 3 75 125 8 105 230 .. .. .. .. .. .. and so on. Maybe someone give me a tip. Thanx a lot Regards Kurt Hi Kurt, That is one of the big advantages of using Pivot Tables One place to start would be Debra Dalgleish's Contextures.com/techtip.html http://www.contex...

Date dependant values.
Up to 10 times a year, I want to add a value of one to cell V4 starting with zero. I have come up with the following formula =IF(TODAY()<38530,(0),IF(TODAY()<38540,(1),IF(TODAY()<38550,(2),IF(TODAY()<38560,(3),4)))) etc. The problem is that I can only use the IF formula seven times. I would very much appreciate it if you could write a new formula for me as I am quite new to the whole thing and it took me weeks to work mine out! (I have been advised to use the VLOOKUP formula but cannot get my head round it.) Thanking you in anticipation. -- Big Rick Can you tell us when you wa...

No "line-in" option in sounds & audio devices
Hi, I am trying to use Windows Movie Maker to convert analog cassettes to .mp3 format. I have WindowsXP, and I need to have the "line in" option enabled in "Sounds & Audio Devices", under the audio tab. The only option I have is my default sound card...a SoundMax Digital Audio. Can you please tell me how to enable the "line in" option? Thanks in advance. sheana wrote: > I am trying to use Windows Movie Maker to convert analog cassettes > to .mp3 format. I have WindowsXP, and I need to have the "line in" > option enabled in...