17 Awesome Things Google Sheets Can Do

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.

comment and send emails

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.

named 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.

Keyword cost

Learn more about 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.

range edit
Data validation

See more instructions on protecting your sheets’ data.

4. Conditional formatting

You can change the colour of a cell based on its value, which is great for spotting errors and outliers.

conditional format

You can even have a cell colour set based on a custom formula, for example always highlighting the largest value in a column.

conditional rules

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.

staff lunch

Once the responses are received, you can use all of the other sheet functions for analysis and visualisation.

timestamp

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.

filter views

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.

common filters

Find out more.

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.

sumifs and countifs

Find out more about SUMIFS and COUNTIFS.

8. Use CLEAN and TRIM to make text cells usable

If you regularly deal with dirty data, the TRIM function will strip whitespace from either end of a cell and the CLEAN function will remove any non-printable characters.

clean and trim

Find out more about CLEAN and TRIM.

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)

array

For more see Google’s formula reference, especially the ARRAYFORMULA, SORT, FILTER, UNIQUE and TRANSPOSE formulas.

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:

importhtml

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:

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.

sparkline

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.

image inside cell

Learn more.

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).

translate

See some more examples.

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.

data vis

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:

regex spreadsheet

Find out more about REGEXMATCH, REGEXEXTRACT, REGEXREPLACE.

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.

query

Learn more about QUERY.