Lesson 6.4 – Protecting Shared Workbooks & Version Control

Lesson Overview

When multiple users collaborate in Excel, it’s essential to protect shared workbooks and maintain version control to prevent errors, unauthorized edits, and data conflicts. This lesson explains practical techniques for workbook protection, version tracking, and maintaining integrity.

1. Protecting Shared Workbooks

1.1 Purpose

• Prevents users from making unintended changes

• Maintains data integrity while allowing collaboration

1.2 Steps to Protect a Shared Workbook

1. Open shared workbook

2. Go to Review → Protect Workbook → Structure

3. Set a password to prevent sheet deletion or moving

4. Allow users to edit only unlocked cells

5. Combine with data validation to restrict inputs

1.3 Example

• Protect a budget workbook while team members can update only assigned expense categories

2. Version Control in Excel

2.1 Importance

• Tracks changes over time

• Helps restore previous versions in case of errors

• Supports accountability in collaborative environments

2.2 Methods for Version Control

2.2.1 AutoSave & Version History (Microsoft 365)

• Save workbook on OneDrive or SharePoint

• Click File → Info → Version History to view or restore previous versions

2.2.2 Manual Versioning

• Save multiple versions with date or version numbers

• Example: ProjectDashboard_v1.xlsx, ProjectDashboard_v2.xlsx

3. Tracking Changes in Shared Workbooks

3.1 Highlight Changes

• Go to Review → Track Changes → Highlight Changes

• See who changed what and when

• Approve or reject changes to maintain accuracy

3.2 Combining with Protection

• Protect sheet structure while tracking changes

• Prevent accidental deletion of critical formulas

4. Best Practices for Shared Workbook Protection

1. Always backup before sharing

2. Use strong passwords for sensitive data

3. Restrict editing to specific ranges or users

4. Enable track changes and review periodically

5. Encourage cloud-based collaboration for real-time updates

5. Real-Life Examples

• Finance: Protect quarterly reports while accountants update figures

• HR: Version control for performance reviews across departments

• Sales: Protect dashboards while teams update regional data

• Project Management: Track milestones and changes in project plans

6. Practice Questions

1. Protect a shared workbook so users can edit only certain ranges.

2. Enable version history for a workbook on OneDrive and restore an older version.

3. Track changes in a shared workbook and review edits by a specific team member.

4. Explain why passwords are important for protecting shared workbooks.

5. Discuss the advantages of combining protection with track changes in collaboration.