Brought to you by:

Hi Reader,

Welcome to the Google Sheets Tips newsletter #354, your Monday morning espresso, in spreadsheet form!

In last week's newsletter we compared wide and tall data, and what the pros and cons of each data "shape" were.

Today, I want to show you how to move from one format to the other. How to transform tall data into wide data and vice versa.

Going in one direction is easy. Going back, in the other direction, is less so.

Find out which way round below!

➜ News

I.

It's that time of year when this newsletter takes a summer break!

I'll be back with issue 355 on the 18th August 2025. 🌞

II.

More details about the AI function in Google Sheets, including examples of more use cases.

​Read more here >>​

III.

Read/write support for Smart Chips in Google Sheets is now available with the Sheets API. This is welcome news for Apps Script developers!

​Read more here >>​

Brought to you by: BudgetSheet Track & Analyze Your Spending With BudgetSheet Automate the most tedious part of tracking your spending and preparing for taxes. BudgetSheet can automatically categorize and import all your bank transactions and track account balances in Google Sheets. No more manual copying & pasting from CSV exports for bank data! It's quick, secure, and free to try. Join over 15,000 satisfied users today! Try BudgetSheet For Free

➜ Sheets Tip #354: Turning Tall Data Into Wide Data And Vice Versa

We saw last week that tall data is good for analysis and pivot tables, because the data is in columns.

However, wide data is easy to read and good for charts because the data is in a 2-D grid, separated into series.

Here's a quick reminder of what these tables look like:

Now let's see how to move from one format to the other, and back again.

Let's start with the easier transformation.

Turning Tall Data Into Wide Data (easy)

This is the easier transformation of the two, because pivot tables work well with tall data.

Select a cell inside a tall dataset or table and go to the menu: Insert > Pivot table

Then we can create a 2-D wide dataset by selecting a column for the rows and a column for the column headings.

In our example, it looks like this:

Turning Wide Data into Tall Data (hard)

This is harder, but possible with formulas. It's sometimes called an unpivot.

Suppose we have a dataset in A1:E9.

Enter this formula in a blank cell:

(You can pick any emoji or symbol to separate the range references!)

This combines the headers, row labels and values in a grid:

Next, we use the TOCOL or FLATTEN functions to stack these vertically:

which looks like this in our Sheet:

Finally, we split the rows into columns with the handy SPLIT function with the emoji as the delimiter:

This puts our data into the correct tall format:

You can manually add the headings: Product, Region, Value

It's more difficult than creating the pivot table, but not too bad if you build the formula in steps (what I call the "Onion Framework").

With the knowledge from last week and today's newsletter, you'll know which data shape to use for pivot tables or charts. And how to get to it if you don't have it yet.

Never get stuck again!

Template

​Grab the template here >>​

Click on "Use Template" in the top right corner to make your own copy.

If you enjoyed this newsletter, please forward it to a friend who might enjoy it.

Have a great week!

Cheers,
Ben

P.S. Stop taking bad photos and enjoy the moment​