# Quick Excel Trick to Unstack Data

# from one Column to Multiple Columns

In this Excel tutorial, we’ll take a look at the easiest method of unstacking data.

The great thing about this method is that it’s dynamic, so if your source data changes, the stacked version updates automatically.

If you have ever tried to take a column of data in Excel like the following…

and transform it into something like this…

Many different solutions exist for just such a transformation. Some of those solutions include:

- writing a macro using VBA
- developing a Power Query script
- creating an array formula using CTRL-Shift-Enter
- writing advanced formulas using functions like INDEX , COUNTA, ROWS, and COLUMNS
- purchasing third-party add-ins that are programmed for just such a transformation

This post will show you what we consider the “laziest way” to unstack data. But don’t let the word “lazy” turn you away; this may be the FASTEST and easiest way solve the problem.

## Step 4: Create the Pattern References

We need to discover the pattern at which **App** names appear in the original stack of data. We can see that there is a new **App** name every **third** row, just as there is a new **Sale** value and a new **Profit** value every **third** row.

Using our “LG” replacement trick, create a second row of cell references in cells **C7** through **E7** that are offset by three rows each.

## Step 5: Create the Full List of References

Now that we have an established pattern for Excel to work with, highlight all the reference cells (**C6:E7**). Click and hold the **Fill Series** handle and drag down. Our original list runs down to row **87**. We will produce a list that runs a few references beyond **87** to allow for later expansion of the list.

Notice how Excel has recognized the row reference pattern in each column and continued the reference.

## Step 7: Observe Your Beautiful List of Transformed Data

Notice that we have zeroes in the last few rows of the new list. These will automatically update when new items are added to the original list.

If you wish not to see the zeroes, consider applying the following **custom number format** to the list.

Activate the **Format Cells** dialog box (**CTRL-1**) **-> Number (tab) -> Custom (category) -> Type (field)** and enter the following:

#,##0;-#,##0;

Custom number formats define how positive numbers, negative numbers, zeroes, and text appear. By defining a format for the positive and negative numbers, but omitting the formatting codes for zeroes, Excel will not be able to display a zero value in one of these customized cells.

## Formula Alternative

If you would rather create a formula, so as not to rely on the “lazy” method, the following formula will produce the desired result:

=INDEX($A$4:$A$87,ROWS($H$6:H6)+(COUNTA($H$5:$J$5)-1)*(ROWS($H$6:H6)-1)+COLUMNS($H$5:H5)-1)

Let’s break this formula down.

**$A$4:$A$87** is the original list of items in column **A**.

**ROWS($H$6:H6)** will count the rows in the defined range. The range expands by locking the first reference and leaving the second reference relative. When the formula is repeated in the descending rows, the row count will increment (1, then 2, then 3, etc…).

**COUNTA($H$5:$J$5)** will return the number of non-empty cells in the range **H5:J5**; in this case, **3**.

**COLUMNS($H$5:H5)** will count the columns in the defined range. The range expands by locking the first reference and leaving the second reference relative. When the formula is repeated in the adjacent columns, the column count will increment (**1**, then **2**, then **3**).

Using the first instance of the formula in cell **H6** to retrieve the first **App**, the initial evaluations appear as follows:

=INDEX($A$4:$A$87,**1+(3-1)*(1-1)+1-1**)

Which reduces to…

=INDEX($A$4:$A$87,**1+(2)*(0)+0**)

Further reducing to…

=INDEX($A$4:$A$87,**1**)

This will return the item in the **first** cell of the list; in this case, cell **A4** (the app named “WenCal”).

If we look at this for the formula on the same row but for the **Sales**, we see the following logic:

=INDEX($A$4:$A$87,**1+(3-1)*(1-1)+2-1**)

Which reduces to…

=INDEX($A$4:$A$87,**1+(2)*(0)+1**)

Further reducing to…

=INDEX($A$4:$A$87,**2**)

This will return the item in the **second** cell of the list; in this case, cell **A5** (the sale value 14432).

The formula requires a fair amount of analysis, but to summarize…

**ROWS($H$6:H6)+(COUNTA($H$5:$J$5)-1)*(ROWS($H$6:H6)** will calculate the location of the next app in the original list.

**COLUMNS($H$5:H5)-1** will add 0, 1, or 2 to the previous calculation. This offsets the position to allow for the return of the associated sale or profit value.

Using the Evaluate Formula tool located on the **Formulas** tab, **Formula Auditing** group will aid greatly in dissecting the logic in a step-by-step fashion.

## Additional Resources

Additional functions that can aid in the transformation of data can be found in the following videos:

## Practice Workbook

Feel free to Download the Workbook HERE.

AliJanuary 17, 2019 at 11:41 amThank you

Muhammad AhmedJanuary 25, 2019 at 5:56 amNice Approach Leila!

But what when the data is not consistent, Suppose in the above scenario there are two field (Sales and Profit ), if in the next row cost and other item also added, How to approach in this scenario. ?

KayaFebruary 22, 2019 at 7:23 pmSame question here. Would love to know how to tackle this issue.

Bryon SmedleyApril 7, 2019 at 3:00 pmIn such a scenario you are better served using a more capable tool, such as Power Query, to clean and transform your data into a usable state.

NabilJuly 23, 2019 at 4:03 pmThis is so brilliant because it is so simple!

You’ve just saved me soo much work. Thanks,

Leila GharaniJuly 25, 2019 at 6:58 amI’m glad to hear that Nabil!

AnuraNovember 10, 2019 at 10:26 amExcellent Explanation. Thank you Leila.