We can use several Google Sheets functions to **convert a wide format dataset into a long or tall dataset.**

A characteristic of wide tables is that they have a column for each variable. Tall format data has values that repeat in a column. Tall data is a much better format when creating pivot tables.

##### Table of Contents

Both formats are useful in different situations.

Wide-format data is useful when setting up a dataset for a chart or visualization. It’s also the easiest format to analyze because it’s easy to see the values for each variable.

A tall format is most useful when you need to have a base dataset to create a pivot table from. Wide data converts a portion of the information in the column headings, which cannot be used when creating a pivot table.

To unpivot a table, we’ll have to flatten the data. This involves selecting a range and “flattening” it into a single column. The FLATTEN function allows us to do this quite easily. We also need to do some formatting tricks to get the column headers and split them correctly for the final result. We’ll be using the SPLIT function to work on this.

Let’s learn how to write the unpivot formula ourselves in Google Sheets and later test out the formula with an actual wide-format dataset.

**A Real Example of Turning Wide Data into Tall Data**

Let’s look at a real example of using the unpivot formula being used in a Google Sheets spreadsheet.

In the spreadsheet seen below, we have two tables. The first table is in a wide format. Each pairing of quarter and product is shown as a row and column pair.

The second table, on the other hand, has twelve rows. Each row corresponds to a product and quarter pair, with the corresponding sales as the third row. It’s taller in the sense that there are fewer columns and more rows than its above counterpart.

To get the values in the second table, we just need to use the following formula:

=ArrayFormula(SPLIT(FLATTEN(B1:E1&"|"&A2:A4&"|"&B2:E4),"|"))

Let’s break down what this formula does.

First, we want to combine the row headings with the column headings. We can do this with an ArrayFormula. The ArrayFormula function allows us to manipulate and return multiple cells in our dataset.

The formula used to achieve the result above is the following:

=ArrayFormula(B1:E1&"|"&A2:A4&"|"&B2:E4)

The ArrayFormula combined the row headers, column headers, and numerical values. Each variable is separated by a delimiter, since we’ll have to split it later on.

After combining the headers and values, we’ll also need to flatten the data. We can use the FLATTEN function to achieve the result seen below.

The formula used to get the result above is the following:

=ArrayFormula(FLATTEN(B1:E1&"|"&A2:A4&"|"&B2:E4))

Lastly, we use the `SPLIT`

function to split our flattened data into separate columns.

You can make your own copy of the spreadsheet above using the link attached below.

If you’re ready to try out the unpivot function in Google Sheets, let’s start using it on a dataset ourselves!

**How to Turn Wide Data into Tall Data in Google Sheets**

In this section, we will explain each step you need to start turning wide data into tall data in Google Sheets. This guide will show you how to unpivot the wide format dataset seen in the previous example.

Follow these easy steps to start using the unpivot formula:

- First, let’s add the column headers for our tall data. We want to have the following columns: Quarter, Product, and Sales.

- Next, we need to sort out the string we’ll be forming by combining the row headers, column headers, and numerical data. In this example, we should start with column headers (Quarter). This is then followed by the row headers (Product), then lastly, the numerical data (Sales).
- The string should look something like this

.**B1:E1&”|”&A2:A5&”|”&B2:E4** - We input this snippet into the unpivot formula to form this:

. Place this in cell**=ArrayFormula(SPLIT(FLATTEN(B1:E1&"|"&A2:A5&"|"&B2:E5),"|"))****A8**and hit the Enter key. - We finally have the output in the cell range
**A8:C23**. We have successfully converted wide format data to tall format data.

**Frequently Asked Questions (FAQ)**

**Why do I need to convert my wide data?**

Tall format data is easier to convert into a pivot table in case you need to cut your data a different way. The long format is efficient to use when working with various plotting and data analytics software since it’s more “computer-readable”.

That’s all you need to remember to start using the unpivot formula in Google Sheets. This step-by-step guide shows how easy it is to turn a wide format dataset into tall data in Google Sheets.

The unpivot formula is just one example of a helpful formula you can make in Google Sheets. With so many other Google Sheets functions out there, you can surely find one that can help you best understand your data.

Are you interested in learning more about what Google Sheets can do? Stay notified of new Google Sheets guides like this by subscribing to our newsletter!