From the course: Excel for Financial Planning and Analysis (FP&A)

Unlock the full course today

Join today to access over 23,200 courses taught by industry experts.

Using OFFSET for dynamic ranges

Using OFFSET for dynamic ranges - Microsoft Excel Tutorial

From the course: Excel for Financial Planning and Analysis (FP&A)

Start my 1-month free trial Buy for my team

Using OFFSET for dynamic ranges

“

- [Instructor] So what does all of this offset functionality do for us in FP&A? Well, it allows us to dynamically capture ranges and minimize the manual work that we have to do to update them. Let's go back to Marketing Expenses 2018, and this time, let's go down to line 49. Here you can see that I have this formula that says equals offset, A34 comma blank comma, one comma blank comma A49. Well, there's a bit to decipher here. So let's start out with the first argument. Offset reference, rows, columns, height and width. To start with, this says A34. So if I move up, this is saying, let's start right here at A34 in the cell that contains the words secondary research. Next, it's saying let's not move any rows down or up. So it stays exactly where it is. The next argument is the column movement. Column movement being a +1. This is moving the formula reference one cell to the right. So it's saying let's start out at A34.…

Contents