Excel Video 88 continues our discussion of OFFSET. Instead of having our data in rows (so OFFSET’s height is variable and the width is 1), the data is in columns (so OFFSET’s height is 1 and the width is variable.) Just like in the last video, one of the tricks is to come up with an Excel function that keeps track of the variable height or width of your data. This time we’ll use the same COUNT function, but instead of counting numeric cells greater than zero in a set range like $B$2:$B$13, we’ll count all numeric cells greater than zero in row 2. As long as you don’t have any stray numbers in row 2, COUNT will give you an accurate way to keep track of how big you want your data range to be.
I’ll remind you in Excel Video 88 where the Name Manager is and where to put the name of your data range so that the chart will update automatically. Please note that my typing the OFFSET formula and variables underneath the chart is simply to make the OFFSET formula easier to see. You don’t need to do that in your charts, you simply need to put the OFFSET formula in Name Manager and assign a unique name.
Now that you’ve seen an OFFSET example in rows and columns, it’s time to make things a little more powerful and a little more complicated. In the next video, we’ll use OFFSET multiple times in the same chart and start looking at using other Excel functions inside of OFFSET.
