16 incredibly useful things you didn’t know Google Sheets could do

I’ll admit it: For the majority of my adult life, spreadsheets have remained shrouded in mystery. I’ve used them plenty, of course—to track income, compare statistics, even maintain databases for various types of work-related info—but I’ve always felt like I’ve barely been scratching the surface of what they’re able to do.

And that’s a shame. With Google Sheets, in particular, sticking only to spreadsheet basics seems akin to sitting on a mountain of untapped potential. The service has a profusion of advanced functions, options, and shortcuts; but until you identify and internalize them, you’re getting only a fraction of the value it can provide.

So after all these years, I decided to take action. I dug deep into Sheets’ darkest nooks and crannies to uncover some of its most useful and easily overlooked features. Whether you’re a casual spreadsheet explorer or a more ambitious data-crunching pro, I’d be willing to wager there are plenty of worthwhile possibilities just waiting for you to discover, too.

Read on, and get ready to take your Google Sheets experience to a whole new level.

While some of these items will also work in the Sheets’ mobile apps, the instructions below are all for the service’s web version.

1. Summon the date in no time

Sheets has plenty of keyboard shortcuts, but one series that’s especially worth noting is the collection of commands that quickly insert the current date and/or time wherever you want: 

Hit Ctrl or Cmd and the semicolon key within any spreadsheet to add the date; Ctrl or Cmd with Shift and the semicolon key to add the time; and Ctrl or Cmd along with Alt and Shift and the semicolon key to add the date and time together.

2. Format text fast

Google Sheets’ fast-formatting shortcuts are also worth remembering. With the right combination of keys, you can format any cell or selection of cells however you want, without having to dig around in menus. 

Commit these to memory:

• Ctrl-Shift-1: Format as decimal
• Ctrl-Shift-2: Format as time
• Ctrl-Shift-3: Format as date
• Ctrl-Shift-4: Format as currency
• Ctrl-Shift-5: Format as percentage
• Ctrl-Shift-6: Format as exponent

3. Make your own custom Sheets shortcut

You can actually create your own personalized shortcut within Sheets to perform a complex series of custom actions with a single command. 

Open the Extensions menu, select Macros, then select Record macro. If you want the shortcut to always be performed on the same specific cells, select the Use absolute references option; otherwise, select Use relative references. Then perform whatever actions you want to record.

You could do something like set a specific sort of formatting for a cell’s contents (bolded white text with the Open Sans font and a dark-gray background, for instance) or you could manipulate data in a more involved manner, like copying a cell’s contents and then erasing that cell and pasting the contents one cell over to the left. When you’re finished, click Save in the macro-recording panel, and you’ll be able to give your new shortcut a name and assign it to any available key combination for future activation.

4. Copy cells swiftly

Speaking of copying a cell’s contents, if you ever need to duplicate a cell’s data and have it appear in multiple cells above it, below it, or on either side of it, click the original cell to outline it in blue, then look for the little blue square in its lower-right corner. Click that square and drag it in whatever direction you want, for as far as you need. When you let go, the original cell’s contents will instantly appear in all the other cells you selected.

5. Link two sheets together

Need to show some live data from one spreadsheet inside another? Copy the full URL of the sheet with the data and paste it into Sheets’ ImportRange function, using the following format (with your own URL, sheet number, and cell range in place):

=IMPORTRANGE (“https://docs.google.com/spreadsheets/d/1aBcDEfgHiJKLMnOPQRSTuVWXZ”, “Sheet1!D1:D15”)

Then paste that function into the sheet where you want the data to appear. You’ll have to hover over the cell and click a button to allow the two sheets to be connected, but then—hocus-pocus! All the data from your other sheet will magically appear and remain current whenever any changes are made.

6. Grab data from the web

Sheets can pull in data from any publicly available web page, too, as long as the page has a properly formatted table. The secret lies within the ImportHTML command. Use it with whatever URL you need and the number indicating which table on the page you want to import (“1” for the first table, “2” for the second, and so on):

=IMPORTHTML(“https://en.wikipedia.org/wiki/List_of_sandwiches”,”table”,1)

And just like that, all of the info will appear within your spreadsheet.

7. Bring any feed into your sheet

A similar kind of command can let you import recent entries from a website’s RSS feed into any spreadsheet. All you do is enter the ImportFeed command along with the URL of the feed you want. For instance, if you wanted to see all my Fast Company stories in Google Sheets, you could enter:

=IMPORTFEED(“https://fastcompany.com/user/jrraphael/rss”)

If you wanted only the titles of the stories—and wanted only, say, the most recent five entries—you could add in the following parameters:

=IMPORTFEED(“https://fastcompany.com/user/jrraphael/rss”,”items title”,false,5)

And then if you wanted to place the links to each story in a separate column alongside that, you could use this:

=IMPORTFEED(“https://fastcompany.com/user/jrraphael/rss”,”items URL”,false,5)

8. Eliminate extra spaces

If you spot some extra spaces before or after data in your spreadsheet, whether you’re looking at numbers or text, don’t forget the Google Sheets function TRIM. You can type it in for whatever cell you want (=TRIM(A1), for instance), and it’ll take away any leading or trailing spaces, and give you a cleaner version of the cell’s value.

If you want to perform the function for multiple cells at once, use this format for whatever range you need:

=ArrayFormula(TRIM(A2:A50))

9. Fix your formatting

Looking at lots of data with RanDoM or ImPropeR CaPitaLiZaTion? Sheets can standardize case formatting for you with a few simple functions:

=Upper(A1) will make all the text uppercase for whatever cell you mention; =Lower(A1) will do the same with lowercase; and =Proper(A1) will capitalize the first letter of each word for a title-case effect.

10. Enforce your email addresses

Maybe you have a database of user-submitted email addresses. Tell Sheets to look through the addresses and determine if they’re all properly formatted: Use the function IsEmail(A1) with whatever cell you need—or if you want to perform the function for a range of cells, use this format:

=ArrayFormula(ISEMAIL(A2:A50))

Sheets will give you a True or False answer for every email address you feed it.

11. Check out a chart

You can easily create a tiny chart within a single cell using Sheets’ nifty Sparkline feature. Just type the command =Sparkline followed by the cells you want to include, the word “charttype,” and then the type of chart you want to create—such as line, bar, or column—formatted like this:

=SPARKLINE(E12:E23,{“charttype”,”column”})

If you really want to get wild, you can even include a variety of customization commands that’ll control the colors used in different parts of your chart along with other visual factors.

12. Add some color to your rows

Want a quick and easy way to make your spreadsheet shine? Look for the Alternating Colors option in Sheets’ Format menu. It’ll give you a simple set of options that’ll apply a sharp-looking color pattern to your rows—no thought or effort required.

13. Try a Sheets text trick

While you’re jazzing up your spreadsheet’s appearance, think about letting Sheets rotate the text in your header row. Highlight the row, then click on the icon that shows an “A” with a right-facing arrow beneath it (toward the right of the top-of-screen toolbar—or possibly within the three-dot menu icon, if your window width is narrow). 

You can then pick from several eye-catching effects that’ll set your header text apart and give your spreadsheet a snazzy new look.

14. Stay on top of edits

When you’re sharing a spreadsheet or using a form to accept survey-like responses, you can ask Sheets to notify you whenever an edit or addition occurs—either immediately or as a once-daily email digest. Look for the Notification settings option in the Tools menu to set your preferences for any particular spreadsheet.

15. Finesse financial data

Sheets has the power of Google Finance (which—who knew?—is still a thing) baked right in for your stock-knowledge needs. The system is able to give you real-time or historical stock prices along with all sorts of other market-related data for any publicly traded company.

Simply use the function GoogleFinance followed by the info you desire, using the variables and formats described on this page.

16. Translate right within Sheets

Ever find yourself scrolling through a list of responses in different languages? Sheets can identify any language used in a spreadsheet and even translate it into your own native tongue on the spot. To detect a language, use the following function (with the appropriate cell number in place of “A1”):

=DETECTLANGUAGE(A1)

You can also enter in a word in place of a cell number, if you want:

=DETECTLANGUAGE(“pepinillo”)

Google will give you a two-letter code telling you the language that was used. To translate, meanwhile, use the following command—with your own word or cell number in place of “A1” and the code for whatever language you want to translate into (if it’s anything other than English, as referenced below):

=GOOGLETRANSLATE(A1,”auto”,”en”)

It just goes to show: Much like the language of love, the language of spreadsheets truly is universal.

This article is a free excerpt from our special report, “77 incredibly useful tips for Google apps: Gmail, Docs, Sheets, and beyond.” Read the entire package here.

For even more next-level Google knowledge, check out my free Android Intelligence newsletter and get three fresh and useful tips in your inbox every Friday.

https://www.fastcompany.com/91068267/16-best-google-sheets-productivity-tips?partner=rss&utm_source=rss&utm_medium=feed&utm_campaign=rss+fastcompany&utm_content=rss