Skip to content
Tutorials Center
My learning

Topics

Lesson 80 of 93 ยท Automation and Collaboration

Collaboration, Comments and Protecting Work

Work with others in Excel: co-authoring, threaded comments and notes, version history, locked cells, protecting sheets and workbooks, and external links.

A workbook rarely lives alone. Colleagues add data, managers ask questions in comments, and a careless click can overwrite a formula that took an hour to build. Excel has tools for both sides of teamwork: collaboration (several people editing, discussing and tracking changes) and protection (deciding who can change what).

Excel cannot run on this site, so the menu steps below are read-only text. The TryIts are small JavaScript and Python stand-ins that model the ideas: a comment thread, a locked-cell check and a change comparison between two versions. They are not Excel's code, and Excel's real behavior has more options than shown here.

Working together

Co-authoring Many editors, one file.
Comments Threaded talk on a cell.
Version history See and restore old versions.
1 / 2

Co-authoring

Save the workbook to OneDrive or SharePoint (not to a local folder or an old-style network drive), then click Share and invite people by email or link. Everyone with edit permission can open it at the same time, in desktop Excel, Excel for the web or the mobile app, and see each other's cursors and changes within moments. Autosave is typically on for these files, so there is no "who saved last" problem.

Good habits when several people edit one file:

  • Put data in an Excel Table so rows added by different people join the same structure.
  • Agree who owns which sheet or range, and use protection (below) to enforce it.
  • Avoid features that conflict with live editing, such as some legacy options (for example older shared-workbook mode, which is replaced by co-authoring) and macros in .xlsm files; co-authoring with macros is limited, so check Microsoft's current notes if you rely on them.
  • Name versions clearly when you finish a milestone, for example by saving a copy "Budget 2027 - approved".

Comments and notes

Excel has two different things that look alike, and mixing them up confuses teams.

Threaded comment Note
Purpose A conversation A static remark about a cell
Marker Purple indicator Red triangle
Replies Yes, with names, time, resolve No, just text
Mentions @name notifies a person No
Create Right-click > New Comment (or Review > New Comment) Right-click > New Note

Use a comment when you need an answer ("Is this price still current?" Reply: "Updated on Friday", then Resolve thread). Use a note for documentation that stays with the cell ("Source: finance export, column H"). Both appear in the Comments pane and the Review tab, where you can step through them or delete all at once. Old versions of Excel called today's notes "comments"; if you open an old file you may see notes where you expected comments.

Run it: a comment thread

A threaded comment is a tiny data model: an anchor cell, a list of messages, and a resolved flag. This stand-in captures that and prints the thread the way the pane would.

JS A threaded comment model

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Version history and track changes

Modern Excel does not have the old Track Changes feature of Word; instead it offers these:

  • Show Changes (Review tab), for workbooks in OneDrive or SharePoint: a pane lists who changed which cell, with old and new values and the time. It shows recent edits made by anyone with the file open or since you last opened it.
  • Version History (File > Info, or the file name menu in the title bar): browse and open earlier versions of the file, then Restore one or save it as a copy. Versions are kept per the storage service's rules, so do not treat them as permanent archives.
  • Compare tools, such as the Spreadsheet Compare app that ships with some Microsoft 365 and Office editions, find differences between two files.

If you need a permanent audit trail (who approved a budget and when), keep a log in the workbook or in a proper system, rather than trusting version history to hold everything forever.

Run it: compare two versions

Whatever the tool, comparing versions means walking both grids and listing cells that differ. Here is that idea in real Python, with two small versions of a budget stored as dictionaries.

Python Listing changes between two versions

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Protecting a sheet

By default every cell is locked, but locking does nothing until you protect the sheet. This is the part that surprises people: Locked is a property of cells; Protect Sheet is the switch that makes it count.

The usual recipe for a form or template where people may only type in certain cells:

  1. Select the input cells, open Format Cells > Protection and untick Locked.
  2. Go to Review > Protect Sheet.
  3. Optionally set a password and tick what users may still do (select cells, format cells, sort, use AutoFilter and so on).
  4. Click OK.

Now typing in an unlocked cell works; trying to change a locked formula cell shows a warning. To let specific people edit specific ranges, use Allow Edit Ranges (the ranges can be tied to a password or to named users in a domain).

Hiding formulas

On the same Protection tab, the Hidden checkbox hides a cell's formula from the formula bar once the sheet is protected. The result still shows, but nobody can read the logic. It is useful for templates where you do not want the calculation tampered with or copied casually.

Protecting the workbook structure

Review > Protect Workbook stops people from adding, deleting, renaming, moving or unhiding sheets. It does not stop edits to cells on sheets; that is what sheet protection does. A common combination for a distributed template is: sheet protection on every sheet plus workbook structure protection.

An honest warning about passwords

Sheet and workbook protection passwords are designed to prevent accidents, not to resist someone who wants in; there are well-known ways to remove them, and many tools do. If data is truly confidential, encrypt the file with File > Info > Protect Workbook > Encrypt with Password (which uses real encryption and cannot be recovered if forgotten) or control access in SharePoint. And never rely on hiding a sheet, a column or a formula to protect sensitive data in a file you send out.

Run it: locked cells and a protected sheet

This stand-in models the two switches separately, exactly the way Excel does: each cell has a locked flag, and the sheet has a protected flag. Edits to locked cells are rejected only when the sheet is protected.

JS Locked cells only count when the sheet is protected

Press Run (or Ctrl + Enter) to see the console output here.

No output.

A formula that points at another workbook, such as =[Prices.xlsx]List!B2, is an external link. Excel shows a security warning when you open the file, asking whether to Update the links. Know the trade-offs:

  • Links keep numbers in sync with a source file, but break if the source is renamed, moved or deleted. Excel then shows the old stored values or #REF!.
  • Manage them in Data > Queries & Connections > Edit Links (labelled Edit Links to Files in some versions): you can update, change the source, open it, or Break Link, which replaces formulas with their current values.
  • For repeatable imports, Power Query is usually more robust than long chains of cross-workbook formulas.
  • Before sending a workbook outside your team, check Edit Links, hidden sheets, comments and defined names, so nothing leaks a source file's path or internal notes.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. You tick Locked on a formula cell, but people can still overwrite it. Why?

    Locked is a cell property that does nothing by itself. Review > Protect Sheet is the switch that enforces it. (Every cell is locked by default.)
  2. 2. What is the difference between a threaded comment and a note?

    Threaded comments are conversations, with names, replies, @mentions and a resolve state. Notes (the red triangle) are plain text attached to a cell.
  3. 3. Which statement about sheet protection passwords is true?

    Sheet and workbook structure passwords are for accident prevention. Use file encryption or access control for confidential data.
  4. 4. What does Protect Workbook (structure) prevent?

    Structure protection locks the set of sheets. Cell edits are controlled by sheet protection instead.

Summary

  • Co-authoring needs the file in OneDrive or SharePoint; then several people can edit at once.
  • Threaded comments are conversations you can reply to and resolve; notes are static text on a cell.
  • Show Changes and Version History help you see and undo earlier edits; keep your own log for a lasting audit trail.
  • Unlock input cells, then protect the sheet: locking has no effect until protection is on. Use Hidden to hide formulas.
  • Protect Workbook locks the sheet structure; passwords prevent accidents, not determined attackers. Encrypt the file for real confidentiality.
  • External links keep files in sync but break when sources move; check Edit Links before sharing a workbook.

Next: Project: Monthly Budget Tracker.