I’m not a spreadsheet expert by any means, but I’ve spent a lot of time in Google Sheets. From organizing monthly marketing deliverables in a color-coded content calendar to tracking profits for my small clothing recycling business, Google Sheets has become one of my favorite tools. When used effectively, it can simplify processes and make your life easier in general.
In this article, I’ll guide you on how to use Google Sheets, introduce you to some useful formulas, and give you a few tips to help boost your work.
Overview: The Essential Terms in Google Sheets
To start off, let’s look at a few basic terms in spreadsheets that you’ll need to know when using Google Sheets:
- Cell: A data point or individual element in a spreadsheet.
- Column: A vertical set of cells.
- Row: A horizontal set of cells.
- Range: A selection of cells that spans across a row, column, or both.
- Function: A built-in operation in the spreadsheet application that you use to calculate cell, row, column, or range values, manipulate data, etc.
- Formula: The combination of functions, cells, rows, columns, and ranges used to achieve a specific result.
- Sheet: Named sets of rows and columns that make up your spreadsheet. A spreadsheet can contain multiple sheets.
- Spreadsheet: The entire document that contains your sheets.
1. How to Create a Spreadsheet
There are four ways to create a new spreadsheet in Google Sheets:
- Option 1: Click the multicolored “+” button on your Google Sheets dashboard.
- Option 2: Open the menu from within a spreadsheet and select File > New > Spreadsheet.
- Option 3: Click the multicolored “New” button on your Google Drive dashboard and select Google Sheets > Blank spreadsheet.
- Option 4: Type “sheets.new” in your browser.
This will create a new blank spreadsheet (or a Google Sheets template if you choose one; for this Google Sheets tutorial, start with a blank spreadsheet).
The Google Sheets interface should remind you of at least one other spreadsheet application you’ve seen before, with familiar text editing icons and tabs for additional 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(s).
When you open a new spreadsheet and simply start typing, you’ll see your data immediately fill the top-left cell. There’s no need to double-click cells when adding information, and you won’t need to use your mouse much.
An individual square in a spreadsheet is called a cell; they are organized in rows and columns with numeric and alphabetic identifiers, respectively. Each cell should contain a value, word, or data.
Feel free to select any cell you want, then type something. Once you’re done entering data in a cell, you can do one of four actions:
- Press the Enter/Return key to save the data and move to the beginning of the next row.
- Press Tab to save the data and move right in the same row.
- Use the arrow keys on your keyboard (up, down, left, and right) to move a cell in that direction.
- Click any cell to directly access that cell.
If you don’t want to type everything manually, you can also add data to your sheet in bulk via 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 a value in a cell across a range of cells by clicking and dragging.
3. How to Edit and Format Data for Easy Visualization
Whether you’re tracking expenses, keeping notes, or making your to-do list, you’ll want to sort, manipulate, and format your data for easy visualization.
How to Use the Google Sheets Toolbar
Basic formatting options in Google Sheets are available above your first cell. They are labeled in the image below, but for a quick reference while you work on a sheet, simply hover over an icon to see its description and shortcut key.
Functions such as printing, undo/redo, font settings, and style work similarly to what you would 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’ll quickly create a list of potential breakfast options for tomorrow morning, along with their ingredients, quantities, prices, and links to YouTube videos explaining how to prepare them (who knew you could make a three-minute video on pouring cereal into a bowl?).
For this simple example, minimal formatting is acceptable. It does the bare minimum—store my information and allow me to save it. But it’s not something I’d want to come back to every day.
Now that 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
From the same spreadsheet, we’ll freeze the first row in place. This means that if we scroll the spreadsheet down, the first row will always remain visible, no matter how much data is below it. This allows you to have a long list and keeps your eyes on what you’re actually looking at.
There are two ways to freeze rows:
- Click on View > Freeze > 1 row in the navigation bar to lock the first row in place.
- Hover your mouse over the dark gray bar in the top-left of the spreadsheet (until it becomes a hand), then drag between rows 1 and 2.
Freezing my header row is the first thing I do every time I create a new sheet.
How to Hide Rows and Columns
Now let’s say, for example, you no longer have bread, so French toast is no longer an option. To temporarily hide that column from your list, you’ll right-click on Column C and click Hide column. (Here’s a complete guide on how to hide rows.)
How to Format Text
Now, let’s make the header text more visible with simple text formatting (remember, text formatting tools are located in the toolbar, just above your first row):
- Drag to select the cells you want to format.
- Make the text bold.
- Increase the font size to 12 points.
- Align the text in the center of the cell.
- Give your cells a gray background.
The next thing I’m going to do to clean up this spreadsheet a bit is format my “Average Price per Serving” as currency. Select the row, then click on the dollar sign icon to display the selected cells as a dollar amount instead of just a number.
How to Add a Sheet
Now let’s say you want to create a similar list for dinner as well. Instead of creating a whole new spreadsheet, click on the “+” button in the bottom left corner to add additional sheets.
4. How to Use Formulas in Google Sheets
Google Sheets, like most spreadsheet applications, has a set of built-in formulas to perform various statistical tasks and data manipulation. You can also combine formulas to create more powerful calculations and chain tasks. If you’re already familiar with manipulating numbers in Excel, the same formulas generally work in Google Sheets.
For this tutorial, we’ll focus on the five most common formulas, which are displayed in the formula dropdown menu in the top navigation.
You can click on 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 name of the formula. Sheets will auto-fill or suggest formulas based on what you type, so you don’t need to memorize them all.
The most basic formulas in Sheets are:
- SUM: Adds a range of cells together (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, empty, 3, 4, 5 = total of 4 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 while enhancing our breakfast spreadsheet.
SUM Formula
Let’s start by adding up the total number of ingredients needed for each recipe. I’ll use the SUM formula to add up each value in the recipes and get a total amount.
There are three ways to use the basic formulas accessible through the top navigation:
- Select a range, then click on the formula (this will either place the result below or to the right of the range).
- Select the result cell (i.e., the cell where you want to see the result), then click on the formula you want to use in the toolbar. Finally, select the range of cells you want to perform your operation on.
- Type the formula in the result cell (don’t forget the equal sign), then manually type a range or select it.
I’ll demonstrate all three methods in a tutorial video here. First, I sum up my ingredients by selecting a range and clicking on SUM in the formula menu. Next, I select a result cell and highlight the range of cells that will be added together. Finally, I show how to type a formula and range manually. When you’re finished selecting the cells you want to add, press Enter/Return.
Now that a formula is set up to add all the ingredients together, you can select the formula cell and drag the blue dot to the other cells to copy the formula into those cells.
COUNT Formula
Now that we know how many servings each recipe requires, I’d like to know how complicated the recipe is to make. I’ve simplified this by assuming that fewer ingredients mean the recipe is less complicated.
To count the number of ingredients in each recipe, I’ll use the COUNT formula. The COUNT formula essentially checks if the cells in a range are empty or not and returns the total of those 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’s too expensive? What if the extra effort of cooking another meal allows me to save money?
Now, I’ll refine my decision by calculating the average cost per serving for the breakfast choices using the AVERAGE formula.
AVERAGE Formula
I’ve added minimum and maximum prices per unit to my ingredient list to the right of my breakfast options. We want to get an average price for each ingredient using the low and high rates.
I’ll start by highlighting the value range (in this case, it’s two values side by side rather than a vertical range) and selecting the AVERAGE formula from the toolbar.
This will populate the result in the column to the right of the maximum price column. Then, I’ll drag the formula down to apply it to the rest of the list. Watch how I do it here.
I’ll label my column “Average Cost per Unit” so we know what we’re looking at.
MIN Formula
Now let’s say I want to figure out which option has the cheapest price per unit among the choices. While you could easily see it by looking at the table and picking, this formula is very useful for large data sets and will save you from scrolling indefinitely. Watch how I use this formula here.
MAX Formula
Similarly, if I wanted to determine which option has the most expensive price per unit among the choices, I would use the MAX formula. Watch how I use it here.
5. Additional Tutorials and Tips for Google Sheets
Now that you know how to create a spreadsheet, import data, and use formulas, I’ll introduce you to a few additional tips.
How to Create a Pivot Table
A pivot table is a useful way to analyze and visualize data. To create a pivot table, follow these steps:
- Go to Insert > Pivot table.
- In the pivot table editor, add the row and column values you’ve chosen.
- Next to Values, click Add and select the desired value from the dropdown menu (you can also choose from one of the suggested pivot table options).
In the example below, I created a fake data set and used a pivot table to display the average training scores for each department.
How to Use Shortcuts to Save Time
When using Google Sheets, shortcuts can help you save time and work faster. Here are some of the shortcuts I use most often:
- Ctrl + C / Command + C: Copy
- Ctrl + V / Command + V: Paste
- Ctrl + Z / Command + Z: Undo
- Ctrl + Y / Command + Y: Redo
- Ctrl + B / Command + B: Bold
- Ctrl + I / Command + I: Italic
- Ctrl + U / Command + U: Underline
- Ctrl + S / Command + S: Save
How to Create Charts and Graphs
An important step in data analysis is visualizing that data, and Google Sheets can help you do that using charts and graphs. Simply highlight the essential data and click on Insert > Chart. A chart will be created, and you can modify it in the sidebar to your liking.
You can play around with different chart types and find the one that best suits your data set.
How to Share, Protect, and Move Your Data
What makes Sheets so powerful is how easy it is to collaborate with colleagues. Co-editing a spreadsheet is one of Sheets’ core functions, and Google has made it a seamless experience.
Here’s how it works:
- Click on File > Share or use the green Share button in the top right.
- Enter the email addresses of the people you want to share your spreadsheet with.
- Select any other desired privacy options, then click Done.
Once you’ve added people to share with, you can give them either view, comment, or edit access. You can also add an expiration date if you only want comments for a limited time.
In addition to sharing with specific people, you can also give access to all members of your organization or anyone with the link.
Sharing Spreadsheets with Your Devices and Apps
While Google Sheets is designed for sharing between users, you’ll find that your spreadsheets are often created as internal documents, with sharing being secondary to actually getting the 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 great addition 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.
- Third-party tools like Zapier: You can use Zapier to automatically add data to your spreadsheets, send files to your Google Drive account, notify you of changes to your sheets, regardless of the application.
How to Download Data
If you need to send your files to external collaborators, download a file into another system, or simply have backups, you can use one of the many Google Sheets data export options.
The most common exports will be either XLS (Excel document), PDF, or CSV (comma-separated values). If you’re unsure which format to use, CSV is usually the best choice.
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 helpful in any situation where you need to treat Google Sheets as a desktop app, such as on a flight or a road trip.
Here’s what you’ll need:
- Google Chrome
- Google Drive Chrome Web App
- Google Drive Sync
The instructions to set up offline sync are very straightforward, but most of the process simply involves downloading and using the three main components above.
Go to File > Make available offline to enable it.
And voila, you can use Google Sheets even when you’re offline, without the need for Wi-Fi.
7. Bonus: How to Automate Google Sheets with Zapier
Google Sheets is a powerful tool—it’s everything you expect from a spreadsheet with the added benefits of an online application. While the spreadsheet we created in this example may seem a bit trivial, the practical applications of using Sheets for your workflows (both professional and personal) are limitless.
Once you understand how to use Google Sheets, use Zapier to automate specific tasks like URL logging, creating Google Calendar events, etc. Depending on your industry, there are other very specific things you can do, such as:
- Automatically adding subscribers to Mailchimp from Google Sheets.
- Automatically updating inventory.
- Connecting Google Sheets to any application with webhooks.
- Automatically logging Shopify orders into a Google Sheets spreadsheet.
And with everything stored in Google Drive, you’ll never have to worry about losing your files, even if your computer crashes.
This article was originally published by Michael Grubbs in July 2016. The most recent update was made in March 2023.
For more information on using Google Sheets and to discover other online marketing tips, visit Crawlan.com.
