A lot of people have already chosen to work with Google Sheets. One of the reasons for this change, is that with Google Sheets, you can create and edit spreadsheets directly in your web browser—no special software is required. Multiple people can work simultaneously, you can see people’s changes as they make them, and every change is saved automatically.
As you can imagine, working with Google sheets can save your company or team a lot of time. In order to make sure that you use the right tools, we have chosen to write down some tips and tricks. In this article, we have made a selection of 10 tips and tricks that can help you to maximize your results in google sheets.
Tip 1. Freezing columns and row
How can freezing rows and columns help you? When you are working with much data its possible to get lost. This can happen if you don’t know the name, of each column. This function solves this problem, as you can freeze up to ten rows or five columns in any particular sheet in Google spreadsheets.
- Go to the View menu.
- Then, point your mouse to Freeze rows… or Freeze columns….
- Select one of the options. When you scroll, you’ll notice the frozen row(s) or column(s).
Tip 2. Conditional and alternative formatting
Conditional formatting can help you, to see the evolution of KPIs, cashflow, etc. Cells, rows, or columns can be formatted to change text or background color if they meet certain conditions. For instance, if they contain a certain word or a number.
- On your computer, open a spreadsheet in Google Sheets.
- Select the cells you want to apply format rules to.
- Click Format Conditional formatting. A toolbar will open to the right.
- Create a rule.
- Single color: Under “Format cells if,” choose the condition that you want to trigger the rule. Under “Formatting style, choose what the cell will look like when conditions are met.
- Color scale: Under “Preview,” select the color scale. Then, choose a minimum and maximum value, and an optional midpoint value. To choose the value category, click the Down arrow .
- Click Done.
Alternative formatting is a function that helps you, to make a color format template, quickly. First, hit the Format option from the menu at the top and then click Alternative formatting. Once you do, you will see the below screen with a few options available to you.
From here, select the range which you will need to work on. There will be alternate rows filled with the specified color that you’ve chosen and changing the color itself is as easy as clicking on the icon.
Tip 3. Add-ons
Add-ons runs inside Google Sheets, Docs, and Forms, and they’re built by developers. The platform of Google lets you add menu items and sidebars, edit files programmatically, and tie into over a dozen Google services without even spinning up a server. This function integrates seamlessly into Google Sheets and extend its functionality. If you are running Google Sheets, click on add-ons, and then search for companies. The Sheets Add-ons store offers over 250 add-ons. With these Add-ons you can connect spreadsheets, import data from a lot of sources, do mailings etc.
Tip 4. Use of templates
While a lot of businesses may not use templates much in their G Suite, it is actually a necessary feature for them. After all, it costs a lot of time to have to recreate a report or newsletter layout every time you start a new one. Google offers 24 time-saving templates that’ll let you get on with using the documents, rather than struggling with putting them together in the first place.
- Open a Google Docs, Sheets, Slides, or Forms home screen.
- Click a template to open it.
- To see more options, click TEMPLATE GALLERY.
You can also use templates when you’re in a file. Click File New From template.
Tip 5. Add comments and notes and the difference between them
Google Sheets has recently enhanced its cell commenting ability to include the option of inserting a “Note” or a “Comment.” The differences between the two are not obvious.
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. However, if they are not needed, they can add unneeded complexity. Often, users will overlook notes and only notice information that is typed into cells, so use them only when called for. Notes are usually sufficient if one user is creating and viewing a spreadsheet.
However, if you have multiple users, especially in different physical locations, the Comment functionality can be very helpful. It enables users to have a back and forth conversation about a cell without altering its contents. The Comment functionality is especially useful for editors. This as it helps them to work and improve their sheets with their colleagues.
Tip 6. Usage with Google forms
When using Google forms it is possible to plan events, make 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.
- Go to your Google Docs home page
- In the top left corner click on the red “NEW” button
- Click on “more” and then “Google Forms”
Tip 7. Revision story
Users with edit access to a file can view the full history of their documents. This includes changes made by colleagues, via the revision history feature. When you work in Google’s editors (Docs, Sheets, Slides, Drawings) they automatically and constantly save all changes as you work. You can revert to earlier versions of the file(s) as well and see which person made specific edits.
- Open a file (Docs, Sheets, Slides, Drawings)
- Click File and then See revision history.
- You can alternatively click on the All changes saved in Drive text above the features menu.
- In the Revision history panel to the right hand side you can see previous versions of the file.
- 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.
- The edits that each person made are shown in the colour that appears next to their name.
- Text that has been added will be highlighted in a different colour.
- 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 revision.
- To return to the current version of your file, click the back arrow in the top left.
Tip 8. Find a replace
Manually finding and replacing parts of text can be exceedingly tedious for lengthy Excel spreadsheets. With the function Find and replace, you can quickly make revisions.
- On your computer, open a spreadsheet in Google Sheets.
- Click Edit Find and replace.
- Next to “Find,” type the word you want to find, 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
With the Filter function, you simply type your filter equation into the top cell of your summary table. Google Sheets will fill in the cells with all the values that meet your criteria.You can add, delete, and edit the source data, and the filter function will automatically update. Filtering your data can be extremely useful when looking for certain amounts of data.
- Open a spreadsheet in Google Sheets.
- Select a range of cells.
- Click Data Filter.
- To see filter options, go to the top of the range and click Filter. Filter by condition: Choose from a list of conditions or write your own. …
- To turn the filter off, click Data Turn off filter.
Tip 10. Protected sheets and ranges
The Sheets API allows you to create, control and delete named and protected ranges. In addition, named and protected ranges have their own IDs. These IDs are used to identify the ranges when making requests to update or delete them. The ID is returned in the response to an API request that creates a named or protected range. They can also be obtained from a spreadsheets.get request. Shown in the Spreadsheet response body.
- Open a spreadsheet in Google Sheets.
- Click Data Protected sheets and ranges. A box will open on the right.
- Click Add a sheet or range or click an existing protection to edit it.
- To protect a range, click Range. To protect a sheet, click Sheet.
- Range: To change or enter the range you’re protecting, click the spreadsheet icon and highlight the range in the spreadsheet.
- Sheet: Choose a sheet to protect. If you want a set of cells to be unprotected in a sheet, check the box next to “Except certain cells.”
- Click Set permissions or Change permissions.
- Choose how you want to limit editing:
- To show a warning when anyone makes an edit: Select “Show a warning when editing this range.” It doesn’t block people from editing, but they’ll see a message asking them to confirm if they really want to make an edit.
- To choose who can edit the range or sheet: select “Restrict who can edit this range.” Choose:
- Only you: Only you (and the owner if you’re not the owner) can edit the range or sheet.
- Only domain: If you use Google Sheets for work or school, only people in your domain can edit the range or sheet. This option is only available when everyone in your domain can edit the spreadsheet.
- Custom: Only the people you choose can edit the range or sheet.
- Copy permissions from another range: Reuse the same permissions you set up on a different set of cells or sheet.
- Click Save or Done.
To see protected cells, click View Protected ranges. A striped background will appear over the cells.