Google Sheets is a cloud-based application offered by Google that allows its users to create spreadsheets from anywhere. It offers similar functions to Excel, while also allowing users to collaborate in real-time, from anywhere. If you’re like me, a self-proclaimed spreadsheet pro/Excel junkie, you probably feel like learning about Sheets is a waste of time (hint: it’s not). In this guide, we’ll go through some of the more advanced features of Google Sheets, so that you’ll have a solid understanding of the ins and outs of Sheets. Here, we also assume you have a good understanding of Sheets, but if you don’t think you do, or just could use some review, check out Zapier’s great tutorial on the basics of using Google Sheets. Here’s what we’ll be covering:
At first glance, Google Sheets and Excel seem quite similar and, well, they are. Both are spreadsheet tools used for analyzing data. For example, sales managers can analyze sales performance, business owners can manage budgets, and data analysts can analyze large data sets. These tasks seem relatively straightforward and at their core that’s what Excel and Google Sheets are meant for. Both tools allow you to input structured data (or, in some cases, unstructured data), then organize, analyze, and present it efficiently. While, at their core, these products are the same, they do differ in a few key ways.
Price is a great place to start, as it’s a clear advantage for Google Sheets. Google Sheets is completely free to use. (Enterprise access to the entire G Suite, however, is $6/month per user if you want to use Google Sheets in tandem with its other applications.) Meanwhile, full access to Excel costs $8.25/month per user, though there is a web-based version with downgraded features. Most companies choose to purchase Excel, but for individuals or smaller companies, the price tag can be expensive. If you’re not an especially heavy spreadsheet user, or don’t need some of the more complex analytical features and charting tools that Excel offers, Google Sheets may be the answer.
This is a key way in which these two products differentiate themselves. Google Sheets is a web-based spreadsheet software, while Excel is not web-based, and the differences are exactly what they sound like. Google Sheets is on the internet, so multiple teammates can collaborate in real-time. Sharing is also easy; you can specify which users can edit/view the spreadsheet, and even restrict the spreadsheet to users within your organization. If you’re a die-hard Excel user, you can still collaborate using the co-authoring feature on Excel, or with Sharepoint, but it’s a lot more difficult to use and is restricted to certain versions of Excel. Google Sheets is built for collaboration, so while Excel has augmented its sharing capabilities, collaboration with Google Sheets is simply much easier.
We’ve all spilled coffee on our computers and, unfortunately, that usually means losing all of our files. Since Excel files are saved locally, these are wiped when your computer breaks. There are ways to get around this, like OneDrive, or repeatedly sending the document to yourself, but these are manual, and people like me are prone to forgetting about them. Meanwhile, Google Sheets is web-based, so changes are saved automatically, and the document is always accessible via the cloud. Further, we’ve all had infuriating version control issues with Excel, where someone saves down version #550 of an Excel file, and someone else accidentally edits version #449 (so frustrating!!). This problem doesn’t happen with Google Sheets due to document changes being tracked, and the advantage of real-time collaboration.
Add-ons are the last advantage of Google Sheets over Excel that we’ll discuss in this section. Google Sheets gives you access to hundreds of add-ons, which are a powerful tool for automating work and adding analytical complexity. Here are a few that we’ll highlight:
For more useful add-ons, and help with installing them, I’d check out Zapier’s awesome blog post about supercharging your spreadssheet. You can also check out the GSuite Marketplace for more add-ons that work across a variety of Google products. For a more in-depth comparison between Excel and Google Sheets, check out our guide on spreadsheet basics.
Now let’s explore pulling web-based data into Google Sheets. This is yet another advantage that Google Sheets has over Excel. The internet contains copious amounts of data, and Google Sheets, in conjunction with other services and strategies, makes it easy to pull and organize this data. You can always use APIs to set this up for yourself, and you can visit our tutorial on building APIs to figure this out, but there are also a few other great methods, including integrations and formulas, that can make your job really easy. We also went over a few great add-ons in the previous section that can also make this really easy.
This is where we’ll start because it makes scraping data simple if you have any experience with coding in HTML/XML. With IMPORTXML, you can import structured data from any page you want into your spreadsheet. For example, you could import email addresses from a website or stock prices from any website. As long as the data is an XML field, you’re good to go.
IMPORTHTML is helpful for pulling table and lists for websites into It is similar to other functions for importing data, only the syntax is slightly different.
ImportData is also a great function that’s pretty similar to ImportXML. Instead of XML fields, ImportData will pull data from .csv or .tsv content saved on the page of a website. At a high-level, .csv and .tsv are just ways to store text data with records separated by either a comma or tab. We won’t go into the intricate differences between the two, but you can learn more about them here. Visually, they look similar to an Excel or Google Sheets spreadsheet. These are also frequent ways to store data, so this formula can definitely come in handy. If you want to go more in-depth on ImportData, I’d read HowToGeek’s tutorial on how to use it.
IMPORTRANGE is useful if you want to import data from another spreadsheet. Simply find the URL of the spreadsheet you want to pull data from, specify the range, and ImportRange will pull the data into your spreadsheet.
This is a formula for isolating certain data from long strings of words. For example, you could use it to isolate an email address or URL from a paragraph. You could also use it to pull a path or root domain from a URL. Unlike the previous formulas, the data you’re looking for can be hidden within a block of text and Regexextract can still isolate it for you. We won’t go into the specifics on how to use it, but you can check out this article by Distilled if you’re looking for a pretty basic introduction to it. Otherwise, you can use CIFL’s general guide to Google Sheets tools for marketers for a deeper dive.
Integrations are the final, and most powerful, Google Sheets feature that we’ll cover. Integrations are similar to add-ons, except the distinction is that applying integrations are a little more difficult, as integrations are with apps not listed on the GSuite marketplace, and thus cannot connect to Google Sheets directly. However, the concept is exactly the same as add-ons; connect different apps to Google Sheets to increase productivity and automate manual tasks. If you don’t want to use an external service, the best you can do is use an API, which we cover in a different tutorial. Otherwise, just use Zapier! I know we mention them a lot, but Zapier’s main line of business is building really great APIs.
This is a pretty barebones description of the intricacies of pulling data for sheets, so we also wanted to provide a few complementary sources of information. Especially on the formula front, CIFL has a great article on the topic. Zapier is if you’re looking for extra resources to manage your spreadsheet.
Now that you have all of this data, well, what do you do? Assuming you have a solid base in Excel/Google Sheets, we’ll go through a few Google Sheets formulas that can make a big difference in your data analysis. A few of these formulas we’ve gone through in our spreadsheets tutorial, they’re useful for both Excel and Google Sheets, so we’ll only go through a few formulas. There are literally hundreds of useful formulas, so I’d check out this Hubspot article if you want to get more in-depth.
IF statements are one of the most basic functions in the spreadsheets universe, but it's also essential to achieving meaningful work in Google Sheets. An IF statement is a simple logic test as to whether or not something is true, with an action occurring depending on the validity of the statement. IF statements are most crucial when you’re interested in qualifying different data for use. For example, a simple use of an IF statement would be if your spend is greater than your budget, you are over-budget. If it is less than your budget, you are under-budget. Of course, there are much more complex uses of this function and you can also use it in tandem with other Google Sheets functions. Both AND and OR functions form great pairs with an IF statement. You can also use an IF statement within another IF statement (a double IF!). There are a lot of uses and you can see more here.
Vlookup & Index Match are two similar options in Google Sheets to find specific data across multiple sheets or a large collection of data. For example, if you’re a mortgage banker, you may have a list of different leads with different characteristics attached to each lead. If you think this month, people from a certain neighborhood in your city are more likely to respond, you can use a Vlookup or Index Match function to pull information for only these people. These formulas become really useful when you’re dealing with hundreds, or even thousands, of entries in your data table.
This is another way that Google Sheets separates itself from Excel. Though Excel has this functionality, it’s difficult to use and you’re likely better off sticking with Google Sheets. Query is useful because it combines data filtering with some of the functions that you use often, like SUM and AVERAGE. You input the data range you’re analyzing, along with a query string that specifies what you’re looking for. The functionality of Query is hard to understand from a short paragraph, but Query can replace most aspects of a pivot table (something we’ll talk about later). Some people think this is the most useful formula on the internet, so it’s a really useful trick to know.
The last aspect of a great Google Sheet is good presentation. All of this functionality is available on Excel, so you can find a better tutorial in our general spreadsheets walkthrough, but we’ll go over a few key ideas.
Graphs are a pretty obvious place to start. Simply highlight your data, click on insert, and then click on ‘chart’. That will take you to a variety of different chart options. Excel is probably a better service for charting, but, for the vast majority of purposes, Google Sheets will serve you well. The description above is very simple, but you can always go more in-depth.
Pivot Tables are also crucial to effectively use Google Sheets. Pivot Tables are best for analyzing large amounts of data through isolating important rows and columns. You can do pretty much the same thing with formulas, but formulas take longer to write and pivot tables can be rapidly optimized for isolating different areas of the data. To create one, simply click on a cell in your data table, go to data, and then click ‘Pivot Table’. Google Sheets will suggest a few options for you or you can customize your own table. There’s an extensive amount of writing on Pivot Tables because they’re essential to becoming a spreadsheet power user.
This was a short walkthrough of some of the advanced features that Google Sheets has to offer. There are a TON of resources to help you on your journey to become a spreadsheet power use. Here are some of our favorites:
