2) Replace “Code.gs” with the code here. (Skip this if you copied the Sheet above)
3) We included credentials for SeekWell’s demo MySQL database. To connect to your database, replace the six fields below. Note you’ll need to whitelist Google’s IP addresses.
var HOST = 'yourhostname'
var PORT = '3306 or your port'
var USERNAME = 'yourusername'
var PASSWORD = 'yourpassword'
var DATABASE = 'youdatabasename'
var DB_TYPE = 'mysql or your type'
4) You might also want to change the MAXROWS, but you don’t go too crazy, Sheets has a hard limit of 5 million cells and the query will take longer to run with more rows.
5) Save the file and refresh / refresh the Sheet. You’ll see a new menu option of “SeekWell Lite” show up.
6) The script is set up to read the query from query!A2 and write the results to your active cell, so you’ll need to add a sheet called “query” and add the query below in the cell query!A2 (skip if you copied the Sheet above).
7) Go back to Sheet1, click in cell C4 (or any other cell) and click “SeekWell Lite” → “Run SQL”.
In a few moments you’ll see the data show up!
A few problems with this approach
You need to store your password in plain text in the Code.gs file.
Sharing the script with your team and adding the script to different Sheets is a bit of a pain. You can publish an addon, but that comes with some overhead.
And scheduling / automating refreshes can be cumbersome when you need many different queries going to many different Sheets.
Google’s JDBC service doesn’t work for Postgres, Snowflake or RedShift and requires a long list of whitelisted IP’s. It also doesn’t support SSH.
Alternatives to Google Sheets database connections with App Script
A lot of people hate paying for things they can do for free, but you should always do some napkin math when making the “build vs. buy” decision.
In the case of automating reports, the ROI can be pretty high, especially if you have several daily, hourly, or near real time dashboards you need to keep updated. ActionDesk did a good overview of the options out there.
This is a guest post written by Mike Ritchie. Mike is the co-founder of Seekwell and has over 15 years experience in analytics.
SeekWell features include:
Takes < 2 minutes to get your first schedule set up
A shared code repository with every query anyone on your team has ever written
Beautiful query editor with autocomplete and snippets
Mastering Google Sheets formulas is more than just knowing the functions themselves and how to combine them.
True mastery comes when you know all of the little, hidden shortcuts and tricks built in to Google Sheets to help you with your formulas. Individually they may not seem like much, but combine them together in your toolkit and you’ll be more efficient and effective when working with Google spreadsheet formulas.
How many of these Google Sheets Formulas Tips & Techniques do you know?
To copy the formula quickly down the column, double-click the blue mark in the corner of the highlighted cell, shown by the red arrow. This will copy the cell contents and format down as far as the contiguous range in preceding column (column A in this case).
An alternative way to quickly fill in a column is to highlight the range you want to fill, e.g.:
Then press Ctrl + D (PC and Chromebook) or Cmd + D (Mac) to copy the contents and format down the whole range, like so:
You can also do this with Ctrl + Enter (PC and Chromebook) or Cmd + Enter (Mac), which will fill down the column.
If you’re building complex formulas, then I advocate a one-action-per-step approach. What I mean by this is build your formula in a series of steps, and only make one change with each step. So if you start with function A(range) in a cell, then copy it to a new cell before you nest it with B(A(range)), etc.
This lets you progress in a step-by-step manner and see exactly where your formula breaks down.
Similarly, if you’re trying to understand complex formulas, peel the layers back until you reach the core (which is hopefully a position you understand). Then, build it back up in steps to get back to the full formula.
For more detail about this approach, including examples and worksheets for each case, have a read of this post:
💡 Learn more Google Sheets Essentials is my new beginner’s course. It’s 100% online, on-demand video training course designed to boost your spreadsheet skills.
This Google Sheets tutorial will help take you from an absolute beginner, or basic user, through to a confident, competent, intermediate-level user.
Google Sheets is a hugely powerful tool, for everything from digital marketing to finance modeling, from project management to statistical analysis, in fact, just about any activity involving the recording and analysis of data.
And if you’re (relatively) new, it really pays dividends to learn how to use Google Sheets correctly. This tutorial will help you transition from newbie to ninja in short order!
If you’re new to Google Sheets, then I recommend you start from the beginning of this article.
However, if you’ve used Sheets before, feel free to skip sections 1 and 2, and begin with the Data and basic formulas section.
A template is available for copying to your Drive, to accompany this tutorial:
Google Sheets is a free, cloud-based spreadsheet application. That means you open it in your browser window like a regular webpage, but you have all the functionality of a full spreadsheet application for doing powerful data analysis. It really is the best of both worlds.
How is it different to Excel?
No doubt you’ve heard of Microsoft Excel, the long established heavyweight of the spreadsheet world. It’s an incredibly powerful, versatile piece of software, used by approximately 750 million – 1 billion people worldwide. So yeah, a tough act to follow.
Google Sheets is similar in many ways, but also distinctly different in other areas. It has (mostly) the same set of functions and tools for working with data. In fact, some people mistakenly call it “Google Excel” or “Google spreadsheets.”
With the risk of getting into an opinionated debate about the strengths/weaknesses of each platform, here a few key differences:
Google Sheets is cloud-based whereas Excel is a desktop program. With Sheets, you’ll no longer have versions of your work floating around. Everyone always sees the same, most up-to-date version of Sheets, showing the same spreadsheet data.
Collaboration is baked into Sheets, so it works extremely well. Excel is still trying to play catch up here.
Both have charting tools and Pivot Table tools for data analysis, although Excel’s are more powerful in both cases.
Excel can handle much bigger datasets than Sheets, which has a limit of 2 million cells.
Being a cloud-based program, Google Sheets integrates really well with other online Google services and third-party sites.
For the material we’ll cover in this article, there’s very little difference between the programs however.
For a deep-dive into the differences between Excel and Google Sheets, have a look at ExcelToSheets.com
Why use Google Sheets?
How’s this for starters:
It’s collaborative, so teams can all see and work with the same spreadsheet in real-time.
It has enough features to do complex analysis, but…
Click on the Go To Google Sheets button in the middle of the screen. You’ll be prompted to login:
And then you arrive at the Google Sheets home screen, which will show any previous spreadsheets you’ve created.
Click the huge green plus button to create a new Google Sheet:
Opening your first Google Sheet from Drive
You can create new Google Sheets from your Drive folder by clicking on the blue NEW button:
When you create a new Google Sheet, it’ll be created in your main Drive folder (your root folder):
(Note: Don’t panic if you don’t see the Sheet yet, it may not show up until you’ve renamed it. See next step on how to do this.)
Here you can drag it to a different folder if you wish (to keep things organized). Do this by clicking-and-holding the file, and dragging to where you want it to go:
The Google Sheet editing window
This is what your blank Google Sheet will look like:
You can rename your Sheet in the top left corner. Click on where it says Untitled spreadsheet and type in whatever name you want to give your Sheet, in this example “New Sheet”.
So let’s introduce some key terminology and the fundamental concept upon which spreadsheets work:
There are two menu rows above your Sheet, of which we’ll see more further on in this tutorial.
The main window consists of a grid of cells. An individual cell is a single rectangle, at the intersection of one column and one row, and it’ll hold a single piece of data.
The columns are vertical ranges of cells, labeled by letters running across the top of the Sheet.
Rows are horizontal ranges of cells, labeled by numbers running down the left side of your Sheet.
In the example above, I highlighted column E and row 10.
** The fundamental concept of spreadsheets: **
Column E and row 10 intersect at one cell, and one cell only. Thus we can combine the column letter and row number to create a unique reference to this cell, E10. Now when we want to refer to this cell, for example to access data in this cell, we use the address E10 to do that.
Understand this and you understand spreadsheets. The rest is just details!
Entering, selecting, deleting and moving data
Now the fun really starts! Let’s start using this new blank sheet we’ve created.
Click cell A1 (that’s the intersection of column A with row 1, the cell in the top left corner of the Sheet) and you’ll see a blue box around the cell, to indicate it’s highlighted:
Then you can simply start typing and you’ll see the data being entered into that cell:
Hit enter when you’ve finished entering data and you’ll move down to the next cell, having completed your data entry. If you hit the Tab key instead, you’ll move across one cell to the right!
It’s worth pointing out an important nuance here:
Clicking ONCE on the cell highlights the whole cell. Clicking TWICE enters into the cell, so you can select or work with the data only.
If you find yourself stuck inside a cell, you can press the ESCAPE key to deselect the contents and go up a level, to just having the cell selected.
Try it for yourself and see how the cursor shows up inside the cell when you double-click, allowing you to edit the data.
To delete the data we just entered, either click the cell once and hit the delete key, or, click the cell twice and then press the delete key until all your data is cleared out.
💡 Learn more Check out my brand new beginner’s course: Google Sheets Essentials. This is a 100% online, on-demand video training course designed specifically to help you boost your spreadsheet skills.
Help! I made a mistake
First of all, don’t panic!
Google Sheets saves every step of your work so you can always go back a step (or two) if needed.
Press Cmd + Z if you’re on a Mac, or Ctrl + Z if you’re on a PC and you’ll undo your previous step. Keep pressing and you’ll simply go further back through your changes. (Pressing Cmd + Y on a Mac, or Ctrl + Y on a PC moves your forwards, to redo your last step.)
You can also undo using the Undo arrow on the menu:
Creating a basic table
Right, with all that in mind, it’s time for a quick exercise.
See if you can create the following table for our fictitious gym membership site, by entering the data into the correct cells (there is no formatting or other tricks used at this stage):
Feel free to use your own data if you wish. Also note that the dates entered above are in US format, with the Month first, so don’t worry if your table has the Day first.
2. How to use Google Sheets: The working environment
Changing the size, inserting, deleting, hiding/unhiding of columns and rows
To select a row or column, click on the number (rows) or letter (columns) of the row or column you want to select. This will highlight the whole row or column blue, to indicate you have it selected.
To change the width of a column, or height of a row, hover your cursor over the grey line denoting the edge of the column or row, until your cursor changes to look like this:
Then click and drag the cursor left or right to change the width of this column. It’s the same process to change the height of rows.
Pro-tip: To quickly change the column width to fit your cell contents, double-click when you’re hovered over the grey line.
How to add columns in Google Sheets: To insert additional columns or rows, click on the existing column or row next to where you’d like to insert a new column or row. With the column or row selected (highlighted blue), right click to bring up the options menu, then select Insert Before (or Insert After) for Columns, or Insert Above (or Insert Below) for Rows:
Adding extra rows and columns at end
If you reach the outer edges of a Google Sheet, you’ll notice the rows and/or columns stop. But don’t worry, you can add more.
If you’ve scrolled all the way to the bottom of your Sheet (or added that much data), you’ll notice that you’re given 1,000 rows by default. There’s a button to add more rows if you need, either 1,000 as shown, or any number you wish (up to a limit, more on that below).
If you reach the right edge of the Sheet, i.e. the last column, then you add more columns in the standard way. Right-click a cell in the last column to bring up the menu and then choose to add a column to the right.
Pro-tip: If you want to add more than one column, there’s a trick to do it in one go. As an example, say you wanted to add three new columns to the right side of your Sheet, begin by highlighting the last three columns that are there already, then right clicking and choosing to inser new columns. It’ll then insert three new columns for you!
Data Limit: Finally, keep in mind that each Google Sheet is limited to 2 million cells, which sounds like a lot but is 50,000 rows by 40 columns (a totally reasonable sized transaction database for an ecommerce store for example). Anyway, you’ll find Sheets slows down considerably before reaching that limit. Most people report a slight slow down with tens of thousands of rows of data and complex formulas and models.
Adding/removing multiple sheets, renaming them
Click the big plus button in the bottom left of your Google Sheet to add a new Sheet (also called a Tab).
Why use multiple tabs within your Google Sheet?
Well, like a book with chapters on different topics, it can help separate different data and keep your Sheet organized.
For example, you might have a Sheet solely to record your global settings (any variables like name, email, tax rate, headcount…) and another for transactional data, and yet another for the analysis and charts.
The button with the three bars, next to the plus, is your index button, listing all of the tabs in your Google Sheet. This is super useful when you start having a lot of different tabs to manage.
To rename a sheet, or delete a sheet, click the small arrow next to the name (e.g. Sheet1) to bring up the menu. Here you’ll see the option to rename, to delete, or even hide (and unhide) Sheets.
For naming, I try to indicate what’s in that tab, so use names like: Settings, Dashboard, Charts, Raw Data.
You’ll find all of the formatting options on the top tool bar, so you can center your headings, make them bold, format numbers as currency etc. You may find them all on one single row, or you may find some under the More button, as shown in this image:
They’re similar to a word processor and pretty self-explanatory. You can always hit undo if you make a mistake (Cmd + Z on Mac, or Ctrl + Z on PC).
Try the following to format our basic table:
> Make the heading bold and size 14px
> Center the column headings and make them bold
> Center the tier column
> Change the date format to 01-Jan-2018 (Hint: the date format is found under the button that says 123.)
> Add a dollar sign, $, to the fee column
> Add a border around the whole table.
Here’s a GIF to guide you:
(Note, you can also find the formatting options under the Format menu, between the Insert and Data menu options.)
Let me show you the option to add alternating row colors (banding) to your tables.
Let’s apply it to our basic table, by highlighting the table and then from the menu:
Format > Alternating colors
as shown here:
Remember, a little bit of formatting goes a long way. If you Sheet is more readable and tidy, people will be more likely to understand it and absorb the information.
This is my number 1 productivity tip in Google Sheets.
To remove all formatting from a cell (or range of cells), hit Cmd + \ on a Mac or Ctrl + \ on a PC.
This will save you so much time when you’re wanting to remove formatting that isn’t yours or that you no longer want or need.
3. How to use Google Sheets: Data and basic formulas
Different types of data
You’ve already seen different data types in Google Sheets in our basic table.
The key point to understand with spreadsheet data is that each cell contains the data itself, and a format applied to that data.
For example, suppose a cell contained:
2, or 2.00, or $2, or $2.00
In each case the underlying data is the number 2, but with a different format applied each time. If we add 2 to each of these cells we get back the number 4 in every case (with formatting applied).
You’ll notice that currency data, percentage data and even dates are actually just numbers under the hood (dates? Really? Yes, they are, but that’s a discussion for another day). They’re all right-aligned, hanging out on the right edge of their cell.
Text is left-aligned by default.
If you want to force something to be stored as text, you can prepend a single quote, ' before the cell contents. So typing in '0123 will show as 0123 in your cell and be left-aligned. If you omit the single quote mark, then it’ll be stored as a number and show up as 123 without the 0.
Doing math on numbers
Easy-peasy, just like you do on a calculator.
You click the cell you want to do your calculation in, type an equals sign (=) to indicate you’re performing a calculation and then type in your formula, e.g.
Notice how calculation will show in the formula bar (1) as well as in the cell (2).
You’ll notice that you get a preview of the answer (in this case, 25) above the formula.
Starting with functions: COUNT, SUM, AVERAGE
Technically you’ve already written your first formula in the section above on math calculations, but really, your formula career begins when you start using the built-in functions (of which there are hundreds!).
Returning to our basic table, let’s count how many members we have, what the total monthly fees are and what the average monthly fees are.
Click on cell B8, type an equals (=) and then start typing the word COUNT. You’ll notice an auto-complete menu comes up showing all of the functions beginning with C, like this:
You can either keep typing COUNT in full, or find it and select in from the list (hover over it and click on it OR hit enter OR hit tab).
Next you’ll see the formula helper window show up, which tells you about the formula syntax and how to fill it in correctly:
In this case, the COUNT function is expecting a list of numeric values.
You have to select the range of cells you want to count. So click on B4, hold you mouse down and drag down to B7, so that the four cells are highlighted in orange and B4:B7 is showing up in your function:
Then, close the function with a closing bracket “)”:
Note: COUNT is used to count numbers. If you want to count text (for example the names) then COUNT won’t work (it’ll give you a 0). Instead use COUNTA (with an A at the end), otherwise the method is the same.
Your turn! Try creating a total for the membership fees in cell D8. Follow the same process as the count function, except use SUM and highlight the values in column D.
You’re on a roll, so go ahead and calculate the average of the membership fees. Use the AVERAGE function in cell D9.
Psst, you’ll notice that Google even helps you out sometimes and suggests the exact formula you were after:
Here you go:
If you make a mistake with your formula, you’ll see an errors message, probably something like #N/A, #REF!, #DIV/0 etc.
You’ll need to re-enter your formula and correct it before proceeding. These error messages do give a lot of context though, so they’re worth understanding.
What’s the difference between a function and a formula?
Well, both are used interchangeably and rather loosely so I wouldn’t get hung up on it.
For the pedantic, a function refers to the single method word (e.g. SUM) whereas a formula refers to the whole operation after the equals sign, often consisting of multiple functions.
Separating data with the Text to columns feature
Let’s suppose you wanted First Name and Last Name, rather than just simply Name as we have in our dead famous authors membership table. How do we go about doing that?
Well of course, as with everything in spreadsheets, there are lots of ways, but let me show you the easiest, using the Text to Columns feature.
Back to our basic table, create a new column to the right of Name before the Tier column, i.e. create a new, blank column B.
Highlight the four names and click:
Data > Split text to columns…
On the sub-menu that shows up choose SPACE and marvel at how Google Sheets separates the full name into a first and last name. Feel free to rename the columns First Name and Last Name too.
Oh, blast I hear you say! You meant to keep hold of that full Name column as well.
No problem, let’s learn how to combine text so we can rebuild it.
Insert a new blank column between B and C (between Last name and Tier) and call it Full Name, in cell C3.
Add this formula in cell C4:
= A4 & B4
That’s A4, Ampersand, B4.
What it does is combine the data in cell A4 with the data in cell B4 and output it in cell C4.
Hmm, but this gives an output like this:
That’s obviously not good enough! We need a space between the names!
Change the formula to this, by clicking right after the ampersand and adding double quote, space, double quote, ampersand:
= A4 & " " & B4
Here we’ve told Google Sheets to add a space into the mix, and the output now will be:
Voilà, that’s better!
Your formula is sitting pretty in cell C4, but how do you get it to work for the other rows?
You can either:
i) right-click, copy, move down to select the next cell, right click again, click paste, or
ii) Cmd + C (on Mac) or Ctrl + C (on PC), move down to select next cell, then Cmd + V (on Mac) or Ctrl + V (on PC), or
iii) drag the formula down by holding the little blue box at the bottom right corner of the blue highlighting around the original cell.
The neat thing is that as you copy this formula down, the cell references will change from row 4 to row 5, row 5 to row 6, etc., automatically! How cool is that!
(This is what’s known as relative references. More on that in section 5 below.)
Let’s see some of the unique, powerful features that Google Sheets has, as a cloud-based piece of software.
Comments (and Notes)
Want to add some context to numbers in the cells of your Sheets, without having to add extra columns or mess up your formatting?
Add a comment to a cell!
You can tag people (via their email address) who you want to see the comment too. They can reply and mark it resolved once it’s been acted upon.
You can also add simple notes to cells as well if you wish.
Comments and Notes can also be deleted when not required anymore.
To add a comment to a cell, first select the cell, then right click to bring up the menu of options. Select “Insert comment” and then simply type in your comment.
To tag somebody in your comment, type the plus sign (+) and their name or email address (you’ll see auto-complete options from your contacts, so you shouldn’t have to type in the whole email address).
You’ll notice a small orange triangle in the top right corner of the cell to indicate the comment. The comment will show up when you hover over this cell. If you click on the cell, it’ll also add orange shading to the cell background.
Comments can be edited, deleted, linked to, replied to and resolved (comment disappears from Sheet and is archived).
You can reach and control all the Comments in your Sheet from the big Comments button in the top right of the screen, next to the blue Share button.
(The first time you tag someone in a comment, you’ll be asked to share the Sheet with them. See more on this below.)
You can also add a note to cells in the same way (look for it in the menu next to Insert Comment). It’s like a pared down version of a comment, intended for your own reference.
Share your sheets
(If you just added a comment and tagged someone else, as shown above, then you may have already done this step!)
You can share your Google Sheets with other people. Since it’s on the cloud, they can access your Sheet and see the same, live Sheet that you’re in.
In other words if you make changes, they will show up automatically and in near real-time for everybody viewing the Sheet.
You can have multiple people viewing and working on the same Sheet.
Essentially you have three options to share you Sheet with:
View-only access, so that person can not change or comment on any data
Comment-only access, so that person can add comments but still not make any changes to the data in the Sheet
Editing access, so that person can make changes to the sheet (including comments)
The Sharing options are found by clicking on the big blue button in the top right corner, which will open up the Sharing settings:
You can grab the link (the URL) to the Sheet, choose the share setting (view/comment/edit) and then share that link with people you want to see the Sheet. (1)
Or, you can enter someone’s email address directly, choose the share setting (view/comment/edit) and then share the Sheet directly with the person. (2)
If you want to review the sharing settings or have even more control, click the Advanced options buttons. (3)
The Advanced sharing settings window:
Here you can:
> Grab the sharing link (1)
> Review who has access (2)
> Change the access rights of anyone listed (3)
> Invite new people to access the Sheet (4)
> Change the advanced owner settings, to restrict who can control the sharing settings and specific view/comment rights (5)
> Confirm when you’re finished (6)
I’ve used the link from the sharing settings to share the template for this tutorial with you!
Ok, so you’ve shared your Sheet with someone. If they open it whilst you’re still working in the Sheet you’ll see their cursor show up on whatever cell (or range) they’ve selected. It’ll be a different color, for example green to your blue.
If they enter data or delete data you’ll see it happening in real-time!
In this case my active cell is the blue-outlined cell. I see somebody else, denoted by the green-outlined cell, show up in this Sheet and enter data into a few cells before deleting it.
5. How to use Google Sheets: Intermediate techniques
This is one of the most useful tricks you can learn in Google Sheets, which is why I’m recommending you learn it today.
Sooner or later you’ll work with a table of data that continues beyond the area you see on the screen (right now for example, I can see as far as row 26, but it depends on your screen size and other factors).
When you scroll down to look at data further down in your table, you lose the column headings off the top of your screen, and therefore can’t see the context of your columns.
Of course, scrolling up and down to see what the column headings are makes no sense. It’s a sure path to spreadsheet errors and insanity!
Have a look at this data table showing the tallest buildings in the world, which extends below the bottom of what you can see on a single screen in Sheets. Scrolling results in the heading row disappearing, so you no longer know which columns are which:
What you need to do is freeze the heading rows.
Thankfully it’s super easy.
Click on the row number of the row with your column headings in (e.g. row 3), then from the menu choose:
View > Freeze > Up to current row (3)
Now your headings will stay in place. Ah, that’s much better!
You can do the same with columns, if you wished to freeze names for example, so you can scroll horizontally across lots of columns of data.
This is arguably the hardest concept to grasp in this tutorial. If you understand it and can apply it, then you have a really good understanding of how spreadsheets work and you’re well on your way to being a skilled user.
Suppose you have some data in cell A1 and you enter the following formula into cell B1:
This formula will retrieve whatever data is in cell A1 and show it in cell B1.
Now copy the formula (Cmd + C on a Mac, or Ctrl + C on a PC) and paste it (Cmd + V on a Mac, or Ctrl + V on a PC) somewhere else on your Sheet, for example cell D5.
Nothing will show up in D5. In fact you may be wondering whether your copy-paste worked. Have a look in the formula bar and you should now see this however:
The formula is there, but it points to a different cell, not A1, so does not show the data from A1.
But it still points to the cell that is ONE TO THE LEFT AND ON THE SAME ROW as the original formula.
Ah ha! Eureka!
The formula copied perfectly, keeping the same structure, pointing to the cell on the left.
This amazing property is called a relative reference, meaning it’s in relation to the cell where the formula is (e.g. one to the left).
That’s why you can drag formulas down columns and they’ll change automatically to calculate with data from their row.
Now then, if you want to fix your formula (for example so it always point to cell A1) then you’ll want to use what’s called Absolute Referencing.
We lock the cell reference in the formula, so Google Sheets knows to not move the reference when the formula is moved.
The syntax uses a dollar sign, $, in front of the column reference and in front of the row reference to lock them each respectively, like so:
Now, wherever you copy this formula, the output will always point to cell A1 and return you the data from cell A1.
Note, you can just lock the column or just lock the row reference, and leave the other part as a relative reference, but that is beyond the scope of this tutorial.
Working with formulas across sheets
Sticking with the topic of referencing other cells for the moment, how does one go about linking to data on a different Sheet?
Returning once again to our basic gym membership table for dead famous authors, in Sheet 1, let’s retrieve the table heading and print it out in Sheet 2 with this formula, entered into cell A1 on Sheet 2:
Note the exclamation point at the end of the reference to Sheet1, i.e. Sheet1!
Now let’s do a simple sum of data on Sheet 1, but show our answer on Sheet 2.
In cell A3 in Sheet 2, enter the following formula:
= SUM( Sheet1!F4:F7 )
This will return the sum of the range of cells F4 to F7 in Sheet 1 and print out the answer in Sheet 2.
Basic conditional formatting
Conditional formatting is a powerful technique to apply different formats (for example background shading) to cells based on some conditions.
Let’s see an example of conditional formatting, that is, formatting based on variable conditions.
For example, in a financial model, you might show positive asset growth with a green font color and a light green background, whilst negative growth might be shown with red lettering on a light red background. This gives extra context to your numbers, and pre-attentive attributes (the colors) help to convey the message more efficiently.
Taking the plain copy of the membership table (hit Cmd + Z on a Mac, or Ctrl + Z on a PC to go back if you need), highlight the final column of the membership fee, then from the menu:
Format > Conditional formatting
Here you can choose a rule, for example, values less than $100 and highlight them red:
Notice the range is shown (1), then a drop-down menu to choose a rule (2) and then the formatting option (3), which is default red in this case, although you can go completely custom if you choose.
The power of conditional formatting is to highlight data dynamically. The formatting is based on a rule, so if another value should drop below the threshold ($100 in this case), it will trigger the formatting rule and be highlighted red.
Sorting your data is a common request, for example to show transactions from highest revenue to lowest revenue, or customers with the greatest number to least number of purchases. Or to show suppliers in alphabetical order. You get the idea.
So let’s sort the dead famous authors gym membership table, from earliest members to most recent members, i.e. we’ll sort our table based on the date column.
Highlight the whole table, including the header row. Then from the menu:
Data > Sort range…
and be sure to check the “Data has header row” option. Then you can select the column you want to sort by, and sort option from A to Z, or Z to A.
This re-sorts the table, showing the earliest members first:
The next step after sorting your data is to filter it to hide the stuff you don’t want to see. Then you can just look at the data that is relevant to the problem at hand.
Taking the world’s tallest buildings data again, let’s apply a filter to only show skyscrapers built before the 2000s.
Click somewhere inside the data table (click on a cell containing data in the table), and then add the filters from the menu:
Data > Filter
or by clicking this icon on the menu bar above your Sheet:
You’ll notice a light green shading applied to row and column headings of your filtered table, and also a green border around your table. Most importantly though, you’ll now have little green filter buttons in each of your heading cells.
To filter out all the buildings built in the year 2000 or after, click the little green triangle next to the column heading Built, to bring up the filter menu.
You’ll notice you can manually select or de-select items to show. Let’s create a rule this time though.
Under the “Filter by condition” section, choose “Less than” and enter 2000 into the value box:
You’ll see a reduced table with just 9 results. The 9 skyscrapers built before the year 2000:
To remove the filter, click the green triangle button again (now solid green) and under the “Filter by conditions” set the rule to “None”.
There’s also a native FILTER function, by which you can formulaically filter your data.
As a final exercise with the tallest building data, let’s draw a chart to show the buildings built by year, so we can see the trend graphically.
First, let’s sort the table by the year Built column, from oldest to newest. We can do this using the sort function that is provided in the menu when you click on the green triangle (from the filter).
Then highlight this single column called Built and from the menu:
Insert > Chart
This creates a default chart in your window and opens the chart editing tool in a sidebar.
Here you can change your data range and chart type, as well as a multitude of chart custom formatting options (which are also generally accessible by clicking on the elements directly in the chart).
The chart is an object in your Sheet now. Click it to select it, so it has a blue border around it. You can resize it and drag it to move it, just as you would with an image.
Using the Explore feature
The robots are coming!
Soon we won’t have to create complex formulas or charts ourselves. We’ll simply ask our Sheet to do it for us. Sound far fetched?
Well, you can do that now! The future is here.
(Soon we won’t have to open Google Sheets at all, we’ll simply type, or more likely speak, our data questions into a dashboard console and out will pop the answers, but I digress.)
Google Sheets has a feature called Explore, powered by Machine Learning/AI/Deep Learning/Neural Network sorcery, that will ingest your data, analyze it and show you some common answers (like the SUM, COUNTs etc. we’ve seen so far) and basic charts:
Let’s see an example, using the data about the world’s tallest buildings. Highlighting the column showing the years when each skyscraper was built and then clicking on the Explore button (bottom right corner) leads to these insights:
52 buildings in my set. The earliest tower on the list was built in 1931 (the venerable Empire State Building!) and the most recent built in 2017. The average of all the years built is 2007.
Some interesting and useful data points, without having to do a lick of work!
I mentioned charts, so let’s see an example of that with the Height (ft) column. Google Sheets Explore creates a Histogram for us, showing the distribution of tower heights:
We also get this interesting and insightful summary:
“Ranges from 1,148 to 2,717, but 80% of values are less than or equal to 1,480.”
Wow! At a glance we have the min and max heights, but more impressive, we know that 80% are under 1,480ft tall.
Not all of the “insights” are useful, and I don’t use this feature much myself yet, but it shows a glimpse of how we’ll all work with Sheets in the future.
If you’re still not worried about AI taking over your job (whether that’s data analysis work or something else) in the next 10 to 50 years, then have a read of this article.
No “How to use Google Sheets” article would be complete without at least a quick look at the VLOOKUP function.
Because it’s the most famous function. Tell anyone you work with data and spreadsheets and they’ll immediately ask, “yes, but do you know how to do a VLOOKUP?”
It’s a good bellwether for spreadsheet competency, even though there are ultimately better ways to work with data. It’s also a relatively advanced formula compared to what we’ve seen so far, so if you can understand it, it bodes well.
What does it do then?
It’s used to search for a term and return information about that term from a different table.
Generally it’s used when you have two tables that share some common attribute (e.g. a name, or ID number, or email), but otherwise store different information. Suppose you want to bring this information together though. Well you can, and you link the information via that common attribute.
One table might have details an employee’s name and address, and the other table might have their name and work details like title and salary. You can use the VLOOKUP function to bring these bits of data together in a single table.
In words: you select a search term which you search for in the first column of the search table. If you find it, you return a piece of information from the search table that relates to the search term (because it’s on the same row). The column number refers to which column of the search table you return the data from (1 being the column you searched in, so typically this number is 2 or greater).
Let’s see an example, by adding addresses to the dead famous authors gym membership table.
I’ve added a second table to Sheet 1, showing the addresses of our dead famous authors. What I’d like to do is add that data to the original gym table.
I want to do it efficiently with the VLOOKUP formula, rather than adding them manually. Not only is it much, much faster with larger tables (imagine ten thousand rows of data), but it’s also less error prone.
The address table is in columns I and J, with the names in column I and addresses in column J.
So I’ll search for my author name in column I and return the address from column J, and print the output into whichever cell I created my formula in.
The formula is:
= VLOOKUP( C4, $I$3:$J$7, 2, FALSE )
And this is what’s happening:
I search for the name (1) in the search table (2) and return the data from column 2 of the search table (3).
I don’t expect you to understand all of this immediately (and there’s a lot more to this formula than what I’ve shown here), but if you try it out and persevere, you’ll get there and realize it’s actually not too difficult.
(The FALSE argument, the final piece of information in the VLOOKUP formula means you want to do an exact match. 99.9% of the time you use a VLOOKUP, you’ll want to use FALSE.)
Have you heard of the Google Sheets Query function?
It allows you to use database-type commands (a pseudo-SQL, Structured Query Language, the code used to communicate with databases) to manipulate your data in Google Sheets and it’s incredibly versatile and powerful.
It’s not an easy function to master at first, but it’s arguably the most useful function in Google Sheets.
This single function does the job of many other functions and can replicate most of the functionality of pivot tables.
24. The output returns three values – the max, min and average populations of the dataset, as follows:
Equivalent real world SQL code:
SELECT max(population), min(population), avg(population)
GROUP BY clause
Ok, take a deep breath. This is the most challenging concept to understand. However, if you’ve ever used pivot tables in Google Sheets (or Excel) then you should be fine with this.
The GROUP BY clause is used with aggregate functions to summarize data into groups, in the same way a pivot table does.
25. Let’s summarize by continent and count out how many countries per continent. Change your query formula to include a GROUP BY clause and use the COUNT aggregate function to count how many countries, as follows:
=QUERY(countries,"SELECT C, count(B) GROUP BY C",1)
Note, every column in the SELECT clause (i.e. before the GROUP BY) must either be aggregated (e.g. counted, min, max) or appear after the GROUP BY clause (e.g. column C in this case).
26. The output for this query is:
Equivalent real world SQL code:
SELECT continent, count(country)
GROUP BY continent
27. Let’s see a more complex example, incorporating many different types of clause. Modify the formula in G1 to read:
=QUERY(countries,"SELECT C, count(B), min(D), max(D), avg(D) GROUP BY C ORDER BY avg(D) DESC LIMIT 3",1)
This may be easier to read broken out onto multiple lines:
=QUERY(countries, "SELECT C, count(B), min(D), max(D), avg(D)
GROUP BY C
ORDER BY avg(D) DESC
This summarizes our data for each continent, sorts by highest to lowest average population and finally limits the results to just the top 3.
The code is very similar to real SQL and it’s looking pretty complex now! Good job!
28. The output of this query is:
Equivalent real world SQL code:
SELECT continent, count(country), min(population), max(population), avg(population)
GROUP BY continent
ORDER BY avg(population) DESC
We ran a number of polls throughout the event, related to talks happening on the main stage. It was interesting to gauge audience in real-time and provided useful feedback.
One of the unique features of the Hopin platform we used was the networking feature.
Networking is one of the most important parts of going to an in-person conference for many people, so I was really excited to have this feature available for this online conference.
It’s a super interesting idea.
If you choose to participate, you get paired up with another random attendee and given a limited time to video chat, one-on-one. If it’s a productive conversation then you can click to exchange contact details to connect.
In practice, it received a mixed reception.
Many people did not want to connect with other random attendees and be on camera. Some experienced technical difficulties that prevented them getting a solid connection. Others tried but found the connections to be too random to be useful.
However, many tried it and loved it!
We could have improved the networking feature by having people network within groups (e.g. educators networking with other educators) and better educating people about how it works.
Some of the feedback we received for this part of the event was very positive:
+ One of my favorite parts of the whole conference! Great addition!
+ Very good. Talked to folks from all over the world. Made a work connection.
+ Loved getting to know people & loved how it forced conversations to be short.
Whereas for others it didn’t really work:
+ It was just ok. The experience felt very random and impersonal.
+ Wasn’t fond of it tbh. I didn’t like the randomness or how it cut off after speaking to someone.
+ Wasn’t comfortable for me, and I didn’t have a camera.
We have some areas to work on to improve the execution for SheetsCon 2021 but I think it will become a really valuable part of our online conferences in the future.
We ran three roundtable sessions on day two of SheetsCon, covering 1) Google Sheets for Educators, 2) Google Sheets Tips and Tricks and 3) the Apps Script Zone.
These roundtable discussions were open from 3pm to 4pm on day 2, between presentations on the main stage.
Each roundtable discussion had anywhere between 4 and 15 people speaking on the screen and another 80 – 100 listening and participating in the chat.
It was so cool to see these roundtable sessions come to life and happen simultaneously. The community came together and self-regulated these sessions, which were open to any attendees.
There was a fourth roundtable session — Google Sheets for Digital Marketers — that didn’t gain any traction.
My overall impression of the roundtables were that they were really popular but I need to have some more direction for next year. I think having a moderator to run each session would fix this.
The feedback on the roundtable sessions was overwhelmingly positive and basically just confirmed my thoughts above:
+ Lots of sharing information and ideas.
+ Very good. Great thoughts and feedback and discussion.
+ It was good, I was able to talk about my knowledge on getting google sheets addons approved in the g suite marketplace.
+ It was a bit confusing without having a moderator leading the discussion and topics, etc.
+ I enjoyed the roundtables – I was only an observer but this allowed me to pop in and out of rooms as the discussions progressed around topics I was interested in and at levels that were appropriate for my knowledge base
+ It was actually interesting. Intimidating but really cool. Very much like in person meeting people at a conference
SheetsCon 2020 brought all the aspects of a conference online.
We had an expo hall (a page within our SheetsCon event) that showcased our sponsors.
Each sponsor had their own booth (page) with a dedicated chat window and the option to have a live video session (e.g. for product demos, answering customer questions etc.) or an on-demand promo video.
The vendors that ran live sessions saw significantly more engagement than the vendors that only had an on-demand YouTube video.
At one stage, I browsed around the Expo Hall and saw four of the vendors running live software demos simultaneously. Much as the roundtable discussions rooms came to life, it was another eye-opening moment for me as an organizer to see the community in action.
We got some great feedback on the vendor booths, almost all super positive with some constructive critique that will help us improve next year:
+ I am glad the platform had this option. It is great to learn about the apps and add ons.
+ Some were obviously better than others but I was impressed to see how the platform worked.
+ When there was a live person there, it was great. When it was just a video, not so much.
+ Amazing! My two favorites were interactive and applied to me best. SheetsGo were the most interactive, they were there 100% of the time during breaks, and even contributing in main stage chat!
+ It was fun and unique process.
+ I preferred the demo and interactive ones, rather than just watching a youtube video. it would be great if they could show you the video and also advertise what time they would be giving a demo or available to chat.
I think one obvious improvement for next year would be to give each vendor a time for their product demos, so that people could plan to visit the booth when it was live.
Thanks to the following companies that supported SheetsCon 2020:
We partnered with the online conference platform Hopin to run this event.
From their website:
“Hopin is the first all-in-one live online events platform where attendees can learn, interact, and connect with people from anywhere in the world.”
That’s exactly what SheetsCon was all about, which is why Hopin were the perfect platform for the vision I had for SheetsCon.
Would I use Hopin again?
Absolutely! 100%. I’m super impressed with the platform.
It wasn’t perfect, but it’s a new category of software. The core concept is AMAZING! I have total faith that the team will continue to improve an already fantastic product.
Online Conference Timeline
3 – 6 months out (October – December 2019)
The idea for an online conference happened around 6 months prior to the event, in October 2019. I was watching a demo webinar from Hopin and was seriously impressed with the software.
I thought about my 2020 plans and really wanted to include more live elements to my training programs.
Thus, SheetsCon was born.
I bought the SheetsCon.com domain on the 29th October and bought a WordPress hosting account with GoDaddy on 11th November 2019.
Through November, I planned out the event and made shortlists of possible speakers, session topics and vendors. I reached out to them in November to get people on board.
Here’s an example email I sent out to potential speakers to see if they were interested:
How are you?
I want to share a new project with you and hopefully get you interested in being part of it.
Early next year I’m running SheetsCon — a 2-day online conference for all things Google Sheets.
I’d love for you to be one of the speakers.
My vision is to create an online experience that brings together thousands of Google Sheets users from diverse industries, to learn from experts (like you!), network with other professionals and be inspired by the magic of Google Sheets.
It’s completely online and will happen on the 5 – 6th February 2020.
In addition to sessions on the main channel (which will be like a series of webinars), there’ll be networking rooms, sponsor rooms and panels.
I’d love to have you talk about [XYZ].
Are you interested in hearing more? I’d love to jump on a call and tell you more.
As you can see, I was planning on a February event date at this point in time.
However, I got sick for a week in December and decided that there was too much to do to meet the early February deadline.
2 months out (January 2020)
I made the decision to delay SheetsCon by a month and move it to 11th/12th March 2020.
So far I’d only confirmed speakers, so it was a relatively small number of people to check in with and thankfully they all agreed to the new dates.
This is the email I sent out to announce the date change:
Quick update on SheetsCon 2020, the online Google Sheets conference.
Unfortunately, I got sick at the end of the year so the site is not where it needs to be yet.
I’ve moved the date to the 11th/12th March. Are you still available?
I’ll be in touch soon with more updates. Thanks again for being involved!
Best wishes for 2020!
1 month out (February 2020)
I created a logo design contest on 99designs to create a better logo for SheetsCon. I was really pleased with how this turned out.
I announced SheetsCon publicly to my email list, on my website and on social media. I sent all the traffic to the SheetsCon.com from where people could sign up.
The vendors also began sharing with their lists and social channels and this really drove a lot of sign-ups.
The momentum stalled when my whole family got the flu for a week, so I lost a lot of time. Not a major setback though, as everything was still on track at this stage.
Throughout the month, I was onboarding new vendors and doing SheetsCon calls to show vendors how the platform works and what to expect.
I also did at least one practice call with each speaker to show them how the platform worked and what the workflow was like on the day. We also discussed session topics with and ironed out any issues (e.g. ensuring that Google Sheet formulas or code was large enough to be legible on the screen).
I continued to put out information onto Twitter (e.g. this thread) and occasionally on LinkedIn.
1 week to go (March 2nd week)
Disaster! I got sick again for 4 days, urgh!
I had to postpone a bunch of practice run-through calls and had still not prepared my keynote. This illness was a setback for sure, but thank goodness it didn’t come the following week during SheetsCon.
3 days to go
All hands on deck!
My wife joined me full-time this week as the SheetsCon assistant.
I spent sometime creating and practicing my opening keynote, as well as getting the final pieces in place for the event. I created several process docs (Google Docs) for tasks on the day, key info for speakers and vendors, swag bag plans, etc.
We worked from 8am to midnight during the SheetsCon week, so it was a pretty intense week!
This week was also the tipping point for the coronavirus here in the U.S., where public opinion changed, schools began to close, events were cancelled and life changed seemingly overnight.
Coronavirus wasn’t on anyone’s radar when I began planning an online conference, so although it looked prescient from the outside, I always envisioned SheetsCon as an online conference. It allowed us to run the event without any major concerns or changes to the program.
SheetsCon Event (11th/12th March 2020)
Everyone has a plan ’till they get punched in the mouth. – Mike Tyson
The event began with almost 1,500 people active on the SheetsCon event site and watching the opening keynote livestream. The energy was palpable. The chat was going pretty crazy!
I was somewhat insulated as the speaker as I focussed on my presentation.
Part way through my opening keynote, we suffered some technology issues and my livestream went down for about 15 mins. Not the best start and for a while I was oblivious! However, my wife was working with the Hopin team (who were on hand and super helpful) to get things up and running again. My wife was also handling all the comments in the sidebar chat and keeping things positive.
It was a really intense (and stressful) first hour. But we survived and everything was solid thereafter. There were no other major snafus at all.
The event ran for 2 days, which went by in a flash.
The work never stops!
As the dust settled on the live event, I had a long checklist (around 30+ items) to work through to wrap up SheetsCon.
Surveys for feedback from attendees and sponsors. Downloading all the presentation recordings and putting them together into a replay package with the accompanying slides and templates. Preparing and delivering swag bags to winners.
And of course, planning for SheetsCon 2021 begins!
What went well?
More people than we expected
Really great community with lots of energy and enthusiasm throughout
11 fantastic speakers on a variety of topics and skill levels
The full conference experience online thanks to our technology partner, Hopin
Amazing to see roundtable discussions happening simultaneously, with up to 15 speakers and hundreds watching in each room
Likewise, amazing to see live demos from at least 4 of the vendors happening simultaneously
89.5% of the attendees plan to return (based on 330 post-event survey responses)
10.0% said they will “maybe” return
Only 0.5% said they don’t plan to return (2 responses)
We had really fantastic and supportive feedback from attendees too:
+ Overall it was a great conference. Looking forward to seeing what you add for next year.
+ THANK YOU Ben, and the rest of your team who brought this to us. Events such as this are so extremely helpful!!! 🙂
+ This con was brilliant!! It really got the fire rolling in terms of ideas and applications of sheets. SO happy I can across it and will most definitely be attending next year and every year following!!
+ Thanks for all of your hard work! It was a great first time event. Can’t wait to see how it grows in the future.
+ Thanks for battling through the glitches at the beginning. It was awesome to be a part of the whole thing! I’m super stoked to see what next year’s event brings.
What we need to improve
For a first event I think it went extremely well, but of course there are plenty of things to fix for next year:
Better explanations of how the various features work
At least one other person to help in the build-up and during the event
Moderators for each of the roundtable discussion rooms to lead the conversations
Group topics into tracks and/or skill levels
Better communication about the exact start time (confusing with the time difference)
Potentially making templates and slides from the speakers available ahead of time
Better way of communicating with speakers whilst presentation is in progress (e.g. to alert speaker they forgot to share their screen). We used text messaging in the end.
T-shirts and merch available throughout the conference and globally, not just the U.S.
More structured marketing campaign and possibly paid ads
I’ll try not to get sick 3 times (!!) in the 3 months leading up to the event
Having more swag bags and merch for attendees 🙂
I’m already thinking about improvements for the next event and can’t wait to bring SheetsCon 2021 to life.
How much did SheetsCon cost?
Here’s a breakdown of the expenses for SheetsCon:
Domain name purchase – $156 (included several SheetsCon domain variations)
Web hosting for SheetsCon – $131
WordPress theme – $79
Logo design on 99designs – $599
Fee to use Hopin platform – Redacted*
Swag bag t-shirts – $535
Swag bag stickers – $110
Postage for swag bags – $250
TOTAL – $1,860*
* Does not include the fee we paid to Hopin (I’m not sharing this publicly as it varies based on your unique situation)
What about revenue?
SheetsCon was free to attend. (The plan is to make it free to attend next year too!)
I also waived sponsor fees this year. I worked with a select few vendors as an experiment with the expectation that they would promote the event through their marketing channels.
I have a lot of ideas for how to monetize next year’s event, SheetsCon 2021.
Whilst SheetsCon 2020 did not make any direct revenue, it was still tremendously beneficial to my business.
Benefits From Running An Online Conference
If I didn’t make any money from SheetsCon, what did I get from it?
It was a lot of work, but it was a really fun and energizing two days.
I developed a lot of really good relationships with speakers, vendors and attendees as a result of this online conference.
It was a huge growth and learning experience for me too, as I’ve never put together an event like this.
It established the SheetsCon brand and laid the foundations for future, bigger, events.
It certainly raised my profile within the G Suite and Google Sheets communities.
And it resulted in about 3,500 new and high-quality leads. These are potential future customers for my online training business and future SheetsCon event attendees. For people on my mailing list I typically see a 1 – 2% conversion rate, so applying that here would look something like this: 2% * 3,500 = 70 new buyers @ $299 = $20,930 future revenue (this is a hypothetical illustration).