When it comes to Google Sheets vs Excel, many people choose Google Sheets because it’s free or cheaper than Excel and because it offers enough features to satisfy even spreadsheet power users.
But even if you’ve been using Google Sheets for a while, you may not know all the tricks and tips for getting more done in less time. This post covers 8 Google Sheets productivity hacks you can use to streamline your workload better organize your data.
The Complete List of Google Sheet Productivity Hacks
The following Google Sheets productivity hacks are not arranged in any particular order but generally move from simpler techniques to more complex ones.
Google Sheets is powerful software and there are many things it can do that we didn’t include on this list.
What you’ll find below are some of the most important tips any user can apply to supercharge their productivity when working with Google Sheets.
1. Keyboard Shortcuts
Keyboard shortcuts are the quintessential “hack” and Google Sheets has plenty that you should know. The first one you should know about helps you access the full list of keyboard shortcuts within Google Sheets itself.
If you’re using Apple, hold Command and “forward slash.”
If you’re using Windows, hold Ctrl and “forward slash.”
A popup should appear with a LOOONG list of keyboard shortcuts. Don’t feel overwhelmed. On the left-hand side of this popup will be categories for the keyboard shortcuts:
- Editing
- Menus
- Formating
- Data
- Review
You can click any of these to be taken to the specific shortcuts in that category.
Some that we recommend are:
Clear All Formatting in a cell or range
- Apple: ⌘ +
- Windows: Ctrl +
Insert the current date in a cell
- Apple: ⌘ + ;
- Windows: Ctrl + ;
Find and Replace
- Apple: ⌘ + Shift + H
- Windows: Ctrl + H
Plus, Google Sheets released a few exciting updates to their shortcuts last May. You can now “enable compatible spreadsheet shortcuts” and use common keyboard shortcuts from other spreadsheet software like Excel within Google Sheets.
This added feature makes switching to Google Sheets that much easier.
2. Comment on Cells
Remote team communication is happening more than ever today, and one of the most important features Google Sheets offers is the ability to leave comments on specific cells.
There are 3 ways to leave a comment in cells:
- Right-click on a specific cell and choose “Comment” from the menu.
- Left-click on a cell then click “Insert” from the Google Sheets menu and select “Comment.”
- Left-click on a cell and enter keyboard shortcut Ctrl + Alt + M (Apple users enter
When you leave a comment, a small yellow triangle will appear in the upper right-hand side of the cell. This makes sure that if anyone checks the spreadsheet, they’ll notice a comment was left.
But you can also leave a specific comment for a specific person and let Google Sheets notify them that they were tagged in a comment.
To do this, type the “@” symbol followed by their email address.
An example comment tagging someone would look like this: “Hey @[email protected] have you seen this?”
Instead of sending a long, complicated explanation in an email, you can tag teammates and show them what you’re talking about – streamlining communication.
3. Conditional Formatting
Conditional formatting is a feature that every spreadsheet software has to have, and Google Sheets lets you take advantage of this key function.
Conditional formatting changes the text or background color of cells, rows, or columns based on predefined criteria that you set.
It’s extremely helpful in visualizing data and breaking up the monotony of black letters on white background. You’re less likely to commit errors using conditional formatting because it organizes and arranges data for quick understanding.
To use conditional formatting, highlight all the cells you want to format, navigate to the menu in Google Sheets and select “Format,” then click on “Conditional Formatting.”
A window will pop up on the right-hand side of your screen and let you create as many “rules” as you would like to format your spreadsheet.
4. Add Images to Google Sheets
Spreadsheets are known for collecting all the text and numerical data you can input, but Google Sheets lets you add images as well.
This is helpful if you’re using spreadsheets to track brands (and need their logos), competitor products (if you’re conducting a competitive analysis), or any other images you may need to track alongside other data.
There are two ways to do this:
- Left-click on a cell and select “Insert” in the menu and then scroll the cursor over “Image” and select “Image in cell.”
- Left-click on a cell and type the formula =image then paste the link to the image in quotation marks within a pair of parenthesis. Ie. =image (“link”)
When you select “Insert” from the menu and scroll over “Image” you’ll get another option that says “Image over cells.” This lets you paste an image that free-floats above the cells in your spreadsheet that you can freely move.
5. Group Cells In Order
Rarely do spreadsheets contain random information. You’re using Google Sheets to organize information, not spill it out.
Most often, you’ll need to arrange data in a particular order.
For this, you can use the ARRAYFORMULA().
An array is simply a table of values.
The ARRAYFORMULA() processes your data in a single batch. With it, you’re able to make changes to a single cell and have it ripple through the rest of the data range.
The formula is =ARRAYFORMULA(array_formula).
Where it says array_formula, you’ll place either a range of cells, a mathematical expression using one cell range or multiple ranges of the same size, or a function that returns a result greater than one cell.
Here’s an example from Ben L. Collins:
6. Pivot Tables
A basic spreadsheet has rows going horizontally and columns going vertically. If you’re inputting a lot of data over a long period of time, it will become difficult to measure and understand it all.
A pivot table is a tool to summarize and aggregate data to see it in more than just 2 dimensions.
The best way to explain it is to show you how it works.
Take this set of data provided by Google:
This is a pretty standard spreadsheet for organizing basic inputs for products. The larger this list grows, the more difficult it will be to manage.
To turn this data into a pivot table, highlight all of it and then select “Data” from the menu and click on “Pivot table.” A popup will appear asking if you want to create a new sheet, select that option and press “Create.”
Your pivot table will look something like this:
Now instead of a confusing list of inputs, you have a well-organized table summarizing and automatically categorizing the data.
7. Data Validation
Sometimes you may want to ensure certain cells only contain a specific kind of information. If you have a column for sales numbers, you may need to block someone from inputting words or symbols into those cells.
Data validation restricts what values are allowed to be entered into cells.
Select all the cells you want to constrain to certain values, click on “Data” in the menu and select “Data validation”
Based on the settings you prefer, you can allow Google Sheets to show a warning when incorrect data is entered or outright reject the input.
You can go one step further and select the option “show validation help text” to display a friendly message informing someone how to input the correct data if they enter the wrong information.
8. Automate Tasks
Repetitively entering the same information over and over again is not only mind-numbing, but it can also lead to harmful errors.
Google Sheets allows you to automate simple tasks using the “Macro” feature.
It records specific actions you take within a spreadsheet and saves the entire process so you can repeat it endlessly in the same sheet or an infinite number of new ones with the click of your mouse or the pressing of a few hotkeys.
To create a Macro, click on “Tools” from the Google Sheets menu, hover your mouse over “Macros” and select “Record Macro.”
You’ll be given a choice to either record a macro using “absolute references” or “relative references.”
Absolute references means that whatever you input into specific cells, the macro will always record the same data in those same cells.
Relative references means that you can enter data into cells, and this macro can be reproduced on any cells you later select.
When you finish recording your macro and click “Save,” you’ll be able to name the saved macro and create a keyboard shortcut to execute the macro at any time.
How to Boost Team Collaboration When Using Google Sheets
The data collected in Google Sheets (and organized using the productivity hacks on this list) will most likely be shared with one or more members of your team.
However, these team members may need more help in understanding the data or specific processes you used to compile it.
Instead of typing up explanations or trying to talk them through it, you can use different visual communication tactics to clarify their confusion.
You can turn a certain process into a GIF.
Annotate screenshots of the data to illuminate its meaning.
Or record a webcam where you demonstrate your calculations or present the data so that your team members can see it while you discuss it.
Zight (formerly CloudApp) gives you these options and plenty more.
We’ve been ranked by G2 Crowd as one of the top sales enablement tools and can help you be more productive by communicating better.
Find out why Zight (formerly CloudApp) should be paired with Google Sheets productivity hacks for enhanced teamwork today.