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)
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)
You can also specify which language you want to translate text from and to, if you want.
=GOOGLETRANSLATE("A2", "fr", "es")
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)
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")
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())
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)
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:
