21 Awesome Things Google Sheets Can Do

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.

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.

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.

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.

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.

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.

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.

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!

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:

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.

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.

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.

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.

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.

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.

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.

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=200×200&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.

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.

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.

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:

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.