16 incredibly useful Google Sheets productivity tips

I’ll admit it: For almost all of my grownup life, spreadsheets have remained shrouded in thriller. I’ve used them lots, in fact—to trace income, examine statistics, even preserve databases for numerous forms of work-related information—however I’ve at all times felt like I’ve barely been scratching the floor of what they’re capable of do.

And that’s a disgrace. With Google Sheets, particularly, sticking solely to spreadsheet fundamentals appears akin to sitting on a mountain of untapped potential. The service has a profusion of superior capabilities, choices, and shortcuts; however till you determine and internalize them, you’re getting solely a fraction of the worth it might probably present.

So in any case these years, I made a decision to take motion. I dug deep into Sheets’ darkest nooks and crannies to uncover a few of its most helpful and simply missed options. Whether or not you’re an informal spreadsheet explorer or a extra formidable data-crunching professional, I’d be keen to wager there are many worthwhile potentialities simply ready so that you can uncover, too.

Learn on, and prepare to take your Google Sheets expertise to a complete new degree.

Whereas a few of these objects can even work within the Sheets’ cell apps, the directions beneath are all for the service’s net model.

1. Summon the date very quickly

Sheets has loads of keyboard shortcuts, however one collection that’s particularly value noting is the gathering of instructions that rapidly insert the present date and/or time wherever you need: 

Hit Ctrl or Cmd and the semicolon key inside any spreadsheet so as to add the date; Ctrl or Cmd with Shift and the semicolon key so as to add the time; and Ctrl or Cmd together with Alt and Shift and the semicolon key so as to add the date and time collectively.

2. Format textual content quick

Google Sheets’ fast-formatting shortcuts are additionally value remembering. With the correct mixture of keys, you possibly can format any cell or choice of cells nonetheless you need, with out having to dig round in menus. 

Commit these to reminiscence:

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

3. Make your individual customized Sheets shortcut

You possibly can truly create your individual customized shortcut inside Sheets to carry out a posh collection of customized actions with a single command. 

Open the Extensions menu, choose Macros, then choose File macro. If you need the shortcut to at all times be carried out on the identical particular cells, choose the Use absolute references possibility; in any other case, choose Use relative references. Then carry out no matter actions you wish to report.

You can do one thing like set a particular kind of formatting for a cell’s contents (bolded white textual content with the Open Sans font and a dark-gray background, for example) or you could possibly manipulate knowledge in a extra concerned method, like copying a cell’s contents after which erasing that cell and pasting the contents one cell over to the left. While you’re completed, click on Save within the macro-recording panel, and also you’ll have the ability to give your new shortcut a reputation and assign it to any obtainable key mixture for future activation.

Talking of copying a cell’s contents, for those who ever must duplicate a cell’s knowledge and have it seem in a number of cells above it, beneath it, or on both facet of it, click on the unique cell to stipulate it in blue, then search for the little blue sq. in its lower-right nook. Click on that sq. and drag it in no matter route you need, for so far as you want. While you let go, the unique cell’s contents will immediately seem in all the opposite cells you chose.

5. Hyperlink two sheets collectively

Want to indicate some reside knowledge from one spreadsheet inside one other? Copy the complete URL of the sheet with the info and paste it into Sheets’ ImportRange perform, utilizing the next format (with your individual URL, sheet quantity, and cell vary in place):

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

Then paste that perform into the sheet the place you need the info to seem. You’ll need to hover over the cell and click on a button to permit the 2 sheets to be related, however then—hocus-pocus! All the info out of your different sheet will magically seem and stay present every time any adjustments are made.

6. Seize knowledge from the net

Sheets can pull in knowledge from any publicly obtainable net web page, too, so long as the web page has a correctly formatted desk. The key lies throughout the ImportHTML command. Use it with no matter URL you want and the quantity indicating which desk on the web page you wish to import (“1” for the primary desk, “2” for the second, and so forth):

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

And similar to that, the entire information will seem inside your spreadsheet.

An identical sort of command can allow you to import latest entries from an internet site’s RSS feed into any spreadsheet. All you do is enter the ImportFeed command together with the URL of the feed you need. As an example, for those who wished to see all my Fast Company stories in Google Sheets, you could possibly enter:

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

If you happen to wished solely the titles of the tales—and wished solely, say, the latest 5 entries—you could possibly add within the following parameters:

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

After which for those who wished to put the hyperlinks to every story in a separate column alongside that, you could possibly use this:

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

8. Get rid of further areas

If you happen to spot some further areas earlier than or after knowledge in your spreadsheet, whether or not you’re numbers or textual content, don’t overlook the Google Sheets perform TRIM. You possibly can kind it in for no matter cell you need (=TRIM(A1), for example), and it’ll take away any main or trailing areas, and offer you a cleaner model of the cell’s worth.

If you wish to carry out the perform for a number of cells without delay, use this format for no matter vary you want:

=ArrayFormula(TRIM(A2:A50))

9. Repair your formatting

numerous knowledge with RanDoM or ImPropeR CaPitaLiZaTion? Sheets can standardize case formatting for you with just a few easy capabilities:

=Higher(A1) will make all of the textual content uppercase for no matter cell you point out; =Decrease(A1) will do the identical with lowercase; and =Correct(A1) will capitalize the primary letter of every phrase for a title-case impact.

10. Implement your electronic mail addresses

Possibly you’ve got a database of user-submitted electronic mail addresses. Inform Sheets to look by means of the addresses and decide in the event that they’re all correctly formatted: Use the perform IsEmail(A1) with no matter cell you want—or if you wish to carry out the perform for a variety of cells, use this format:

=ArrayFormula(ISEMAIL(A2:A50))

Sheets will provide you with a True or False reply for each electronic mail tackle you feed it.

You possibly can simply create a tiny chart inside a single cell utilizing Sheets’ nifty Sparkline characteristic. Simply kind the command =Sparkline adopted by the cells you wish to embody, the phrase “charttype,” after which the kind of chart you wish to create—resembling line, bar, or column—formatted like this:

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

If you happen to actually wish to get wild, you possibly can even embody a variety of customization commands that’ll management the colours utilized in completely different components of your chart together with different visible elements.

Desire a fast and straightforward technique to make your spreadsheet shine? Search for the Alternating Colours possibility in Sheets’ Format menu. It’ll offer you a easy set of choices that’ll apply a sharp-looking shade sample to your rows—no thought or effort required.

When you’re jazzing up your spreadsheet’s look, take into consideration letting Sheets rotate the textual content in your header row. Spotlight the row, then click on on the icon that reveals an “A” with a right-facing arrow beneath it (towards the correct of the top-of-screen toolbar—or presumably throughout the three-dot menu icon, in case your window width is slim). 

You possibly can then decide from a number of eye-catching results that’ll set your header textual content aside and provides your spreadsheet a snazzy new look.

While you’re sharing a spreadsheet or utilizing a type to just accept survey-like responses, you possibly can ask Sheets to inform you every time an edit or addition happens—both instantly or as a once-daily electronic mail digest. Search for the Notification settings possibility within the Instruments menu to set your preferences for any specific spreadsheet.

Sheets has the facility of Google Finance (which—who knew?—remains to be a factor) baked proper in in your stock-knowledge wants. The system is ready to offer you real-time or historic inventory costs together with all types of different market-related knowledge for any publicly traded firm.

Merely use the perform GoogleFinance adopted by the data you want, utilizing the variables and codecs described on this page.

Ever end up scrolling by means of a listing of responses in numerous languages? Sheets can determine any language utilized in a spreadsheet and even translate it into your individual native tongue on the spot. To detect a language, use the next perform (with the suitable cell quantity rather than “A1”):

You can too enter in a phrase rather than a cell quantity, if you need:

=DETECTLANGUAGE(“pepinillo”)

Google will provide you with a two-letter code telling you the language that was used. To translate, in the meantime, use the next command—with your individual phrase or cell quantity rather than “A1” and the code for no matter language you wish to translate into (if it’s something apart from English, as referenced beneath):

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

It simply goes to indicate: Very like the language of affection, the language of spreadsheets really is common.

This text is a free excerpt from our particular report, “77 extremely helpful suggestions for Google apps: Gmail, Docs, Sheets, and past.” Read the entire package here.