Are you new to Google Sheets? Sheets is an intuitive and accessible piece of online spreadsheet software. Free to use, you can create and edit spreadsheets directly in your web browser without installing anything. It’s ideal for collaborating in real-time too: multiple people can work in the same file simultaneously, meaning you can see people’s changes as they’re made and everything is saved automatically. Working with Google Sheets can save your company or team a lot of time. To help you maximize the potential of Google Sheets, we’ve pulled together a selection of 10 spreadsheet tips and tricks.
Tip 1. Freeze rows and columns
Let’s start with one of the most useful spreadsheet tips and tricks: freezing rows and columns. How can freezing rows and columns help you? When you’re working with large volumes of data it’s easy to get lost. This is a problem when you can’t see the column name in the header row, for example.
The freeze function solves this problem by pinning the data to the same place so you can always see it on your screen while you navigate the spreadsheet. You no longer need to scroll up to read which column you’re in!
- Select a cell in the column or row you want to freeze.
- Go to the View menu.
- Select and option.
- When you scroll, you’ll notice the frozen row(s) or column(s) are visible.
Tip 2. Use conditional and alternative formatting
Conditional formatting
Conditional formatting can help you to track the evolution of metrics like KPIs, with colored cells. You can make a cell, row, or column change color if the data meets certain conditions, e.g., if it contains a certain word or a number. This makes your spreadsheet dynamic and the data easier to read. For example, you can format your spreadsheet so that poor performance, low grades, or minus figures are automatically highlighted in red.
- Open a spreadsheet in Google Sheets.
- Select the cells you want to apply conditional formatting to.
- Click Format > Conditional formatting.
- In the pop-up menu on the right, create a rule.
- Single color: Under Format cells if, choose the condition. Under Formatting style, choose the color and style of the cell or text.
- Color scale: Under Preview, select the color scale. Set a minimum and maximum value, and an optional midpoint value.
- Click Done.
Alternating colors
This is another big timesaver. If you want your rows to have alternating colors in Google Sheets (which can make your spreadsheet easier to read) instead of coloring the cells manually, you can do it in two clicks. Hit the Format option from the menu at the top and then click Alternating colors. Choose a default template or choose custom colors for the header and alternating row colors.
Tip: If you want to color every third or fourth row, use conditional formatting with a formula. Go to Custom formatting > Format cells if > Custom formula is.
Tip 3. Use add-ons
Add-ons run inside Google Sheets, Docs, and Forms. They’re small programs built by developers that enable you to do more with the spreadsheet or file. You can add menu items and sidebars, edit files, and connect to over a dozen Google services instantly. From inside Google Sheets, click add-ons in the menu bar, and then search. You will find add-ons to connect spreadsheets, import data from multiple sources, schedule mailings and more.
Tip 4. Use a template
Templates are one of the best ways to save time when you and your colleagues are working in G Suite. Recreating a report or newsletter layout every time you start a new one is unproductive. Google offers many time-saving templates that let you get on with using the documents, rather than struggling to put them together.
- Open the homescreen inside Google Sheets, Slides, or Forms.
- Choose a template.
- To see more options, click Template gallery.
You can also access templates when you’re inside a file. Click File >New > From template.
Tip 5. Learn how to use comments and notes
Google Sheets allows you to insert a Note or a Comment into a cell. Use the Note function to leave a simple note in the cell just like you would with Excel’s comment function. With Note, you can simply add information about the cell. Notes can be helpful but beware of adding unnecessary complexity to the spreadsheet. Keep in mind that users often overlook notes, so they’re usually best if it’s only you who will be using and viewing the spreadsheet.
However, if you have multiple users, especially working in different locations, the Comment function can be very helpful. It enables users to have a back and forth conversation about a cell without altering its contents. The Comment function is particularly useful for editors. It helps them to work and improve their spreadsheets with their colleagues.
Tip 6. Use Google Forms with Google Sheets
Google Forms help you plan events, conduct a survey or poll, give students a quiz, or collect other information in an easy, streamlined way. You can create a form from Google Drive or from an existing spreadsheet that can record the responses to your form.
Read our guide on how to connect Google Forms and Google Sheets.
Tip 7. Check the version history
Users with edit access to a file can view the full history of the Google Sheet. This includes changes made by colleagues, via the revision history feature. G Suite’s editing tools (Docs, Sheets, Slides, Drawings) automatically save all changes as you work in real time. You can revert to earlier versions of the file(s) and see which person made specific edits.
- Open a Google Sheet.
- Go to File > version history.
- You can also click on the All changes saved in Drive text (or Last edit was X minutes ago) in the menu bar.
- Note the Show more detailed revisions in the lower right corner of the revision history panel.
- Select a timestamp to show a previous version and who made the edits.
- Text that has been added will be highlighted in a different color.
- Text that has been deleted will be shown with a strikethrough.
- To revert the file to the version you’re currently viewing, click Restore this version.
- To return to the current version of your file, click the back arrow in the top left.
In the version history also allows you to make copies of files from certain points in time. If you need to regularly preserve different versions of your spreadsheet, check our our guide on how to automatically track changes in Google Sheets to create a historical data log.
Tip 8. Find and replace
Manually finding and replacing parts of the text can be exceedingly tedious for big spreadsheets. With the Find and replace function, you can quickly make revisions. Here’s how to do it:
- Open a spreadsheet in Google Sheets.
- Click Edit > Find and replace.
- Next to Find, type the word. If you want to replace the word, enter the new word next to Replace with.
- To search for the word, click Find. To see the next time the word is used, click Find again.
- Optional: Narrow your search by using an option below.
- Match case: Makes your search case-sensitive.
- Match entire cell contents: Searches for cells that are an exact match.
- Search using regular expressions: Searches for cells that match a pattern.
- Also search within formulas: Searches include formulas.
- To replace the highlighted word, click Replace. To replace the word every time it’s used, click Replace all.
Tip 9. Create filters
There are several ways to filter data in Google Sheets. With the FILTER formula, filters and the filter view function.
Sort your data numerically or alphabetically
- Select the cells you want to sort.
- Click data > Sort range.
Apply a filter to the spreadsheet
This option lets you hide data in the spreadsheet. Note that anyone who has access to the spreadsheet will see this filtered version of the data. Those with edit access can change the filters.
- Open a spreadsheet in Google Sheets.
- Select a range of cells.
- Click Data > Create a filter.
- Click the Filter icon at the top of the range to choose from a list of conditions.
- To turn the filter off, click Data > Turn off filter.
Create filter views in a Google Sheet
This option is better when you have multiple users who need to see different data in the spreadsheet. You can save and name different filter views, allowing different team members to view their own filtered data in one click.
- Go to Data > Filter views > Create new filter view.
- Sort and filter the data.
- When you click Close the filter view will be saved.
Learn more about the different types of filters and how to filter Google Sheets without affecting other users.
Tip 10. Protected sheets and ranges
Google Sheets is great for collaborating but sometimes you might want to lock part of the spreadsheet to prevent others from making edits. You can do this with named and protected ranges. If you want to learn more about how this works, check out Google’s Developer documentation on spreadsheet.get requests.
Note that protecting a range or a tab(sheet) stops others from being able to make changes to that part of the spreadsheet but it does not hide the data from view.
- Go to Data > Protected sheets and ranges.
- Click Add a sheet or range.
Get detailed step-by-step instructions here.
To check which areas of the spreadsheet are locked, click View > Protected ranges. A striped background appears over those cells.
To protect and control who can access specific data, try using separate, but connected Google Sheets.
Do more with spreadsheets
Start improving the way you and your team work in spreadsheets with these 10 spreadsheet tips and tricks. Want to do more with Google Sheets? Check out our guide on how to filter one Google Sheet to another.
