how do i make a chart with two seperate lines of data?

hi

i have a historical sales chart from Mar 2003 to present that i'm trying to 
graph.

however, excel is making me keep the data in one straight line

if i try to seperate it into two rows, it adds another bit to the chart

how do i graph sales using two seperate lines of data?
0
Jason4159 (666)
10/18/2006 10:55:02 PM
excel.charting 18370 articles. 0 followers. Follow

3 Replies
588 Views

Similar Articles

[PageSpeed] 5

Hi,

This is best done in 2 steps. Create the chart with the first set of 
data. Then use the Source Data dialog to reselect the 2 data ranges, 
using the CTRL key to select the 2 separate ranges.

So for data that is in the range A1:E1 continued in A2:E2 you would end 
up with a series formula like this,

=SERIES(,,(Sheet1!$A$1:$E$1,Sheet1!$A$2:$E$2),1)

Cheers
Andy

Jason wrote:
> hi
> 
> i have a historical sales chart from Mar 2003 to present that i'm trying to 
> graph.
> 
> however, excel is making me keep the data in one straight line
> 
> if i try to seperate it into two rows, it adds another bit to the chart
> 
> how do i graph sales using two seperate lines of data?

-- 

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
0
andy9699 (3616)
10/19/2006 6:37:58 PM
thanks andy

when i select the first step the source data is this
=Sheet1!$A$1:$E$2 with row 1 being years and row 2 being sales data

when i select the next data it says "reference not valid" and looks like this
=Sheet1!$A$1:$E$2+Sheet1!$A$7:$E$8

i even tried to copy and paste your formula in and it says "that function is 
not valid" and highlights the SERIES in the formula

thanks for your help







"Andy Pope" wrote:

> Hi,
> 
> This is best done in 2 steps. Create the chart with the first set of 
> data. Then use the Source Data dialog to reselect the 2 data ranges, 
> using the CTRL key to select the 2 separate ranges.
> 
> So for data that is in the range A1:E1 continued in A2:E2 you would end 
> up with a series formula like this,
> 
> =SERIES(,,(Sheet1!$A$1:$E$1,Sheet1!$A$2:$E$2),1)
> 
> Cheers
> Andy
> 
> Jason wrote:
> > hi
> > 
> > i have a historical sales chart from Mar 2003 to present that i'm trying to 
> > graph.
> > 
> > however, excel is making me keep the data in one straight line
> > 
> > if i try to seperate it into two rows, it adds another bit to the chart
> > 
> > how do i graph sales using two seperate lines of data?
> 
> -- 
> 
> Andy Pope, Microsoft MVP - Excel
> http://www.andypope.info
> 
0
Jason4159 (666)
10/19/2006 9:23:01 PM
i figured it out, thanks Andy!

"Jason" wrote:

> thanks andy
> 
> when i select the first step the source data is this
> =Sheet1!$A$1:$E$2 with row 1 being years and row 2 being sales data
> 
> when i select the next data it says "reference not valid" and looks like this
> =Sheet1!$A$1:$E$2+Sheet1!$A$7:$E$8
> 
> i even tried to copy and paste your formula in and it says "that function is 
> not valid" and highlights the SERIES in the formula
> 
> thanks for your help
> 
> 
> 
> 
> 
> 
> 
> "Andy Pope" wrote:
> 
> > Hi,
> > 
> > This is best done in 2 steps. Create the chart with the first set of 
> > data. Then use the Source Data dialog to reselect the 2 data ranges, 
> > using the CTRL key to select the 2 separate ranges.
> > 
> > So for data that is in the range A1:E1 continued in A2:E2 you would end 
> > up with a series formula like this,
> > 
> > =SERIES(,,(Sheet1!$A$1:$E$1,Sheet1!$A$2:$E$2),1)
> > 
> > Cheers
> > Andy
> > 
> > Jason wrote:
> > > hi
> > > 
> > > i have a historical sales chart from Mar 2003 to present that i'm trying to 
> > > graph.
> > > 
> > > however, excel is making me keep the data in one straight line
> > > 
> > > if i try to seperate it into two rows, it adds another bit to the chart
> > > 
> > > how do i graph sales using two seperate lines of data?
> > 
> > -- 
> > 
> > Andy Pope, Microsoft MVP - Excel
> > http://www.andypope.info
> > 
0
Jason4159 (666)
10/19/2006 9:41:01 PM
Reply:

Similar Artilces:

current and future chart section in different colours
Hi All Helpers, I have yearly charts set up for totals of income etc (monthly and cumulative). How can I make the chart a different colour after the current month? Preferably also hiding the cumulative lines after the current date. Thanks Mon Jon Peltier has information on creating dynamic charts: http://www.peltiertech.com/Excel/Charts/Dynamics.html that may help you. mon wrote: > Hi All Helpers, I have yearly charts set up for totals of > income etc (monthly and cumulative). How can I make the > chart a different colour after the current month? > Preferably also hid...

Matching data in different sheets but slight difference in time va
Hi Please help. I have two sheets as follows: Sheet 1: Date / Time / Number Dailled / Duration /Cost 22/1/10 10:00 01234123456 00:01:57 0.789 Sheet 2: Date / Time / Number Dailled / Duration /Extension 22/1/10 10:02 01234123456 00:01:56 1501 I am trying to put the cost of the phone call against the relevant extension on sheet 2 that has dialled the number dialled. Obviously I can match the number dialled in both cases, and the...

Bar Chart Question Multiple Elements
Greetings excel people, I have the following data below and I want to create a bar chart fo the total # of yes answers, and the total # of no answers for eac category, for the current location. In addition, I need each tota displayed to its corresponding bar. How do I do this? Thanks for your needed help Location Answer Q1 Q2 Q3 Q4 TB YES 12 23 23 2 TB NO 34 33 4 -------- Message sent via www.excelforums.com Put the data in columns in Excel like you posted below. Highlight all the data (should be 6 columns and 3 rows) and either click the Chart icon or click on Insert in the toolbar...

restrict data in a pivottable
Hi, how can I resrtict the column and row values in a pivot table. for example of "date" is my column heading, and the base data has dates from 2004 and 2005, can I force the date "drop down" to only include those dates from 2005 (so you could never have the option of clicking a 2004 date) thanks Hi confused, Right-click the date column heading. Select Group and Show Detail, then Group... from the pop-up menu. Group by Days and Years, then click OK. Drag the Years column heading to the Page Field area. Select a year from the page field drop-down. More detail is avail...

Export CRM Data to Outlook
Hello, is it possible to transfer CRM 3.0 contacts and activities from CRM to Outlook? Contacts should have to be found in contacts and activities in the calendar. Any ideas? Stefan If you install the CRM for Outlook client, you can sync the contacts into your Outlook. Dave "Stefan" <Stefan@discussions.microsoft.com> wrote in message news:431EAA1A-1D92-46D3-A73E-7967ABB41CDE@microsoft.com... > Hello, > > is it possible to transfer CRM 3.0 contacts and activities from CRM to > Outlook? > Contacts should have to be found in contacts and activities in the ...

Chart Question #3
is it possible to create a chart, skipping columns or rows I need to creat a chart using data from every other column and another using data from about every 5th row Any help appreciated David Hopper I'm open to correction on this, but I believe the best way to do this (assuming you don't want to delete the in between data, and resort the columns somehow) would be to create a macro that copies the data you want into the right format (probably on another sheet) and make the chart from there. Something along the lines of: sub MyCopy() dim NewSheet as worksheet dim OldSheet as w...

RE: Question about numbers lined up against dates
This works below for 1 year BUT when I get more than 1 year it still just looking at months. ow can I change toe formula to look at years as well as months. TiA Mag()() mag A= #ID, B= Price, c = DueDAte, D= Qty, E= Numb Due formula in e2: =SUMPRODUCT(--(MONTH($C$2:C2)=MONTH(C2)*(--($A$2:A2=A2)))*($D$2:D2)) Copy the formula down, and multiply the result by the price in column f. Regards Peter "Mag()()" wrote: > Hey all, > Lets see if I can explain this one..... > > In Column "A" I have a series pf part number- IE 123, 442, ER45 and a > whole &g...

Getting data out of and into a dialog while it's "active"
Situation: I put up a dialog that contains, among other things, 2 drop-list combo boxes and on dropdown combo box. there's also a button nearby which the user can press to bring up another dialog to allow selecting the values for these three items using a different set of controls. I did something like this earlier when I had a multi-line edit control and a button to load the data from a file. When the user pushed the button, I used a file-open dialog to get the file and its contents and stuffed it into the CString variable and then returned to the main dialog. This worked perfectly. ...

Pivot Table border formatting and pivot chart formatting
I've haven't been able to eliminate the border formatting in a pivot table. Every time I change the page selection it defaults back to having borders. Does anyone know how to correct this? I've also been having the same problem with pivot charts. When I increase the width of a line in a chart and change the page selection it defaults back to the previous format. ...

Making a command button work!!!!
Hello I have a sheet with a combo boxes and a command button. Is there a way that would make the command button work only when a selection is made in a combo box Eg combo box Please select Std shaped finish Shaped finish with trim So if the bottom select is made (shaped finish with trim) the command button when press will work. BUT if any of the other selections have been made pressing of the button will have no effect? The combo and command button are on a spreadsheet from the forms toolbox Thanks -- raw ----------------------------------------------------------------------...

Make Cells Diagonal
How do I make the top "label row" of the columns diagonal (45 degree angle) instead of horizontal. I have seen it done but don't know how to do it. Thank you. ...

Pie Chart percentage
I am working in Excel 2007. I have a simple pie chart. Two numbers are used: 9,607,932 261,228,187 I am using percentages out to two decimal places in the data labels, but the labels don't match the percentages that were calculated in the Excel spreadsheet. Calculated = 3.68% and 96.32% Displayed labels = 3.55% and 96.45% How can I show the correct percentages in my chart labels? Am I missing something in the label attributes? Figured out my problem. The second number is my total. The percentages are correct. Oops! "KristaR" wrote: > I am working in Excel 2007. I ha...

connecting cells with connector lines
Hello! Is it possible in Excel (2007) to draw a connector line (with or without an arrow ...) between two cells in a worksheet, so that the line-tips will follow their cells even when the latter are moved ? Thanks Michael On Feb 25, 7:00=A0am, Michaelprem123 <michaelprem...@gmail.com> wrote: > Hello! > > Is it possible in Excel (2007) to draw a connector line (with or > without an arrow ...) between two cells in a worksheet, so that the > line-tips will follow their cells even when the latter are moved ? > > Thanks > > Michael The line will automatically a...

Copy data from one worksheet to another.. but not contiguous data
I have searched through everything looking for a simple answer to what seems a simple task.. but I have not been lucky thus far... Worksheet1 Column G Row 1-14 nothing needed Row 5 Data #1 Row 10 Data #2 Row 15 Data #3 .... Row 2200 Data ... I am trying to take data from worksheet #1 starting at G5 then every 5 cells until row 2200 and put it in a seperate Worksheet in order... Worksheet2 Column C Row 1-10 nothing pasted Row 11 Data #1 Row 12 Data #2 Row 13 Data #3 .... Row 370 Data #... I could do it by hand, but it is a pain to do 370 entries. I have been trying with Indirect and Offse...

Column Chart Conditional Formatting
I have a column chart that compares 2 y values against a common x value. I want to compare the second y value to the first, if it is greater then make that bar green, if less then red, if same grey. Any ideas on how to go about this? Thanks Adam Bush You'll need to do this by manipulating the actual data. Setup 3 dummy series, first with a static value of 10. On the chart, format these green, red, and grey respectively. Now, go back and change the static values to formulas similar to this: =IF(B2>A2,B2,NA()) =IF(B2<A2,B2,NA()) =IF(B2=A2,B2,NA()) If ...

How to combine data?
Hello, Sorry if this question has been asked before, this is the first time I encounter this problem. I have 2 XML files 1st file: <record ID = "1"> <title>some text</title> </record> <record ID = "2"> <title>more text</title> </record> <record ID = "3"> <title>another text</title> </record> 2nd file: <info ID = "1"> <desc>some information here</desc> <url>http://some_links_ here</url> </info> <info ID = "2&q...

Chart Type
We have a chart that combines both a bar and one line. Both use the same axis and scale. We need to add a 2nd line to the chart and whenever we try to select both columns of data we get an error using the source data-series-values. Any ideas would be greatly appreciated. Thanks. The bars are the current results and the original 1st line is the budget. The 2nd line they want is the prior year results. Hi, I'm a little confused why you would select 'both columns' when trying to add another series of data. Try this, right click the chart and select Source Data... On the series ...

requesting data from exchange server
I am running Office 2000 on an XPpro machine. Some of my users still use Office 97, but those on Office 2000 are not yet on XP OS. None of them have mentioned having this problem: on a daily basis [now, several times a day] my Outlook freezes up "requesting data from exchange server". This is getting terribly annoying. I have posted this Q on the Exchange message board also, in hopes that someone has an idea of what is causing the lag. I clean out the BadMail folder [on the server] often, thinking that maybe our Symantec AV bogs down the server there. ...

Splitting a workbook's sheets into seperate files
I have a workbook with several spreadsheets in it, how do I quickl seperate these sheets into seperate files -- Turnipbo ----------------------------------------------------------------------- Turnipboy's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2452 View this thread: http://www.excelforum.com/showthread.php?threadid=47779 I think the only way, is programing it with vba. I suggest using "For Each" statement to scroll through all sheets in a given workbook, and within the "For" creating a new workbook (using "Add" statement),...

Scaling XY charts with suppressed starting value
The autoscaling feature of the Excel graphing function seems to have (limited) mind of its own. I'm trying to graph Y-values in the -1.5 t 1.5 range and X-values from 190 to 410 with the minimum from 190 to 29 and the max value from 290 to 410. The autoscaling of Y works fine, but the X-axis always start at 0 while the high value properly sets the maximum. Of course, I could fi the minimum at 190, but that is not what I had in mind. Does anyone know how to overcome this limitation without using a macro -- Message posted from http://www.ExcelForum.com Per - You need to make an XY Sca...

how to open email from opt on line to excel
We have optimum on line as our internet server and have purchased the MIcrosoft 2003 Standard office version.. have installed the programs but now have received an email which would require excel program and can't seem to open the spreadsheet .. do we need to collate our email address with excel in order to open this email or can we still use opt on line email and get it to open in excel.. we need this explained in very laymans terms as we are not computer literate.. Danny Is the Excel spreadsheet an attachment? If you aren't sure, perhaps you need to check with your ISP ...

Charting #10
I am using Excel 2007. I have a title (with a date in it) in my worksheet and it is the same on a graph. For my report, I always change that date on the worksheet. how do I automatically change the title in the graph. Hi Charles, Click in the title on the chart and select (highlight) whatever is in there and then click up in the formula bar and enter = sign and then click in the cell containing the title that you want. -- Regards, OssieMac "Charles Eaves" wrote: > I am using Excel 2007. > I have a title (with a date in it) in my worksheet and it is the same on ...

XML error "Some Data Was Imported As Text"
Daily, I recieve a 3rd party XML file that I parse in Excel (using an excel macro) and then import into an Access database. Infrequently, the data will import into Excel, then parse and then the routine stops with the error message above. I look through the raw XML file and everything appears to be within the brackets, etc. Can someone give me some clarification to the meaning of the error message? Thanks in advance and Mery Christmas and Happy Holidays! The error message might be generated within your macro - as it is parsing the data it might detect that some field which should be ...

How can I know when a field is repeating during while data entry
My manager has this query. He is entering the stock details of his phamacy. He has 6 columns. One is "name of the distributor", the second is "bill number or invoice number", the third is "amount to be paid", the fourth is "cheque number" the fifth is whether it has been "paid" or is "unpaid". The lst coumn I have given conditional formatting, so that if it is paid, it will show the fonts in green and if it is unpaid, it will show the fonts in red. Now our query is that after say about 50 rows of enteries made, he would like ...

Access mailing labels--blank lines
The database I am using for mailing lables has some null fields on some records. Can Access condense the lables down to the useable fields so I don't have blank lines? For example, some records use a second address field, some do not. Certainly, check help for the CanGrow and CanShrink properties of the Controls. If it is labels, to preserve the spacing, you probably do not want to also set CanShrink on the Detail Section, as you might in a regular report. Larry Linson Microsoft Access MVP "kato719" <kato719@discussions.microsoft.com> wrote in message news:321...