You don’t need any plugins or Apps Script to unstack data in Google Sheets. All you need is my custom array formula.

Understanding Unstacking Data

Before we dive into unstacking data into corresponding groups in Google Sheets, let’s clarify what “unstacking data” means.

Stacked Data, also known as long-format data, has key characteristics that set it apart from unstacked (wide-format) data:

• In stacked data, each row generally shows one record or entry related to a specific detail. For example, in a dataset tracking sales, each row might represent an individual sale.
• Each row often includes an item, its category, and quantity. The next row may represent a different or the same item, possibly under the same or a different category, with its corresponding quantity.

Unstacking transforms stacked data into a format where categories or groups are represented as separate columns, allowing for easier analysis and visualization.

For instance, unstacking can organize items and their quantities under each respective category in separate columns, grouping data more visibly by category.

Side note: Long-format (stacked) data is especially useful for statistical analysis in Google Sheets. You can use functions like QUERY or the Pivot Table tool to summarize the data, and also apply lookup functions more efficiently.

Unstacking Two-Column Stacked Data in Google Sheets

Consider a sample dataset with country names in column A and their assigned groups in a football event in column B. This is stacked data since each row represents a single team.

When unstacking, there will be a key column. In this case, column B contains the groups assigned to each country.

You can use the following formula in cell D1 to unstack the data in columns A and B:

=IFNA(
   REDUCE(
      TOROW(,1), 
      TOCOL(UNIQUE(B1:B), 1), 
      LAMBDA(acc, val, HSTACK(acc, VSTACK(val, FILTER(A1:A, B1:B=val))))
   )
)

In this formula, replace B1:B with the key (category) column and A1:A with the other column.

Formula Breakdown

This formula is simple to implement, but it can be somewhat complex to understand, as it uses a custom LAMBDA function within the REDUCE function. Here’s a step-by-step explanation of the formula: LAMBDA(acc, val, HSTACK(acc, VSTACK(val, FILTER(A1:A, B1:B=val)))).

Understanding the REDUCE Function:

The REDUCE function takes an initial value (an empty array specified as TOROW(,1)) and applies the LAMBDA function to each value in the unique array: TOCOL(UNIQUE(B1:B), 1).

Key Elements of the LAMBDA Function:

1. FILTER(A1, B1=val) – This part filters the country names in A1:A where the group in B1:B matches val. Here, val refers to a unique group name from column B. For instance, if val is “Group A,” the formula will return:
   Russia
Saudi Arabia
Egypt
Uruguay
2. VSTACK(val, FILTER(A1, B1=val)) – This vertically stacks the val (e.g., “Group A”) with the filtered data, resulting in:
   Group A
Russia
Saudi Arabia
Egypt
Uruguay
3. HSTACK(acc, VSTACK(val, FILTER(A1, B1=val))) – The HSTACK function horizontally appends the accumulated result (acc) with the output from the previous step. In the LAMBDA function, acc refers to the accumulated data, and val represents each unique category in column B:
   LAMBDA(acc, val, HSTACK(acc, VSTACK(val, FILTER(A1:A, B1:B=val))))

As a result, you obtain the unstacked data.

Unstacking Three-Column Stacked Data in Google Sheets

In the next example, suppose we have item names in column A, their categories in column B, and quantities in column C.

To unstack the data based on the category in column B, we’ll modify the filter slightly.

Previously, the formula was:

FILTER(A1:A, B1:B=val)

Now, we want to include both A1:A and C1:C, so the filter becomes:

FILTER(HSTACK(A1:A, C1:C), B1:B=val)

The updated formula will be:

=IFNA(
   REDUCE(
      TOROW(,1), 
      TOCOL(UNIQUE(B1:B), 1), 
      LAMBDA(acc, val, HSTACK(acc, VSTACK(val, FILTER(HSTACK(A1:A, C1:C), B1:B=val))))
   )
)

Conclusion

That’s all about how to unstack data in Google Sheets. This process allows you to organize your data into a more usable format for analysis.

Resources

• How to Stack Data in Google Sheets: Tips and Tricks
• Unstack Multiple Form Responses in Google Sheets
• A Simple Formula to Unpivot a Dataset in Google Sheets
• Unpivot Excel Data Fast: Power Query & Dynamic Array Formula
• Split Your Google Sheet Data into Category-Specific Tables
• Dynamic Formula: Split a Table into Multiple Tables in Google Sheets