18 best practices for working with data in Google Sheets

This article outlines 18 best practices for working with data in Google Sheets.

It’s a compilation of my own experiences of working with data in spreadsheets for 15+ years, along with the opinions of others I’ve worked with and reports and articles I’ve read online.

By no means is it meant to be exhaustive or the last word on the subject, but if you follow these guidelines, you should have a robust data workflow.

Why bother?

Following these best practices for working with data will make you and your team work more efficiently and reduce the chance of errors (human or computer) creeping in. It’ll make your work easier to follow and understand, and add value to your team’s or client’s workflow process. It’s a good habit to have, and it’ll serve you well as you progress with your data career.

Contents

  1. Organize your data
  2. Keep a backup copy of your data
  3. Document the steps you take
  4. Go with wide-format data tables
  5. Use good, consistent names
  6. Use data validation for data entry
  7. Even better, use Google Forms for data entry
  8. One cell = one piece of information
  9. Distinguish columns you add
  10. Don’t use formatting to convey data
  11. Add an index column for sorting & referencing
  12. Format the header row
  13. Freeze the header row
  14. Turn formulas into static values after use
  15. Keep copies of your formulas
  16. Create named ranges for your datasets
  17. Avoid merged cells
  18. Tell the story of one row
NEW DATA ANALYSIS COURSE LAUNCHES FRIDAY AUGUST 3RD!

Data Analysis course
Data Analysis with Google Sheets, teaching you how to make data-driven decisions in Google Sheets, is launching at the beginning of August.

18 best practices for working with data

Continue reading 18 best practices for working with data in Google Sheets

How to create and interpret a Scatterplot in Google Sheets

Whenever I’ve taught data analysis classes or data visualization classes, for General Assembly or privately or online, I find that the humble scatterplot is often poorly understood.

Perhaps it’s because they’re less common than simple bar charts, line charts or pie charts? Or maybe it’s because they take a bit more mental effort to understand what they’re telling us?

Regardless, they’re a crucial tool for analyzing data, so it’s important to master them. This post looks at the meaning of scatterplots and how to create them in Google Sheets.

What is a scatterplot?

Simply put, a scatterplot is a chart which uses coordinates to show values in a 2-dimensional space.

In other words, there are two variables which are represented by the x- and y-axes.

scatterplot in google sheets

In this example, the scatterplot shows the relationship between pageviews of a website and the number of signups that website received. As you can see, when the number of pageviews increases, the number of signups tends to also increase. They are positively correlated, but more on that in a minute.

Often the variable along the x-axis is the independent variable, which is the variable under the control of the experimenter, and the variable up the y-axis is called the dependent variable, or measured variable, because it’s the variable being observed to see how it changes when the independent variable changes.

It’s possible for both variables to be independent, in which case it doesn’t matter which axis they’re plotted on and the scatterplot shows any correlation between the two.

NEW DATA ANALYSIS COURSE LAUNCHES FRIDAY AUGUST 3RD!

Data Analysis course
Data Analysis with Google Sheets, teaching you how to make data-driven decisions in Google Sheets, is launching at the beginning of August.

Continue reading How to create and interpret a Scatterplot in Google Sheets

What the world’s richest man can teach us about averages

This is a story about a bar, 10 regular folks and the world’s richest man. Somewhere along the way, we’ll seek to demonstrate the robustness of the different average measures, but more on that in a minute.

I want you to picture your favourite bar or pub.

For me, it might be a pint of ale at The Dickens Inn, near the River Thames in London:

Dickens Inn London pub

I should just finish this blog post here, and we could all spend the rest of the day in happy reverie, supping our favourite tipple.

Alas, that won’t do! We have work to do and things to learn, so let’s get started.

The dataset

Imagine ten friends, all regular folks, sitting at the bar, eating and drinking, chatting and laughing. A most convivial scene. The beer tastes delicious of course, the floor is dappled with sunlight and the comforting aroma of Pie & Mash wafts by their nostrils. Anyway, I digress.

Let us play a little game. Our subjects don’t mind because they’re fictional.

We ask them all to write down their salaries in our Google Sheet, so we have the following results:

Ten salary values

Good. That’s our dataset.

Calculating the averages

Continue reading What the world’s richest man can teach us about averages

Checkboxes (☑️) are now available in Google Sheets! Here’s three ways you can use them.

Checkboxes are now available in Google Sheets!

They give you a visual way to toggle between boolean values (true and false) in a spreadsheet cell. This opens up all sorts of opportunities to make your Sheets more interactive.

Adding a checkbox

You’ll find checkboxes under the Insert menu:

Insert checkboxes menu

At the moment they have a NEW label next to them, but that disappears after a while.

FALSE status

When you add a checkbox, it will show up in the cell or range of cells that you have highlighted, and it will be unchecked. If you look in the formula bar, the cell has a value of FALSE.

This means you can link to this checkbox cell with any formula, for example an IF statement, and it will behave as a FALSE value (when it’s unchecked).

False checkboxes

TRUE status

When you click on the checkbox itself, it will become checked (shown by a tick mark and grey background) and the cell value will change to TRUE. Again, you can use this in your formulas.

true checkboxes

Advanced Checkbox Options

Continue reading Checkboxes (☑️) are now available in Google Sheets! Here’s three ways you can use them.

Slow Google Sheets? 🐢
Here are 27 ideas to try today

Slow Google Sheets?

We’ve all been there, stuck watching the little loading bar creep slowly, frustratingly to it’s conclusion:

Slow Google Sheets loading bar

How can you speed up a slow Google Sheet?

First off, this is a difficult question to answer because there are so many factors that may or may not be causing you to have a slow Google Sheet.

What follows in this article is some suggested optimization strategies and some research into what causes slow Google Sheets.


Strategies to speed up Google Sheets

  1. How to recognize slow Google Sheets (details)
  2. Know the size limits of Google Sheets (details)
  3. Measure a Google Sheet’s size (details)
  4. Measure a Google Sheet’s calculation speed (details)
  5. Delete un-used cells (details)
  6. Convert formulas to static values wherever possible (details)
  7. Use closed range references (details)
  8. Remove volatile functions or use with caution (details)
  9. Vlookup strategies (details)
  10. Index-Match strategies (details)
  11. Query function strategies (details)
  12. Array Formula strategies (details)
  13. Import Formula strategies (details)
  14. Google Finance function strategies (details)
  15. Use IF statements to manage formula calls (details)
  16. Manage expensive formulas with a control switch (details)
  17. Use Filter, Unique and Array_Constrain functions to create smaller helper tables (details)
  18. Avoid long calculation chains (details)
  19. Reference data on the same Sheet (details)
  20. Use helper columns (details)
  21. Split your slow Google Sheet into separate Sheets (details)
  22. Use Conditional Formatting sparingly (details)
  23. Leverage the power of Apps Script (details)
  24. Use custom formulas sparingly (details)
  25. Other troubleshooting tips for slow Google Sheets (details)
  26. Understand changes in the cloud can take time to propagate (details)
  27. Know when it’s time to move to a database (details)

Continue reading Slow Google Sheets? 🐢
Here are 27 ideas to try today