Excel Video 136 takes advantage of the OFFSET tricks I discussed starting with Excel Video 85. If you missed those videos or it’s been a while since you’ve used OFFSET, spend a few minutes watching the videos starting with Excel Video 85. The beauty of OFFSET is that without changing anything on my chart, every time the value in cell E1 changes, the collections I’m charting and the month range on the horizontal axis update automatically.
OFFSET has 5 parameters, the cell reference where you want to start, how many rows to move, how many columns to move, how high (how many rows) do you want your range, and how wide (how many columns) do you want in your range. The first three parameters are required, the height and width are optional. You’ll see that when I calculate the collections range, I simply base that range off of the range of months I created first.
Watch for one more trick that I’ve added since the OFFSET videos starting in Excel Video 85. If you want to see what the range is for a given name, simply type the name to the left of the formula bar. Excel will highlight the current range based on OFFSET for you to make it easier for you to chart exactly what you want to.
OFFSET can be a bit of work to set up, but if you’re going to use a chart repeatedly and your data is going to change or you want to change the way you look at your data, OFFSET can save you a ton of time in the long run. Once we have a named range using OFFSET, the next step is to use those names in setting up our charts. We’ll do that in the next Excel Video.
