• Home

  • Productivity

  • App tips

App tips

3 min read

6 Google Sheets functions that do more than math

By Justin Pot · July 8, 2020
A hero image for Google Sheets app tips with the Google Sheets logo on a green background

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.

Want to feed information from thousands of apps into your spreadsheet? Learn how Zapier works and then check out popular ways to automate Google Sheets.

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

alt

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.

Not sure how to find feeds? Here's how to find the RSS feed for almost any site.

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.

Related reading:

  • Google Sheets add-ons to supercharge your work

  • 21 free Google Sheets templates to boost productivity

  • How to create and use pivot tables in Google Sheets

Get productivity tips delivered straight to your inbox

We’ll email you 1-3 times per week—and never share your information.

tags
mentioned apps

Related articles

Improve your productivity automatically. Use Zapier to get your apps working together.

Sign up
See how Zapier works
A Zap with the trigger 'When I get a new lead from Facebook,' and the action 'Notify my team in Slack'