How to Unpivot Data in Google Sheets

It’s very convenient to have data organized in a two-dimensional table with actual rows and columns. This layout works particularly well when you need to input data. However, there’s a catch: this format can make it rather challenging to analyze the data using formulas or to construct charts.

Consider this example: a sales tracker. You have several salespeople and track their performance daily in a spreadsheet, where you record the total number of sales per day, with one sheet dedicated to each week. For the sake of illustration, we’ll use a scaled-down version of the table but this method is equally effective for tables of any size.

Now, imagine having data accumulated over several weeks, spread across multiple sheets, and you want to analyze the overall performance dynamics as well as individual employee performance. To leverage formulas, you need to convert the tabular data into rows with the date, name, and sales volume all in a single row.

Of course, you could manually process the data, but that would be time-consuming, limited in scope, and prone to errors. There must be a more efficient solution.

Solution: Unpivot with Formulas

In spreadsheet parlance, this transformation process is called “unpivot,” which is essentially the reverse of creating pivot tables. Google Sheets lacks a built-in feature to unpivot data, this is where the following formula will help (ensure to adjust the ranges as per your requirements):

=ARRAYFORMULA(SPLIT(FLATTEN(B3:B4&"📗"&C2:E2&"📗"&C3:E4),"📗"))

Here’s what the formula looks like with the relevant ranges highlighted:

How it Works

The unpivot formula is composed of four main components. Let’s break them down:

1. ARRAYFORMULA: This function serves as the backbone of the entire formula since it enables the subsequent steps to process multiple cells simultaneously.

2. A2:A4 & "📗" & A1:B1 & "📗" & B2:C4: This segment utilizes concatenation to join values from the relevant ranges.

The “📗” emoji character is employed as a delimiter. It is crucial that this symbol does not appear in the data itself.

Note: Google Sheets might automatically convert dates into numbers, their internal representation. To revert them to dates, simply apply the appropriate date format to the cells.

3. FLATTEN: This function takes a range and reshapes it into a single column of values.

4. SPLIT: This function is deployed to segregate the concatenated strings into separate columns.

Level Up: Named Functions

While the unpivot formula is already highly effective, its complexity can make it difficult to interpret and reuse. Enter named functions, a feature introduced to Google Sheets in 2022. With named functions, you can create a new custom function once and reuse it throughout the document or even copy it from one document to another. Here’s how it can be defined:

Observe the UNPIVOT named function in action – it’s neater and more user-friendly:

---

Unpivoting your data in Google Sheets is a powerful way to transform it from a tabular format to a more analysis-friendly structure. This can be particularly useful when working with time series data or when you need to create charts and apply complex formulas. Additionally, leveraging the named functions in Google Sheets further enhances the readability and reusability of your formulas.

As an analyst or anyone working with data, mastering the art of unpivoting data can significantly boost your productivity and broaden the range of analyses you can perform.

Happy unpivoting!