Brought to you by:

Hi Reader,

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

Today we're looking at the shape of data. It's foundational concept that underpins pivot tables and charts in Google Sheets.

Once you understand the difference between wide data (for charts) and tall data (for pivot tables), you'll be off to the races!

This week is all about the pros and cons of each data shape.

Next week, I'll show you how to use formulas and pivot tables to transform wide data into tall data, and vice versa.

➜ News

I.
​This Thursday, I'm speaking on a webinar with my friends at GRID, exploring the relationship between spreadsheets and AI.

We'll talk about the current landscape and where we're headed.

Join this free webinar at 12 pm Eastern Time, this Thursday, June 26th.

​Register for free 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 #353: The Shape of Data (Wide vs. Tall)

In this tip, we look at wide vs. tall data or spreadsheet vs. database format, and what the pros and cons of each format are.

It's a super important concept to grasp. Not only for working with data in Google Sheets, but also to have a broader understanding of how data and databases work.

It'll help you understand how pivot tables and charts work in Google Sheets much more deeply.

Wide data

Wide data refers to data in a 2-D grid layout, meaning that information is recorded in both the row labels AND the column headings.

Consider this table:

When we read across a row, we also look up to the heading to determine the region that each value is attached to.

For example, Product 3 for East has a value of 9.

Each column is treated as a separate series, which is why this format works well for charts but not for pivot tables.

For example, it's impossible to calculate an average value across the regions in a pivot table.

Pros of Wide data

• Easy to add row and column totals
• Correct format for Google Chart tool, with data in series

Cons of Wide data

• Wrong format for pivot tables
• Data captured in header row, which is not machine readable
• Hard to turn wide data into tall data

Tall data

Tall data refers to data in a tall orientation. All data for a specific series (e.g. Region) is recorded in a single column.

Consider this tall dataset, which contains the same data as the wide table above:

This time, each product has 4 rows, with one row per each region.

Pros of Tall data

• Best format for analysis
• Best format for pivot tables
• Easy to transform into wide format data

Cons of Tall data

• Wrong format for the Google chart tool
• Best format for machines to read (how databases work)
• Can be harder for humans to read with a lot more rows

Exercises

​Open the template >>​

👉 Compare the two data tables above and identify the differences.

👉 Take the wide dataset and try creating a pivot table that calculates the average value for each product. Then create a chart based on the wide data and see what it looks like.

👉 Take the tall data and try calculating the average product value in a pivot table. Then see what happens when you create a chart.

👉 Which dataset works best with pivot tables?

👉 Which dataset works best with charts?

Next week

We'll look at how to go from wide to tall (unpivot) and how to go from tall to wide (pivot).

One is significantly easier than the other!

But after next week, you'll know how to do both. So until then, happy formula-rizing.

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

Have a great week!

Cheers,
Ben

P.S. End of the workday in the cartography department​