How to keep Chart Range Fixed In Excel

[Scooby62]

Hi there.

I have a situation where on a weekly basis I am updating a data source (spreadsheet) by adding a column on the right side and deleting a column on the left side. This creates a rolling type of chart for dates.

Problem is that the chart based upon the data source keeps changing and reverting back to standard labels, etc.

My question is: Is there a way to fix all or several of the chart aspects so they never change? I want the range to always be the same.

Hopefully someone can help me.

Thanks in advance.

 

[sahmiele]

Instead of deleting columns, use clear contents, then copy and paste the columns, and add your new column on top of the far right column. You also might want to play with the $ signs in your graph series to lock them in.

 

[SkipVought]

Hi,

Why delete ANYTHING?

What you have is valid historical data that just MIGHT be invaluable at some time in the future.

Take a look at How can I rename a table as it changes size faq68-1331

In particular the OFFSET function which defines a RANGE. Using the second or third arguments to change the relative offset, you can create a moving "window". Put that offset value in a cell and with a simple entry you can displace the range each week after you add you new column of data.

It would be a simple paste and incriment the offset value by one -- a MUCH better solution than deleting good suff that you THINK that you'll never need. HA! Been there. Done that. BAD NEWS!

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[Scooby62]

You guys are Gods!!! Thank you very much.