Posted on Leave a comment

Unstack data using new dynamic array formulas

Microsoft introduced new Dynamic Array Formulas for Excel 365 users. In this article you will learn how to use SEQUENCE function to unstack your data.

You are provided a set of data. All the data is stacked in column A. Your task is to convert one column data range into multiple columns data range.

Each source data is in a different row. Headers of the table are in rows:1, 2, 3. We can find the data regarding the first product in rows 4, 5, 6 and the data concerning the second product in rows 7, 8, 9 and so on. You should put these rows numbers in a table. Each line of the table should only contain rows numbers of a single product. The table we need to create should look like the one in the picture below.

You can easily generate such a table using new Excel Dynamic Array Formula called SEQUENCE. Click an empty cell and start typing the formula. The first argument is the number of rows the formula should spill into. In the example it is 11(headers and ten products). The second argument is the number of the columns the formula spills into. In the example formula should spill into 3 columns (Product, Quantity and Sales)


The final step is to embed SEQUENCE function in INDEX function. INDEX functions returns a value from the cell at an intersection of a row and a column. But first you need to know the row’s and column’s number. The row number is returned by SEQUENCE function and the column number is 1 since the source data is entirely in one column i.e. column A.