Pinterest Stumbleupon Whatsapp
Ads by Google

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 Excel vs. Google Sheets: Which One Is Better for You? Excel vs. Google Sheets: Which One Is Better for You? Do you really need Excel? Both desktop and online solutions have their advantages. If you have trouble choosing between Excel and Google Sheets for managing your spreadsheets, let us help you decide. Read More  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.

The Basics

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.

Exercise Setup

Ads by Google

To create a random set of data for this demonstration, I used the Random Generator, one of many useful Google Drive add-ons 5 Google Drive Add-ons You Need To Use 5 Google Drive Add-ons You Need To Use Google Docs and Google Spreadsheet are both amazing free services, but when you include some of the add-ons listed here, it can breathe new life into these tools. Read More .

Random Generator Add-on

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.

Random Data

Apart from entering integer numbers, you can also use the Random Generator to add Boolean values Become Better At Finding Stuff With Search Engines: Boolean Search Logic Explained Become Better At Finding Stuff With Search Engines: Boolean Search Logic Explained It only takes Google's thousands of servers half a second to query approximately 50 billion indexed pages, it takes you significantly longer to scan only the first page of search results. On top of that,... Read More , 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.

Formula Auto Suggest

You can review all formulas in the Google’s function list or review a few basic ones by taking the Creating Simple Formulas course at GCFLearnFree.org.

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 How To Import Data From Other Google Spreadsheets How To Import Data From Other Google Spreadsheets Most users only use a small fraction of Google Spreadsheet's potential. Do you know how to get information from one Google Spreadsheet into another? Here's a cell function to do the job. Read More , 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:

=ImportRange("SHEET_ID", "TAB_NAME!CELL_OR_RANGE")

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.

Google Sheets ID

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.

Allow Sheet Access

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:

=TAB_NAME!CELL

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:

=SUM(START:END)

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:

=COUNTIF(B5:B15, "<4")

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:

=DIVIDE((SUM(B5:B15)),(COUNTIF(B5:B15, "<4")))

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 Mini Excel Tutorial: Using Advanced Counting and Adding Functions in Excel Mini Excel Tutorial: Using Advanced Counting and Adding Functions in Excel Counting and adding formulas may appear mundane compared to more advanced Excel formulas. But they can help you save a lot of time when you need to collect information about the data in your spreadsheet. Read More 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 Find Anything in Your Excel Spreadsheet with Lookup Functions Find Anything in Your Excel Spreadsheet with Lookup Functions In a giant Excel spreadsheet, CTRL+F will only get you so far. Be clever and let formulas do the hard work. Lookup formulas save time and are easy to apply. Read More . 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 Perform Amazing Feats With These Useful Google Spreadsheet Functions Perform Amazing Feats With These Useful Google Spreadsheet Functions I am ashamed to admit that I've only recently decided to start experimenting with Google Spreadsheets, and what I've discovered is that whatever allure or power Excel had over me due to the amazing things... Read More .

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.

Hide Sheet

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.

Google Sheets Warning

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.

Notification Rules

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.

Comment Notifications

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 10 Amazingly Useful Spreadsheet Templates to Organize Your Life 10 Amazingly Useful Spreadsheet Templates to Organize Your Life Is your life a hotch-potch of missed deadlines, forgotten shopping and reneged commitments? Sounds like you need to get organized. Read More , 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.

Revision History

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 How To Use Google Forms To Create Your Own Self-Grading Quiz How To Use Google Forms To Create Your Own Self-Grading Quiz Are you a teacher or trainer? Someone involved in a hiring process? Do you need to check somehow that other people have the skills or knowledge that you expect them to? Well, you're going to... Read More or with Google Analytics to produce an automated report How To Create An Automated Google Spreadsheet Report From Analytics Data How To Create An Automated Google Spreadsheet Report From Analytics Data Read More . Your imagination is the limit.

What are you doing with Google Sheets and which formulas, Google Sheets scripts Boost Productivity With These Excellent Google Spreadsheet Scripts Boost Productivity With These Excellent Google Spreadsheet Scripts If you use a spreadsheet application to crunch data, then custom scripts could be the master key. Start rolling with these excellent Google Spreadsheet scripts and make use of your data in new ways. Read More , or add-ins could you not live without?

Leave a Reply

Your email address will not be published. Required fields are marked *