Practical use for Excel's EXPAND function | VSTACK mystery solved

In my previous video I uncovered something odd about VSTACK. Turns out, VSTACK is doing what it’s supposed to do. I show that in the video. And that leads to something fascinating: EXPAND.

The EXPAND function on its own seems strange and pointless. However, in this situation EXPAND saves the day in a practical example.

We have a start date and number of days and want to list each date. Once their listed, we’d like them to be compiled in a single array. The problem is, the rows don’t have an even number of dates. That’s where EXPAND comes in! EXPAND makes all rows equal so that the data can be both, dynamic and compiled in a single array.

You’ll also see the TEXT function used to convert a number into a date.

#DynamicArrays #EXPAND #ExcelTips

For a list of my Excel courses at Lynda/LinkedIn:
https://www.linkedin.com/learning/instructors/oz-du-soleil
There are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 3rd Edition
https://www.mrexcel.com/products/guerrilla-data-analysis-3rd-edition/
My old blog: http://datascopic.net/blog-2-2