Line graphs in Excel 2007

I am trying to plot weekly sales in a given year.
Eventually in a year, there will be 52 weeks of sales labeled date,this yr 
sales,last yr. sales.
I am using a line graph.
Everything is working fine except:
I would like excel to automatically update my chart for each week that I 
have sales data for.
In the near fuure,when I enter data for the months of  Oct,Nov,Dec and the 
last part of Sept., I would like Excel to automatically update my chart.
I have tried to in my data table, put all the weeks of the year in the table 
and as I get sales data, enter them into the table.
The chart will update but when the chart plots the data, it drops down to 
zero because it doesn't have any sales data for the future months ahead with 
no data.
How do you get Excel to update my chart as I am updating my data table for 
the months ahead as I get them?
Thanks



0
9/29/2008 4:09:15 PM
excel.charting 18370 articles. 0 followers. Follow

6 Replies
755 Views

Similar Articles

[PageSpeed] 51

You could use an OFFSET formula to make the chart dynamic.
See
http://peltiertech.com/Excel/Charts/Dynamics.html
 or
http://office.microsoft.com/en-us/excel/HA011098011033.aspx


OR
Turn your data into an Excel 2007 Table by selecting the current data and 
using Insert | Tables | Table. A chart made from a Table (must be defined as 
such) is automatically dynamic

best wishes
-- 
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Charles Eaves" <charles.eaves@worldnet.att.net> wrote in message 
news:%23x3V42kIJHA.1060@TK2MSFTNGP03.phx.gbl...
>I am trying to plot weekly sales in a given year.
> Eventually in a year, there will be 52 weeks of sales labeled date,this yr 
> sales,last yr. sales.
> I am using a line graph.
> Everything is working fine except:
> I would like excel to automatically update my chart for each week that I 
> have sales data for.
> In the near fuure,when I enter data for the months of  Oct,Nov,Dec and the 
> last part of Sept., I would like Excel to automatically update my chart.
> I have tried to in my data table, put all the weeks of the year in the 
> table and as I get sales data, enter them into the table.
> The chart will update but when the chart plots the data, it drops down to 
> zero because it doesn't have any sales data for the future months ahead 
> with no data.
> How do you get Excel to update my chart as I am updating my data table for 
> the months ahead as I get them?
> Thanks
>
>
> 


0
bliengme5824 (3040)
9/29/2008 4:20:28 PM
Thanks. I will try it but it is all greek to me especially the OFFSET 
command.
I am a newbie to excel.
Thanks again.

"Bernard Liengme" <bliengme@stfx.TRUENORTH.ca> wrote in message 
news:upP2J9kIJHA.3708@TK2MSFTNGP02.phx.gbl...
> You could use an OFFSET formula to make the chart dynamic.
> See
> http://peltiertech.com/Excel/Charts/Dynamics.html
> or
> http://office.microsoft.com/en-us/excel/HA011098011033.aspx
>
>
> OR
> Turn your data into an Excel 2007 Table by selecting the current data and 
> using Insert | Tables | Table. A chart made from a Table (must be defined 
> as such) is automatically dynamic
>
> best wishes
> -- 
> Bernard V Liengme
> Microsoft Excel MVP
> http://people.stfx.ca/bliengme
> remove caps from email
>
> "Charles Eaves" <charles.eaves@worldnet.att.net> wrote in message 
> news:%23x3V42kIJHA.1060@TK2MSFTNGP03.phx.gbl...
>>I am trying to plot weekly sales in a given year.
>> Eventually in a year, there will be 52 weeks of sales labeled date,this 
>> yr sales,last yr. sales.
>> I am using a line graph.
>> Everything is working fine except:
>> I would like excel to automatically update my chart for each week that I 
>> have sales data for.
>> In the near fuure,when I enter data for the months of  Oct,Nov,Dec and 
>> the last part of Sept., I would like Excel to automatically update my 
>> chart.
>> I have tried to in my data table, put all the weeks of the year in the 
>> table and as I get sales data, enter them into the table.
>> The chart will update but when the chart plots the data, it drops down to 
>> zero because it doesn't have any sales data for the future months ahead 
>> with no data.
>> How do you get Excel to update my chart as I am updating my data table 
>> for the months ahead as I get them?
>> Thanks
>>
>>
>>
>
> 


0
9/29/2008 4:47:27 PM
The second method is really very easy - just try it and you will go "Oh! 
Yes!"
-- 
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Charles Eaves" <charles.eaves@worldnet.att.net> wrote in message 
news:uZ%231PMlIJHA.1060@TK2MSFTNGP03.phx.gbl...
> Thanks. I will try it but it is all greek to me especially the OFFSET 
> command.
> I am a newbie to excel.
> Thanks again.
>
> "Bernard Liengme" <bliengme@stfx.TRUENORTH.ca> wrote in message 
> news:upP2J9kIJHA.3708@TK2MSFTNGP02.phx.gbl...
>> You could use an OFFSET formula to make the chart dynamic.
>> See
>> http://peltiertech.com/Excel/Charts/Dynamics.html
>> or
>> http://office.microsoft.com/en-us/excel/HA011098011033.aspx
>>
>>
>> OR
>> Turn your data into an Excel 2007 Table by selecting the current data and 
>> using Insert | Tables | Table. A chart made from a Table (must be defined 
>> as such) is automatically dynamic
>>
>> best wishes
>> -- 
>> Bernard V Liengme
>> Microsoft Excel MVP
>> http://people.stfx.ca/bliengme
>> remove caps from email
>>
>> "Charles Eaves" <charles.eaves@worldnet.att.net> wrote in message 
>> news:%23x3V42kIJHA.1060@TK2MSFTNGP03.phx.gbl...
>>>I am trying to plot weekly sales in a given year.
>>> Eventually in a year, there will be 52 weeks of sales labeled date,this 
>>> yr sales,last yr. sales.
>>> I am using a line graph.
>>> Everything is working fine except:
>>> I would like excel to automatically update my chart for each week that I 
>>> have sales data for.
>>> In the near fuure,when I enter data for the months of  Oct,Nov,Dec and 
>>> the last part of Sept., I would like Excel to automatically update my 
>>> chart.
>>> I have tried to in my data table, put all the weeks of the year in the 
>>> table and as I get sales data, enter them into the table.
>>> The chart will update but when the chart plots the data, it drops down 
>>> to zero because it doesn't have any sales data for the future months 
>>> ahead with no data.
>>> How do you get Excel to update my chart as I am updating my data table 
>>> for the months ahead as I get them?
>>> Thanks
>>>
>>>
>>>
>>
>>
>
> 


0
bliengme5824 (3040)
9/29/2008 4:55:33 PM
Hi,

It depends on the layout of your data.  If you lay the data out vertically 
meaning February shows up on a row below January then the best solution if 
you are using Excel 2003 or 2007 is to use the List (Table in 2007) feature.  
Plot your chart from a table where you haven't entered a month name until you 
have data, then the chart will update automatically.

1. Define the data range as a List (2003) or Table (2007) by choosing Data, 
List, Create List, OK in 2003. Or press the shortcut key Ctrl+L (or in 2007 
Ctrl+T 
also). 
2.  You can do this before or after you create the chart.
3.  Then when the new months data comes in put it below the previous months 
figures.


-- 
Thanks,
Shane Devenshire


"Charles Eaves" wrote:

> I am trying to plot weekly sales in a given year.
> Eventually in a year, there will be 52 weeks of sales labeled date,this yr 
> sales,last yr. sales.
> I am using a line graph.
> Everything is working fine except:
> I would like excel to automatically update my chart for each week that I 
> have sales data for.
> In the near fuure,when I enter data for the months of  Oct,Nov,Dec and the 
> last part of Sept., I would like Excel to automatically update my chart.
> I have tried to in my data table, put all the weeks of the year in the table 
> and as I get sales data, enter them into the table.
> The chart will update but when the chart plots the data, it drops down to 
> zero because it doesn't have any sales data for the future months ahead with 
> no data.
> How do you get Excel to update my chart as I am updating my data table for 
> the months ahead as I get them?
> Thanks
> 
> 
> 
> 
0
9/29/2008 8:18:01 PM
Hi again,

Here is a second way which may be one of the suggestions Bernard gave you, 
if so, sorry for the duplication, but I don't follow the links everybody 
gives.

1 Plot your chart for the months you already have, don't include months 
without data.
2. Add new month data when you get it
3. Click the series on the chart and not the blue lines around the data 
area.  Note the blue square at the bottom corner of the blue rectangle.  Put 
you mouse over the blue square and when it becomes a double-headed arrow and 
drag down until the appropriate range is selected - the new data is included 
in the range.
4. Repeat the process for the purple highlight around the months.

You may be dragging to the right instead of down depend on your data layout.

-- 
Thanks,
Shane Devenshire


"Charles Eaves" wrote:

> I am trying to plot weekly sales in a given year.
> Eventually in a year, there will be 52 weeks of sales labeled date,this yr 
> sales,last yr. sales.
> I am using a line graph.
> Everything is working fine except:
> I would like excel to automatically update my chart for each week that I 
> have sales data for.
> In the near fuure,when I enter data for the months of  Oct,Nov,Dec and the 
> last part of Sept., I would like Excel to automatically update my chart.
> I have tried to in my data table, put all the weeks of the year in the table 
> and as I get sales data, enter them into the table.
> The chart will update but when the chart plots the data, it drops down to 
> zero because it doesn't have any sales data for the future months ahead with 
> no data.
> How do you get Excel to update my chart as I am updating my data table for 
> the months ahead as I get them?
> Thanks
> 
> 
> 
> 
0
9/29/2008 8:25:01 PM
Thanks everyone for the great help. I tried the second method: insert/ 
tables/ table and everything works fine. I use the filldown handle to create 
the next date in the table and I then enter the sales data and the chart is 
updated.
Where do you define the table is automatically dynamic incase I don't use 
the filldown handle method?
thanks again everyone
"Bernard Liengme" <bliengme@stfx.TRUENORTH.ca> wrote in message 
news:upP2J9kIJHA.3708@TK2MSFTNGP02.phx.gbl...
> You could use an OFFSET formula to make the chart dynamic.
> See
> http://peltiertech.com/Excel/Charts/Dynamics.html
> or
> http://office.microsoft.com/en-us/excel/HA011098011033.aspx
>
>
> OR
> Turn your data into an Excel 2007 Table by selecting the current data and 
> using Insert | Tables | Table. A chart made from a Table (must be defined 
> as such) is automatically dynamic
>
> best wishes
> -- 
> Bernard V Liengme
> Microsoft Excel MVP
> http://people.stfx.ca/bliengme
> remove caps from email
>
> "Charles Eaves" <charles.eaves@worldnet.att.net> wrote in message 
> news:%23x3V42kIJHA.1060@TK2MSFTNGP03.phx.gbl...
>>I am trying to plot weekly sales in a given year.
>> Eventually in a year, there will be 52 weeks of sales labeled date,this 
>> yr sales,last yr. sales.
>> I am using a line graph.
>> Everything is working fine except:
>> I would like excel to automatically update my chart for each week that I 
>> have sales data for.
>> In the near fuure,when I enter data for the months of  Oct,Nov,Dec and 
>> the last part of Sept., I would like Excel to automatically update my 
>> chart.
>> I have tried to in my data table, put all the weeks of the year in the 
>> table and as I get sales data, enter them into the table.
>> The chart will update but when the chart plots the data, it drops down to 
>> zero because it doesn't have any sales data for the future months ahead 
>> with no data.
>> How do you get Excel to update my chart as I am updating my data table 
>> for the months ahead as I get them?
>> Thanks
>>
>>
>>
>
> 


0
9/30/2008 2:07:48 PM
Reply:

Similar Artilces:

Converting Excel table with multi-line cells into Access DB
Hello ~ I need to export a MSWord table to an Access table. The original Word table contains multiple lines of text in each cell, separated with carriage-return(line return). .. I would like to retain these multiple lines within in each destination field in Access or Excel I have the following structure in my Word table: Column1: PhotoID (alphanumeric,single line) Column2: Image of the photos(jpeg/bmp) Column3: Description(text, multiline) Column4: Code#(alphanumeric, single line) Column5: Notes(text, multiline) In short, how do you tranfer those multi-line cells(with carriage-return...

Link to "Video Demos" in Get started tab in Office 2007 not workin
Hello, I have installed the Get Started tab in Office 2007. The link to the "Video Demos" is not working. Has MS changes the link ? How can I fix this ? I have installed this on many computers, how can I get this fixed on all of them... Thanks Jan ...

Line Spacing and Line Justification
I am a Publisher novice. On a blank page I pasted a PowerPoint Presentation slide. Below the slide I drew a line and copied that same line multiple times. How do I make the spacing between theses lines of equal ditance? How do I align these lines so that they are basically at the same starting point on the left side of the page? Any assistance would be greatly appreciated! Regards, Mercury The best way would be to create guidelines. Select your created line, while holding the ctrl, (the cursor will turn into a + sign) pull the copy to the next guideline. If you go the copy route, ...

Skipping a line by condition
Thanks for reading this. I have a report that is grouped by [positiontitle] in a two column report. It prints out ok. The thing is what I want it to do is when a new [positiontitle] comes up I want it to skip a line in the report. For example: Worker George Fred Non Worker Bill Tom The way it comes up now is: Worker George Tom Non Worker Bill Tom The [positiontitle] is located in the header of the report. Thanks for your help. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/2...

In Excel -- How do I cross out (strike through) text?
In Exel -- How do I cross out (strike through) text? Select the cell you want to format. Then, go to the Format menu, choose Cells, then the Font tab. There, check the "Strikethrough" checkbox. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "E.Walsh" <E.Walsh@discussions.microsoft.com> wrote in message news:6BD0B20E-DE93-4F5F-90F8-83CF908C622C@microsoft.com... > In Exel -- How do I cross out (strike through) text? ...

Windows 98 and Excel
I have Windows 98 on my home computer. I received an Excel spreadsheet and cannot open it in Works. What do I need to do to open the xls spreadsheet and work with it, i.e., simple functions like sorting? The simple answer is that you need Excel to "work" with it. You can get a free excel viewer but that's all you can do. -- Don Guillett SalesAid Software donaldb@281.com <anonymous@discussions.microsoft.com> wrote in message news:21fce01c45b80$7afc67c0$a001280a@phx.gbl... > I have Windows 98 on my home computer. I received an > Excel spreadsheet and cannot open...

Exporting the subreports to an excel
Hi , once i am able to create the sub reports when i export this to an excel i am not able to see the data.. can you please help me out with this.. Thanks in Advance Kasaiah ...

Excel 2002 Template Wizard
Doe anyone know if the Excel 2002 Template Wizard add-in will work in Excel 2003? I don't use xl2003, but I've seen posts that say that it does. John Fox wrote: > > Doe anyone know if the Excel 2002 Template Wizard add-in > will work in Excel 2003? -- Dave Peterson ec35720@msn.com ...

help with a scatter graph of conditioned values
I'm a big newbie in matter of charting and vba...I hope somenone can help me....here is my problem I need to plot a XY scatter graph. X and Y data values are mutually dependents one each other and they change under an extra condition that I want to represent qith a macro control. Hmmmm...let's try to give you an example: first of all I need to define EXTRA CONDITIONS as follows name S A 1 B 1,15 C 1,20 D 1,25 E 1,35 ..... ..... My goal is to have a control in cell C1 that traduces "if 'name'=A then 'S'=1, if &...

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...

Objectsi n Frames Word 2007
Is it possible to have floating objects in frames? I have a user that is having trouble doing that. Is there a trick to it? Thank you. Frames are no longer an option in Word. This functionality is in Publisher. "LisaK" wrote: > Is it possible to have floating objects in frames? I have a user that is > having trouble doing that. Is there a trick to it? > > Thank you. Frames are certainly still available in Word, just a little harder to access. You can add the Insert Frame button to the QAT or access it in the Legacy Forms tools on the Developer...

Excel 2 web
Using Excel VBA, i am trying to login to the web site. I was able to login. but after login, there is an image on top of the website. If i move mouse i will get more option to select. But I am not write code for mouse over on the image. Any suggestions. On Dec 7, 11:43=A0am, "fi.or.jp.de" <fi.or.jp...@gmail.com> wrote: > Using Excel VBA, i am trying to login to the web site. > > I was able to login. but after login, there is an image on top of the > website. > > If i move mouse i will get more option to select. > > But I am not w...

Line colors in a bargraph
ColorNum and m_Array are set before entry which works fine except for all bars are outlined in black which is not a major problem, but would like to eliminate. How do I draw the line in the code below a different color than the bars? Below is a snip from my BarChart() routine: CBrush BlackBrush(RGB(10,10,10)); CBrush CyanBrush(RGB(1,39,229)); CBrush MagentaBrush(RGB(176,4,174)); CBrush YellowBrush(RGB(187,176,3)); CBrush GrayBrush(RGB(230,230,230)); if (m_Array.GetSize()) { // ***** THE PROBLEM IS HERE WHERE I WOULD LIKE THE DRAWN LINE TO BE LIGHT GRAY ***** MemDC.Sele...

Please help me .... (CListCtrl multi-line column)
Dear all, I use VC++ 6.0 MFC CListCtrl. And, some data displayed in the control contains multiple lines. By default, the control displays the multiple-line text in a single line. In the multiple-line text, it uses "\r\n" (CR-LF). How can I set the column of CListCtrl to display the column text in multiple line like the data cell in Excel? -- Kathy This is the nearest i can find. Hope it helps http://www.codeguru.com/Cpp/controls/controls/lists,treesandcombos/article.php/c2291/ "Kathy" wrote: > Dear all, > > I use VC++ 6.0 MFC CListCtrl. And, some data di...

OWA - Hangs "Loading" Inbox
2 of our OWA servers have recently stopped functioning. The login appears ok and the interface starts to load, but the fonts are wrong (times new roman is shown) and the inbox doesn't load at all, showing "Loading..." I've searched the forums/web and can see many people had the same issue, but have found no fixes that work. "Error on Page" Line: 56 Char: 1 Error: Object expected. Any suggestions or help really will be gratefully received. Thanks I had this same issue except one of my owa boxes would work and the other one didn't. I don't have a KB ar...

Excel showing old or new version of file for 2
We have a matrix that we've built in Excel. It is a shared workbook. As you open the file, an image of the most recent content appears for just a few seconds, then it reverts to the old content. I believe we have other files where it happens as well, but the old data appears for just a few seconds, then the new data appears. The one I'm most concerned with is the one where the new data shows first, then is overlayed by the old data. I need to be able to get the new data. I have taken a snapshot of the screen via the print screen button, but I'd rather not recreate by hand, a...

saving to csv file from excel 2007
I need to save file to csv format to transfer to website...2003 always worked fine - 2007 doesn't - help?? -- jo Hi pourlance Office Button >SaveAs Or Office Button >SaveAs (menu) Other formats Or use F12 Choose CVS in the list and save the file -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "pourlance" <pourlance@discussions.microsoft.com> wrote in message news:11D7C094-BD37-4CBD-8E8B-6C3BB3736581@microsoft.com... >I need to save file to csv format to transfer to website...2003 always worked > fine - 2007 doesn't - help?? > ...

Line chart with value on the line
Hi, I have implement a line chart by thousands of data rows for a stock. Now I want to show values on 10 particular points with the lable E.g, one extra column for displaying the label. TradeDate Ticker Close Label 04/01/1999 1234 63.67 05/01/1999 1234 63 06/01/1999 1234 63 Mr. A buy 07/01/1999 1234 67.33 08/01/1999 1234 68.33 11/01/1999 1234 68.67 Mr. B buy Does anyone teach me how to do this ?? thanks Ginola "Ginola" <ginola@fake.email.com> schrieb im Newsbeitrag news:3fdc1aa6.8209734@msnews.microsoft.com... > Hi, > > I have implement a line chart b...

Too Much Chart Data for Excel Col Limits
I have a user who wants to generate a chart from a really large set of data, too much to fit within the 256 columns available in Excel 2003. (No, transposing the data won't work either, there's too much in BOTH directions!) Can anybody suggest a way to handle that? I thought about dumping all the values into an array variable, but don't know a way to make that the data source for a chart. 1) Have you recalled that there is a 32,000 limit on the size of a data series? 2) Will the chart be readable even with 1,000 data points? best wishes -- Bernard V Liengme Microsoft Exce...

removing black lines
Sometimes I type an underline bar across the page. At the end of the page I'll hit the return (enter) key. When I do, the underline bar turns into a big black bar. Then I can't get rid of it. It just stays there. How can I get rid of this black bar? See http://word.mvps.org/FAQs/Formatting/CantGetRidOfLine.htm -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> &...

Moving Office 2007 Pro OEM to new computer
Hi, I've got a customer that wants to buy Office 2007 Pro OEM CD/License and install and activate it on their current computer. In about 6 months, they plan on scraping/wiping their current computer and purchasing a new one. Can they then transfer/install and activate their OEM Office 2007 Pro to the new computer and activate it (I'm assuming by calling the activation call center to get a new number)? The old computer will be wiped/junked an no longer functional. Thanks No. OEM is good for that specific machine. Once it dies, that's it. That's why it ...

Can I use Excel to calculate VAT?
I'm a very new user of Office and have very little clue of whether or not such things are possible. Any help would be much appreciated. Exactly how you do it depends on your tax laws but these formulae should get you going. In each case I have assumed the starting data is in A1 and the VAT percentage is in B1 Calculate VAT to be added to a base price =ROUND(A1*B1/100,2) Calculate selling price from base price =ROUND(A1*(100+B1)/100,2) Calculate Base price from retail price =ROUND(A1*100/(100+B1),2) Depending on accounting rules you may need to use Roundup or Rounddown to get the ri...

want to filter out emails, with subjest line starting with a specfic sring
Hi All, I am using MS outlook 2003, I am trying to find out how can i create a rule to filter out emails with subject line starting with a specific string, but not to filter if that string is in the middle of subject line. i.e. lets take the sting as Foo Filer if Foo is in the starting don't filter if if the Foo is not in the starting. if any of you aware of the trick to make this happen please let me know, i tried to google it, but couldn't find anything related to it. Thanks Anand outlook doesn't offer anyway to build that sort of rule. If you can coun...

Line graph question
How do I get my date axes (the horizontal axes) to start at zero followed by the first day of the month. -- WH99 Hi, If your dates are entered in the spreadsheet as dates then you may have problems - Excel stores dates as numbers so today is 39594 and 0 would be January 0, 1900. This would tend to spread the x-axis out quite a ways. If your dates are not entered as dates then make the first entry 0,0 - that is put another entry in the data area whose date value is 0 and whose value is 0. There are potential problems with this approach - each entry will be spaced equidistant apart ...

gnatt chart in excel?!
Hi Im trying to create a schedule of classes in different locations. I would like to have them displayed in a “gnatt chart”, however im having difficulty trying to get the chart wizard to make it for me. The classes are scheduled at different dates, run for different lengths of time and are in different locations. There will also be classes in different locations at the same time. Is it possible to display this sort of data in a chart form in excel, and if it is, how do I do it? I can leave the start & finish times out, it is the dates and the class numbers that are the most im...