Most people reading this post probably use spreadsheets at some point during their work day. But, we’d bet that a lot of you aren’t aware of their full functionality. Used well, they can save you a lot of time (and can go horribly wrong when used badly!). Here are some awesome things you can do with Google Sheets to blow your mind. A few functions listed below are Google-specific; so don’t go trying all of them in Microsoft Excel! Have a read and let us know how you went – do you know know all of these Sheets functions? Let us know if we’ve missed anything in the comments.
1. Comment and send emails through comments
Since Google Sheets is cloud-based it’s great for collaboration. Like with other spreadsheets, you can make a comment on any cell – but did you know you can use a + to enter email addresses and generate an email directly from the comment? This is great for sheets you’re collaborating on with a team, and really useful when you want to address a specific person about a specific cell.
2. Named ranges
Most formulas get you to enter ranges, eg. SUM(A5:B10) but these are very hard to read so you can assign names to your ranges.
You can then rewrite your formulas in a much more readable way, and easily review or update your named ranges at a later stage. Just navigate to Data > Named Ranges to review your current named ranges.
3. Data protection and validation
In big collaborative sheets there are sometimes issues with people modifying data they shouldn’t. In Google sheets you can lock them out. You can also ensure that certain cells have only a specific type of data, whether numbers or even a pre-defined list that Google will format into a dropdown.
4. Conditional formatting
You can change the colour of a cell based on its value, which is great for spotting errors and outliers.
You can even have a cell colour set based on a custom formula, for example always highlighting the largest value in a column.
Need more help on conditional formatting?
5. Integration with Google Forms
You can create a Google form where each response is logged as a row in a Google Sheet.
Once the responses are received, you can use all of the other sheet functions for analysis and visualisation.
Get detailed instructions on setting up a Google Form.
6. Filter Views
You might be aware of filters that let you select only the rows that are of interest to you.
Google Sheets also lets you save commonly-used filters for future reference, very handy for when you’re repeatedly filtering the same columns over and over again.
7. Use SUMIFS and COUNTIFS to quickly summarise data
These let you sum and count cells based on multiple criteria coming from other columns. They can be a quick alternative to pivot table and lookup formulas.
9. Use array formulas to perform work in bulk
Regular spreadsheet formulas have just one answer that the sheet writes to a single cell. Array formulas are much more powerful since their output can be a whole range of cells. Array formulas let you become a power-user since they can quickly fill out an entire sheet with what you need.
Below is just one example: how to work out interest earned from multiple deposits, through the array formula in the top row (C2)
10. Automatically import data from other sheets, an RSS feed or a webpage
There are a number of import functions:
- ImportRange to import from another Google Sheet (great for linking sheets and reducing the need for duplicate data entry)
- 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 SmartTraveller.gov.au’s travel advice statuses for each country:
Love this? Find out more.
11. Google Sheets scripts and add-ons
If the custom formulas provided by Google Sheets aren’t enough and you know how to program, you can always create a Google Apps Script to be attached to a sheet. This lets you write code that uses a whole range of Google Apps services with almost endless possibilities from automatically sending emails, creating Google Docs, interacting with web services and more.
You can also install add-ons from a large public library:
Our favourite add-on is of course Google’s official Analytics add-on that lets you create complex Google Analytics reports on the fly.
12. Create a sparkline graph inside a cell
Need we say more? There are also a range of options that let you modify what this sparkline looks like.
Learn how to add a sparkline.
13. Create an image inside a cell
If you have an external URL, you can use this to create in-line images, including stretching and changing the dimensions.
14. Translate text inside a sheet
This is really useful not only if you’re learning a foreign language, but if your work involves lists of keywords in multiple languages (eg. Google AdWords and SEO for multi-region websites).
15. Visualise your data more creatively
While bar and pie charts have their place, you can do a lot more in terms of creative visualisations. This includes interactive ones such as treemaps and an animated motion chart to produce a representation of metrics over time in the style of Gapminder’s Hans Rosling.
16. Use regular expressions inside a sheet
Regular expressions make life much easier for advanced work with Google Analytics, SEO, data analysis and more. If you’re not sure of their power check out our guide to regex in Google Analytics and our regular expressions cheat sheet. But did you know that you can also use regular expressions inside Google Sheets formulas? Here’s one to quickly extract the year of publication from a list of blog post URLs:
17. Use the QUERY function to perform an SQL-like query on a sheet
If you’re now complaining that regular expressions are nice but not an actual method to perform data analysis, you’re probably a die-hard techie and know SQL (structured query language). Rest assured that Google Sheets lets you perform a query on a table in a language that’s very similar to SQL, meaning the only limit to what you can do is your imagination.
Learn more about QUERY.