27 Google Sheets Tricks and Tips for Increasing Efficiency

Anyone who’s previously worked with spreadsheets will be hard-pressed to dismiss great features and the collaborative aspect of Google Sheets. 

Most likely, you already have a free Google account and access to plenty of advanced applications that not so long ago came with a hefty price tag. Using Google Sheets is free, and if you work with spreadsheets, it would be a shame if you didn’t try it out. 

Most users barely scratch the surface of what this powerful and free tool can do. That’s why this time, we’ll dig a bit deeper to find the best Google Sheets tricks. Sit back in your comfortable computer chair and read on - you’ll soon see what makes this software so valuable for organizing your workflow, project collaboration, crunching data, and much more.  

Useful Functions and Options for Google Sheets Users

1. Conditional formatting 
2. Sparkline data visualization
3. Create charts
4. Quick data visualization - the Explore tool
5. Use filters
6. Functions to properly format cell values - CLEAN and TRIM
7. Change capitalization
8. Split names and other data
9. Check if an email address is valid
10. Validate cell data
11. Protect data - lock cells
12. Translate text
13. Easy data summary 
14. Automate repetitive tasks with macros
15. Add Google Sheets chart to your Google Docs
16. Embed Google Sheets charts on your website
17. Website and RSS feed data import
18. Import data from other sheets
19. Insert images from a URL
20. Share and collaborate on spreadsheets 
21. Notifications for editing or adding data
22. Simultaneously send emails and leave comments
23. Integrate with Google Forms
24. Integrate with Google Finance
25. Google Sheets add-ons
26. Keyboard shortcuts
27. Create a checklist 

Conditional Formatting

Conditional formatting is one of the handiest Google Sheets tricks, perfect for focusing on important data in your table. With this tool, you can easily highlight low or high values that require attention.

Google Sheets also allows you to apply a color gradient to your data, effectively creating a heatmap that enables you to quickly identify areas to focus on. 

You can find this option in the Format dropdown menu under Conditional formatting. Conditional format rules enable you to choose a single color or a color scale and set up various rules depending on your needs and preferences. 

Sparkline Data Visualization

Among useful Google Sheets features, you’ll find sparkline data visualization. Using the SPARKLINE function and selecting the cell range allows you to create helpful visualization within a single field.

Create Charts

Google Sheets has a great selection of charts you can use to showcase your data in full glory. Adding charts is a simple matter of finding them in the Insert dropdown menu and selecting a column or row range you want to visualize. You have a selection of line, area, column, bar, pie, scatter, and even map charts.

Quick Data Visualization - The Explore Tool

Besides charts, Google Sheets can quickly visualize your data and suggest how to present it using the Explore function. It’s one of the Google Sheets hacks that can help you quickly process and prepare data for a presentation or a meeting.

Use Filters

Using filters to find relevant data from tables with thousands of entries can make your life drastically easier. Before applying filters, you have to create them by locating the “Create a filter” option in the Data dropdown menu. 

After creating filters, you have multiple ways of narrowing down data by value, condition, and color, with various subcategories at your disposal. 

Functions to Properly Format Cell Values - CLEAN and TRIM

You’ll often use Google Forms or other survey solutions to import data to Google Sheets and analyze it further. Since multiple people enter data, formatting mistakes can happen, and knowing how to format cells in Google Sheets is essential. 

That’s where the TRIM function comes to your rescue by removing whitespace from the start and end of cells, while CLEAN can remove nonprintable characters. 

Change Capitalization

Same as our previous tip, changing capitalization may be helpful for data collected in surveys. Furthermore, if you have large user databases like customer experience software solutions have, not having to capitalize the text in each cell manually can save you loads of time. Instead, use the PROPER function to switch everything to title case and LOWER to change all letters to lowercase. 

Split Names and Other Data

You can split data into separate columns by selecting the “Split text to columns” option you’ll find in the Data dropdown menu. This may help you with formatting Google Sheets and assigning data to various columns. 

Check if an Email Address Is Valid

Google Sheets has an in-built function that checks if a cell containing an address follows the proper email address format. Note that it won’t check if it’s a valid address, only whether it uses the correct format or not. 

Validate Cell Data

If you need to have cells contain only specific values, you can set up data validation rules for your spreadsheet. Set up entire rows to show only items from a list, number values, dates, and other entries. You can easily access “Data validation” by selecting required fields and right-clicking on your selection to open the dropdown menu. 

Protect Your Data by Locking Cells

One of the Google Sheets tips we’re confident you’ll use all the time is related to data protection: To prevent accidental changes when multiple people work on the same document, you can lock or restrict certain sheets, cells, rows, and columns. You can also warn users when making changes. This option can be found in the Data dropdown menu under “Protected sheets and ranges.”

Translate Text

Take advantage of Google’s translation tools by calling the GOOGLETRANSLATE function to automatically translate text to English or set up parameters to translate into other languages. 

Easy Data Summary

For anyone looking to make a quick summary of data, there are SUMIF, SUMIFS, COUNT, and COUNTIFS functions. Format cells in Google Sheets to enable adding values or count cells using these functions if one or multiple conditions are met. 

Automate Repetitive Tasks With Macros

Google Sheets users who have to go through the same actions over and over again can record each step of the process in custom macros. To start recording your macro, go to Tools/Macros/Record macro. 

The Macro tool will prompt you to choose between absolute or relative references. With absolute references, the macro will execute tasks on selected cells, and with relative, it’ll include other cells as well. 

Upon completing the task for the macro, you need to save it, give it a name, and, optionally, add a keyboard shortcut. 

Add Google Sheets Charts to Your Google Docs

Knowing how to format Google Sheets can help you with creating great, professional-looking Google Docs. If you are writing a report based on calculations you’ve made in a spreadsheet, you can import charts from that spreadsheet. While in Google Docs, go to the Insert menu, Charts, and choose the “From Sheets” option. Find the spreadsheet with the chart you need and insert it. 

Embed Google Sheets Charts on Your Website

You can create wonderful charts with Sheets. What’s even better is that you can showcase them by publishing them on your website.

You don’t have to be an advanced Google Sheets user to take advantage of this handy option: Just click on “More” at the top-right corner of the chart you want to share. From the dropdown menu, choose “Publish chart” and select “Embed.” Google Sheets will generate HTML code for you to paste on your website. 

Website and RSS Feed Data Import

Google Sheets has various functions for importing data from external sources, such as:

• IMPORTHTML - for importing tables and lists
• IMPORTXML - for importing a specific section of a webpage via Xpath
• IMPORTDATA - for adding data from CSV web-based files
• IMPORTFEED - for importing RSS feeds

Users looking for useful Google Sheets tips and tricks will surely find these functions worth a try. 

In this example, we’ve imported a table with all NBA clubs from Wikipedia using the IMPORTHTML function. 

Import Data From Other Spreadsheets

Instead of having the same data across several sheets, you can import it to a single document with the IMPORTRANGE function. This is helpful, especially if you need to update data in a central sheet instead of going through multiple ones.

Insert Images From a URL

Google Sheets has a function that enables you to insert images from a URL directly into a cell. The function IMAGE has four arguments, including the URL, mode, height, and width. 

We’ve selected mode “1,” fitting the image to the cell size in our example above. 

There are three other Google Sheets formatting options for this function: Mode “2” stretches the image to fill the cell, “3” preserves the image’s original size, and “4” allows for specific height and width. 

Share and Collaborate on Spreadsheets

Like other Google’s web and mobile apps, Google Sheets can share documents via URL or directly with specific people. Furthermore, you can set up permissions who can edit the document or make suggestions. 

When you send someone the shared link, you can immediately create your copy if you have a Google account. When you replace “edit” with “copy” in the link, you’ll be free to format your Google Sheets file copy since you’ll create a separate file.

You can see an example URL below and the prompt you’ll get when copying a file.   

https://docs.google.com/spreadsheets/d/1NdzGROQ6EuasgNpFngv4pY829202HFgFjYtyzw45K70/copy 

Notifications for Editing or Adding Data

You can adjust shared spreadsheets or the ones used to accept survey responses to send you notifications whenever someone makes a new entry or edits the document. Admittedly, Google Spreadsheets has fewer notification options than, for example, appointment scheduling software, but it’s still more than enough for most people’s needs. 

Simultaneously Send Emails and Leave Comments

Real-time updates on Google Sheets are perfect for teams. The option to leave comments is one of the most valuable Google Sheets tricks for people working on the same projects. By adding a “+” in front of an email or a name when you comment, you can immediately email the project participant.   

Integrate with Google Forms

Google Forms are a great way to collect data and create surveys. Furthermore, it integrates perfectly with Google Sheets for data analysis and visualization. 

You can send data to Google Sheets from the “Responses” tab within the form. Just click the Google Sheets icon to send the existing and new responses to a specific spreadsheet. 

Integrate with Google Finance

Google Sheets has an excellent function that can fetch current or previous stock prices from Google Finance. The GOOGLEFINANCE function is one of those Google Sheets advanced tips financial experts use to get a detailed overview of their investments. 

Google Sheets Add-Ons

Google Sheets allows for the integration of multiple add-ons that can help speed up your workflow or import data from other platforms.

Keyboard Shortcuts

For any kind of software, keyboard shortcuts are essential to use the program effectively. The same goes for Google Sheets. Google has a comprehensive list of keyboard shortcuts for both PC and Mac. Users can also customize their shortcuts by pressing Control + “/” on Windows or Command + “/” on macOS. 

Create Checklists

There’s nothing more satisfying than checking off a completed task. You can use Google Sheets to format a checklist in a spreadsheet. Adding checkboxes is a simple matter of locating “Checkbox” in the Insert dropdown menu. After that, you can place your assignments in the adjacent column and mark completed tasks. 

These are some of the tricks that caught our attention. We hope you’ll find them as useful and interesting as we did. Google Sheets has many other outstanding features and tools. It’s also available and works smoothly even on the smallest of Android smartphones, which is great in situations when you need to quickly check something on your spreadsheets on the go.