Check out this round-up of 20 of the best Google Sheets tips to save you time and help you use Google spreadsheets easily.
Advertisement Advertisement Advertisement Advertisement Advertisement May 17, 2018 • 11 min read This post is part of a series called How to Use Google Sheets (Essential Tutorial Guide).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.
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.
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:
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.
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.
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.
See the complete guide to managing the integrity of your spreadsheets with the tutorial below:
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:
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.
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.
Make sure to check out the quick screencast below to see this tip in action:
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:
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:
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.
A 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.
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:
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:
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.
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:
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.
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:
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:
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:
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.
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.
