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

Unlock this course with a free trial

Join today to access over 24,900 courses taught by industry experts.

EXPAND for automated calculations on expanding data

EXPAND for automated calculations on expanding data - Microsoft Excel Tutorial

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

Start my 1-month free trial Buy for my team

EXPAND for automated calculations on expanding data

“

- [Instructor] What if your formulas could grow with your data automatically? Well, the expand function gives you dynamic control over how big your data ranges and arrays should be, without having to manually resize them or break fragile references. Expand can be used whether you are adding new columns for scenarios, reshaping a data block for forecasts, or wanting your row totals to quite literally move themselves as time evolves and new data comes in. Expand is one of Excel's most underrated tools, and it can make your models far more flexible, scalable, and error proof. Let's take a look at an example. Up here, I'm going to type in =EXPAND, and let's take this one step at a time. Array, quite simply, is a range of information. Rows, I'm only selecting one row, so I'm going to bypass this. Columns is not the number of columns that I have, it's the number of columns that I want. So you can see here that I have 12 columns, but I really want this to be 13. What I'm going to do is go to…

Contents