26 Google Sheets Tips – Awesome Things You Can Do in Google Sheets

I love Google Sheets. It has a clean interface, it’s easy to collaborate with people and it integrates with other Google products that I use every day (like Google Analytics and Looker Studio). It’s really easy to start using Google Sheets, but did you know it offers some great ways to get even more out of your data?

We’re going to cover a range of tips that will help you save time and do some awesome things, all using the power of Google Sheets! Let’s get started!

And if you like, you can jump straight to a tip:

1. Send emails when you comment
2. Add heat maps using conditional formatting
3. Apply filters
4. Clean up values with CLEAN and TRIM
5. Protect data in cells
6. Validate data in cells
7. Integrate with Google Forms
8. Insert a chart into a Google Doc
9. Import data from a website
10. Change capitalization in cells
11. Translate text
12. Split names and other data
13. Check for valid email address
14. Quickly summarize data
15. Import data from other sheets
16. Visualize data with a sparkline
17. Create QR codes
18. Quick data visualization
19. Extend Google Sheets with add-ons
20. Quickly learn formulas
21. Keyboard shortcuts
22. Create pivot tables
23. Remove duplicates
24. Upload spreadsheets and data
25. Check for spelling mistakes
26. Freeze rows and columns

Are you just getting started? Then take a moment to watch my Google Sheets tutorial on YouTube. It covers important foundations for creating and using spreadsheets.

Get the Google Sheets Tips

In a hurry? You can also download the resources included in my post. Get my Google Sheets Tips PDF.

Download the Google Sheets Tips

1. Send emails when you comment

Google Sheets is great for collaboration because everything is updated in real time, but what if you need to ask someone to check something? Well, you can send an email to let them know when you add a comment in Google Sheets. Just add a plus sign (+) and then type their email address (or name) and when you add your comment they will automatically receive an email.

2. Add heat maps using conditional formatting

Heat maps are a great way to draw attention to important data in your sheet. You can highlight particular values, outliers or errors using conditional formatting. 

Applying a color scale allows you to quickly highlight lower and higher values in your data. I find this especially useful when I’ve pulled in data from Google Analytics so that I can quickly identify where to focus my analysis.

Learn more about conditional formatting.

3. Apply filters

You can use filters to only view rows of interest within your sheet. This can be really useful when you’re working with a larger set of data. Just select the filter icon in the menu (or select 'Data' and then 'Create a filter'). You'll then be able to click the filter icon in the top row of your sheet and select the values you want included or excluded.

If you’re using a filter on a regular basis you can save it to use again in the future. To do this, select 'Data' and then choose 'Create filter view'.

Learn more about filters.

4. Clean up values with CLEAN and TRIM

If there are unwanted characters in your cells, you can use the CLEAN function to remove any non-printable characters and the TRIM function to remove whitespace from the start and ends of cells.

Learn more about the CLEAN and TRIM functions.

5. Protect data in cells

If multiple people are working on a sheet, you might want to lock down some of the data to prevent mistakes. You can lock sheets and individual cells so that data isn’t accidentally changed.

If you don’t want to completely lock down cells in the sheet, you also have the option to show a warning before they’re edited.

6. Validate data in cells

You can ensure that certain cells only contain selected data by applying data validation to your sheet. For example, you can set validation so that particular cells only contain numbers or even a value from a predefined list.

Setting a predefined list of values will also make a drop-down selector available in the sheet.

7. Integrate with Google Forms

You can automatically capture information into a sheet by linking it to a Google Form. This is especially useful for surveys, registrations, and feedback forms. Data is instantly added to the sheet as responses are submitted, providing real-time data collection and analysis.

To send data into a sheet, open the Google Form and select the 'Responses' tab. You can then click 'Link to Sheets' so that existing and future responses are available in Google Sheets.

8. Insert a chart from Google Sheets into a Google Doc

Once you’ve created a chart in Google Sheets you can insert it into a Google Doc. To do this, open your document, select ‘Insert’, then ‘Chart’ and choose ‘From Sheets’. Or you can simply copy the chart in your spreadsheet and then paste it into your document. When you do this, Google Docs will ask if you want the chart linked to Google Sheets.

This can save a lot of time since you can update the chart in the document to reflect any changes you’ve made in Google Sheets. All you need to do is click the ‘Update’ icon in the document and you’re done!

9. Import data from a website or RSS feed

You can use different functions to import data into your sheet from websites, including:

• ImportHTML for importing HTML tables and lists
• ImportData for importing a web-based CSV file
• ImportXML for importing a section of a webpage

For example, here we can see the top social networks imported from Wikipedia:

10. Change capitalization in cells

You can capitalize the first letter in each word using the PROPER function. This is useful if you need to clean up values to make them consistent.

Or you can use the LOWER function to make all letters lowercase.

11. Translate text

If your sheet contains cells that use a different language, then you can use the GOOGLETRANSLATE function to automatically translate the values in another language. 

Learn more about the GOOGLETRANSLATE function.

12. Split names and other data

If you ever find that you need to split from one cell into multiple cells, you can easily do this in Google Sheets. Select the cells that contain the values you want to split, then select 'Data' and choose 'Split text to columns'.

You can use this if you need to split first names and last names into separate columns.

13. Check for valid email addresses

If you have a list of emails and you want to ensure that they’ve used a valid email address structure, then you can check them using Google Sheets. It won’t check that your emails will be delivered, but it will help identify any email addresses that are likely to bounce (like addresses missing the @ or .com).

Learn more about the ISEMAIL function.

14. Quickly summarize data

If you’re looking for a quick way to summarize data, then SUMIFS and COUNTIFS are good options because you can define multiple criteria. For example, you could sum all numbers over a certain value or count the number of rows that contain a particular string or value.

Learn more about SUMIFS and COUNTIFS.

15. Import data from other sheets

Instead of maintaining data in multiple sheets, you can import data from one sheet into another. This also means that you only need to update data in one place (and not multiple sheets) which can be a big time saver.

Learn more about the IMPORTRANGE function.

16. Visualize data with a sparkline

You can easily add spark lines to your sheets in order to quickly see trends in your data. This can be especially useful if you’re comparing data (like metrics from Google Analytics) or if you want to turn your sheet into a dashboard.

To add a spark line, just enter something like =SPARKLINE(B2:B31) so that it references cells with the values you want included.

Learn more about the SPARKLINE function.

17. Create QR codes

QR codes are a useful way to connect offline touch points to online actions. For example, promoting an online offer in a brochure, or letting people access additional information at a conference or event. 

You can quickly create QR codes in Google Sheets. The following example turns the value of the A1 cell into a QR code:

=IMAGE("https://api.qrserver.com/v1/create-qr-code/?size=150x150&data="&A1&"")

I’ve used a similar method to create coupon codes that people could use to redeem and in-store offer. So there's a whole range of potential applications.

18. Quick data visualization

If you’re ever looking for new ideas on how to visualize your data, you can view suggestions in Google Sheets. To do this, select the cells you want to visualize in your sheet, then select 'Insert' and choose 'Chart'. You can then select the default chart type drop-down to see a list of suggested visualization options based on your selected data.

19. Extend Google Sheets with add-ons

There are lots of add-ons you can use with Google Sheets. They can make it easier to get things done quickly. My top picks are the Google Analytics add-on for pulling data into sheets and Dataslayer for pulling in data from other platforms like Meta, Microsoft, LinkedIn, and more.

20. Quickly learn formulas

Google Sheets makes it easy to learn formulas while you’re working. When you start typing a formula you’ll see a handy reference that includes important details about the formula you’re adding. Just start typing the formula, for example, start typing =DATEDIF( and you’ll see this:

21. Keyboard shortcuts

You can speed things up by using keyboard shortcuts in Google Sheets. From clearing formatting inside cells, to adding comments, and hiding rows, the built-in keyboard shortcuts can help save you time. To access keyboard shortcuts (and even create your own) hit Control and / (forward slash) on Windows or Command and / (forward slash) on Mac.

22. Create pivot tables

Pivot tables are powerful for summarizing and analyzing extensive data without alteration. Select the range you want to analyze, select ‘Insert’ and choose ‘Pivot table'. Depending on your setup, you can use this feature to summarize sales data by product or month.

23. Remove duplicates

Cleaning up your data by removing duplicates can provide clearer, more accurate insights. To remove duplicates, choose the range you want to check, select ‘Data’, then ‘Data cleanup’, and choose ‘Remove duplicates’, and Google Sheets will remove any identical rows based on your selected range.

Tip: For additional suggestions on how to clean up the data in Google Sheets, select 'Data', then 'Data cleanup, and choose 'Cleanup suggestions'.

24. Upload spreadsheets and data

You can upload existing spreadsheets and files to use in Google Sheets. For example, if you have a CSV file or an Excel file, you can upload this to create a spreadsheet in Google Sheets.

To upload existing data, select 'File', then 'Import'. You can then choose from existing spreadsheets or upload a file. When you upload a file you can choose from a range of options that allow you to create a new spreadsheet or add the imported data to the spreadsheet you're currently working on.

25. Check for spelling mistakes

Tired of correcting spelling mistakes? Use the spell check option by selecting ‘Tools’, then ‘Spelling’ and choose ‘Spell check’ to fix up any spelling mistakes across your sheet.

26. Freeze rows and columns

Keep important headings visible as you scroll through your data by freezing rows and columns. This is especially useful when working with larger spreadsheets.

Use These Google Sheets Tips and Tricks to Your Advantage

By mastering these Google Sheets tips, you can turn a basic spreadsheet into a powerful tool that handles complex tasks, making it an essential asset for your business.

If you're interested in learning more about Google Sheets and how it can enhance your use and management of spreadsheets, watch my Google Sheets tutorial and check out my other resources covering Google Analytics, Google Ads, and more.

Get the Google Sheets Tips

In a hurry? You can also download the resources included in my post. Get my Google Sheets Tips PDF.

Download the Google Sheets Tips