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 Google Data 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:
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.
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.
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
After you’ve collected responses using Google Forms you can use Google Sheets to analyze and visualize your data.
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.
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, Twitter and LinkedIn.
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.
Get the Google Sheets Tips
In a hurry? You can also download the resources included in my post. Get my Google Sheets Tips PDF.