6 productivity tricks that will make using Google Sheets faster and easier

Google SheetsGoogle

  • If you use Google Sheets for your job or to keep personal files organized, these tricks can help you do your work faster and more effectively.
  • They were compiled by Ben Collins, a freelance data analyst and developer, Google Developer expert, and online course creator at The Collins School of Data.
  • For instance, you can alert someone to a specific comment, quickly freeze certain panes, and create hyperlinks within your Google Sheets.
  • He also delivers six useful keyboard shortcuts that might feel clumsy at first, but will help become more efficient in your work.
  • Visit Business Insider's homepage for more stories.

Google Sheets can be intimidating to those who are new to using spreadsheets. But even if you're an old pro at Excel, chances are good that you could be getting more out of your work with Google Sheets. Certain keyboard shortcuts and other hacks can make your work faster, easier, and more efficient, especially the more you practice them.


Let's kick off this post with one of the all-time top Google Sheet productivity tips.

1. Keyboard shortcuts

1Courtesy of benlcollins.com

Use keyboard shortcuts to work more efficiently. It might feel clumsy at first, but persevere and it'll pay off in spades as you become more efficient in your work.

To see all of the available shortcuts, go to Help > Keyboard shortcuts

Here are my top shortcuts:

Clear All Formatting in a cell or range

Mac: ⌘ + \
PC/Chromebook: Ctrl + \

Insert the current date in a cell

Mac: ⌘ + ;
PC/Chromebook: Ctrl + ;

Select all the data in a table

Mac: ⌘ + A
PC/Chromebook: Ctrl + A

Find and Replace

Mac: ⌘ + Shift + H
PC/Chromebook: Ctrl + H

Open the drop-down menu on filtered cell

Mac: Ctrl + ⌘ + R
PC/Chromebook: Ctrl + Alt + R

Quickly repeat your previous action in a new cell

Mac: F4
PC/Chromebook: F4

(Note, this will also copy-paste static values, but not formulas. If you type some text into a cell and then hit F4 in a different cell before doing anything else, it'll copy that same text to the new cell. This quirk only works cell to cell, not cell to range though.)

Today, I challenge you to use keyboard shortcuts! (Click here for the Google Documentation for shortcuts.)

2. Stay informed of changes to a Google Sheet

Stay informed of changes to a Google Sheet by setting Notification Rules to send you an email.

You'll find it under this menu: Tools > Notification rules…

Here you can decide what should trigger a notification and how they should be delivered (immediately or a daily summary):

2Courtesy of benlcollins.com

3. Alert someone to a specific comment

Comments are one of the killer collaboration features embedded in Google Sheets.

You can alert someone to a specific comment in a Google Sheet by adding their email address.

You add a comment by right-clicking a cell and choosing Insert comment, or through the menu Insert > Comment

Google Sheets commentsCourtesy of benlcollins.com

You can then tag specific people in each comment if you wish, which means they'll receive an alert that they've been mentioned in a comment.

For example, you might tag Jenny in Finance to ask her to check if your calculation is correct. It's way easier than trying to explain something via email!

You tag someone in a comment very easily by typing a "+" or "@" in the comment box, and then adding the email address of the person you want to notify. As you start typing, Google will auto-suggest email addresses from your contacts list.

Google Sheets comments 2Courtesy of benlcollins.com

4. Create hyperlinks within your Google Sheets

Create hyperlinks within your Google Sheets to make it easier to find information.

You can add hyperlinks to different tabs or even directly to individual cells within your Google Sheets.

They're super easy to create. You don't even have to write any formulas yourself. Simply:
  1. Right click on the cell that you want to turn into a clickable hyperlink
  2. Click "Insert link"
  3. Choose either "Sheets in this spreadsheet" or "Select a range of cells to link"

That's it!

Hyperlinks in google sheetsCourtesy of benlcollins.com

Here's a few examples of how you could use this:

  • Add a Home button to every tab in your Sheet so you can quickly get back to the first tab
  • Create a "table of contents" for your Sheet
  • Link to important calculation cells so they can be easily accessed

5. Quickly freeze panes

Quickly drag the column header row down to freeze panes.

Freeze panes is a useful and simple technique to lock the top row (or rows) of your spreadsheet, so that they remain in view even as you scroll down your datasets. You're effectively anchoring them in place.

It's really useful because you can keep your column headings in view as you move about your data.

Traditionally, you freeze panes through the View > Freeze menu, but there is another quicker way.

Hover over the bottom of the column heading letters until you see the hand icon, like this:

freeze panes google sheetsCourtesy of benlcollins.com

Click and hold down your mouse button, and then drag the thick line down:

highlight freeze panesCourtesy of benlcollins.com

Let go when you've reached the row level you want to freeze (in this case, just the top row):

freeze panes final google sheetsCourtesy of benlcollins.com

This technique also works for freezing columns.

6. Quickly create a new Google Sheet

Method 1: Create a new Google Sheet instantly by typingsheet.newin your web browser:

create new google sheet 2Courtesy of benlcollins.com

It has the one drawback of creating the Sheet in your root Drive folder, so you have to manually move it to the folder you want.

Method 2: Create a new Google Sheet in the folder you're in by using Shift + S.

create a new google sheetCourtesy of benlcollins.com

If you have a specific folder open in Drive and you want to create a Sheet in that folder, just hit the shortcut Shift + S when you're inside that folder to create a new Google Sheet there.

Ben Collins is a freelance data analyst and developer, Google Developer Expert and online course creator at The Collins School of Data. He creates online courses and writes about working with data, with a particular focus on tutorials for data analytics, automation and dashboards, using Google Sheets, Apps Script and Data Studio. Ben previously taught Data Analytics for General Assembly and began his career as a forensic accountant.

Find him at benlcollins.com or on Twitter as benlcollins.
{{}}
Subscribe to whatsappSubscribe to whatsapp
Add Comment()
Comments ()
X
Sort By:
Be the first one to comment.
We have sent you a verification email. This comment will be published once verification is done.