My new course, Data Analysis with Google Sheets, is open for enrollment!

Data Analysis with Google Sheets course

Are you drowning in data, but not using it to make better decisions?

Do you run a small business and want to use your data to inform your strategy?

Has your business migrated from Excel to Google Sheets, and you’re looking to further your skills?

My new course, Data Analysis with Google Sheets, is now open for enrollment!

(For the next week, until Thursday 9th August at midnight EST, I’m offering $25 off the regular price of the course.)

In this course, you’ll learn how to:

  • Understand and describe what your data tells you
  • Predict what things might look like in the future
  • Understand your customers to better serve them
  • Discover what factors affect your business
  • Best practices for doing all of this…
  • …and much, much more!

Working with real-world examples, including example datasets from industries like sales, marketing, real-estate and social media, you’ll learn how to make the most of your business data today.

Now you can learn data analysis, using the same techniques and best practices I’ve spent the past 15 years learning.

This course will teach you the key foundational techniques in data analysis, even if you’re new to this topic.

Here’s what the first batch of students had to say about the course:

What others say about the course:

Charlie Headshot“I will definitely be using the statistical functions to test whether the conclusions of my analysis are statistically significant or not.

I have also learnt a whole number of little tips and tricks that were dropped in throughout the course.

Ben is a seriously knowledgeable tutor, and presents the information in a way that is easy to follow.”

Charlie Pearson, PricewaterhouseCoopers


Geordie Headshot“I was really excited by the idea of using an application I was familiar with, Google Sheets, to complete data analysis that had seemed so challenging and foreign before.

I think people like me, who are not data scientists but have a passion for statistics and analytics, and want to find an easy entry level to start playing with data, would benefit from this course.

I absolutely love this course!”

Geordie Frost, Bang Digital


Devin Headshot“I wanted to learn more about what I can do with Google Sheets to get an idea about how my agency can benefit. My SEO company uses Google almost entirely for all our project management, etc.

I take every course from Ben Collins. I’m always looking to up my data game. If you can understand your Data you can make correct decisions about your business and *almost* guarantee success.”

– Devin Schumacher, The Search Marketing Company


Enroll now to get:

✅ Actionable, 5 – 10 minute video lessons teaching key data analysis topics

✅ All raw datasets for you to follow along

✅ All solution files for your own use

✅ Access to the private course Facebook group for Q&A

✅ Certificate upon completion

Are you ready to learn how to use Google Sheets to make data-driven decisions?

Any other questions? Leave a comment or drop me a line here.

Exciting new features coming to Google Sheets

26 July 2018: I’ve been at the Google Next cloud conference this week, in San Francisco. They announced a ton of exciting new features and products for both G Suite and the Google Cloud Platform.

Google Next conference

The Google Sheets product team announced a bunch of exciting new features coming soon to Google Sheets.

Here’s a brief recap:

5 million cells! (Sneak peek) 🔥

Nice! A big jump in the size of data we can work with in Sheets. This will open up Sheets for bigger data projects now.

This was shown as a “sneak peek” so I don’t have a date as to when this change will happen. Hopefully soon, since they announced it!

Slicers (sneak peek) 🔥

This is a great addition for those of us who build reports and dashboards. Slicers are like checkbox buttons we can add to pivot tables and charts to make them much more interactive from a user stand point.

You’ll be able to add a slicer for a given field so that a user can then filter to just see the data they want.

It’ll be much more functional and elegant than the data validation drop-down method or checkbox methods you can use at the moment.

Google Sheets slicers

Charting upgrades (sneak peek) 🔥

It’s great to see charts getting some love! It’s one area where Google Sheet has fairly limited functionality, but we’ll soon have much more granular control over how our charts look.

For example, the updates will include the option to color datapoints individually (as shown in this image):

Google Sheets chart update

Pivot Table upgrades (recently launched) 🔥

Pivot tables recently got a facelift, with a new, more user-friendly UX.

Even more useful though, pivot tables now have the option to group data (for example to group dates into months, or quarters etc.) and drill-down on data (so you can select an aggregated record and see all the data behind it with a single click).

These are really, really strong updates to Pivot Tables and dramatically increase the power of pivot tables.

Google Sheets Pivot Tables

I’ll be working on updating the Data Cleaning and Pivot Table course later this summer to showcase the new UX and features.

BigQuery Data Integration (sneak peek) 🔥

There’s been a huge buzz around BigQuery this week, so it was only natural that they announced a native connector for Sheets and BigQuery. It’s in beta pre-release at the moment.

I’ve enjoyed learning more about BigQuery this week and I’m really excited to start using it to build data pipelines involving Sheets and/or Data Studio.

BigQuery to Google Sheets connector

Partner Integrations (sneak peek) 🔥

The team announced several new data integrations during the session. They spent time discussing what they’re working on to bring data from web services into Sheets so you can analyze it.

Three new integrations were announced:

Salesforce and Sheets

You’ll soon be able to export Salesforce data into Sheets with a single click. Salesforce will also be rolling out a feature where you can work on your data in a Google Sheet that is embedded inside of Salesforce.

Sheets saved in Box

You’ll soon be able to work with Google Docs but save the files into your Box account, i.e. use Box instead of Drive as your cloud storage. This makes a lot of sense if you’re already setup on the Box platform.

The team did a live demo showing the collaborative features live from a Box hosted Google Slide deck. Super slick!

SAP to Sheets

You’ll soon be able to export directly from SAP to Sheets.

Other notable updates in the works 🔥

> Text to columns will soon support fixed width splits, which is a useful upgrade.

> Continuing improvement of the Explore feature, which lets users ask questions about their data and uses natural language machine learning to extract answers and suggest insights.

> Improved printing options to meet enterprise needs.

> Images in cells, which stay with that cell even when you move it or insert other rows or columns. Currently you can insert floating images or use the IMAGE function to insert into a cell. Neither is ideal however, so this is a nice touch.

The session recording

Check out the recording of the session from the Google Next 18 conference:

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
CHECK OUT MY NEW DATA ANALYSIS COURSE

Data Analysis course
Data Analysis with Google Sheets, will teach you how to make data-driven decisions using Google Sheets.

18 best practices for working with data

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

Thoughts on productivity

Slow Google Sheets loading bar

I’ve been thinking about productivity a lot in the past year.

I’ve had to.

Life is busier now than it’s ever been.

My wife and I have a young family (two sons under the age of three) so we have our hands full at home. We both work full time and have ambitious career goals.

Balancing these two worlds has undoubtedly been the most challenging puzzle of my life thus far.

In an earlier stage of my career, when time seemed to be an almost unlimited commodity compared to today, I could work until 9, 10 or 11pm (or later) no problem. Work at the weekend if necessary.

Now, with a young family I don’t have that option (and nor do I want to be working at the weekend), so I have to look more critically at how I use my time.

I’m continually trying to be more productive.

Imagine this scenario, and ask yourself if you relate:
Continue reading Thoughts on productivity

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.

CHECK OUT MY NEW DATA ANALYSIS COURSE

Data Analysis course
Data Analysis with Google Sheets, will teach you how to make data-driven decisions using Google Sheets.

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