Whether you’re a digital marketer, a small business owner, or any other professional, Google Sheets is proven to be a handy tool for productivity. However, the vast majority of users still don’t know how to utilize the full potential of spreadsheets.
In this post, we’ll uncover some interesting tips for getting more out of Google Sheets that will set you apart at work.
Trick #1: Master Keyboard Shortcuts
Being proficient in Google Sheet keyboard shortcuts is one of the best long-term efficiency skills you can acquire. It could feel awkward at first if you’re not used to shortcuts, but it will pay off as you get more proficient at using Google Sheets.
Here are some of the most common shortcuts that could increase your productivity.
1. Clear All Formatting in a cell or range:
Mac: ⌘ + \
Windows/PC: Ctrl + \
2. Insert the current date in a cell:
Mac: ⌘ + ;
Windows/PC: Ctrl + ;
3. Select all the data in a table:
Mac: ⌘ + A
Windows/PC: Ctrl + A
4. Find and Replace:
Mac: ⌘ + Shift + H
Windows/PC: Ctrl + H
5. Open the drop-down menu on filtered cell
Mac: Ctrl + ⌘ + R
Windows/PC: Ctrl + Alt + R
To see all of the available shortcuts, you may go to the menu: Help > Keyboard shortcuts and choose the ones that make sense to you.
Trick #2: Freeze Rows and Columns
The problem with working with large data sets is that if you scroll away from the headers, you lose track of the data. Therefore, freezing rows/columns is a cool technique that will make sure these headers are always visible no matter where you go in the worksheet.
To learn the trick of freezing the top row in Google Sheets, here are the steps you can follow:
- In the top-left part of the worksheet, you will see a gray empty box.
- Your mouse will change to the hand icon when you place it on the thick gray line below this box.
- Left-click from the mouse and drag it down to one row.
If you have headers in more than one row such as three rows, you can drag the gray line and position it below the third row. Similar to that, drag the gray line to the right to freeze a column that has headings.
Trick #3: Set Conditional Formatting in Sheets
An excellent feature for visually organizing data in a spreadsheet with plenty of data is conditional formatting. You can specify numerous rules to apply to a cell or range of cells in Google Sheets. Let’s see how to employ the desktop version of conditional formatting.
- Select all the cells you want the conditional formatting to apply to after opening a document in Sheets.
- To access the Conditional formatting rules pane on the right side of the screen while your selection is active, right-click inside the selection (you might need to scroll down and hover over view more cell actions first).
- In the Conditional formatting rules pane you can define the rules to apply to the series of cells.
- Choose whether you want a gradient or a single color for your rule using the tabs at the top of the rule pane.
- For each point in the gradient, you can choose from a variety of variables and colors if you selected a gradient in the previous stage. Use the Format rules drop-down list and text box to specify when the rule formats a cell if you only used one color. This could involve confirming that a certain piece of text is present in the cell or verifying that the number is inside a specified range.
- Your formatting will be applied automatically after selecting your color and rule. By selecting Add another rule and following the steps again, you can add multiple rules.
Trick #4: Create a Drop-Down Menu in Sheets
If you want to limit the information in a cell to a preset data set, drop-down menus can be your friend. When you have a preset set of responses or states, formulas can be used to automatically track the progress of projects and collect data without much human involvement.
To create a drop-down menu in Sheets, you’ll use the Data validation function. By selecting a cell or range, right-clicking, and choosing Data validation, you can access Data validation options.
Select List of items from the Criteria drop-down menu in the Data validation choices pop-up, and then enter the values you want to appear in the drop-down menu in your chosen cells in the field to the right of the Criteria drop-down. A comma must be used to separate each choice.
Finally, to enable the drop-down menu, make sure the Show drop-down list in the cell checkbox is selected. Once you have defined your values and configured the data validation options, click Save to create the drop-down list.
Trick #5: Create Filters
This trick of filtering data into different views may be necessary if you have a lot of data. For example, if you’re keeping track of event registrations, you might want to filter by job title, location, or sign-up destination. Maybe you want to sort customer data by the product they purchased. Create unique views in Google Sheets to parse out information.
To create these filters, you only have to head to Data —> Filter Views —> and Create a New Filter View. Adjust the filter by sorting, color, condition, or value. Now, you’ll be able to see limited information based on specific parameters.
Trick #6: Use FileDrop Add-On For Bulk Uploads
For this last trick, you can easily add images and files in Google Sheets by using an add-on called FileDrop. It is a free Google Sheets add-on that allows you to drag and drop files such as images into a spreadsheet cell with automatic linking and add them to Google Drive.
Moreover, you can insert any file type you need in your Google Sheets cells using FileDrop. The files will be added automatically to your Google Drive folder. You can also add files that exist in your Google Drive from the Drive tab.
All in all, this simple add-on saves you time and makes adding bulk files as easy as possible. The paid version offers great features including file history, adding multiple files at once, and inserting images over cells or in cells. Learn more by visiting FileDrop today.
For a more detailed tutorial on mass uploading on Google Sheets using FileDrop, here’s our previous article you can read: The Best Way To Mass Upload Images In Google Sheets
Trick #7: Use Pivot Tables
You can use a pivot table as a technique to compile a substantial amount of data. More exactly, you take a conventional two-dimensional table and pivot it around data aggregation to create a third dimension when you create a pivot table.
To create a pivot table, follow this simple guide:
- Open your Google Sheets, and highlight all the cells containing data.
- Click “Data” from the top menu, then “Pivot Table”.
- You’re good to go if Google’s suggested pivot table analysis answers your question.
- If not, you’ll have to create a customized pivot table. Locate “Rows and Columns”, and click on “Add” next to it. Select the data you want to analyze.
- Locate “Values”, and click on “Add” next to it. Select the values you want to display within the rows and columns.
- Click “Filters” to display the values meeting your criteria.
Trick #8: Lock Cells to Prevent Unwanted Changes
Want to make some Google Sheets cells locked? You can actually achieve this using a function.
It is as easy as selecting the cells you want to protect by highlighting them. From your top menu, select “Data” and then “Protected sheets and ranges.” Then, name your protected range and set the range’s permissions using the sidebar that appears. You have the option to either restrict access so that only you can alter the range or to display a warning whenever anyone does so.
Trick #9: Embed Charts on Your Website
We’re all aware that Google Sheets makes it simple to generate stunning charts and visuals, but did you know that you can also quickly publish these charts to a website?
To do that, you just have to click on your chart and then select “More” from the menu that appears in the top right corner. After that, select “Publish chart” and “Embed.” You’re ready to go once you paste the HTML code that was generated for you onto your website.
Trick #10: Enable Offline Mode
With Google Drive being a web-based service, you might assume if you don’t have an internet connection, you can’t access your files. Luckily, you can still access your file if you are using Google Chrome and have enabled offline mode in Google Drive. Doing this will allow you to access and edit your Docs, Sheets and Slides. And once you get back online, your changes will sync up.
Therefore, this trick is useful when you want to remain productive even when you are not at the office or at home. To achieve this trick, you just have to go to the setting gear icon in the top-right corner, select “Settings”, scroll down a little, and turn on offline mode.
Trick #11: Format The Text in Cells using Formulas
When you copy some content from a webpage or other location, chances are it will be poorly formatted when you paste on your spreadsheet.
To fix this very usual issue, you can use Google Sheets’ UPPER, LOWER, and TRIM features to tidy up your text. With the help of these tools, you can change the case of your data, get rid of superfluous spaces, and more.
To transform data to uppercase using the UPPER Function, you can use this formula: UPPER(text), where “text” – the string to convert to uppercase.
To automatically change all the Uppercase text to lowercase, use the LOWER Function using this formula: LOWER(text), where “text” – the string to convert to lowercase.
Lastly, you can use the TRIM function to remove those extra spaces within the data without manually doing it. The formula: TRIM(text), where “text” – the string or reference to a cell containing a string to be trimmed.
Trick #12: Convert PDF to Text Using FileDrop
Although manual PDF data extraction is a laborious operation, there are approaches to overcoming it. For starters, you can use Google Docs to Convert PDF to Google Sheets or you can also convert PDF to CSV and then open it with Google Sheets.
However there is a more efficient way to convert PDF to text. It is by using a Google Sheet add-on called FileDrop. Using this will allow you to easily convert PDF files to text directly from Google Sheets. This method is extremely easy to use and does not require any extra software downloads. All you need is to include a Google Sheet add-on and a PDF file in order to convert it into a Google Sheets file.
Learn here how you can batch ocr PDFs with Google Sheets and Drive.
Using this tool, you can extract texts from PDF files by uploading them or selecting them from your computer. You just have to go over the file in FileDrop and select the TXT icon over it and FileDrop will convert the PDF in text.
To learn more, here is a tutorial that provides in-depth information about this: How to Convert PDF to Google Sheets
Trick #13: Collaborate Using Sharing Option
Google Sheets has evolved into a crucial tool for collaborating and maintaining synced, up-to-date files for many teams and businesses. Therefore, it’s best to learn how to share your files and folders appropriately if you’re just starting off.
To do this, you simply click on the “Share” button, then “Get shareable link”. If you wish to give edit access to your team, click on the drop-down menu to change it to “Anyone with the link can edit”. Then copy this link and share it with all your colleagues.
Trick #14: Export Chart as Image
When using Google Sheets, you may want to download the charts and graphs as images for your reports, presentations, or for other places. While you can technically do this with your computer’s screenshot function, it isn’t the most elegant solution. Google Sheets natively allows you to download your charts as either a PNG or SVG image. This method is the most straightforward and easiest to execute.
To do this, click on the chart you want to export. You should see a three-dot icon on the upper right corner. This prompts the dropdown menu which includes Download.
Then you can now download charts as PNG or SVG format images. You should take note that most viewing applications accept the PNG format, thus most users should have no problems utilizing it.
To learn more about this trick, here is an in-depth tutorial: Google Sheets Export Chart as Image
Trick #15: View Google Sheets File History
Google Sheets has a feature called Version History that allows users to revisit older versions of a document. As you work on your document, Google automatically saves copies of your documents called versions. Your older edits reside on these backups from where you can start over when things go wrong or when you are branching off to a different project.
When checking your data and updating your information, this can save you a ton of time. You can go back and retrieve lost data if you or another team member makes a mistake, such as accidentally deleting crucial tables, saving you from having to start over from scratch.
To do this, you can navigate to Version history. Go to File > Version history > See version history. If this comes up empty or if you couldn’t find older edits, check out the previous section entitled Conditions to View Google Sheets File Version History. Then click on See version history to view older edits sorted by date and time.
To learn more about this topic, read our article dedicated to explain it: How to View Google Sheets File History
Trick #16: Link Sheets in Google Sheets
Occasionally, every Google Sheets user needs to reference data from another sheet, or even a spreadsheet, to create a combined master view. Doing this will simplify the process of consolidating information from multiple worksheets into one.
The simplest method is to link cells from the current sheet cells from the current sheet to another tab in Google Sheets. This is applicable if you wish to reference data from the current and other sheets.
Achieving this method requires following the steps below:
- Open a sheet in Google Sheets.
- Place your cursor in the cell where you want the referenced data to show up.
- Use this formula if you want to link data from the current sheet: ={A1:A3}
- Where A1:A3 is the range of cells from your current active sheet. Use curly brackets for this argument.
- Use this formula if you want to link to another tab in Google Sheets: ={Sheet1!A1:A3}
- Sheet1 is the name of your referenced sheet and A1:A3 is a specified range of cells that you want to import data from. Use curly brackets for this argument.
To learn other methods and in-depth tutorials about linking sheets, you can check out this article: How to Link Sheets in Google Sheets
Trick #17: Create a Checklist by Adding a Checkbox
For this next trick, you can use Google’s new checkbox feature to spruce up your to-do lists if you’re using Google Sheets to plan a project.
You just have to click on “Insert”, then “Checkbox”, and you’re all set!
Trick #18: Clone Formatting without Manual Copy-Paste
When using Google Sheets for your workflow, there would be times when you have to copy and paste data and need to clone format. The good news is that you can do it in a few simple steps.
Your toolbar includes a format painter icon that lets you clone your formatting. You just have to highlight the cells that you want to copy the format from, then click the above icon, and drag your mouse over the cells that you want to apply the formatting to.
Trick #19: Analyze the Data using Filters
The “Create A Filter” function is found under the “Data” tab, but few people take advantage of it to its full potential.
To learn about this trick of creating a filter, simply click on “Create A Filter”. This allows you to:
- Sort your data from A to Z
- Sort your data from Z to A
- Filter your data by condition, and
- Filter your data by values
Take note that after creating a filter, you’ll still be able to see all your data once you turn the filter off. There’s “Filter views” you can utilize that allows you to:
- Save multiple views, and access them later
- Name each view
- Share different filters with people
- To get started, click on “Data”, “Filter views”, and “Create new filter view”.
Then you can now arrange and refine your data. When finished, close and save your filter view by clicking the close button in the top right corner. By selecting “Duplicate” from the options menu in the top right corner, you can remove your filter view.
Trick #20: Transfer Ownership of File
Whether you are working through a personal account, an organization, or a school account, there may be instances when you need to completely transfer a file to someone else, such as when you leave an organization, team, or university account.
To do this, you just have to share the file. Before someone can be invited to become the new owner of a file, you must first share it with them. On Google Drive, right click on the file you’d like to transfer and press “Share”.
This opens the Sharing menu. On the box provided, enter the email address of the user you wish to transfer ownership to and click Share. To verify that you’ve given access to the file, you can check the sidebar under Who has access. If the user has successfully been added, their icon should appear here.
Read this guide for a more in-depth explanation on this topic: How to Transfer Ownership of File in Google Drive
Trick #21: Add Special Characters Into Spreadsheets
Special characters and symbols are occasionally required, ranging from copyright symbols to simple checkmarks. You might be shocked to learn that there isn’t a way to add a unique character or symbol to the menu in Google Sheets.
Alt code works in Google Sheets but you need to be in edit mode in the cell before you press the Alt key. Check below steps on how to use this:
- Select the cell where you want to place the symbol.
- Press F2 to enter edit mode.
- Press ALT plus an appropriate number to enter the desired symbol in the cell – for example, Alt 30 to enter a delta symbol (▲).
Moreover, if you want to insert symbols into Google Sheets, open a Google Doc and copy and paste them from that document into the Google spreadsheet. Check the following instructions.
- From the File menu, choose New > Document. A new tab will open in the browser, displaying a new Google document.
- From the Insert menu, choose Special Characters. In the left drop-down box, you can choose from various categories, such as icon, emoji, punctuation, etc.
- Select Symbol in the left field and Currency in the right field.
- Click the symbol you selected to insert it into the Google document.
- Click the X in the upper right corner to close the Insert Special Character box.
- Highlight the icon with your mouse, and then press CTRL + C to copy the symbol.
- Switch back to the Google sheet and press CTRL + V to paste the symbol into a cell.
Trick #22: Create a QR in Google Sheets
Using a QR code on a mobile device is a convenient way to capture information. You can visit a website by scanning a code to receive contact information. Here’s how to create a QR code in Google Sheets to make sharing simple. You or your audience can scan a QR code that has been created and added to Google Sheets to obtain the additional data they require.
Google offers a link you can insert into the formula for the Image function to create the QR code. Despite the fact that it sounds complicated, it really isn’t that hard. The code can be used to link to a website, contact information, and text.
To start, you just have to select the cell where you want to insert the QR code. Here is a basic formula linking to a website in cell A1.
=image(“https://image-charts.com/chart?chs=150×150&cht=qr&choe=UTF-8&chl=”&ENCODEURL(A2))
With this code, anything you type into the A2 cell of the document, whether it be a URL or otherwise, will appear as a QR code. It’s as easy as that to convert a link to QR code. From here, you can scan your freshly created QR code to any URL listed in your URL column. Remember that your URL column in this instance is not limited to links as well. Any alphanumeric plain text entered into the A2 cell will function in this way.
Bonus Trick: Use FileDrop Bot
Powered by ChatGPT, the FileDrop Bot will answer any question about Google Sheets or give you tips and tricks that might help you increase your productivity. Give it a go and you will not be disappointed. Also view our Google Sheet formula generator, powered by AI as well.
Conclusion on Productivity Tricks That Will Make Using Google Sheets Easier
Google Sheets can be intimidating to those who are new to using spreadsheets. However, the tips and tricks mentioned above, such as keyboard shortcuts, add-ons, and other hacks can make your work faster, easier, and more efficient, especially the more you practice them.
Whether you’re tech-savvy or not, there’s always a way or two this user-friendly Google Sheets add-on can streamline your workflows and processes. Find out how FileDrop can improve your productivity in Google Sheets with a trial today!
