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 1: Setup the Headings

In our sample file, we will establish the following headings in cells **C5** through **E5**.

## Step 2: Create the Reference Formulas

Now create cell references in cells **C6** through **E6** that create a structure that represents *the way you wish the data looked*.

## Step 3: Implement the “LG” Replacement Trick

Replace the **equal signs** in cells **C6** through **E6** with the letters “**lg**” (*these are my initials; feel free to personalize this trick with your own initials.*)

## 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 6: Convert the “LG” References to Real Formulas

Select all the cells that contain “LG” references and perform the following **Find/Replace** operation (*Ctrl‑H*):

- Find: “lg” (
*exclude the double-quotes*) - Replace with: = (
*equal sign*) - Replace All

Close the **Find and Replace** dialog box when complete.

## 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.

Featured Course

## Black Belt Excel Package

## Bonus Step: Adding Additional Data

Because we included additional rows of cell references, if we add a new entry to the bottom of our original stacked list in Column A…

…the newly added data automatically appears at the bottom of our transposed stack of data.

## 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.

### Leila Gharani

I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.

My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.