Setting Y axis scale on XY chart where X axis only shows partial Series

**Autum** · 04-04-2018, 09:11 PM

Hello All. I'm using Excel 2016
I have a simple time series XY chart of closing stock prices. To scroll through the data, rather than create a dynamic chart using range names, I am alternatively trying to do so by changing the X axis range [ eg. Series 1 on the chart is daily data which begins Jan 1 1970 and extends to Jan 1 2018, however to zoom in on the most recent 2 years I set the X range start point to Jan 1 2016 and end point to Jan 1 2018]. this works well, however I would like to control the Y axis scaling as excel will often use Zero as the minimum value. In my example of the 2 year period, I am unable to simply take the Min of series 1 as it extends back to 1970 and would provide the wrong value [ I need the min for the past two years ].
I was wondering if there was a way to determine the range of what is actually shown on the chart rather than the entire Series - and if so, I could take the Min of that range... or if there is some other way?
Thanks again..

**dflak** · 04-05-2018, 02:44 PM

T
his sounds like a job for a named dynamic range and then using those ranges as your source data in the charts. Without a sample spreadsheet, I can't give you specifics, but in general, here is how to do it:
http://www.utteraccess.com/wiki/Offs...Dynamic_Ranges
http://www.utteraccess.com/wiki/Dynamic_Charting

If you want to attach a sample workbook, here's how:
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

**Autum** · 04-08-2018, 10:30 PM

thanks for the links.. much appreciated. My spreadsheet has about 3000 rows of closing stock price data, with many additional columns of calculated cells which are also plotted. in total the graph might have up to 40 different series. from a processing/speed perspective I was wondering .. are the dynamic ranges cumbersome in terms of overhead? I felt that it was quite simple to just modify the X axis range displayed on the chart; vs. recalculating dynamic ranges for 40 series. thoughts? thanks again

**dflak** · 04-09-2018, 10:42 AM

I never noticed any overhead associated with named dynamic ranges. They seem to do what computers do very well: point to locations. It's when you start moving things around or do complex calculations that you start noticing overhead.

It may be a pain in the neck (or lower part of the anatomy) to have 40 named ranges, but only the first named range needs to have the calculation. The rest can be offset from it. So if you come up with a calculation for Plot_Date (the X-axis) that works then you can have other named ranges like Plot_Metric01 which =Offset(Plot_Date,0,1) meaning the same range as Plot_Date only one column to the right.

Attached is an example of dynamic charting. Set the target date and the chart will reflect a 10-day period.