Whether you are budgeting expenses or managing a complex group project, a spreadsheet can go a long way in organizing things properly. And, while MS Excel might have served well a decade ago, these days, there is no better spreadsheet application than Google Sheets.
Not only does it have features that cannot be found on an offline spreadsheet application, but with its intuitive UI and clean design, it is also far more accessible. And, with our Google Sheets 101 guide, you can start using Google Sheets to its fullest extent today!
Everyone has encountered MS Excel at least once. Maybe it was only once during a computer class at school, but the chances are that you have already seen a spreadsheet.
Google Sheets is an online, browser-based spreadsheet application. Along with Google Docs and Google Slides, it forms a suite of free-to-use office applications bundled with a simple Google Drive account.
Contrary to what you might believe, Google Sheets isn’t a "lightweight" version of Excel, but a full-fledged spreadsheet app that actually boasts of more functionality than even Microsoft’s own offering.
Google Sheets is completely free to use for users with a Gmail account. 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.
Google Sheets is a cloud-based app. This means that your spreadsheets are stored on an online server that can be accessed on multiple devices from multiple accounts. You can start a sheet on your laptop and finish adding details on your workstation, using your mobile to make last-minute changes.
More importantly, your entire team can work on a single spreadsheet simultaneously, making collaboration a breeze.
And don’t worry about it becoming a mess of conflicting edits; Google Sheets has options for customizing the access level of each user, besides keeping track of every change.
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 every few seconds, 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.
Anyone who has ever made a presentation knows the value of visual information. Hard numbers might be what decide the effectiveness of a business proposal, but most people find a spreadsheet difficult to understand.
That is why every spreadsheet application needs tools to convert all that neatly organized data into a visually appealing format.
While in Excel you had your good old bar graphs and pie charts, in Google Sheets you have much, much more. With features like slicers and scorecard charts, you can get more out of your graphs and tables.
But, the most powerful visualization tool in Sheets is the Google dashboard. With a few clicks, you can turn your boring spreadsheet into a dynamic representation that conveys a wealth of information at a glance.
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 spreadsheet. 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.
To get started with Google Sheets, you first need a Google account. In case you have never even used Gmail before, take a moment to signup with a simple form.
Once you have an account, simply head over to Google Sheets to get started with your first spreadsheet.
When you open Google Sheets for the first time, you are offered a tour of its features. Feel free to take the tour if you want (though our guide will teach you all that you need to know anyway).
After dismissing the prompt you can finally see the ‘homepage’ of Google Sheets. It looks a little empty right now, but once you have created a few spreadsheets you can easily access and organize them from this screen.
As you can see, there are a few handy templates at the top to help you get started. Common use cases of a spreadsheet such as a to-do list or budgeting are already present for bootstrapping your progress.
For now though, let us get acquainted with the basics of using Google Sheets with a blank document.
This view should be familiar to anyone who has used a spreadsheet before. If anything, the interface is actually simpler, as Google has opted for a cleaner approach than most offline spreadsheet applications.
The spreadsheet itself is composed of a grid of horizontal rows and vertical columns, creating ‘cells’ at every intersection. Each of these cells can hold any kind of data, ranging from simple numbers to names and even entire sentences.
Filling up these cells with data is a simple matter of clicking on a cell and typing it in. Even better, you can move from one cell to another without having to move your hand from the keyboard.
Hitting ‘enter’ after typing in the data takes you to the next cell down the column while tapping the ‘tab’ button takes you down the row. You can also use the arrow keys to freely jump between cells in any direction.
By default, a new sheet begins entering data from the very first cell on the top left corner; feel free to click elsewhere if you have a different layout in mind.
Generally speaking, formatting works the same in Google Sheets as in any word processor like Google Docs. Drop down the ‘Format’ menu to get access to options such as font size, italics, font color, and alignment.
But, a spreadsheet also has some additional options that you may not have seen earlier. One of them is currency formatting.
Unlike a document, a spreadsheet is going to deal a lot with currency values (especially if you are making a budget), and without the proper formatting they end up looking a bit odd. In very large spreadsheets, the lack of good formatting can make it hard to parse information quickly.
For example, this is how a row of expenses looks like by default.
While we can go in and insert a dollar sign before each number, that approach is not advised. For one, it can quickly get tedious in larger sheets. Second, such a method is not reliable, as you might miss a few entries. But the most important reason is that manually adding a dollar symbol changes the number into a text entry, making it impossible to apply mathematical formulae on it (more on that later).
Fortunately, the method for currency formatting is rather straightforward. Select the row containing our currency values (by clicking on the row number itself, or click-dragging through the cells) and then click on the ‘$’ symbol in the toolbar above.
This immediately applies the correct formatting to the currency values, along with decimal points.
Perhaps, instead you want to have consistent formatting for percentages, or dates, or even phone numbers. You can typically set these by going to Format, then selecting the Number drop down menu. If you have a more specific format in mind, check out our tutorial on how to set your own number formats with queries.
Another formatting feature applicable to spreadsheets is alternate colors.
To improve the readability of your tables, you can apply bands of alternating color as a background. To do so, head to the Format menu and select the ‘Alternate Colors’ option.
A box pops up with a variety of color schemes to choose from. Click whichever you like, and your table will become a lot more interesting to look at.
A common issue faced when working on large spreadsheets is that the header row (where you put column titles) disappears once you have gone too far down the sheet. This forces you to scroll back up every few seconds simply to determine the identity of each column, which not only wastes time but also leads to errors.
To overcome this problem, Google Sheets allows you to ‘freeze’ a row (or even multiple rows). The frozen row will remain on the top no matter how far you scroll down. This can be applied on columns as well, for the situations when you are using the leftmost columns for denoting categories as well.
But how do you freeze a row? First, take a look at a normal table of values.
See that unnamed grey cell at the top-left corner? The thick horizontal and vertical borders are actually rulers that you can drag. Say we want to freeze the topmost header row. We will click and drag the horizontal border between rows 1 and 2.
And that’s it. If we scroll down now, we will notice that the frozen row stays in place. If you need a more in-depth guide on freezing rows/columns, check out our full tutorial.
Often you might find yourself needing an additional column between two existing columns already filled with data. Before you start copy-pasting half the table to move it aside, let’s see how can insert new columns (or rows).
To begin, select any of the two columns you wish to insert your new columns between. (In case you are wondering how, just click on the column name. The same goes for selecting rows as well.)
With the column selected, right-click on it, to be presented with a drop-down menu. There are two options before you: ‘Insert 1 left’ or ‘Insert 1 right’. Clicking on the latter gives us this.
Notice that Google Sheets adjusts the column numbering automatically. This works with formulae as well (which we will work with later), so adding a new column or row does not mess up all those carefully constructed calculations populating your spreadsheet.
Sometimes, there are columns that need to be wider than others. In a spreadsheet holding employee information, for example, the name column must be wide enough to accommodate two words, even when other columns holding numeric data can much narrower.
Changing the size of rows and columns is actually quite easy. Hovering the cursor at the edge of the name cells (the alphabets for the columns and the numbers for the rows) changes it to a double-sided arrow, which can then be click-dragged to resize the entire row or column. This is similar to how one resizes windows.
In this sheet, we have increased the width of the D column and shrunk the others.
Beyond inserting and resizing, there are other ways to manage your spreadsheet data. Here are some other actions to check out:
When collaborating with a bunch of different people on a single spreadsheet, it can be a bit difficult to keep track of what changes were made by whom. Often, the additions by one member can overwrite the work already done by another, leading to confusion.
That is why Google Sheets lets you set the accessibility of individual cells in addition to restricting access to the whole document itself.
You can decide who gets to make changes to a designated range of cells (or a whole spreadsheet). This is very useful for important columns that hold the field names of the table, as they are usually not meant to be tampered with after the spreadsheet has been created.
Spreadsheets differ from word processors in not only how they are presented, but also in how they handle data. At the very basic level, Google Sheets differentiates between numbers and text, allowing you to perform automated calculations with your data.
Here, it’s important to know all the different data types to organize your spreadsheets more efficiently and use your data.
Now that you have all of this data, well, what do you do? We've included some key formulas below to get started. There are literally hundreds of useful formulas, so I’d check out this Hubspot article if you want to get more in-depth.
One of the most advanced – and the most useful – features of Google Sheets are functions. Functions enable you to set up automatic calculations in your spreadsheet. These calculations are also self-updating; any changes to the data (or even new entries) result in re-evaluating the result.
But you don’t have to be a maths prodigy to take advantage of dynamic calculations in your spreadsheet. Using Google Sheets’ built-in functions, you can implement simple calculations without typing a single formula.
Clicking on the formula drop-down menu gives you access to five most commonly needed formulae: SUM, AVERAGE, COUNT, MIN, MAX. From adding up numbers to finding the maximum value in a large range, these formulae include all the common functions you would require in a usual spreadsheet.
Using any of these functions is quite easy. Just select the range of cells you wish to apply the formula to, and select it from the menu.
As you can see, the appropriate formula appears in the cell (and in the formula bar above). Tap enter and the result will appear. Try changing the values of any of the entries, and the total will change to match the correct result by itself.
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!).
To learn more about the IF statement, check out our tutorial 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.
The formula, however, takes some getting used to. For a more detailed how-to, see here.
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. Some people think this is the most useful formula on the internet, so it’s a really useful trick to know.
Lucky for you, we have a plethora of tutorials on using queries in Google Sheets. Click here to search through the use-cases.
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 easy. We also went over a few great add-ons in the first 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. (If you don’t, you can always learn more or use the other tools that we’ll cover later in this section.)
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.
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.
If you'd like to learn more, check out the function in our tutorial on how to import data from another workbook.
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.
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. For instance, Google Sheets still allows you to create charts for multiple ranges of data.
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. These are essential to becoming a spreadsheet power user, and more information can be found in our Pivot Tables 101 article here.
While Google Sheets is very intuitive to get started with, it has a fair share of advanced features that can help you get more out of your spreadsheets. There are a TON of resources to help you on your journey to become a spreadsheet power use. Here are some of our favorites:
