The Beginner’s Guide to Google Sheets

If you’re just beginning to use Google Sheets, its extensive features and add-ons can be a bit overwhelming. Here are some tips to get you on the fast track to start using this powerful, free cloud-based alternative to Microsoft Excel.

What Is Google Sheets?

If you’ve heard of Google Sheets before, feel free to skip ahead. If you’ve never heard of it, here’s a crash course on what you need to know. We’ll go over the basics and get you brushed up with what Google Sheets is and how you can get started right away.

Google Sheets is a free, cloud-based spreadsheet program offered by Google as part of its complete office suite Google Workspace to compete with Microsoft Office. The other main services included in the cloud-based suite are Docs (Word) and Slides (PowerPoint).

Google Sheets is available on all devices and platforms; all you need is an internet connection and a web browser (or, in the case of mobile, the applicable app). Google does the rest and handles the brunt of the heavy lifting while it runs the software in the cloud. And since Sheets is an online spreadsheet program, you can share and collaborate with multiple people on the same document, tracking revisions, changes, and suggestions all in real time.

Sheets supports several different file types, including XLS, XLSX, XLSM, TXT, ODS, and CSV, making it easy to view and convert Microsoft Office files directly from Google Drive.

How to Sign Up for an Account

The first thing you’ll need to do before you can use Google Sheets is sign up for a Google account. If you already have an account, feel free to move on to the next section. If not, we’ll go over the simple way to create a Google account and get you set up with Sheets.

Head over to accounts.google.com, click on “Create Account,” and then “For Myself.”

On the next page, you’ll type in some basic information, like a first name and (optional) last name.

Then, you’ll need to provide your birthday and gender. Some Google services are age-dependent so the birthday is required. Google goes into more detail on why it requests this information in this helpful support doc.

Next, you’ll finally get to pick your email address. Google generates a few automatically for you based on your name, but there’s also a button to click where you can type your own email in.

At this point, it’s time to create a strong password. For security purposes, don’t use a password that you’ve used elsewhere, and don’t write it down on a piece of paper somewhere. I recommend using a password manager, something I’ve used for many years now and swear by. Not only does a password manager help generate strong, secure, and unique passwords, but it remembers them all for you.

Lastly, you’ll need to confirm that you’re not a robot and also set up a recovery email. Start by providing a cell phone number where Google can send you an SMS verification code and enter that. Then, on the next page, you’ll put in a recovery email that will be used if you lose access to the account for any reason.

How to Create a Blank Spreadsheet

Now that you have a Google account, it’s time to create your first spreadsheet. Head on over to Google Sheets and click on the “Blank spreadsheet” button with the multicolored “+” icon.

If you’re using Google Chrome, you can type sheets.new directly into the address bar to create and open a new blank sheet.

How to Import a Microsoft Excel Spreadsheet

Even if you’re entirely new to Google Sheets, you may already have some Microsoft Excel files you’d like to be able to use in the cloud. If that’s the case, then all you have to do is upload all your Excel documents, and Google does the rest. While it might not support some of the more advanced features and effects of Excel, Google Sheets works pretty well with a majority of existing formulas and techniques, and is always adding more features.

To import an Excel document, you can use either the Google Sheets website or Google Drive to upload your files. Both methods let you drag and drop a file from your computer directly into the web browser for easy upload. Your Drive houses all of your uploaded files, but for convenience’s sake, when you head to the Sheets homepage, it only shows you spreadsheets you’ve uploaded or created, and nothing else.

To upload an Excel file directly to the Google Sheets website, you’ll want to start by clicking the folder icon on the right-hand side of the screen above the file names. Then, click “Upload” in the tab options of the popup window. Now, you can either click to browse your computer or drag-and-drop the Excel file into the browser window to upload it.

Once the Excel file uploads, Sheets automatically converts and opens it, ready for you to start editing, sharing, or collaborating. To open an Excel file or Sheet that you want to edit that’s already uploaded, simply find the filename on the Sheets homepage and click it.

When you finish with the document, you can download it as an XLSX, or ODS, PDF, HTML, CSV, or TSV format. Click File > Download, then click on the desired format, and it will download directly to where files save from your browser.

How to Collaborate on Documents With Others

One of the best features of Google Sheets is the ability to generate a shareable link that lets anyone with it to either view, comment on, or edit your document. Instead of sending a file back and forth between collaborators, you can make edits and suggestions all at once, as if you were all huddled over the same computer in real time.

From the document you want to share, click on the “Share” button in the upper right-hand corner to choose how and with whom you want to send a link to your file. You can enter email addresses manually or click “Get Sharable Link” in the top corner to distribute the invitation yourself.

From the drop-down menu, you can edit how much power the shared user(s) have over the file when you select one of these options:

• Off: Sharing is disabled. If you’ve previously shared a link with others, it will no longer work and revokes any permissions they once had.
• Anyone with the link can edit: Gives the shared users full read/write access. They still can’t delete it from your Drive, though—this is just for the contents of the file.
• Anyone with the link can comment: Allows shared users to leave comments if desired—this is great for team projects.
• Anyone with the link can view: Shared users can view the file, but can’t edit it in any way. This is the default action when you share a file, and the best option if you’re trying to share a file for download.

You can do a lot more with these shareable links, which also work with other Drive files and on mobile.

Protect Cells in Shared Sheets From Editing

The ability to share and collaborate on documents with Google Sheets is one of the best features, hands down. However, sometimes you want to maintain the integrity of certain cells and stop anyone trying to alter them. So, if you’ve shared a spreadsheet with a group of people but want to prevent them from being able to edit specific cells in the file, then you might want to revoke their access to them.

Highlight all the cells you want to protect, then click Data > Protect Sheets and Ranges from the toolbar. The Protected Sheets and Ranges pane appears on the right. Here, you can enter a brief description and then click “Set Permissions” to customize the cell’s protection permissions.

By default, anyone who already has permission to edit the document is allowed to edit every cell on the page. Click the drop-down menu under “Restrict Who Can Edit This Range” and then click “Customized” to set who is permitted to edit the selected cells. You can also copy the permissions from another range if you’ve already set up permissions elsewhere in the document.

Under the list of people who can edit, everyone with whom you’ve shared edit permissions is already selected by default. Deselect anyone you don’t want to be able to edit the selected cells and then click “Done.”

Any time someone tries to alter or edit the cells you’ve just protected, they see a prompt informing them that the cells/sheet.

How to See All the Recent Changes

When you share documents with others, it’s difficult to keep track of all the small changes that happen if you’re not present. For that, there’s revision history. Google Sheets keeps track of all the changes that occur in a document and groups them into periods, keeping the clutter down.

You can even revert a file to any of the previous versions listed in the history view with the click of a mouse. Changes made by each team member are different colors, making it easy to track who did what while you weren’t looking.

You can see a list of all recent changes by clicking File > Version History > See Version History.

How to Use Google Sheets Offline

What happens if you need to access Google Sheets but don’t have an internet connection? Although Sheets is a web-based product, that doesn’t mean you can’t use it offline. You need to download an extension for Chrome and make sure you enable the file for offline use beforehand. Any changes you make to the file update the next time you connect to the internet.

After you download the official extension for Chrome, go to the Google Sheets homepage and in the top-left corner, click the Hamburger menu > Settings. Once here, toggle “Offline” to the On position, then click “OK.”

To save storage space on your local machine, Google only downloads and makes the most recently accessed files available offline. To manually enable a file, click the three dots icon, then toggle “Available Offline” to On.

How to Reference Other Sheets

Now that we have most of the basics out of the way, let’s dive into a few more advanced features of Google Sheets. These functions are similar to what you’d find in Excel, but might be slightly tweaked to work in Google Sheets. One of the biggest things that I use in Google Sheets is referencing information between sheet tabs.

To reference other tabs in a Google Sheet, simply type the tab name followed by an exclamation point and the cell number.

=Tab1!A3

How to Use a Form to Collect Responses Automatically

Google Sheets can function as a public online form collection system as well. This can be really useful if you’re running a poll, having customers or clients fill out an intake form, or just want to gather data for any other reason.

To create a form, you’ll start by either making a new Google Sheet or selecting an existing document. From there, click “Tools” then “Create a New Form.” A new tab will open that lets you customize the form questions and style.

All data inputted into the form will be dropped into a newly created tab in the document called “Form responses 1.” You’re able to rename this tab and customize it as needed, as well as reference the information from there in other tabs as mentioned above.

Translate Words Directly in Google Sheets

Need to translate words or phrases from one language to another without leaving the page? Well, there’s a special formula you can use to translate single words—or even a batch of them—directly in the cells of Google Sheets. The formula looks like this:

=GOOGLETRANSLATE("text", "source language", "target language")

When you want to translate text in your document, you can either type the words to translate directly into the formula or type in the cell containing the word/phrase to translate. Here we reference the text in cell A2, specify the input language as English (en), and the output as Chinese (zh), then hit Enter and watch it do its thing.

Automate Tedious Tasks With Macros

Google Sheets lets you automate repetitive tasks with a specific sequence of instructions to increase productivity within your spreadsheets. When you click “Record,” Sheets creates a script with all the code to replicate tedious and monotonous steps.

When you record a macro in Google Sheets, it automatically creates an Apps Script with all the code to replicate your actions for you. This means you can create complex macros without knowing how to write code. The next time you run it, Sheets will do everything you did when you recorded the macro. Essentially, you’re teaching Google Sheets how to manipulate a document to your liking with a single command.

Here are a few examples of its powerful functionality:

• Apply formatting and styles.
• Create completely new spreadsheets.
• Use any Google Sheets function, toolbar, menu, or feature.

To get started, fire up a Google Sheet and click Extensions > Macros > Record Macro.

This opens the recording menu at the bottom of the window, with two options for capturing your actions:

• Absolute References: The macro will do tasks only on the exact cells you record. If you italicize cell B1, the macro will only ever italicize B1 regardless of what cell you clicked on.
• Relative References: The macro will do tasks on the selected cells, regardless of where they are in the sheet. If you italicize B1 and C1, you can re-use the same macro to italicize cells D1 and E1 later.

Select whether you want an absolute or relative reference, then you can start to click, format, and teach Sheets what order you want these actions to replicate.

Now, you can start to move cells, apply formats, create functions, and so on.

After you’ve captured all the actions for this macro, click “Save.”

Enter a name for your macro. Google also lets you create shortcuts for up to ten macros. If you want to bind a macro to a keyboard shortcut, enter a number from 0-9 in the space provided. When you finish, click “Save.”

If you need to tweak your macro’s name or shortcut, you can edit a macro by clicking Extensions > Macros > Manage Macros.

From the window that opens, tweak as desired, and then click “Update.”

The next time you press the shortcut associated with the macro, it will run without having to open the macro menu from the toolbar.

If your macro is an absolute reference, you can run the macro by pressing the keyboard shortcut or go to Extensions > Macros > Your Macro and then click the appropriate option.

Otherwise, if your macro is a relative reference, highlight the cells in your spreadsheet on which you want the macro to run and then press the corresponding shortcut, or click on it from Tools > Macros > Your Macro.

How to Easily Remove Duplicates

Need to remove a bunch of duplicate entries in your spreadsheet? That’s no problem at all with Google Sheets’ built-in feature that does just that. With Google’s data cleanup, you’ll be able to tidy up duplicates or whitespace with the simple click of a button. Google even has cleanup suggestions now, all without having to write any scripts.

Highlight the table/cells that contain duplicates you want to remove, then click Data > Data Cleanup > Remove Duplicates. If the data has a header row, be sure to tick the box “Data Has Header Row,” then click “Remove Duplicates.”

---

Now that you’ve learned all the basics of Google Sheets, you can become a real power user with the addition of add-ons. Add-ons are a lot like extensions for web browsers, but are specific to Google Sheets and let you gain additional features from third-party developers. You can install tools to find facts and compute formulas automatically, hand out documents to students, generate business reports, and even send emails directly from a document.