It can be fairly challenging to combine data in Excel. There are two new functions HStack() and VStack() to combine dynamic arrays or ranges, by either stacking the data horizontally or vertically.
As shown in the example below by simply using =VSTACK(B2:D3,F2:H3) it’ll stack both cell ranges on top of each other. Using =HSTACK(B2:D3,F2:H3) will put both cell ranges side by side.
- HSTACK – stacks the arrays horizontally.
- VSTACK – stacks the arrays vertically.
Once stacked, the new dynamic array can be rearranged with Sort() or Sortby(), see rows 10-14 in this example.
HSTACK
Syntax
=HSTACK(array1,[array2],…)
Arguments
- array – the arrays to add.
VSTACK
Syntax
=VSTACK(array1,[array2],…)
Arguments
- array – the arrays to add.
Note: the formatting of the source ranges are not copied, just the values.
The sources do not have to be dynamic arrays.
Who gets it?
All the Excel array transformation features (Hstack/VStack, ToRow/ToCol, WrapRows/WrapCols, Take, Drop, ChooseRows/ChooseCols, Expand) are currently in the Insiders/beta releases of Excel 365 for Windows and Mac.
Excel gets new text splitting powers
Excel now has Dynamic Arrays – Windows, Mac and more …
