Spreadsheets are an integral part of day-to-day data management. Both Excel and Google Sheets have become powerful tools that offer a variety of intelligent features to help users optimize work, whether that’s managing, analyzing, or transferring large amounts of data.

But what about when we want to collaborate with others on Excel or Google Sheets?

Both Excel and Google Sheets offer online collaboration. The cloud-based Google Sheets tool has always allowed us to receive real-time edit updates as we work alongside others; Excel was recently updated with a “Co-authoring” feature that allows users to work on a workbook at the same time (available on the Windows and macOS Excel applications and online Excel version).

However, is this collaboration as straightforward as it seems? Many users struggle to effectively collaborate on their spreadsheets online because it can often lead to more confusion than collaboration! But don’t ditch the spreadsheet altogether just yet.

In this guide, we offer 11 different ways to make collaboration better in Excel and Google Sheets, so you can continue using spreadsheets as your go-to tool for managing your data.

Excel and Google Sheets collaboration limitations

Despite being the most sought productivity tools for teams, both Excel and Google Sheets have limitations as workflow platforms. Although they offer online collaboration features, these still require a lot of manual work.

Not only does this make spreadsheets more prone to error with multiple collaborators present at the same time, but it also decreases productivity and efficiency in the way users work together as a team.

Cross-platform limitations

• You can’t protect your sensitive data from being viewed.
• Any request for data entry has long response times.
• There’s no way to easily track what changed and who did it.
• There’s no gatekeeping functionality

Excel collaboration limitations

• You don’t have any version history (for offline Excel).
• You need to upload the spreadsheet to the cloud-based platform (for Excel 365).
• You must refresh or save the file for the rest to see the changes made (for Excel 365).
• You won’t be able to use all the features available in the desktop version (for Excel 365).

Google Sheets collaboration limitations

• Becomes less agile as the data and number of collaborators grow.
• Is limited to 100 users. If exceeded, only the owner and appointed users can edit; to work simultaneously, it needs to be published as a web page.

However, there are ways to collaborate on Google Sheets and Excel online without having to struggle or abandon spreadsheets altogether. Here are 11 ways your team can level up their spreadsheet skills and optimize productivity as you collaborate!

11 tips to collaborate better with spreadsheets

1. How to share your Excel file for collaboration? Create individual carve-outs

If you have an Excel file with sensitive data that cannot be shared with others, one of the most common ways to work around it is to create carve-out spreadsheets and share them with each of the stakeholders.

Each carve-out can be stripped of the sensitive data that the respective stakeholder shouldn’t have access to.

Once they are filled and sent back to the file owner, they have to be merged back and controlled by the original file owner, which can be done manually or automatically, depending on the size of the group you manage but also on how much trust you have on the submitted data to be correct. 

Alternatively, you can use Sheetgo to automate the entire process. Set up a custom workflow to split or filter the data you want to transfer from the original file to the corresponding stakeholder files. Once they’re done editing, you can update the original file with a single click or schedule automatic updates.

2. Create rewards or penalties

To tackle one of the most frustrating aspects of data collection - chasing for input - some organizations set up rewards and penalties to praise on time and condemn late submissions, respectively.

This is a fine idea on paper but can leave a bitter taste when collaborating on more hierarchical organizations.

3. Co-authoring is not enough? Go back to basics

When data quality matters, and if you are worried about inaccuracies coming from data input - despite the Excel Co-authoring option being available, you should collect data input outside the spreadsheet via email, chats, or other Excel files and then update the Excel workbook manually.

This way, you’ll ensure no one else interferes with your file since the co-authoring option doesn’t allow you to control any other co-author changes in your spreadsheet. 

Alternatively, you can use forms to standardize data input. If you prefer to work with spreadsheets but would like the advantages of using forms, consider signing up for Sheetgo. Sheetgo Forms is a great option since it was built for spreadsheets. It will ensure consistent and reliable data collection, as the data is automatically saved to your spreadsheets, you can also forget all about manual updates. To learn more about this, watch this video on how to create a form with Sheetgo.

4. Create an instruction sheet

By creating an instruction sheet in the first tab of your spreadsheet, you can make sure that every collaborator on your spreadsheet knows every single detail about the dataset and, more importantly, what they can or cannot do. This will be the first thing they see when they enter the file, so it will completely avoid any risk of uncertainty.

By creating an instruction sheet, you can:

• Give an overview of what the dataset is about and the overall objective of the project.
• Give step-by-step instructions on how users should deal with the data.
• Identify any tabs/ranges of data that specific users should/should not edit.
• Highlight any rules associated with data - e.g. text and number formatting, use of formulas, etc.

How to create an instruction sheet on both Excel and Google Sheets?

1. 1. Add a new tab to your file and label it clearly as the instructions (make sure it’s the first tab in your spreadsheet).
2. 2. Highlight a group of cells and select the “Merge” button in the toolbar. This will combine these cells together, perfect for longer text.
3. 3. Write your instructions clearly in the merged cells.

5. Restructure your spreadsheet: Separating inputs from outputs

Your inputs are all your raw data from multiple sources. Your outputs are the conclusions taken from your input data (results from calculations, charts, and graphs depicting raw data, etc.).

We recommend separating your input and output data in your spreadsheets, as any accidental changes made to the data could have a knock-on effect on other cells in your spreadsheet. Using Sheetgo, you can keep multiple input and output files connected and decide exactly what data to share with each collaborator to avoid confusion and accidental changes.

By separating your input data and output data into different sheets or tabs, you can:

• Separate important values and avoid mixing them up with other data.
• Make sure that users don’t make any accidental changes to the output values.
• Assign users to the tab or sheet they're responsible for.
• Identify the reasoning behind each type of data more easily.

How to add an input and output tab in Excel and Google Sheets?

1. 1. Create a new tab and clearly label it as "Inputs".
2. 2. Import your input data directly into the sheet using your preferred method (copy and paste, IMPORTRANGE, etc.).
3. 3. Repeat the same steps to create the "Output" tab.

6. Hide specific sheets

Hiding specific sheets in your file is a great way to prevent users from changing key data that shouldn’t be touched. Hiding it out of sight avoids the risk of any users feeling tempted to click on the tab and take a sneak peek.

We recommend hiding tabs on your spreadsheet if you’re looking for a straightforward, temporary way to conceal specific data. However, if you’re looking for a more effective, long-term solution that allows you to share individual tabs, you should consider signing up for Sheetgo.

By hiding specific sheets, you can:

• Prevent users from seeing specific tabs that shouldn’t be touched.
• Hide and unhide sheets easily, depending on when you need them.
• Control how much access users have to important or private information.

How to hide a sheet in Google Sheets?

1. 1. Go to the sheet or tab you would like to hide, click on the arrow to prompt the menu, and select “Hide sheet”.

1. 2. To make the sheet visible again, go to “View” and the “Hidden sheets” option should appear. Select “Show [tab name]”.

How to hide a sheet in Excel?

1. 1. Right-click on the sheet and select “Hide”.

1. 2. To make it visible again, right-click on the sheet and select “Unhide…”.

7. Protect sensitive sheets

If you want to fully restrict editing, we recommend protecting the sheets. By hiding, you are simply making it invisible to users, but they can make it visible again if they have editing permissions. This is great if you have any tabs containing private data that you need to keep securely hidden.

By protecting a sheet in Google Sheets or Excel, you can:

• Select which users keep editing permissions.
• Prevent all or specific users from modifying, copy/pasting, or deleting significant data.
• Make specific ranges editable within the protected sheet.
• Hide or protect cell formulas so as not to affect the rest of the spreadsheet data.

How to protect sheets in Google Sheets?

1. 1. Go to the sheet or tab you would like to hide, click on the arrow to prompt the menu, and select “Protect sheet”. The best option is to “Restrict who can edit this range” and then untick all the boxes of users you would like to restrict access to.

How to protect sheets in Excel?

Please note: To protect a sheet in Excel, you will need to use the Microsoft 365 Excel app on your desktop - this process is a little more complex, so we recommend hiding sheets if you’re looking for a simpler alternative.

However, you can find detailed step-by-step information on how to protect sheets in the Excel app on the Microsoft Support page. Alternatively, read our article on how to password-protect an Excel file.

8. Add and assign comments to users

Both Google Sheets and Excel online allow you to add a comment to specific data. The comment feature becomes even more useful in collaborative work if you use “@” to address the user and notify them that they need to make a change to specific data.

By adding comments to your spreadsheets, you can:

• Provide in-depth instructions for a specific range of data assigned to certain collaborators.
• Assign a specific action to a collaborator, for which they’ll receive a direct email notification.
• Give detailed feedback by placing a comment on the specific cell or range of cells containing the data.
• Communicate back and forth with your collaborators on any important updates, tasks, or observations.

How to add and assign a comment in Google Sheets?

1. 1. Click on the box icon with a “+”, between the “Link” and “Graph” icons. Select or type in the email of the recipient collaborator.

1. 2. Tick the “Assign to …” box to notify them via email.

How to add and assign a comment in Excel?

1. 1. Go to “Insert” tab and click on “New Comment”. In this case, you won’t have to tick any box to send; it will do so automatically.

9. Add notes to your spreadsheets

Similar to comments, adding notes is a great way to communicate with other collaborators and provide vital information that can help with overall productivity.

While we recommend comments for more direct communication with specific collaborators. Notes are a great way to provide more context to specific areas of your spreadsheets and act as a more permanent feature.

By adding notes to your spreadsheets, you can:

• Provide in-depth instructions for a specific range of data.
• Give detailed feedback linked to a certain range of data.
• Supply more context to a specific area of cells that users need to know at a glance.

How to add a note in Google Sheets?

1. 1. Go to the “Insert” tab and select “Note”.
2. 2. Write your note.

How to add a note in Excel?

1. 1. Go to the “Review” tab and select “New Note” to the far left.
2. 2. Add text to the note.

10. Color coding

Color-coding is a great way to immediately separate key parts of your data in a more visually appealing way. Associating key rules to colors is an extremely easy yet effective way to tell collaborators what they can and cannot touch.

For example:

• Green cells = can edit
• Red cells = do not touch

By color-coding your spreadsheets you can:

• Apply rules to your datasets in a simple way.
• Assign users by color to specific areas of your spreadsheets.
• Separate, organize and manage larger amounts of data.

Adding colors to cells is straightforward enough. But color-coding using conditional formatting is a more advanced way to organize your spreadsheets in a more effective way.

By color-coding using conditional formatting, you can:

• Automate formatting to color and font style without the need for each user to do it manually.
• Avoid human error since the formatting condition is placed directly on the cell, which will act as the filter for you.
• Highlight key information for a quick visual analysis.

The following steps will focus on color-coding using conditional formatting.

How to color-code in Google Sheets?

1. 1. Select the range of cells that you want to color code and then head to Format > Conditional Formatting. In “Format cells if” you can select the formatting rule of your preference.

1. 2. Enter the value that your formatting rules should apply to and click “Done”.The selected range will be automatically color-coded.

How to color-code in Excel?

1. 1. In the ‘Home’ tab, select “Conditional Formatting”. Choose the rule you would like to apply in the “Highlight Cell Rules” option.

1. 2. Since we chose “Greater Than…”, we need to enter a value. We can simply select the value by clicking on the cell containing it.

11. Use filter views

Both Google Sheets and Excel let you filter the data for collaborators. This is a great way to ensure that each collaborator only accesses the tabs or data that are necessary to them, so they can easily focus on their own personal tasks and not accidentally make changes to other collaborators’ work.

By using filter views, you can:

• Narrow down the data that users see and edit on.
• Copy and paste the data that is filtered, without affecting the other cells.
• Save and customize the filter views for future use, regardless of the changes made to the data.

How to use filter views in Google Sheets?

1. 1. In the toolbar, head to Data > Filter views > Create new filter view.

1. 2. Click on the drop-down list in the column header and “Filter by condition”.

1. 3. Input the values you wish to filter to and click “OK”.

Please note: You can name your filter view or save it as the default filter number. If you want to make changes to this filter or delete it entirely, head back to Data > Filter views in the toolbar.

How to use filter views in Excel?

1. 1. In the toolbar, head to Home > Sort & Filter > Filter.

1. 2. Select “Sheet View”, then “New Sheet View”.

1. 3. For conditional filtering, select “Number Filters” and then select the rule you would like to apply. Save by clicking on “Apply”.

Now you can collaborate better with spreadsheets!

Spreadsheets continue to be the most widely used tool for data management by users thanks to their flexibility, ease of use, and common understanding – which is exactly why you should persevere with them when it comes to collaboration!

Excel’s core design is to allow us to perform complex calculations, analyze and swiftly model data, and not follow workflows.

We’ve seen new spreadsheet solutions popping up every now and then that aspire to solve Excel’s collaboration issues by providing a complete alternative to Microsoft’s spreadsheet platform. However, most of these require you to completely transfer your models to a new platform that likely will live short of its predecessor on some level you are not yet aware of.

By implementing just a few of these tips, you completely transform the way you and your collaborators work together on Excel or Google Sheets.

We recommend that you further polish your team’s spreadsheet skills (with the help of our expert blog), rework your models and processes, and even hire external help before ditching spreadsheets altogether. After all, spreadsheets are the best way to carry out powerful processes in the simplest way!

Alternatively, if you want to keep your Excel files and Google Sheets and add the power of automated workflows, sign up for Sheetgo and start saving time today.