6 Google Sheets functions that do more than math

Justin Pot
Justin Pot / Published July 8, 2020

Functions are what make spreadsheets powerful. Most Google Sheets users know you about the functions for basic calculations—SUM, AVERAGE, COUNT, that sort of thing.

But Google Sheets functions can do so much more than math. You can use functions to translate text into other languages, pull in information from around the web, and even clean up your terrible formatting. Google offers a complete function list, which you should obviously read from beginning to end every morning before breakfast. For now, though, here are a few surprisingly useful Google Sheets functions that everyone should know about.

Fix weird capitalization issues using PROPER, UPPER, and LOWER functions

You'd think that people could type their own names, but set up any kind of form for the general public, and you'll somehow end up with names in lowercase or otherwise featuring wEiRD cAps iSSues. PROPER is a Google Sheets function that can fix this for you—it can re-format any text so that every word starts with a capital letter. Here's an example for fixing the item in cell A2.

=PROPER(A2)

Using PROPER to clean up text in Google Sheets

There are a few similar functions for cleaning up text.

  • UPPER converts text to uppercase
  • LOWER converts text to lowercase
  • TRIM removes spaces before and after any text

These are all good to know about if you've got a Google Form set up and want to clean up the formatting, particularly if you're going to export information to your CRM or contact management app.

Translate any text with GOOGLETRANSLATE

Speaking of forms: sometimes people are going to fill them out in a language you don't know. GOOGLETRANSLATE can automatically detect what language something is written in and translate it for you. Like this:

=GOOGLETRANSLATE(A2)

Translating text with Google Translate

You can also specify which language you want to translate text from and to, if you want.

=GOOGLETRANSLATE("A2", "fr", "es")

Translating French to Spanish with Google Translate

There's a lot more you can break down here, so check out Google's documentation for more information. And, if you just want to know what language something is in, you can use DETECTLANGUAGE. That could prove handy if you're trying to compile what percentage of a column is in a given language.

Get data from other spreadsheets with IMPORTRANGE

You can link your spreadsheets together, pulling in information, using the IMPORTRANGE function. You will need to provide a link to the spreadsheet you want to pull data from, then specify which sheet and cells you want to grab. Here's an example:

=IMPORTRANGE("spreadsheeturl", "Sheetname!A1:D57")

Note that you'll have to replace spreadsheeturl with an actual spreadsheet URL. This isn't a one-time import: any information that changes in your source spreadsheet is updated in real-time.

This allows you to combine information from various sources in one place. It could prove useful if, for example, you wanted to share some information without sharing an entire spreadsheet. Use IMPORTRANGE to grab the relevant bits in a new spreadsheet, then share that.

Pull in data from other websites with IMPORTHTML and IMPORTFEED

You can also pull in information from around the web—provided the information is formatted in an HTML table—using the IMPORTHTML function. You only need to specify the website and which table you want. Here's an example from Wikipedia's list of the most popular given names, pulling in the third table on the site:

=IMPORTHTML("https://en.wikipedia.org/wiki/List_of_most_popular_given_names", "table", 3)

Importing data from another Google Spreadsheet

It's a quick way to import information instead of copy-pasting and probably ending up with wonky formatting.

You can also use IMPORTFEED to pull in the latest headlines and content from any RSS feed. Here's an example using the New York Times' personal tech section:

=IMPORTFEED("https://rss.nytimes.com/services/xml/rss/nyt/PersonalTech.xml")

Importing RSS feeds in Google Sheets

You could use this to make a dashboard of updates from all of your favorite blogs and websites.

Pull in stock prices with GOOGLEFINANCE

Google Finance is a service that provides updates on stocks, currencies, and more. You can pull information from this service into any spreadsheet using the GOOGLEFINANCE function. Here's a quick example pulling in Google stock prices from the past 30 days:
=GOOGLEFINANCE("NASDAQ:GOOG","price",TODAY()-30,TODAY())

Pulling in financial data in Google Sheets

This is a powerful function that can do all sorts of things, including currency value. You can easily build your own financial dashboard, with only the information that's relevant to you. Or, if you own stocks, you could use this to calculate the current value of your assets. Be sure to read Google's documentation because there's a lot to dive into here.

Make a mini chart in a cell with SPARKLINE

This one's simple, sure, but it's also surprisingly useful for spotting trends at a glance. SPARKLINE can show a simple miniature chart right in a cell. Here's an example using the Google stock values I pulled in above:

=SPARKLINE(B3:B22)

Making a quick sparkline in Google Sheets

It's a quick way to add visual context to almost any information. The default is a line graph, but you can also build mini bar or column charts. Check Google's documentation for more information.

Get Productivity Tips In Your Inbox

Learn about workflow, company building, and how to get things done.

Load Comments...

Comments powered by Disqus

Workflow

Take the Work out of Workflow

Zapier is the easiest way to automate powerful workflows with more than 2,000 apps.