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 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, web-based spreadsheet program offered by Google as part of its complete office suite—Google Drive—to compete with Microsoft Office. The other main services included in the cloud-based suite are Docs (Word) and Slides (Powerpoint).
RELATED: The Beginner’s Guide to Google Docs
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 apps). Google does the rest and handles the brunt of the heavy lifting while it runs the software in the cloud.
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.
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.
Have you heard enough? Let’s get started.
How To Sign Up for an Account
The first thing you’ll need to do before you can use Google Sheets is a sign up for a Google account (an @gmail 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 need to provide some information to create an account, like first and last names, username, and password.
Also, you have to verify your phone number to make sure you’re an actual human and not a bot.
After you verify your phone number, the subsequent pages require you to provide a recovery email address, date of birth, and gender, as well as agreeing to the privacy statement and terms of service. Finish that, and you’re the proud new owner of a Google account.
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 place the cursor on the multi-colored “+” icon in the bottom right corner.
The + turns into a green pencil icon; click on it.
You can also type sheets.new into the address bar and hit Enter to automatically create and open a new blank spreadsheet in the current tab.
How to Import a Microsoft Excel Spreadsheet
Even if you’re entirely new to Google Sheets, you may already have a stockpile of Microsoft Excel files you’d like to be able to use. If that’s the case, then all you have to do is upload all your Excel documents to, and Google does the rest. While it might not support some of the more advanced features and effects of some Excel Spreadsheets, it works pretty well and is always adding more features.
When you import an Excel document, you can use either Google Sheets or Drive to upload your files. Both methods let you drag and drop a file from your computer directly into the web browser for easy uploads. Your Drive houses all of your uploaded files, but for convenience’s sake, when you head to the Sheets homepage, it only shows you spreadsheet-type files.
From the Google Sheets homepage, click the folder icon in the top right, click the “Upload” tab, then drag an Excel file over from your computer.
Once the Excel file uploads, Sheets automatically converts and opens it, ready for you to start to edit, share, and collaborate.
To open an Excel file that you want to edit that’s already uploaded, click the file with the green ‘X’ next to the filename from your Google Sheets homepage.
Click either view the Excel file or edit it in Sheets from the dialog that appears.
When you finish with the document, you can download it as XLSX, or ODS, PDF, HTML, CSV, or TSV format. Click File > Download As then click on the desired format, and it will download directly to where files save from your browser.
RELATED: How to Import an Excel Document into Google Sheets
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. The only difference is that each person has their own text entry cursor to use on their personal computer.
From the document you want to share, click on the green “Share” button 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 hand out 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. For a deeper look at how these links work and how to generate them, check out our post.
RELATED: How to Create Shareable Download Links for Files on Google Drive
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 “Customised” to set who is permitted to edit the selected cells.
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
Want only to display a warning before someone edits cells, or perhaps protect an entire spreadsheet? Check out our post for more details.
RELATED: How to Protect Cells From Editing in Google Sheets
How to See All the Recent Changes to a Document
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 with the click of a mouse.
You can view a list of all recent changes by clicking File > Version History > See Version History.
Changes made by each team member are different colors, making it easy to track who did what while you weren’t looking.
RELATED: How to See Recent Changes to Your Google Docs, Sheets, or Slides File
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 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.
RELATED: How to Use Google Docs Offline
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.
If you have a list of words in a single column, all you need to do is select the cell that contains the formula, then drag the blue square down to the desired row.
If you’ve ever used Google Translate before, you know that it isn’t accurate 100% of the time, but it works fine as a reliable solution for common words and phrases.
RELATED: How to Use Google Translate Directly in Google Sheets
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.
RELATED: How to Supercharge Your Google Apps with the Script Editor
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.
The sky is the limit.
To get started, fire up a Google Sheet and click Tools > 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, etc.
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 Tools > 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 Tools > 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.
While this should get you started, we have a deeper dive into using Google Sheets macros if you want more info.
RELATED: How to Automate Google Sheets With Macros
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. Easily remove duplicate values from your spreadsheet without having to write a script or manually do it yourself.
Highlight the table/cells that contain duplicates you want to remove, then click Data > Remove Duplicates. If the data has a header row, be sure to tick the box “Data has header row,” then click “Remove Duplicates.”
The Best Google Sheets Add-ons
Now that you’ve learned all the basics to 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.
If you’re looking for some of the best add-ons to get you started, we have you covered.
RELATED: The Best Google Sheets Add-Ons
