Hey guys – I have a couple of data sets in excel that I update for every month. The data set has the dates running across the x-axis, and every month I want to update the graphs so that they capture the data one cell to the right. For example, in order to update the graphs, I have to make sure they now capture cell F2 instead of E2. Is there a quick way to replace all the formulas in the graphs so that I can capture this? I notice that control-H (replace) doesn’t work, but am not sure if there is a fast way to search inside the formula of a table or graph. I have dozens of graphs containing different types of monthly data, and every month I want to make sure the graphs are stretched one month to the right to capture the updates…so any shortcuts would be appreciated. Thanks.
if you could create PIVOT tables/ charts instead of regular ones you can auto refresh the new data points. Wouldn’t work w/ scatter plots however.
Sounds like you want to have a cell that has a master count of the number of columns you have, using COUNT(), and then you want to use something like LOOKUP() that specifies your master array. Or you could just name your data area and rename it when you add data, perhaps with a Macro. To get that into a chart might be challenging, but I think if - when you make the chart - you select more columns than you currently have data for, the chart will ignore columns. So you just select a looooong space to your right when defining the chart data area. When you add new columns, they automatically show up on the chart.
There’s no way I can do anything to update scatter plots then? Hard to imagine that this is not possible…
I don’t know 
The reason my idea wouldn’t work is bc scatter plots are not available for pivot charts. If you can automate a normal chart however it should be possible. I just don’t know how- but I’m bad w/ charts of the non-pivot variety.
http://articles.techrepublic.com.com/5100-10878_11-6064314.html check this, may be of some use if i understand your problem
Here’s the situation: I have a simple data set that’s updated manually each month. I want to have scatterplots driven off this data set, such that the scatterplots are updated monthly to include a new month. I don’t want to use pivot tables here unless they’ll make my life a lot easier.
farney – that’s certainly one way to accomplish it. If I only had one graph and one set of data to plot against time, that would be really easy. However, there are several dozen monthly data sets – I could set it up as how you instructed, but it would take a while to “define” each set of data and update the graphs accordingly. At that point, it might just be easier to change the formulas manually just because it would take so long to set up the graphs in a manner that is described by that website. That’s why I’m thinking that replacing the formulas within the graphs/tables themselves would be the easiest way to roll. Speaking of which, I just found this site that describes how to change series formulas in VBA: http://peltiertech.com/Excel/Charts/ChgSrsFmla.html What do you guys think of this solution? I don’t know how to use VBA but now might be a good time to learn.
Hope this helps: http://spreadsheetpage.com/index.php/tip/update_charts_automatically_when_you_enter_new_data/
If you are dealing with monthly data, set your chart range from A:L. Then just hide all of the columns to the right that have no data. Excel will not plot values that are in hidden columns. So then each month all you need to do is unhide the next months column. So for example if your chart range is A:L and data is only in columns A:E, then hide F:L and your chart will only show plotted data for A:E.
wanderingcfa – SUPERB. Thanks a bunch. Thanks to everyone else to their solutions as well – I appreciate the ideas. Given the size of this data set, I figure the easiest thing to do is to follow wanderingcfa’s advice here.
numi Wrote: ------------------------------------------------------- > Here’s the situation: I have a simple data set > that’s updated manually each month. I want to have > scatterplots driven off this data set, such that > the scatterplots are updated monthly to include a > new month. I don’t want to use pivot tables here > unless they’ll make my life a lot easier. Depending on how complicated your data is, if you select the whole sheet, paste into a new document, move the rows over one and then re-paste into the sheet where the data was coming from, it might accomplish the same thing. Or you could keep your formula sheet on one tab and ctrl-e-s-v it onto the tab where you derive the graphs, and ctrl-e-s-v it one to the left every month as the months progress.
wanderingcfa Wrote: ------------------------------------------------------- > If you are dealing with monthly data, set your > chart range from A:L. Then just hide all of the > columns to the right that have no data. > > Excel will not plot values that are in hidden > columns. So then each month all you need to do is > unhide the next months column. > > > So for example if your chart range is A:L and data > is only in columns A:E, then hide F:L and your > chart will only show plotted data for A:E. Cool - nice. I will have to try that.
No problem. Glad I could do something productive before lunch.
Very cool. Will try to remember this.
I don’t think you even need to hide the columns. That was what I was trying to say in my post above. My version of Excel (admittedly Mac, but I can fire up XP to try windows too) will simply not plot anything in empty columns. When you fill them up (hidden or not), it automatically adds them. The only thing you have to do is chart out a section that includes both your existing data and a bunch of empty columns for future use.
wanderingcfa Wrote: ------------------------------------------------------- > If you are dealing with monthly data, set your > chart range from A:L. Then just hide all of the > columns to the right that have no data. > > Excel will not plot values that are in hidden > columns. So then each month all you need to do is > unhide the next months column. > > > So for example if your chart range is A:L and data > is only in columns A:E, then hide F:L and your > chart will only show plotted data for A:E. That’s a nice piece of info my friend, yes indeed.