Collaboration, Protection and Automation

Introduction

Spreadsheets are rarely used alone. Teams share them, change them and depend on them. Without care, one wrong edit can break a model or leak private data. This lesson covers working together safely, and saving time on repeated tasks.

Share carefully

• Share with named people where possible, rather than "anyone with the link".
• Choose the lowest permission that works: viewer (read only), commenter or editor.
• Know who can share further, and whether people can download copies.
• Check the link settings before sending. A public link may be indexed or forwarded.
• Remove access when people leave a project.
• Do not store passwords or highly sensitive personal data in spreadsheets. If you must handle personal data, follow the privacy rules and policies that apply to you.

Version history and comments

Cloud spreadsheets keep version history: see who changed what, and restore an earlier version. Use it to recover from mistakes. Use comments and assigned tasks to discuss specific cells instead of overwriting or sending separate copies. Agree a naming convention for files, and keep one master version.

Protect what matters

• Protect sheets or ranges so formulas and key inputs cannot be changed by accident. Allow editing only in the cells meant for input.
• Use data validation to limit what can be typed.
• Lock headers and structure.
• Keep a read-only copy of final reports.
• Remember that sheet protection is mostly to prevent accidents, and is not a strong security feature. Do not rely on it to hide secrets.

Names and structure

• Named ranges give meaning to cells: =Revenue*TaxRate is clearer than =F10*$H$2.
• Use separate sheets for inputs, calculations and outputs, and name the tabs clearly.
• Add a README sheet that says what the file is for, who owns it, the data source, the date and how to update it.
• Keep colour conventions, such as blue for inputs and black for formulas.

Automating repeated work

Look for tasks you do again and again, such as importing a file, cleaning columns, building the same report each month. Options, from simplest:

1. Built-in features: autofill, templates, pivot tables, filters, conditional formatting and data validation.
2. Formulas that update themselves: dynamic arrays and lookups replace manual copying.
3. Importing data: some programs can pull data from other files, web addresses and services. Check the source is trusted and the data is permitted for use.
4. Macros: record a sequence of actions and replay it. Excel uses VBA macros, and similar features exist elsewhere.
5. Scripts: Google Apps Script (JavaScript) and Office Scripts, and Python tools such as pandas, can automate bigger jobs.

Security warning: macros and scripts can run code. Only enable them from sources you trust. Files from strangers with macros are a common way to spread malware.

Know when to stop

Automation takes effort to build and maintain. Ask whether the task is frequent and stable enough to justify it, and make sure someone else can understand it. Document what any script does, who owns it, and what data it touches.

Backups and ownership

• Keep backups of important files in a second place.
• Make sure ownership is clear, so a file does not vanish when someone leaves.
• Keep sensitive files in approved locations.

Try it yourself

Take one of your spreadsheets. Add a README sheet, name two ranges, protect the formula cells while leaving the input cells editable, share it with a named person with viewer access and check version history.

Summary

• Share with named people and the lowest suitable permission, and use version history and comments.
• Protect formulas against accidents, use named ranges and a README sheet, and remember protection is not strong security.
• Automate stable, frequent tasks with built-in features first, and treat macros and scripts with caution.