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!<\/p>\n
- \n
- Select a cell in the column or row you want to freeze.<\/li>\n
- Go to the View<\/strong> menu.<\/li>\n
- Select and option.<\/li>\n
- When you scroll, you\u2019ll notice the frozen row(s) or column(s) are visible.<\/li>\n<\/ul>[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/spreadsheet-tips-and-tricks-1.png” alt=”spreadsheet tips and tricks 1″ title_text=”spreadsheet tips and tricks 1″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]
Tip 2. Use conditional and alternative formatting<\/h3>\n
Conditional formatting<\/h4>\n
Conditional formatting<\/a>\u00a0<\/span>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.<\/p>\n
- \n
- Open a spreadsheet in\u00a0<\/span>Google Sheets<\/a>.<\/li>\n
- Select the cells you want to apply conditional formatting to.<\/li>\n
- Click Format<\/strong> > Conditional formatting<\/strong>.<\/li>\n
- In the pop-up menu on the right, create a rule.\n
- \n
- Single color: Under Format cells if<\/strong>, choose the condition. Under Formatting style<\/strong>, choose the color and style of the cell or text.<\/li>\n
- Color scale: Under Preview<\/strong>, select the color scale. Set a minimum and maximum value, and an optional midpoint value.<\/li>\n<\/ul>\n<\/li>\n
- Click Done<\/strong>.<\/li>\n<\/ol>\n
Alternating colors<\/h4>\n
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\u00a0Format<\/strong>\u00a0option from\u00a0the menu at the top and then click\u00a0Alternating colors<\/strong>. Choose a default template or choose custom colors for the header and alternating row colors.<\/p>[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/spreadsheet-tips-and-tricks-2.png” alt=”spreadsheet tips and tricks 2″ title_text=”spreadsheet tips and tricks 2″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]
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.<\/p>\n
Tip 3. Use add-ons<\/h3>\n
Add-ons run inside Google Sheets, Docs, and Forms. They\u2019re 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.<\/p>[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/spreadsheet-tips-and-tricks-3.png” alt=”spreadsheet tips and tricks 3″ title_text=”spreadsheet tips and tricks 3″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]
Tip 4. Use a template<\/h3>\n
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\u00a0<\/em>using\u00a0the documents, rather than struggling to put them together.<\/p>\n
- \n
- Open the homescreen inside Google\u00a0Sheets<\/a>,\u00a0Slides<\/a>, or\u00a0Forms.<\/a><\/li>\n
- Choose a template.<\/li>\n
- To see more options, click\u00a0Template gallery<\/strong>.<\/li>\n<\/ol>\n
You can also access templates when you\u2019re inside a file. Click\u00a0File<\/strong>\u00a0>New\u00a0<\/strong>>\u00a0From template<\/strong>.<\/p>\n
Tip 5. Learn how to use comments and notes<\/h3>\n
Google Sheets allows you to insert a\u00a0<\/span>Note or a Comment\u00a0<\/a>into a cell. Use the Note function to leave a simple note in the cell just like you would with Excel\u2019s 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.<\/p>[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/spreadsheet-tips-and-tricks-4.png” alt=”spreadsheet tips and tricks 4″ title_text=”spreadsheet tips and tricks 4″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]
However, if you have multiple users, especially working in different locations, the Comment<\/strong> function can be very helpful. \u00a0It 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.<\/p>\n
Tip 6. Use Google Forms with Google Sheets<\/h3>\n
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.<\/p>\n
Read our guide on how to connect Google Forms and Google Sheets<\/a>.<\/p>[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/spreadsheet-tips-and-tricks-5.png” alt=”spreadsheet tips and tricks 5″ title_text=”spreadsheet tips and tricks 5″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]
Tip 7. Check the version history<\/h3>\n
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<\/strong>. G Suite’s editing tools (Docs, Sheets, Slides, Drawings) automatically save all changes as you work in real time. You<\/span>\u00a0can revert to earlier versions of the file(s) and see which person made specific edits.<\/span><\/p>\n
- \n
- Open a Google Sheet.<\/span><\/li>\n
- Go to\u00a0<\/strong>File<\/strong> ><\/span>\u00a0version history<\/strong>.<\/span><\/li>\n
- You can also click on the\u00a0All changes saved in Drive<\/span><\/em>\u00a0text (or Last edit was X minutes ago<\/em>) in the menu bar.<\/span><\/li>\n
- Note the\u00a0Show more detailed revisions<\/span>\u00a0in the lower right corner of the revision history panel.<\/span><\/li>\n
- Select a timestamp to show a previous version and who made the edits.<\/span><\/li>\n
- Text that has been added will be highlighted in a different color.<\/span><\/li>\n
- Text that has been deleted will be shown with a strikethrough.<\/span><\/li>\n
- To revert the file to the version you\u2019re currently viewing, click\u00a0Restore this version<\/strong>.<\/span><\/li>\n
- To return to the current version of your file, click the\u00a0back arrow<\/span>\u00a0in the top left.<\/span><\/li>\n<\/ul>\n
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<\/a>\u00a0to create a historical data log.<\/span><\/p>[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/spreadsheet-tips-and-tricks-6.png” alt=”spreadsheet tips and tricks 6″ title_text=”spreadsheet tips and tricks 6″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]
Tip 8. Find and replace<\/h3>\n
Manually finding and replacing parts of the text can be exceedingly tedious for big spreadsheets. With the Find and replace<\/strong> function, you can quickly make revisions. Here’s how to do it:<\/p>\n
- \n
- Open a spreadsheet in Google Sheets.<\/li>\n
- Click\u00a0Edit<\/strong>\u00a0>\u00a0Find and replace<\/strong>.<\/li>\n
- Next to Find<\/strong>, type the word. If you want to replace the word, enter the new word next to Replace with<\/strong>.<\/li>\n
- To search for the word, click\u00a0Find<\/strong>. To see the next time the word is used, click\u00a0Find\u00a0<\/strong>again.<\/li>\n
- Optional: Narrow your search by using an option below.\n
- \n
- Match case<\/strong>: Makes your search case-sensitive.<\/li>\n
- Match entire cell contents<\/strong>: Searches for cells that are an exact match.<\/li>\n
- Search using regular expressions<\/strong>: Searches for cells that match a pattern.<\/li>\n
- Also search within formulas<\/strong>: Searches include formulas.<\/li>\n<\/ul>\n<\/li>\n
- To replace the highlighted word, click\u00a0Replace<\/strong>. To replace the word every time it\u2019s used, click\u00a0Replace all<\/strong>.<\/li>\n<\/ol>\n
Tip 9. Create filters<\/strong><\/h3>\n
There are several ways to filter data in Google Sheets. With the FILTER<\/a> formula, filters and the filter view function.<\/p>\n
Sort your data numerically or alphabetically<\/h4>\n
- \n
- Select the cells you want to sort.<\/li>\n
- Click data<\/strong> > Sort range<\/strong>.<\/li>\n<\/ol>\n
Apply a filter to the spreadsheet<\/h4>\n
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.<\/p>\n
- \n
- Open a spreadsheet in Google Sheets.<\/li>\n
- Select a range of cells.<\/li>\n
- Click Data<\/strong>\u00a0> Create a filter<\/strong>.<\/li>\n
- Click the Filter icon at the top of the range to choose from a list of conditions.<\/li>\n
- To turn the filter off, click Data > Turn off filter.<\/li>\n<\/ol>\n
Create filter views in a Google Sheet<\/h4>\n
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.<\/p>\n
- \n
- Go to Data > Filter views > Create new filter view.<\/li>\n
- Sort and filter the data.<\/li>\n
- When you click Close<\/strong> the filter view will be saved.<\/li>\n<\/ol>\n
Learn more about the different types of filters and how to filter Google Sheets without affecting other users<\/a>.\u00a0<\/p>[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/spreadsheet-tips-and-tricks-7.png” alt=”spreadsheet tips and tricks 7″ title_text=”spreadsheet tips and tricks 7″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]
Tip 10. Protected sheets and ranges<\/strong><\/h3>\n
Google Sheets is great for collaborating but sometimes you might want to lock<\/strong> part of the spreadsheet to prevent others from making edits. You can do this with named and protected ranges<\/strong>. If you want to learn more about how this works, check out Google’s Developer\u00a0documentation on spreadsheet.get<\/a>\u00a0requests.<\/p>\n
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.<\/p>\n
- \n
- Go to\u00a0Data<\/strong>\u00a0>\u00a0Protected sheets and ranges<\/strong>.<\/li>\n
- Click\u00a0Add a sheet or range<\/strong>.<\/li>\n<\/ol>\n
Get detailed step-by-step instructions here.<\/p>\n
To check which areas of the spreadsheet are locked, click\u00a0View ><\/strong>\u00a0Protected ranges<\/strong>. A striped background appears over those cells.<\/p>\n
To protect and control who can access specific data, try using separate, but connected Google Sheets<\/a>.<\/p>\n
Do more with spreadsheets<\/h3>\n
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<\/a>.<\/p>[\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]\n","protected":false},"excerpt":{"rendered":"
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\u2019s […]<\/p>\n","protected":false},"author":2,"featured_media":4327,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_et_pb_use_builder":"on","_et_pb_old_content":"A lot of people have already chosen to work with Google Sheets. <\/span>One of the reasons for this change, is that with Google Sheets, you can create and edit spreadsheets directly in your web browser\u2014no special software is required. Multiple people can work simultaneously, you can see people\u2019s changes as they make them, and every change is saved automatically.<\/span>\n\nAs you can imagine, working with Google sheets can save your company or team a lot of time<\/span>. 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. <\/span>\n
<\/h4>\n\u00a0\n
Tip 1. Freezing columns and row<\/b><\/h3>\nHow 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.<\/span>\n
- \n \t
- Go to the View menu.<\/span><\/li>\n \t
- Then, point your mouse to Freeze rows\u2026 or Freeze columns\u2026.<\/span><\/li>\n \t
- Select one of the options. When you scroll, you\u2019ll notice the frozen row(s) or column(s).<\/span><\/li>\n<\/ol>\n\u00a0\n\n
![\"google](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/freeze1.png\")
\n\n\u00a0\n\n\u00a0\nTip 2. Conditional and alternative formatting<\/b><\/h3>\n\u00a0\n
Conditional formatting.<\/strong><\/h4>\nConditional 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.<\/span>\n
- \n \t
- On your computer, open a spreadsheet in <\/span>Google Sheets<\/span><\/a>.<\/span><\/li>\n \t
- Select the cells you want to apply format rules to.<\/span><\/li>\n \t
- Click Format <\/span> Conditional formatting. A toolbar will open to the right.<\/span><\/li>\n \t
- Create a rule.<\/span>\n
- \n \t
- 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.<\/span><\/li>\n \t
- 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 <\/span>.<\/span><\/li>\n<\/ul>\n<\/li>\n \t
- Click Done.<\/span><\/li>\n<\/ol>\n\u00a0\n
Alternative formatting<\/strong><\/h4>\nAlternative formatting \u00a0is a function that helps you, to make a color format template, quickly. First, hit the\u00a0Format<\/em>\u00a0option from\u00a0the menu at the top and then click Alternative\u00a0formatting<\/em>. Once you do, you will see the below screen with a few options available to you.\n\n\u00a0\n\n
![\"colors\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/06\/colors-1024x556.png\")
\n\nFrom here, select the range which you will need to work on. There will be alternate rows filled with the specified color that you\u2019ve chosen and changing the color itself is as easy as clicking on the icon.\n<\/h3>\n\u00a0\n
Tip 3. Add-ons<\/b><\/h3>\nAdd-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. \u00a0<\/span>\n\n\u00a0\n\n
![\"google](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/freeze2.png\")
\n\n\u00a0\n\n\u00a0\nTip 4. Use of templates<\/b><\/h3>\nWhile a lot of businesses may not use templates much in their G Suite, it is actually a \u00a0necessary 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\u2019ll let you get on with\u00a0using<\/em>\u00a0the documents, rather than struggling with putting them together in the first place.<\/span>\n
- \n \t
- Open a Google\u00a0Docs<\/a>,\u00a0Sheets<\/a>,\u00a0Slides<\/a>, or\u00a0Forms<\/a>\u00a0home screen.<\/li>\n \t
- Click a template to open it.<\/li>\n \t
- To see more options, click\u00a0TEMPLATE GALLERY<\/strong>.<\/li>\n<\/ol>\nYou can also use templates when you're in a file. Click\u00a0File<\/strong>\u00a0
\u00a0New<\/strong>\u00a0
Tip 5.\u00a0Add comments and notes and the difference between them<\/b><\/h3>\nGoogle 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. \u00a0<\/span>\n\nUse 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.<\/span>\n\n\u00a0\n\n
![\"google](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/freeze3.png\")
\n\n\u00a0\n\n However, if you have multiple users, especially in different physical locations, the Comment functionality can be very helpful. \u00a0It 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.<\/span>\n\n\u00a0\nTip 6. Usage with Google forms<\/h3>\nWhen using Google forms it is \u00a0possible 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.\n
- \n \t
- Go to your Google Docs home page<\/li>\n \t
- In the top left corner click on the red \u201cNEW<\/strong>\u201d button<\/li>\n \t
- Click on \u201cmore<\/strong>\u201d and then \u201cGoogle Forms<\/strong>\u201d<\/li>\n<\/ol>\n
![\"google](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/freeze4.png\")
\n\n\u00a0\n\n\u00a0\nTip 7. Revision story<\/h3>\n
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\u2019s editors (Docs, Sheets, Slides, Drawings) they automatically and constantly save all changes as you work. You<\/span>\u00a0can revert to earlier versions of the file(s) as well and see which person made specific edits.<\/span><\/p>\n\n
- \n \t
- Open a file (Docs, Sheets, Slides, Drawings)<\/span><\/li>\n \t
- Click\u00a0File<\/span>\u00a0and then\u00a0See revision history.<\/span><\/li>\n \t
- You can alternatively click on the\u00a0All changes saved in Drive<\/span>\u00a0text above the features menu.<\/span><\/li>\n \t
- In the\u00a0Revision history<\/span>\u00a0panel to the right hand side you can see previous versions of the file.<\/span><\/li>\n \t
- Note the\u00a0Show more detailed revisions<\/span>\u00a0in the lower right corner of the revision history panel.<\/span><\/li>\n \t
- Select a timestamp to show a previous version and who made the edits.<\/span><\/li>\n \t
- The edits that each person made are shown in the colour that appears next to their name.<\/span><\/li>\n \t
- Text that has been added will be highlighted in a different colour.<\/span><\/li>\n \t
- Text that has been deleted will be shown with a strikethrough.<\/span><\/li>\n \t
- To revert the file to the version you're currently viewing, click\u00a0Restore this revision.<\/span><\/li>\n \t
- To return to the current version of your file, click the\u00a0back arrow<\/span>\u00a0in the top left.<\/span><\/li>\n<\/ul>\n\u00a0\n
![\"google](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/freeze5.png\")
<\/h5>\n<\/h5>\n\u00a0\n
Tip 8. Find a replace<\/h3>\nManually 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.\n
- \n \t
- Click\u00a0File<\/span>\u00a0and then\u00a0See revision history.<\/span><\/li>\n \t
- Click on \u201cmore<\/strong>\u201d and then \u201cGoogle Forms<\/strong>\u201d<\/li>\n<\/ol>\n
- 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 <\/span>.<\/span><\/li>\n<\/ul>\n<\/li>\n \t
- Select the cells you want to apply format rules to.<\/span><\/li>\n \t
- Then, point your mouse to Freeze rows\u2026 or Freeze columns\u2026.<\/span><\/li>\n \t
- Click\u00a0Add a sheet or range<\/strong>.<\/li>\n<\/ol>\n
- Go to\u00a0Data<\/strong>\u00a0>\u00a0Protected sheets and ranges<\/strong>.<\/li>\n
- Match entire cell contents<\/strong>: Searches for cells that are an exact match.<\/li>\n
- Next to Find<\/strong>, type the word. If you want to replace the word, enter the new word next to Replace with<\/strong>.<\/li>\n
- Go to\u00a0<\/strong>File<\/strong> ><\/span>\u00a0version history<\/strong>.<\/span><\/li>\n
- Color scale: Under Preview<\/strong>, select the color scale. Set a minimum and maximum value, and an optional midpoint value.<\/li>\n<\/ul>\n<\/li>\n