## Data Cleaning and Pivot Tables in Google Sheets — My New Course has launched!

There’s a joke in the data community that goes:

“You’ll spend 90% of your time cleaning data, and the other 10% of your time complaining about that…”

Do you deal with badly formatted data that takes you hours to tidy up, preventing you from making progress on your “real” work?

Ever wondered what the heck a Pivot Table is, and why you should care about them?

Do you ever find yourself repeating a process manually, like extracting zip codes from addresses?

This new course is designed to help you work with messy, real-world datasets. It’ll teach you techniques and best practices for cleaning data and preparing it for analysis and reporting.

This new course is aimed at beginner-to-intermediate Google Sheets users.

## How to add a total row to a Query Function table in Google Sheets

This article looks at how to add a total row to tables generated using the Query function in Google Sheets. It’s an interesting use case for array formulas, using the `{...}` notation, rather than the `ArrayFormula` notation.

So what the heck does this all mean?

It means we’re going to see how to add a total row like this:

using an array formula of this form:

`= { QUERY ; { "TOTAL" , SUM(range) } }`

Now of course, at this stage you should be asking:

“But Ben, why not just write the word `TOTAL` under the first column, and `=SUM(range)` in the second column and be done with it?”

Well, it’s a good question so let’s answer it!

The reason for using this method is because the total line is added dynamically, so it will be appended directly at the end of the table, and won’t break if the table expands or contracts, if more data is added.

It’ll always move up or down, so it sits there as the final row.

## How to reply to Google Forms survey responses quickly with Sheets & Apps Script

You’re happy!

You sent out a Google Forms survey to your customers and received hundreds of responses.

They’re sitting pretty in a Google Sheet but now you’re wondering how you can possibly reply to all those people to say thank you.

Manually composing a new email for each person in turn will take forever. It’s not an efficient use of your time.

You could use an ESP like Mailchimp to send a bulk “Thank You” message, but it won’t be personal. It’ll be a generic, bland email and nobody likes that. It won’t engage your customers and you’ll be missing an opportunity to start a genuine conversation and reply to any feedback from the survey.

Thankfully, there is another way.

Of course there is, otherwise why would I be writing this tutorial! ðŸ˜‰

By adding a reply column to your Google Sheet, next to the Google Forms survey responses, you can efficiently compose a personal response to every single survey respondent.

Then, using Google Apps Script (a Javascript-based language to extend Google Apps, start here if you’re new), you can construct an email programmatically for each person, and send out the responses in bulk directly from your Google Sheet.
Continue reading How to reply to Google Forms survey responses quickly with Sheets & Apps Script

## How to create an annotated line graph in Google Sheets

This post looks at how to create a more advanced line graph in Google Sheets, with comparison lines and annotations, so that the viewer can absorb the maximum amount of insight from a single chart.

For fun, I’ll also show you how to animate this line graph in Google Sheets.

Want your own copy of this line graph?
Click here to access your copy of this template >>

This chart was originally developed for The Write Life during their 4-day product sale earlier this year. It featured as part of a dashboard that was linked to the E-junkie sales platform and displayed sales data in real-time:

## Dashboard Design Checklist: From a Blank Google Sheet to Business Insights

Dashboard design is hard.

You start with a blank canvas, or a blank Google Sheet in this case, and you have to somehow turn that into business insights, which will grow your bottom line, make your organization more efficient or help you understand your customers better.

Want your own copy of this Facebook dashboard?

The blank screen stares back at you, waiting for you to do something.

It feels overwhelming.

You clasp your hands around the back of your head, lean back in your chair and rue the day you mentioned building a dashboard to your boss.

It was supposed to be easy. Easy to create a masterpiece, a thing of beauty to wow your team.

Meanwhile, that blank Sheet continues to stare back at you, emptier than ever.

## How to build a real-time sales dashboard for E-junkie using Google Sheets and Apps Script

It’s day two of a four day product launch. You’ve worked hard all year to create a fantastic product, test your sales systems and tell the world about this amazing offer. You know you’ve sold 100 products so far, but…

…you don’t know whether your ads are effective, which affiliates are really killing it versus which have forgotten about your launch, or even whether your own emails are converting.

Looking at your sales log only, and having to decipher what’s happened since the last time you looked an hour ago, is like trying to drive in the dark without headlights.

Thankfully there is a better way to track your sales, so you can see your data, get insights about what’s working and what’s not, and immediately act to increase your bottom line.

This post looks at how to build a real-time dashboard for the E-junkie digital sales platform using Google Sheets:

Want your own copy of this dashboard?
Click here to access your copy of this template >>

E-junkie is a digital shopping cart, used for selling digital products and downloads. The system handles the shopping cart mechanics, but does not do any data analytics or visualizations.

You can view a transaction log (i.e. a list of all your sales) but if you want to understand and visualize your sales data, then you’ll need to use another tool to do this. Google Sheets is a perfect tool for that.

You can use a Google Sheet to capture sales data automatically in real-time, and use the built-in charts to create an effective dashboard.

You’d be crazy not to have a tracking system set up, to see and understand what’s going on during sales events or product launches. This E-junkie + Google Sheets solution is effective and incredibly cheap (\$5/month for E-junkie and Google Sheets is free).

The Write Life ran a Writer’s Bundle sale this year, during the first week of April. It’s a bundled package of outstanding resources for writers, including ebooks and courses, heavily discounted for a short 4-day sales window.

I created a new dashboard for The Write Life team to track sales and affiliates during the entire event. This year’s dashboard was a much improved evolution of the versions built for the Writer’s Bundle sales in 2014 (which, incidentally, was my first blog post on this website!) and 2015.

The biggest improvement this year was to make the dashboard update automatically in real-time.
Continue reading How to build a real-time sales dashboard for E-junkie using Google Sheets and Apps Script

## How to import social media statistics into Google Sheets: The Import Cookbook

Google Sheets has a powerful and versatile set of IMPORT formulas that can import social media statistics.

This article looks at importing social media statistics from popular social media channels into a Google sheet, for social network analysis or social media management. If you manage a lot of different channels then you could use these techniques to set up a master view (dashboard) to display all your metrics in one place.

The formulas below are generally set up to return the number of followers (or page likes) for a given channel, but you could adapt them to return other metrics (such as follows) with a little extra work.

Caveats: these formulas occasionally stop working when the underlying website changes, but I will try to keep this post updated with working versions for the major social media statistics.

Example workbooks: Each example has a link to an associated Google Sheet workbook, so feel free to make your own copy: `File > Make a copy...`.

## Help, my formula doesn’t work!

Admit it, we’ve all been there.

Whether you’re just starting out with Google Sheets or you’re a seasoned pro, sooner or later one of your formulas will give you an error message rather than the result you want.

It can be frustrating, especially if it’s a longer formula where the error may not be obvious.

# Auditing and Debugging Formula Errors in Google Sheets

Match the error message in your Google Sheet to the sections below, and find out what might be causing your error.

## How to add a hyperlinked index sheet to your large Google Sheet workbooks

Admit it, we’ve all been there. Getting frustrated trying to find a specific sheet inside a huge workbook, and not being able to see it amongst all the other sheets.

Well, here’s a quick Apps Script to create a hyperlinked index page at the start of your workbook.

Quick caveat first: There’s one drawback – the hyperlinks open the sheets in new tabs, which unfortunately I can’t do anything about. However, it should still be useful for anyone working with workbooks with 10+ sheets.

Here it is in action:

What if we already have a sheet by the name ‘Index’?

Well, you’ll be prompted to enter a different index name or to cancel the operation:

# Steps to create your own hyperlinked index sheet in Google Sheets

Step 1: Open up the workbook you want to add the index sheet to and open the apps script editor (`Tools > Script editor...`).

## Funnel charts in Google Sheets using the chart tool, formulas and Apps Script

Let’s talk about funnel charts in Google Sheets.

The charts themselves are a bit of a novelty. Yes, they’re aesthetically pleasing because of that resemblance to a real-world, tapering funnel, which reinforces their message, but a plain ole’ bar chart would be equally suitable and actually easier to read data from (because the bars have a common baseline).

However, they throw up some interesting techniques in Google Sheets and for that reason, merit this long article.

We’ll build them using tricks with the chart builder tool, then with two different types of funky formula and finally, and best of all, we’ll build a tool using Apps Script, as shown in this image:

As with the waterfall charts in Google Sheets, they’re not one of the out-the-box charts available to us, so we have to manually create them with a crafty workaround. Thankfully, they’re relatively simple to create, certainly simpler than the waterfall chart.

For all of these examples, we’ll use this fictitious real-estate dataset:

Here, I’m imagining the real estate agency collects data relating to their sales funnel, and they want to display it in a funnel chart format.

Click here to open up the Google Sheet template and make your own copy `(File > Make a copy...)`.