How to collaborate on Google Sheets

Google Sheets was built around the fabric called collaboration. Unsurprisingly, all the innovations of this cloud-based application are centered around that idea. Unlike the case with other spreadsheet applications of age, one can access Google Sheets without the physical constraints of location or device. In this article, we are going to touch upon the means by which we can use Google Sheets collaboration.

Google Sheets collaboration features

Everyone works in the same file, so nobody emails copies around or merges versions by hand.

• Each person gets a role that decides whether they can view, comment on, or edit the file.
• Several people can edit at the same time, and you can see who is in the file and which cells they’re on.
• Comments let you ask about a cell, mention a teammate, or assign them a task.
• Protected ranges and filter views stop people from overwriting each other’s work or changing what others see.
• The edit history shows who changed a cell, when, and what it said before.
• Notification rules let you get notified when someone edits the file or submits a form.

Share the sheet and set each person’s access

Let’s consider an example. A team of software experts supports a software application. The project manager receives details of issues pertaining to various users of the application in a Google Sheets file. Each member will have to work on one or more issues, based on their availability.

All that the project manager can do now is click on the SHARE button on the top right corner.

On doing that, he will see a popup where he can key in the emails of all the people he wants the file shared with. Then grant them with edit, comment, or view access permissions whichever he deems appropriate.

You set each person’s role in the drop-down menu next to their email address. A Viewer can only look at the data, a Commenter can also leave comments, and an Editor can change the data and share the file with others.

Leave Notify people checked to send an email invitation, add a message if it helps, and click Send. Copy link gives you the file’s address to paste into Slack or a chat, but the link only opens for people who already have access.

To share with people who aren’t on that list, open General access in the same window and switch Restricted to Anyone with the link. Work accounts also offer the organization’s name, which limits the link to colleagues. Then pick the role the link gives.

Avoid giving Editor to a public link, since anyone who gets hold of it can change or delete your data.

For more on access, see how to give edit access in Google Sheets and how to make Google Sheets editable by multiple users. If your team works in Excel, here’s how to share an Excel file with multiple users.

Work on the same sheet in real time

What if multiple users work on it at the same time? No worries! Because Google Sheets handles that scenario so well. On the top, we can clearly see the users who are actively working on the file. Not only that, but Google Sheets also highlights the cells the other users are working on. The cell we activate usually is in blue color.

Look for them in the top-right corner, next to the Share button. Each person shows up as a round icon with their profile picture, or the first letter of their name if they haven’t added one.

Their selected cells take the color of their icon, with their name just above the selection.

To message everyone who has the file open, click Show chat at the top right. Those chats aren’t saved, so check how the Google Sheets chat feature works before you rely on it.

Google caps simultaneous editing at 100 open tabs or devices per file. Once more than 100 are open, only the owner and some of the editors can make changes. If a larger group only needs to read the data, publish it as a web page and share that link instead.

Excel has its own real-time editing for files saved to OneDrive or SharePoint. Here’s how co-authoring in Excel works.

Comment, mention, and assign tasks

Comments let people ask about a number or give feedback without changing the data.

1. Select the cells you want to discuss and click Insert comment in the toolbar, between the link and chart icons. The shortcut is Ctrl+Alt+M, or Cmd+Option+M on a Mac.

2. Type your comment. To bring someone in, type @ and their name or email address, and they’ll get an email with your comment. Click Comment to post it.

To turn a comment into a task, tick the Assign to box under the mention. The button changes to Assign, and the person gets an email. The comment stays open until they mark it as done.

Notes work differently: they’re plain text attached to a cell, with no replies and no emails. Here’s a closer look at comments vs notes in Google Sheets.

Ground rules for a shared spreadsheet

Real-time editing works best when everyone knows which cells are theirs to change.

Start with an instructions tab

Put an instructions tab at the front of the file, where people find it before they reach the data. Say what the data is for, which tabs or ranges each person fills in, and any format rules, such as numbers only. To build one, add a tab and drag it to the far left. Select a block of cells, click Merge cells in the toolbar, and type the instructions in the merged cell.

Keep inputs apart from outputs

Put raw data on an Inputs tab and the calculations, charts, and summaries on an Outputs tab. People type only on Inputs, so nobody overwrites a formula by accident, and you can give each person the tab they’re responsible for.

Protect ranges instead of hiding tabs

Hiding a tab doesn’t keep anyone out, because anyone who can edit the file can unhide it from the View menu. To lock a tab or range, go to Data > Protect sheets and ranges, add the sheet or range, click Set permissions, and choose Restrict who can edit this range. Then tick only the people who should keep editing it.

There’s more detail in our guide on how to protect cells and sheets in Google Sheets. For cells you leave open, color coding is a lighter signal: fill the cells each person may edit in one color, such as green, and explain the colors on the instructions tab.

Use filter views

A regular filter changes the view for everyone in the file. A filter view changes only yours, so several people can sort and filter the same data at once. Go to Data > Filter views > Create new filter view, then filter the columns as usual. You can name the view and come back to it later.

See how to filter without affecting other users for the full walkthrough.

Give each person their own file

Some data shouldn’t sit in one shared file, for example when each person may only see their own rows. Give everyone a separate file with just their part instead. Sheetgo, an AI-powered data automation platform, can split a master sheet into one file per person and merge their updates back into it on a schedule, so nobody copies and pastes between files. See how Sheetgo Automations work.

If you need more guidance on how to work with Google Sheets and its many collaborative features, feel free to read through the following official documentation.