How to Turn Wide Data into Tall Data in Google Sheets

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.

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.

difference between wide and tall data in google sheets

 

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. 

reformat to convert wide data to tall data in google sheets

 

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.

flatten unpivoted data

 

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.

split the flattened data

 

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:

  1. First, let’s add the column headers for our tall data. We want to have the following columns: Quarter, Product, and Sales.
    add headers for tall-format data
  2. 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). 
  3. The string should look something like this B1:E1&”|”&A2:A5&”|”&B2:E4.
  4. We input this snippet into the unpivot formula to form this: =ArrayFormula(SPLIT(FLATTEN(B1:E1&"|"&A2:A5&"|"&B2:E5),"|")). Place this in cell A8 and hit the Enter key.
  5. We finally have the output in the cell range A8:C23. We have successfully converted wide format data to tall format data.
    successfully convert wide to tall data in google sheets

 

 

Frequently Asked Questions (FAQ)

  1. 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!

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like