Services like Google Docs and Google Sheets have taken digital natives by storm and are used across the globe to collaborate on school and professional projects alike.
Google Sheets is the online equivalent of Excel and it’s not the first tool that comes to mind in the sphere of teamwork. But when you’re collectively supplying data, the spreadsheet as an information processing tool becomes inevitable.
This article will take you through Google Sheets functions and features essential for group projects. Some are universal and can be found in Excel, others are unique to Google Sheets and the collaboration options it offers.
Subject of the Demonstration
To demonstrate the power of Google Sheets, I’m using a spreadsheet that was set up to evaluate the results of a team dynamics exercise. The point of the exercise is to identify which team members adopted which roles within the team, as seen by their colleagues. If you’d like to learn more about team dynamics and roles, I recommend looking into the Belbin framework.
Spreadsheet Setup & Data Source
Briefly, I’ve set up one sheet for each participant, on which they can rate every other team member for each of the nine roles in the Belbin framework. After creating the first template, I duplicated it to create multiple identical sheets. The data are imported into a processing sheet, which in turn releases the results to the master sheet. When I say sheets, I mean tabs within a single spreadsheet document. Apart from the average rating for each team member and role, the master sheet also reveals which roles are covered by the team and which ones are missing or underrepresented.
I highlighted the cells I needed filled, set the add-on to insert Integer numbers from 0 to 3 (the rating options for our test), removed the checkmark for Unique values, and clicked Generate. I repeated the process for all individual data sheets. Since team members are not supposed to rate themselves, I went in and removed the respective random data points from each sheet, creating an empty line.
Apart from entering integer numbers, you can also use the Random Generator to add Boolean values, dates, custom lists, and strings.
How to Find the Formulas You Need
Note that whenever you start typing a formula, i.e. an equal sign followed by at least one letter, auto-suggest will pop up with available functions. Hover over the suggested functions to see what they are designed to do.
Make Google Sheets Work for You
Import Data from Other Google Sheets
Provided you have viewing permission, you can import data from any Google Sheet, whether it’s another tab in your present sheet or a different spreadsheet document entirely. You can either import single cells or entire columns and/or rows; the basic formula is the same:
SHEET_ID refers to the string of letters found between two forward slashes in the document URL, as shown in the screenshot below. The string can be rather long.
TAB_NAME refers to the name of the individual sheet within a document. Be sure to make it something simple, ideally without spaces. If the tab name does contain spaces, use single quotes to wrap it. It is separated from CELL_OR_RANGE with an exclamation mark. CELL_OR_RANGE can refer to a single cell, something like B4, or a range of cells, something like B:B to copy an entire column, B4:J4 to copy from a single line, or B4:J15 to copy across several lines and columns.
Add the formula to the first cell of your desired “import range” and it will automatically fill in all the requested cells to the right and/or underneath. Although when you first try to do that, you might get a #REF! error, as shown below. Simply click Allow access for the import to proceed.
If you’re referencing data from within the same spreadsheet document, a simplified formula should work, although for me it only worked with single cells:
Count Data Points & Calculate Averages
To calculate the average rating for each team member, I imported their ratings from all individual sheets into a processing sheet using the formula outlined above. In other words, for each team member, I have 11 rows of data, with at least one empty row, since nobody rates themselves. I also had to take into account that if less than 11 participants show up for the exercise, more than one line will be empty, which creates a challenge for calculating the average. Here’s how I solved that.
To calculate the average, I simply added up their total from all individual ratings. This is the formula for that:
START refers to the first cell, e.g. B5 and END refers to the last cell you want to include, e.g. B15.
To determine the number of data points, I used the COUNTIF formula:
I knew that ratings would be any number between 0 and 3. A cell containing anything other than one of those numbers, which are all smaller than 4, ideally an empty cell, doesn’t need to be counted. Hence, the result of this count will be equal to the number of data points submitted.
Next, I combined the two formulas into a nested function, i.e. to DIVIDE my SUM with the COUNTIF result and thus calculate the average in a single cell:
It’s good to keep in mind that the function inside the parentheses will be processed first.
Note that a missing parentheses or quote is the most common source of spreadsheet error. Also, if you’re using European number formatting, where commas rather than points are used to separate decimals, you might run into problems with the comma used for separating values in your formula; try using a semicolon instead.
I used this formula again, to count how many people show up in specific roles within our team. In that case I used “>1”, since ratings larger than 1, i.e. 2 or 3, indicate the skill is being applied.
You can use the COUNTIF formula for many advanced operations, as we outlined in a separate article. Likewise, if you need to find specific information within a spreadsheet, you can use the universal lookup functions. Finally, if you’re curious about conditional formatting, combining data from different cells, or importing external data, I recommend our article on useful Google Sheets functions.
Hide & Protect Sheets
To help my team focus on the data that mattered, I chose to hide the processing sheet. To do this, click the arrowhead next to the sheet name to expand the menu, then select Hide sheet. As you can see in the screenshot below, you can show a hidden sheet by going to View > Hidden sheets and selecting the sheet you want to display again.
To prevent users from accidentally messing up complex formulas or changing data, you can also choose the Protect sheet… option from the menu shown above. This will give you the option to protect a Range of cells or an entire Sheet. If you go with the latter, you can Except certain cells. In both cases, you can Set permissions and Restrict who can edit this range, essentially allowing selected users to perform edits, or to generally Show a warning when editing this range.
Set Up Notifications to Be Alerted of Changes
When you’re releasing a document for asynchronous group collaboration, it’s a good idea to check in every now and then and see what everyone else has been up to. Or set up notifications, so you know exactly what happens when.
Go to Tools > Notification rules… and pick your preferred routine. You can be notified whenever Any changes are made or when A user submits a form and you can be notified by Email in from of a daily digest or right away.
Since Google Sheets supports comments, you can also receive notifications about comments or replies. Click Comments in the top right, expand the Notifications menu, and select your preference.
To make a comment, first highlight the cell/s you wish to comment on, then go to Comments > Comment.
Use Revision History to Restore Document Versions
Sometimes things happen that were not intended or maybe you want to re-use a sheet and turn it into a template, meaning you need to clear all the data that was entered in the meantime. That’s when the revision history comes in handy. You can access it through File > See revision history or with the keyboard shortcut CTRL + ALT + SHIFT + G.
At first, this will list key edits along the document’s history, along with the date, time, and the username of the person who edited the document. To see every single step, click on Show more detailed revisions at the very bottom of the list. When you found the state in which you’d like to see the document, click Restore this revision.
Do More with Google Sheets
It’s not only its collaboration features that make Google Sheets a much more versatile tool than Excel. Combined with other Google tools, you can create complex operations. For example, you can combine Google Sheets with Google Forms to set up a self-grading quiz or with Google Analytics to produce an automated report. Your imagination is the limit.
What are you doing with Google Sheets and which formulas, Google Sheets scripts, or add-ins could you not live without?