Google Sheets Tips
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:
- Send emails when you comment
- Add heatmaps using conditional formatting
- Apply filters
- Clean up values with CLEAN and TRIM
- Protect data in cells
- Validate data in cells
- Integrate with Google Forms
- Insert a chart from Google Sheets into a Google Doc
- Import data from a website or RSS feed
- Change capitalization in cells
- Translate text
- Split names and other data
- Check for valid email address
- Quickly summarize data
- Import data from other sheets
- Visualize data with a sparkline
- Create QR codes
- Quick data visualization
- Extend Google Sheets with add-ons
- Quickly learn formulas
- Keyboard shortcuts
- Create pivot tables
- Remove duplicates
- Upload spreadsheets and data
- Check for spelling mistakes
- Freeze rows and columns
Are you just getting started? Then take a moment to watch my Google Sheets tutorial for beginners on YouTube. It covers important foundations.
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 collaborating with others – everything is updated in real time, but what if you need to let someone know there is something you need them to check? You can actually send an email to let them know when you’re adding a comment to Google Sheets. Just add a plus sign (+) and then type their email address (or name) and then when you add your comment they’ll receive an email with your message automatically.
2. Add heatmaps using conditional formatting​
Heatmaps 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 the sheet. This can be really useful when you’re working with a larger set of data. Just select the filter icon (or select ‘Filter’ under ‘Data’) and then you’ll be able to click the filter icon in the first column row of your sheet and select the value(s) you want to be included.
If you’re using a filter on a regular basis you can save them to use again in the future. Select ‘Create New Filter View’ by clicking the filter icon (or under ‘Data’ and then ‘Filter Views’).
Learn more about filters.
4. Clean up values with CLEAN and TRIM​
If you find 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 lots of people are working on a sheet, then you might want to lock down some of the data to prevent mistakes. You can lock sheets and even individual cells so that data isn’t accidentally changed.
If you don’t want completely lock down cells, 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 within the sheet.
7. Integrate with Google Forms​
This is one of the most underrated Google Sheets tips. You can automatically capture information into a Google 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 Google Sheets, select ‘Responses’ inside the form and then click on the Google Sheets icon to send existing (and future) responses into a sheet.
8. Insert a chart from Google Sheets into a Google Doc​
Once you’ve created a chart inside Google Sheets you can insert it into a Google Doc. Inside the document, select ‘Insert’, then ‘Chart’ and then ‘From 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 ‘Update’ 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 and RSS feeds, including:
- ImportHTML for importing HTML tables and lists
- ImportFeed for importing RSS entries
- ImportData for importing a web-based CSV file
- ImportXML for importing a custom section of a webpage that you can identify with Xpath
For example, here is an import of all the SpaceX launches 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.
Alternatively, 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 the data contained in one cell into multiple cells, then you can use the ‘split text to columns’ option (which you’ll find under ‘Data’). This can be useful if you need to clean up data, for example, if you need to split first and last names into separate columns in your sheet.
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 will bounce (like ones 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 can be a good option, as they allow you to 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 simply 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 sparklines 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.
Learn more about the SPARKLINE function.
17. Create QR codes​
Barcodes are a useful way to keep track of things, like checking people in at an information session, conference or event. And you can quickly create QR codes in Google Sheets.
This example will pull in the data from the A1 cell to create a QR code:
=IMAGE("https://chart.googleapis.com/chart?chs=200x200&cht=qr&chl="&A1&"")
I’ve used a similar method to create coupon codes on a website that people can then use to redeem an in-store offer.
18. Quick data visualization​
If you’re ever looking for new ideas on how to visualize your data, you can use the Explore feature to see suggested ways to present your data.
19. Extend Google Sheets with add-ons​
There are lots and lots of add-ons you can use with Google Sheets. They can make it easier to get things done quickly. My personal favorites are the Google Analytics add-on for pulling data into sheets and Supermetrics (affiliate link) for pulling in data from platforms like Facebook, X (formerly Twitter), 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 that you’re adding to your sheet. 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 Command and / (forward slash) on Mac or Control and / (forward slash) on Windows.
22. Create pivot tables​
Pivot tables are powerful for summarizing and analyzing extensive data without alteration. Select the range you want to analyze, choose ‘Insert’ and then ‘Pivot Table. Depending on your setup, you can use this nifty feature to summarize sales data by product or month.
23. Remove duplicates​
Cleaning your data by removing duplicates can prevent skewed data analysis. Simply choose the range, choose ‘Data’, then ‘Data Cleanup’, and select ‘Remove Duplicates’, and Google Sheets will remove any identical rows based on your selected range.
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.
25. Check for spelling mistakes​
Tired of correcting spelling errors yourself? Use the spell check option by selecting ‘Tools’, then ‘Spelling’ and choose ‘Spell Check’ to correct 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/or columns which is very helpful with large datasets.
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 free resources covering Google Analytics, Google Ads, and more!