Excel Video 87 is our first take at the OFFSET function in Excel. Understanding how OFFSET works is critical to unlocking powerful, automatically updating charts. We’ll go through a complete example in Excel Video 87 and then do a similar example in Excel Video 88. The OFFSET function has five parameters or variables, each separated by a comma. The five variables are:
Reference: Where do you want to start (anchor) the range?
Rows: How many rows (either positive or negative numbers) do you want to move from the reference/anchor point?
Columns: How many columns (either positive or negative numbers) do you want to move from the reference/anchor point?
Height: How many rows do you want to include in your range?
Width: How many columns do you want to include in your range?
Early on, both Rows and Columns will be 0, since we’ll set the reference point to be exactly where we want it and we won’t need to move from there. In future videos, I’ll give you some situations where you may find it helpful to change Rows or Columns.
Generally, for data stored in rows like this example, the Height will be calculated using another Excel function and the Width will be 1. We’ll switch to data stored in columns in Excel Video 88 so you can see how the Height goes to 1 and the Width is calculated using another Excel function.
The other trick to using OFFSET is to know a couple of Excel functions to include inside the OFFSET function that can calculate on the fly and give you the appropriate number of rows or columns for your range. In this video, the function is COUNT, which gives you the total number of numeric cells greater than 0 in a given range. By combining COUNT with OFFSET, you get a dynamic range that automatically adjusts to the number of numeric cells greater than zero in a given range.
OFFSET is a pretty involved Excel function. I have several examples planned. We’ll change things up a little bit and review OFFSET again in Excel Video 88. Stay tuned.
