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 our 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
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’s 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:
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.