I'm no spreadsheet guru, but I have spent copious amounts of time in Google Sheets. From organizing monthly marketing deliverables into a color-coded content calendar to tracking profits for my small business where I upcycle clothing—Google Sheets has proven to be one of my most beloved tools. When used effectively, it can simplify processes and just make your life easier overall.
In this article, I'll walk you through how to use Google Sheets, go over some helpful formulas, and provide you with some tips and tricks to help you supercharge your work.
Table of contents:
Quick review: What is Google Sheets?
Google Sheets is a spreadsheet app that you can access via the web. So does that mean it's basically just Google's version of Microsoft Excel? Kind of, but not quite.
Is Google Sheets the same as Excel?
If you're familiar with Excel, you'll have an easier time learning how to use Google Sheets. They're both spreadsheet apps, so they have a lot of overlapping features, but there are a few important differences. You can read all about them in Zapier's Google Sheets vs. Microsoft Excel comparison, but here are the main takeaways:
Google Sheets was created with collaboration in mind and makes it easy to share worksheets, grant edit access, and collaborate in real-time. While Excel Online has come out with similar collaboration features, they don't run as smoothly compared to Google Sheets.
Google Sheets recently updated its cell limit to 10 million, but it still pales in comparison to Excel's 17 billion cells per spreadsheet. That makes Excel the better tool for dealing with big data. The good news is that most of us aren't dealing with data sets that large, so Google Sheets works just fine.
Excel has more powerful formulas and data analysis features, including built-in statistical analysis tools and extensive data visualization options. Google Sheets offers the "lite" version of most of those features, but it's nowhere near as in-depth.
Overview: Must-know Google Sheets terms
To kick things off, let's cover some spreadsheet terminology you'll need to know when using Google sheets:
Cell: A single data point or element in a spreadsheet
Column: A vertical set of cells
Row: A horizontal set of cells
Range: A selection of cells extending across a row, column, or both
Function: A built-in operation from the spreadsheet app you'll use to calculate cell, row, column, or range values, manipulate data, and more
Formula: The combination of functions, cells, rows, columns, and ranges used to obtain a specific result
Worksheet (Sheet): The named sets of rows and columns that make up your spreadsheet; one spreadsheet can have multiple sheets
Spreadsheet: The entire document containing your worksheets
1. How to create a spreadsheet
There are four ways to create a new spreadsheet in Google Sheets:
Option 1: Click the multi-colored "+" button on your Google Sheets dashboard.
Option 2: Open the menu from within a spreadsheet and select File > New > Spreadsheet.
Option 3: Click the multi-colored New button on your Google Drive dashboard and select Google Sheets > Blank spreadsheet.
Option 4: Type "sheets.new" into your browser.
This will create a new blank spreadsheet (or a populated Google Sheets template if you choose one of those; for this Google Sheets tutorial, though, you should start with a blank spreadsheet).
The Google Sheets interface should remind you of at least one other spreadsheet app you've seen before, with familiar text editing icons and tabs for extra sheets.
2. How to add data to your spreadsheet
Look around the white and gray grid that occupies most of your screen, and the first thing you'll notice is a blue outline around the selected cell or cells.
When you open a new spreadsheet, if you just start typing, you'll see that your data starts populating the top-left cell immediately. There's no need to double-click cells when you add information, and not much need to use your mouse.
An individual square in a spreadsheet is called a cell; they're organized into rows and columns with number and letter IDs, respectively. Each cell should contain one value, word, or piece of data.
Feel free to select any cell you'd like, then go ahead and type something in. When you finish entering data into a cell, you can do one of four things:
Press Enter / return to save the data and move to the beginning of the next row.
Press Tab to save the data and move to the right in the same row.
Use the arrow keys on your keyboard (up, down, left, and right) to move one cell in that direction.
Click any cell to jump directly to that cell.
If you don't want to type in everything manually, you can also add data to your Sheet en masse via a few different methods:
Copy and paste a list of text or numbers into your spreadsheet.
Copy and paste an HTML table from a website.
Import an existing spreadsheet in CSV, XLS, XLSX, and other formats.
Copy any value in a cell across a range of cells via a click and drag.
How to copy and paste data
Copy and paste is pretty self-explanatory, but there are times when you'll try to copy a "spreadsheet-y" set of data from a website or PDF, and it will just paste into one cell or format everything with the original styling. To avoid pulling your hair out, try looking for data that's actually in an HTML table (like movie data from IMDb, for example) to avoid getting funky pasted data in your spreadsheet. Here's a video showing how to copy and paste data in Google Sheets.
If you do end up with oddly formatted data, don't worry: we'll fix that in the next section!
How to import a file
Importing a file is simple as well. You can either import directly into the current spreadsheet, create a new spreadsheet, or replace a sheet (i.e., an individual tab) with the imported data.
The most common files you'll import are CSV (comma separated values) or XLS and XLSX (files from Microsoft Excel). To import a file from outside of your Google Drive, go to File > Import > Upload. Here's a quick video tutorial to demonstrate how.
I prefer to import the data into a new sheet every time to keep my old data and newly imported data separate. Alternatively, if you have a Google Sheet (or a CSV, XLS, or other spreadsheet file) saved in your Google Drive account, you can import that directly into your spreadsheet using the same process—just search your Drive from the import window.
How to autofill data
Dragging to copy a cell value needs a bit of explanation because you'll use this one a lot once you've set up formulas in your spreadsheets.
By dragging the small blue dot (pictured below) in the bottom-right corner of a highlighted cell across or down a range of cells, you can perform a number of different tasks:
Copying a cell's data to neighboring cells (including formatting)
Copying a cell's formula to neighboring cells
Creating an ordered list of text data
Here's an example of how creating an ordered list might work. Try adding the text "Contestant 1" to Cell A1, then click and drag the little blue dot in the bottom-right corner of the highlighted cell either down or across any number of neighboring cells.
If there was no number after Contestant, this dragging action would simply copy "Contestant" to any cells you drag over. But because the number is there, Sheets knows to increment the next cell +1.
Let's assume that you either copied, pasted, imported, or typed in a good chunk of data, and your spreadsheet looks pretty healthy.
Now, how can we use this data?
In addition to the methods I reviewed above, there are even more ways to manually and automatically import data into Google Sheets.
3. How to edit and format data for easy viewing
Whether you're tracking expenses, recording grades, or creating your to-do list, you'll want to sort, manipulate, and format your data.
How to use the Google Sheets toolbar
The basic formatting options in Google Sheets are available above your first cell. They're labeled in the image below, but for quick reference while you're working on a sheet, just hover over an icon to see its description and shortcut key.
Functions like printing, undo/redo, font settings, and font styling work similarly to what you'd expect from your favorite word processor.
For everything else, the best way to show you how everything works is to dive right into an example.
I'm going to create a quick list of potential breakfast options for tomorrow morning, along with their ingredients, counts, prices, and links to YouTube videos for how to make them (who knew you could make a three-minute video about pouring cereal into a bowl?).
For the simple example above, a lack of significant formatting is just ok. It does the basics: storing my information and allowing me to save it. But it's not something I would want to come back to each day.
Since I eat breakfast every morning, let's take some time to make this spreadsheet more user-friendly with some formatting.
How to freeze rows and columns
Working off the same spreadsheet, we'll freeze the first row in place. That means if we scroll down the spreadsheet, the first row will still be visible, no matter how much data lies below it. This allows you to have a long list and helps to keep tabs on what you're actually looking at.
There are two ways to freeze rows:
Click View > Freeze > 1 row in the navigation bar to lock the first row in place.
Hover over the dark gray bar in the top-left of the spreadsheet (until it becomes a hand), and drag between rows 1 and 2.
Freezing my header row is the first thing I do in every sheet I make.
How to hide rows and columns
Now say, for example, that you ran out of bread, so French toast is no longer an option. To temporarily hide that column from your list, you'll right-click Column C and then click Hide column. (Here's a full guide to how to hide rows.)
How to format text
Now, let's make the header text pop with some simple text formatting (remember, the text formatting tools are in the toolbar, just above your first row):
Drag to select the cells you want to format
Bold the text
Increase font size to 12pt
Center-align the whole row
Give your cells a gray fill
The next thing I'll do to clean this up a bit is format my "Average Price / Serving" to be a dollar value. Highlight the row, and then click the dollar sign icon to display the selected cells as a dollar amount rather than a regular number.
Read more: How to wrap text in Google Sheets
How to add a sheet
Now, let's say you wanted to make a similar list for breakfast and dinner. Instead of creating a whole new spreadsheet, click the "+" button in the bottom left corner to add additional sheets.
4. How to use formulas in Google Sheets
Google Sheets, like most spreadsheet apps, has a bunch of built-in formulas for accomplishing a number of statistical and data manipulation tasks. You can also combine formulas to create more powerful calculations and string tasks together. If you're already accustomed to crunching numbers in Excel, the exact same formulas work in Google Sheets most of the time.
For this tutorial, we'll focus on the five most common formulas, which are shown in the formula dropdown menu from the top navigation.
You can click a formula to add it to a cell, or you can start typing any formula with an equal (=) sign in a cell, followed by the formula's name. Sheets will autofill or suggest formulas based on what you type, so you don't need to remember every formula.
The most basic formulas in Sheets include:
SUM: adds up a range of cells (e.g., 1+2+3+4+5 = sum of 15)
AVERAGE: finds the average of a range of cells (e.g., 1, 2, 3, 4, 5 = average of 3)
COUNT: counts the values in a range of cells (e.g., 1, blank, 3, 4, 5 = 4 total cells with values)
MAX: finds the highest value in a range of cells (e.g., 1, 2, 3, 4, 5 = 5 is the highest)
MIN: finds the lowest value in a range of cells (e.g., 1,2,3,4,5 = 1 is the lowest)
We'll explore these formulas by improving our breakfast spreadsheet.
SUM formula
=SUM(range)
Let's start by adding up the total number of ingredients required for each recipe. I'll use the SUM formula to add each value in the recipes and get a total amount.
There are three ways to use the basic formulas accessible via the top navigation:
Select a range, then click the formula (this will put the result either below or to the side of the range).
Select the result cell (i.e., the cell where you want the result to appear), then click on the formula you want to use from the toolbar. Finally, select the range of cells to perform your operation on.
Type the formula into the result cell (don't forget the = sign), then either manually type a range or select the range.
I'll demonstrate all three methods in a video tutorial here. First, I'll sum my ingredients by selecting a range and clicking SUM from the formula menu. Second, I'll select a result cell and highlight the range of cells that will sum together. Finally, I'll demonstrate typing a formula and range manually. When you're done selecting the cells that you want to add together, press Enter / return.
Now that there's a formula set up to sum all of the ingredients together, you can select the formula cell and drag the blue dot across the other cells to copy the formula to those cells.
COUNT formula
=COUNT(range)
Now that we know how many parts are needed for each recipe, I'd like to know how complicated it is to make. I've simplified this by assuming that fewer ingredients means that the recipe is less complicated.
In order to count the number of ingredients in each recipe, I'll use the COUNT formula. The COUNT formula essentially checks to see if the cells in a range are empty or not and returns the total that are filled. This formula will be set up in my spreadsheet the same way as my SUM row. Watch a tutorial here.
According to my spreadsheet, cereal is the least complicated breakfast, but I'm still not convinced that an easy breakfast is worth it. What if it costs too much? What if the extra effort of cooking another meal saves me money?
Now, I'm going to refine my decision by figuring out the average cost per serving of the breakfast choices by using the AVERAGE formula.
AVERAGE formula
=AVERAGE(range)
I've added some faux minimum and maximum prices per unit on my ingredients list to the right of my breakfast options. We'll want to get an average price for each ingredient using the low and high rates.
I'll start by highlighting the range of values (in this case, it's two side-by-side rather than a vertical range) and selecting the AVERAGE formula from the toolbar.
This will drop the result into the column to the right of the maximum price column. Next, I'll drag the formula down to apply it to the rest of the list. Watch how here.
I'll label my column "Average Unit Cost," so we know what we're looking at.
MIN formula
Let's say I wanted to determine what the cheapest price per unit is of the options. While you could easily just look at the chart and pick it out, this formula is very helpful for large data sets and will save you from scrolling endlessly. See how I use this formula here.
MAX formula
Similarly, if I wanted to determine what the most expensive price per unit is of the options, I would use the MAX formula. See how I use this formula here.
Once you have the hang of the formulas above, there are plenty of other Google Sheets functions to learn.
5. Additional Google Sheets tutorials and tips
Now that you know how to create a spreadsheet, import data, and use formulas, I'm going to walk you through some additional tips.
How to create a pivot table
A pivot table is a helpful way to analyze and visualize data. To create a pivot table, follow these steps:
Navigate to Insert > Pivot table.
In the pivot table editor, add your chosen row and column values.
Next to Values, click Add, and select your desired value from the dropdown menu (you could also choose from one of the suggested pivot table options).
In the example here, I created a faux data set and used a pivot table to show the average training scores of each department.
In addition to this quick tutorial, there are many different ways to build and use pivot tables in Google Sheets.
How to use shortcuts to save time
When using Google Sheets, shortcuts can help you save time and work faster. I've listed some of my most-used shortcuts below.
Command | Shortcut |
|---|---|
Cut | Control + X / command + X |
Copy | Control + C / command + C |
Paste | Control + V / command + V |
Undo | Control + Z / command + Z |
Redo | Control + Y / command + Y |
Select an entire row | Shift + Spacebar |
Select an entire column | Control + Spacebar / command + Spacebar |
How to create charts and graphs
An important step of data analysis is visualizing your data, and Google Sheets can help you do just that using charts and graphs. Simply highlight the essential data and click Insert > Chart. A chart will be created, and you can edit it as you like from the sidebar.
You can play around with the different chart types and find the one that works best for your data set.
Zapier lets you automatically generate charts and reports in Google Sheets.
How to share, protect, and move your data
What makes Sheets so powerful is how in sync you'll feel with your coworkers. Jointly editing a spreadsheet is one of the critical functions of Sheets, and Google made it a seamless experience.
Here's how it works:
Click either File > Share or use the green Share button in the top-right.
Enter emails of who can view or edit your spreadsheet.
Select any other privacy options and hit Done.
Once you add people to share with, you can either give them viewing, commenting, or editing access. You can also add an expiration date if you're only accepting comments for a limited time.
In addition to sharing with specific people, you can also give general access to anyone in your organization or anyone with the link.
Sharing spreadsheets with your devices and apps
Even though Google Sheets is built for sharing between users, you'll notice that many times, your spreadsheets are created as internal documents, and sharing is secondary to actually getting work done.
You can streamline your spreadsheet workflows and real-time data sharing by taking advantage of these helpful add-ons:
Google Sheets mobile apps. You can use the Google Sheets mobile app to view and edit your spreadsheets, share links on the go, and add users. It's a solid companion to—but not a replacement for—the web app.
Google Drive. Google Drive allows you to easily upload files from your local desktop environment to your online Drive. This makes them accessible to your collaborators and also allows you to quickly import them into spreadsheets and other documents.
A third-party tool like Zapier. You can use Zapier to automatically add data to your spreadsheets, send files to your Google Drive account, alert you of changes to your Sheets—you name it.
Check out Zapier's Google Sheets integration page for more information on supported data and triggers.
Read more: How to lock cells in Google Sheets
How to download data
If you need to send your files to external collaborators, upload a file into another system, or just like having backups, then turn to one of Google Sheets' many data export options.
The most common exports will be either XLS (Excel document), PDF, or CSV (comma separated values). If you're not sure which format to use, a CSV is usually the best bet.
Use your spreadsheet in offline mode
Google Sheets has an offline mode that automatically syncs your changes to the document when you reconnect to the internet. This is useful for any situation where you'd need to treat Google Sheets like a desktop application—on a flight or a road trip, for example.
Here's what you'll need:
Instructions for setting up your offline sync are really straightforward, but the bulk of the process is just downloading and using the three core components above.
Navigate to File > Make available offline to turn it on.
And just like that, you can use Google Sheets even when you're offline—no WiFi necessary.
Learn another fun Google Sheets tip: How to add a dropdown list in Google Sheets
7. Bonus: How to automate Google Sheets using Zapier
Google Sheets is a powerful tool—it's everything you'd expect from a spreadsheet with the extra perks of an online app. While the example spreadsheet that we created may have been a bit silly, the practical applications of using Sheets for your workflows (both business and personal) are limitless.
Once you get the hang of Google Sheets, use Zapier to automate specific tasks like savings URLs, creating Google Calendar events, and more. Depending on your industry, there are other super specific things you can do like:
And with everything stored in Google Drive, you'll never worry about losing your files again—even if your computer dies.
This article was originally published by Michael Grubbs in July 2016. The most recent update was in March 2023.
