Despite its reputation as Microsoft Excel's younger, less heavy-duty sibling, Google Sheets has much to offer and your team's productivity can be boosted by knowing the top tips and tricks.
Designed in the style of other Google Workspace products, Sheets has a clean and accessible layout complete with the typical dropdown menus and text formatting options set atop the main table screen.
Don't let this seemingly simple setup fool you though. Under the hood, Sheets is a powerful tool that can be used to house reams of company data or to create plans for complex projects. Below we've rounded up the best tips and tricks to put to use in Google Sheets right now.
Google Sheets top tips and tricks
Linking forms and sheets
While all the products across the Google Workspace suite are designed to work with one another, certain tools work particularly well when paired. An example of this is Google Sheets and Google Forms.
Forms allow users to create their own questionnaires for others to fill out, and Sheets is built so that responses from those forms can be inputted directly into spreadsheets.
This can have many useful applications in a business context. Internally, businesses could send out forms to staff to gauge employee sentiment, understand productivity levels, or gather feedback on a new initiative. These responses would then appear automatically in Google Sheets.
Externally, businesses could garner data from customers using similar forms that enquire about customer satisfaction or product feedback.
To use this feature, navigate to the dropdown 'Tools' menu in Google Sheets. The first option allows a user to instantly create a new form without navigating to the Forms platform, while the tab below allows users to manage existing forms.
Integrated add ons
Google integrates the Google Workspace Marketplace into its productivity suite, a marketplace devoted to plugins designed to enhance user experience and workflow within platforms like Sheets or Docs.
By clicking on the extensions tab and selecting the 'Add-ons' button from the drop-down menu, users are taken to the marketplace page where they can download apps from third-party developers approved by Google.
There are plenty of add-ons to choose from and many are specific to Google Sheets such as Sheetgo, which allows users to easily connect sheets, Excel files, and CSV files to transfer, filter, split, and merge data.
Task focus using filters
When using Google Sheets as a project planner, it can be helpful to use the platform's filter tool. By creating filters for different teams, different people can focus on the tasks assigned to them.
Filters can be created by right-clicking on a cell, or a range of cells, and selecting the 'create a filter' option. When a filter is added, everyone with access to the sheet can find the filter and anyone with permission to edit the spreadsheet can edit the filter.
You can then filter by condition, by value, or by color, and search for data points by typing into the search box. To remove the filter, right-click on the cell and select 'remove filter' from the drop-down menu.
Filters could be handy for team projects, allowing users to streamline their view or use of the sheet. Especially useful is the ability to apply filters and then share the sheet filters attached.
Convert data to tables
Large Google Sheets documents can get complex, but converting data into tables within Sheets projects can help users better maintain information. Tables can help reduce the burden of formatting and inputting data, as format and structure can be applied to ranges of data.
Tables are useful for project tracking, event planning, inventory management, or any situation in which data must be compartmentalized. Users can designate appropriate column types as well as access a unified menu for the table.
To convert existing data into a table, open a Google Sheet project and select a range of either empty cells or ones that contain data. Then, on the menu bar, select 'Format' and 'Convert to table' before choosing the appropriate column types.
Using the 'table views' function in Sheets can also be useful, which can locate data without impacting others collaborating on the sheet. Views can reveal or hide specific rows or apply other configurations to manage data.
To create new views, use the table menu and then select either 'Create group by view' or 'Create filter view.' The former shows users' rows grouped together based on specific fields.
Use Gemini
Google's flagship AI tool, Google Gemini, can be used in several ways throughout Google Workspace. In relation to Google Sheets specifically, Gemini can be used to organize data with ease.
When opening a Sheet, users can enter prompts into Gemini's 'help me organize' sidebar that can instruct Gemini to create research frameworks, set up task trackers for projects, or plan events based on data.
Gemini can also be used in Sheets to generate data analysis and insights via prompts such as "Identify trends in this table," "How can I show regression and prediction of this data?" and "What analysis can you help me perform with this sheet?"
Clean up data
When working with data in sheets, there are a few handy tricks to know that can help keep data clean and well-maintained, avoiding the creation of erroneous data or duplicated data in problematic quantities.
Sheets can be configured to limit data-entry choices so as to reduce the chance of errors. If a Sheet has a status column, users can build in choices such as 'done,' 'in progress,' and 'not started' which means other users could not input their own, erroneous response.
Users can also remove duplicate data, a common problem that can be easy to miss in large data quantities. This can be done by selecting the data range that duplicates need to be removed from and then hitting 'data' followed by 'remove duplicates.' This can also be done for gaps or blank spaces in data.
