updating charts in excel

[chipy]

ok, here's another excel question.

i have a chart that was created based on two columns in an excel sheet. one column is week and the other is sales. An example is as follows (sorry about spacing):

week sales
Oct 08 10
Oct 15 05

Now, if I want to add more weeks with sales numbers, I can... but how can I get the chart to automatically reflect the newly added data?

thanks,
chipy

 

[Concillian]

The chart has a set range of rows. You can either add the data and edit fifteen different things in the chart, or you can do what I do, which is:

Insert a row between oct8 and oct15 rows.
Copy / paste (important to use copy and not cut) oct 15 data from the last row to the newly added row
clear / overwrite data in the last row

This has the effect of automatically adding a row into the chart everyplace you need it in one simple step. You just have to be careful about not duplicating data due to the copy/paste. I will generally highlight the row after copying and pasting and clear the data so I don't accidentally make a mistake.

 

[chipy]

thanks Concillian

it does work but i was hoping for something a little bit easier... especially since i won't be updating the file myself. it'll have to be super super easy for the person updating it.

i appreciate your solution and will keep it in mind! let me know if you come up with anything else.

chipy

 

[Concillian]

There is only one other way to do it, and that would be to insert a bunch of rows beforehand, then copy/paste and clear out the duplicate on the bottom row, then data can be entered up to the number of rows you inserted.

The downfall of this, is that the chart will display empty points for the blank rows you add.

Unfortunately I don't think there is a way to do what you want to do as a one button type of action without writing a macro. It wouldn't be a terribly complicated macro, but still macros seem to scare people off.

 

[Celstar]

all you need to do is hide the last row in the range

 

[PowerEngineer]

I agree with Concillian's posts; excel charting is not especially friendly to changes in the number of plotted points. You didn't specify the kind of chart you are using, but here's a way that might work for you if you can use X-Y plotting.

Set up room for some maximum number of entries (maybe 100) in two columns on Sheet 1. Let's say A1:A100 and B1:B100.

On Sheet 2, set up the following formulas (for example, in A10):

=IF(LEN(TRIM(Sheet1!A10))>0,Sheet1!A10,A9)

Copy this into A1:B100 on Sheet 2.

What this should do (if I avoided typos) is repeat the last entered numbers on Sheet 1 through the remaining rows on Sheet 2.

This means that if you chart the 100 points on Sheet 2, you see just the points as entered on Sheet 1. This is because all the identical points are plotted on top of each other. You can add new points onto Sheet 1 and they will automatically pop up on the chart. THIS ONLY WORKS FOR X-Y PLOTS.

Good Luck!

 

[skinsfan44]

No Concillian, there are about 5 ways to do it. I use this method and it works great.

HERE

EDIT: Now if I could just figure out how to post links!

 

[PowerEngineer]

Originally posted by: skinsfan44
No Concillian, there are about 5 ways to do it. I use this method and it works great.

HERE

EDIT: Now if I could just figure out how to post links!

I like it! I didn't realize that the SERIES function would actually take a named range, and never would have thought about using a formula to define a named range. Clever and obscure at the same time.

Thanks! :thumbsup: