If you think Google Sheets doesn’t offer you the features and functions necessary to create, maintain and update your elaborate spreadsheets and dashboards, think again. Google Sheets provides many of the same basic and advanced functions that you may be familiar with using in Microsoft Excel. However, the processes differ to achieve similar results.
Throughout this series, we will introduce the basic, intermediate and advanced features of Google Sheets. In turn, you can begin utilizing the application to track and manipulate data for your company, or for your own personal use. Whether the task at hand is maintaining your daughter’s softball team’s roster and schedule, or creating visual representations of your quarterly sales data, Google Sheets makes it easy.
In this post, we will discuss the following basic features available to you in Google Sheets:
- Sort Range (Multi-level Sort)
- Merge Cells
- Freeze rows/columns
- Filter
Sort Range:
Sorting data is an excellent way to arrange your data into categories in order to prepare for analysis, or quickly transform a jumbled spreadsheet into a conducive tool for your organization.
Example: The HR, IT and Sales department leads would each like a contact list to help them quickly identify their team’s phone numbers and office locations.
Below is the master contact list, organized alphabetically by employee last name, or, Column A:
The following are steps for sorting a range of data in Google Sheets:
- Highlight the range of data in which you would like to sort.
- Select Data from the menu.
- Choose Sort Range from the drop down list.
- Add a check mark in the box next to “Data has header row” if your highlighted data includes headings. In the example, the Last Name, Dept., Phone and Location cells are the column headings, which we do not want to sort along with the other data. Adding the check mark omits the header row in the Sort Range's actions.
- Determine the level, or levels at which you would like to sort your data (Note: If the example did not have a header row, the selections indicated below would be “Sort by Column B, then by Column D, then by Column A.”).
- At each level, choose to sort in Ascending (A-Z) or Descending (Z-A) order.
Now, the contact list is sorted first by Department, then by Location and then alphabetically by Last Name. The department leads can quickly identify their team members, which are sorted by location and then alphabetically by their last names.
There are various functions available in Google Sheets that help make your spreadsheets easier to comprehend. Merging cells is a simple way to create a heading across multiple columns, and freezing rows allows you to keep information locked in place as you scroll through the spreadsheet.
Merge Cells:
In the example above, new rows were inserted at each change in department to accommodate headings for each department/location. Adding headings allows us to delete or hide the columns specifying which department and which location corresponds to each employee. Merge cells allows us to stretch the headings across multiple columns.
The following are steps for merging cells in Google Sheets:
- Highlight the cells in which you would like to combine as one cell.
- Select the Merge cells icon.
- From the drop-down list, choose Merge All, Merge Horizontally, Merge Vertically or Unmerge. In this case, Merge Horizontally should be selected from the drop down menu in order to merge two cells side by side, and those specifically located in row numbers 2, 6, 10, 13, 16 and 19.
Freeze Rows or Freeze Columns:
Freezing rows or columns in Google Sheets helps make your spreadsheet more reader-friendly. For example, as you scroll down through a large data set, it is helpful when the header row remains displayed at the top of the columns.
Google Sheets allows users to freeze up to five columns, and up to ten rows.
The following are steps for freezing rows or columns in Google Sheets:
- Highlight the row(s) or column(s) you would like to freeze. In the example, row 1 was highlighted by clicking on the row number displayed on the left side of the screen.
- Select View from the menu items.
- To choose the number of rows or columns you wish to freeze, hover over Freeze rows or Freeze columns and select accordingly from the drop-down list. In the example, Freeze 1 row - the header row - was selected.
Filter
Applying a filter to a range of data in Google Sheets is a quick way to choose exactly what criteria to display in each column.
In the example below, the Sales Department employees are filtered in Column B, and then the Philadelphia location is filtered in Column D. As a result, only the Philadelphia Sales Department employees are listed, and the remainder of the contact list is hidden.
The following are steps for filtering data in Google Sheets:
- Highlight the data in which you would like to apply the filter.
- Select Data from the menu.
- From the drop-down list, choose Filter.
After a filter is applied in Google Sheets, the row numbers and column letters displayed in Green indicate which data will be available for filtering action.
To take action, refer to the drop down arrow that is now shown on the right side of each column heading.
- Select the drop-down arrow.
- Specify which criteria you would like to filter by removing/adding a check mark next to the items listed. In the example below, “Sales” is selected to be filtered in Column B.
A green filter icon is now displayed where the drop-down arrow was located next to “Dept.” The icon is a reminder that the data in Column B has been filtered.
To narrow down the list of Sales Department employees even further, filter a specific location in Column D.
- Select the drop-down arrow in the Location column heading.
- Add/Remove a checkmark next to the item(s) you would like displayed. In the example, “Philadelphia” is selected to be filtered in Column D.
Filters are a simple way to segment a master list of data without making formatting changes to the spreadsheet.
In the example below, our end result is a filtered list of Sales department employees working in the Philadelphia office, as well as their contact details.
In two clicks, you can easily revert back to the full employee list.
Simply:
- Select Data from the menu items.
- Choose Turn off filter from the drop-down list.
Google Sheets makes it easy to create a masterpiece on your own, with the bonus of also being able to collaborate with others simultaneously in a spreadsheet. You can choose to share your spreadsheet with others and grant them permission to view only or make edits to the document. Additionally, Google Sheets allows multiple people to make changes to a document at the same time. The revisions are tracked so you can easily view which collaborator made each change. If there are questions or comments along the way, Google Sheets also tracks comments and instant messages that take place in the document.
Below, we’ve outlined how real-time collaboration in Google Sheets can remedy a few circumstances that you may have encountered in the past.
Scenario: I wonder which one of these four versions of the budget report is accurate, or the most up-to-date?
Google Sheets quick fix: Save the master budget report in Google Drive and share the report with the appropriate individuals to ensure only one copy circulating to your team.
Scenario: Please let me know when you are done in the budget report, so I know when I can access it to update my information.
Google Sheets to the rescue: Team members can make edits and add comments in a spreadsheet simultaneously.
Scenario: I am not so sure about my teammate’s changes to the budget report this week.
Google Sheets saves the day: Add a comment to start a discussion regarding a particular cell, where other users will have the ability to reply to the comment, mark it as resolved or clear it when it is not longer legitimate.
As you can see, Google Spreadsheets offer numerous features and functions necessary to create, maintain and update your elaborate spreadsheets and dashboards. We’ve covered a few of those basic functions in this post. If you have any questions about how to use these functionalities, we invite you to chat with one of our experts during Blog Office Hours, every Thursday from 11-12 est. Additionally, if you would like to schedule a Google Spreadsheets training for your organization, please visit the Training section of our website. Stay tuned for the next installment of our Google Spreadsheet Series, “How to: Create Visual Representations of your Sales & Marketing Data with a Dashboard in Google Sheets”.