- 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
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
(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):
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
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.
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:
- Right click on the cell that you want to turn into a clickable hyperlink
- Click “Insert link”
- Choose either “Sheets in this spreadsheet” or “Select a range of cells to link”
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:
Click and hold down your mouse button, and then drag the thick line down:
Let go when you’ve reached the row level you want to freeze (in this case, just the top row):
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:
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.
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.
Join the conversation about this story »
NOW WATCH: There are 7.7 billion humans on Earth today. Here’s what would actually happen if Thanos destroyed 50% of all life on the planet.