20 Awesome Google Sheets (Spreadsheet) Tips & Tricks

20 Awesome Google Sheets (Spreadsheet) Tips & Tricks

20 Awesome Google Sheets (Spreadsheet) Tips & Tricks

20 Awesome Google Sheets (Spreadsheet) Tips & Tricks

20 Awesome Google Sheets (Spreadsheet) Tips & Tricks
20 Awesome Google Sheets (Spreadsheet) Tips & Tricks
20 Awesome Google Sheets (Spreadsheet) Tips & Tricks 20 Awesome Google Sheets (Spreadsheet) Tips & Tricks 20 Awesome Google Sheets (Spreadsheet) Tips & Tricks 20 Awesome Google Sheets (Spreadsheet) Tips & Tricks 20 Awesome Google Sheets (Spreadsheet) Tips & Tricks

20 Awesome Google Sheets (Spreadsheet) Tips & Tricks

When you get started with a new app like Google Sheets, you want to know all of the tips and tricks that you can use to get the most from it. The faster you learn the spreadsheet tricks, the more efficient you’ll be in that app.

Spreadsheets have plenty of power no matter how you use them. They’re easy to get started with, but there’s plenty more power for working with data as you learn more functions and features of your chosen spreadsheet app.

In this tutorial, we’re going to dive into 20 Google Sheets tips that are sure to save you time and help you use spreadsheets in ways that you haven’t thought about before.

Google Sheets 20 tips and tricks

1. Add Images Inside Sheets

Who says that Google spreadsheets have to be just rows and columns of data? You can add images to a spreadsheet for a bit of creativity or style.

To use this feature, try out the =IMAGE function. Just drop in the URL of the image that you want to use in your sheet and close it out to add it right inside of the spreadsheet.

How to use IMAGE in Google Sheets

2. Grab Data from Other Google Sheets

One of the most common things that spreadsheet users will do is connect different spreadsheet 

The function that you’ll want to try out is called IMPORTRANGE. With the IMPORTRANGE function, you can actually connect to an entirely different Google Sheet and pull data over. Best of all, it stays up to date.

To use this function, try out the syntax below:

=IMPORTRANGE("Url of Sheet to link to", "Sheet and cell references")

Here’s an example of a finished function to pull data using IMPORTRANGE:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1xrGsOD-yXuORqd8cFg21XOo3ZIw9QbSiNDcnSEatlPM/edit#gid=0", "Sheet1!A:A")

3. Learn Formulas inside the App

My favorite tools help you tackle the learning curve while also using the app, and Sheets is no slouch in this department. You can start typing a function and Sheets will show the syntax usage for that specific function, along with Google Sheets tips on how to use it.

To use this feature, simply start typing a formula by typing an equals sign into a cell, then the formula name. After you open the parenthesis, you can see a popover box that’ll teach you how to use it.

Formula showing in VLOOKUP
Just start typing a function and leave it open to learn it right inside of Sheets.

4. Connect Sheets to Other Services

Because Sheets lives in a web browser and is connected by default, it makes sense to connect it to other web-based services. Imagine being able to grab data from other sources like Twitter, Instagram, and more.

You can do just that thanks to connectivity services like IFTTT and Zapier. Instead of downloading data from those services and reformatting it for Sheets, you can use these services to sit in the middle and automatically share data between the services.

See an example of this in action with the tutorial below, using IFTTT for a variety of connections between Sheets and the services.

5. Lock Cells to Prevent Unwanted Changes

Sheets is excellent for collaboration, as you can easily invite other users to work inside of the same spreadsheet. But, you may still need to take steps to ensure that 

You can get started with this feature by going to the Data > Protected sheets and ranges menu option. A menu will open up the right side that allows you to simply click and drag over the data that needs to be protected.

Protected sheets and ranges
Manage your protected data from the Data > Protected sheets and ranges menu option.

See the complete guide to managing the integrity of your spreadsheets with the tutorial below:

6. Clean Up Text with Functions

Have you ever had messy text, like all uppercase or lower text that needs a bit of clean-up? Sheets has functions that help you do that without having to manually retype the data.

To use this Google spreadsheet tip, try out some of the following functions on your data:

  • UPPER – transforms all data to uppercase
  • LOWER – transforms all data to lowercase
  • TRIM – removes leading and trailing spaces in data

7. Filter Spreadsheets to Find Specific Data

Filtering can help you refine what’s actively showing in the spreadsheet, and it’s a real life-saver when you’ve got huge datasets to work through.

Just hit the filter button (shown inset below) to add filter buttons to your table. Then, click on the filter button on the column and refine what’s showing.

Filter Data in Google Sheets
Click on the Filter icon on the toolbar to start filtering data in a table.

8. Use AI to Create Charts and Reports

Sometimes, Google Sheets is smarter than this user. That’s thanks to Google’s increasing use of artificial intelligence (AI) to automatically create reports and charts. Instead of creating those from scratch, you can simply type plain-English instructions for generating charts and reports.

To use this in your own Google spreadsheets, click on the Explore button in the lower right corner in Sheets. Then, just look at the built-in options or try typing out a chart you’ve got in mind to generate it easily.

Use AI to Create Charts and Reports
By simply highlighting the data and clicking on “Explore”, Sheets has many ideas for important trends in your data.

Make sure to check out the quick screencast below to see this tip in action:

9. Try Keyboard Shortcuts for Common Tasks

Experts know that one of the most important parts of Google Sheets is learning the secrets for navigating it. I always say that anything that can be done with the mouse can be done more quickly with a keyboard.

Check out the full tutorial below that has some of the best tips and tricks for Google Sheets shortcuts:

10. Convert Excel Files to Sheets

This Google Sheet tip might be perfect if you’re wondering how you can get started with converting your files from Microsoft Excel format. Even if you’re starting with .XLSX spreadsheet files, you can actually convert them to Sheets format and start using them right away.

This process is pretty easy and only requires some dragging and dropping. Check out the quick tutorial below to convert Excel spreadsheets to Sheets:

11. Translate Text Without Any Other Apps

Connecting to Google’s Translate service is really easy. In fact, you can translate text right inside of a spreadsheet. Here’s an example of using the GOOGLETRANSLATE function from French to English:

=GOOGLETRANSLATE("je ne sais quoi","fr","en")

You can use this instead of copying and pasting the text back and forth from the little box on Google Translate. Just point this function to a cell with typed text instead.

12. Use Macros to Take on Repetitive Tasks

macro captures repeated tasks and makes it easy to repeat them. If you do the same steps over and over, you’ve got to try this feature out. 

To get started, go up to the Tools menu and choose Macros > Record Macro. Then, go through the steps that you’ll want to repeat later and save it. 

Whether that’s applying formatting or rearranging columns in the same way each time, this option is really helpful to save time. Just play back the macro from this Tools menu to repeat the task.

Use Macros in Sheets
Use the Tools > Macros menu to record and run macros in your spreadsheet.

13. Invite Collaborators to Your Spreadsheet

You can add others to work on your spreadsheet with just a few clicks. This includes some really impressive features like actually watching others work in the spreadsheet in real-time.

To get started, just click on the Share button in the upper right corner of the Sheets app. Then, use the rest of this tutorial to make sure that you control who accesses the spreadsheet carefully:

14. Print Your Spreadsheets Easily 

Spreadsheets don’t always live exclusively onscreen. Sometimes, you’ll want to print a finished spreadsheet to hand off for review and analysis. 

Spreadsheets are also notoriously hard to print. Because you’ve got a basically limitless canvas of rows and columns, it sometimes takes work to print them on a page that’s limited in size.

Check out the guide below to make sure that you print your spreadsheets correctly the first time:

15. Clone Formatting Easily in Two Clicks

The best Google Sheets tips are the ones that save you serious time. That’s why I use the Format Painter to take cells that are already formatted and paint those same styles into other cells.

Start off by highlighting the cells that you want to copy the format from. Then, click on the format painter and simply drag over the cells that you want to apply the formatting to.

Format Painter example
Highlight the cells you want to copy the format from, then click on the format painter and drag over the cells to format.

16. Use Pivots to Analyze Data Easily

If you’re a power user of Microsoft Excel, this Google Sheets tip is really going to help you make the switch. I love Pivot Tables for their ability to take a big table of data and help you find meaning in them. Basically, think of pivots as a drag and drop tool to build a report.

Pivots take some time to learn and get started with, so make sure to walk through the guide below to find meaning in even the largest datasets:

17. Clean Up Data with Formatting

Formatting brings meaning to data. The larger the spreadsheet, the more that it’ll benefit from some formatting that’ll make it easier to read. Using features like alignment, borders, and font styles will help your audience find meaning simply by scanning the spreadsheet visually.

One of my favorite resources for learning formatting is Sheets is linked below, and I’d highly recommend checking it out. You’ll learn to not only use the technical tools and features to format Google spreadsheets, but also how they should be used to bring meaning to your data.

18. Get Input From a Form Into Sheets

A spreadsheet is a great way to capture and structure data, but it can take time to add data to it. It’s better if you can use a tool like a form to get data from others in a structured format.

Luckily, Google has a form builder that automatically takes input and puts it into a spreadsheet. Any response you receive will be added to your Google Sheet.

Walk through this process using the tutorial below:

19. Grab Up-to-Date Financial Data

Here’s a Google Sheets tip that I find saves me many hours of manually downloading and typing data. If you’re tracking stock and financial market data, using the built-in GOOGLEFINANCE function fetches up-to-date data from Google’s hub.

Here’s an example of using the function to grab Amazon’s stock price from February 7th:

=GOOGLEFINANCE("AMZN","price",date(2017,2,7))

Also, be sure to check out the complete guide to this function for all of the tips and tricks for Google Sheets and Google Finance working together:

20. Protect Entire Spreadsheets from Edits

Not only can you protect specific cells or ranges, you can add protection to entire tabs in the spreadsheet. This is a great idea if you’ve got a single sheet of inputs or variables that you don’t want the public to change. Consider using this tip when you’re sharing your spreadsheet.

Remember to check out the tutorial I shared above in this post on Sheets protection, skipping straight ahead to the information on protecting entire sheets.

Recap & Keep Learning

I’ve linked to plenty of other learning resources in this tutorial, but there’s still plenty more to learn about Google’s cloud-based spreadsheet tool. If you need something more basic or foundational to get started, consider checking out any of the following tutorials:

What are your favorite tips and tricks for Google Sheets? Is it one that I mentioned, or did I miss something that you use often? Let me know with a comment below this tutorial.