Sharing workbooks in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are an office administrator managing a team project.

📋 Request: Your manager wants you to share the project workbook with team members so everyone can update their parts simultaneously.

📊 Data: You have an Excel workbook with project tasks, assigned team members, deadlines, and status updates.

🎯 Deliverable: You need to prepare the workbook for sharing, enable simultaneous editing, and track changes made by team members.

Sample Data

Task ID	Task Name	Assigned To	Deadline	Status
1	Design Logo	Alice	2024-07-10	In Progress
2	Write Content	Bob	2024-07-15	Not Started
3	Develop Website	Charlie	2024-07-20	Not Started
4	Test Website	Diana	2024-07-25	Not Started
5	Launch Campaign	Eva	2024-07-30	Not Started

1

Step 1: Save the workbook to OneDrive or SharePoint to enable sharing.

Click File > Save As > Choose OneDrive or SharePoint location > Save

Expected Result

Workbook is saved online and ready for sharing.

2

Step 2: Share the workbook with team members by inviting them via email.

Click Share button on the top right > Enter team members' email addresses > Set permissions to 'Can edit' > Send invitation

Expected Result

Team members receive an email with a link to the shared workbook.

3

Step 3: Enable 'AutoSave' to allow real-time updates by all users.

Toggle AutoSave switch at the top left of Excel window to ON

Expected Result

Changes made by any user are saved automatically and visible to others immediately.

4

Step 4: Use 'Show Changes' to monitor edits made by team members.

Go to Review tab > Click Show Changes

Expected Result

Recent changes by each team member are displayed for review.

5

Step 5: Ask team members to open the shared workbook from the link and update their assigned tasks.

No formula; team members edit cells under 'Status' column for their tasks.

Expected Result

Status updates from team members appear in real-time in the shared workbook.

Final Result

Shared Workbook Dashboard

+---------+--------------+-------------+------------+-------------+
| Task ID | Task Name    | Assigned To | Deadline   | Status      |
+---------+--------------+-------------+------------+-------------+
| 1       | Design Logo  | Alice       | 2024-07-10 | Completed   |
| 2       | Write Content| Bob         | 2024-07-15 | In Progress |
| 3       | Develop Web. | Charlie     | 2024-07-20 | Not Started |
| 4       | Test Website | Diana       | 2024-07-25 | Not Started |
| 5       | Launch Camp. | Eva         | 2024-07-30 | Not Started |
+---------+--------------+-------------+------------+-------------+

Changes are tracked and visible to all team members.

✓Saving the workbook online enables easy sharing and collaboration.

✓AutoSave ensures all edits are saved and visible instantly.

✓Show Changes helps monitor who made what updates.

✓Team members can update their task status simultaneously without conflicts.

Bonus Challenge

Create a summary sheet that automatically counts how many tasks are 'Not Started', 'In Progress', and 'Completed' using formulas.

Show Hint

Use the COUNTIF function to count statuses in the 'Status' column.

Practice

1. What is the main purpose of sharing a workbook in Excel?

easy

A. Allow multiple users to edit the same file simultaneously

B. Protect the workbook with a password

C. Convert the workbook to a PDF file

D. Create a backup copy automatically

Solution

1. Step 1: Understand workbook sharing

   Sharing a workbook allows several people to work on the same Excel file at the same time.

2. Step 2: Identify the main purpose

   The main goal is to enable collaboration by letting multiple users edit simultaneously.

3. Final Answer:

   Allow multiple users to edit the same file simultaneously -> Option A

4. Quick Check:

   Sharing workbook = multiple users editing [OK]

Hint: Sharing means multiple people can edit together [OK]

Common Mistakes:

• Confusing sharing with password protection
• Thinking sharing creates backups automatically
• Assuming sharing converts file formats

2. Which of the following is the correct way to start sharing a workbook in Excel?

easy

A. Click Home > Format > Share Workbook

B. Click File > Save As > Share Workbook

C. Click Review > Share Workbook > Allow changes by more than one user

D. Click Insert > Share Workbook

Solution

1. Step 1: Locate sharing option in Excel

   The Share Workbook feature is found under the Review tab.

2. Step 2: Choose correct menu path

   Click Review > Share Workbook, then check the box to allow multiple users to edit.

3. Final Answer:

   Click Review > Share Workbook > Allow changes by more than one user -> Option C

4. Quick Check:

   Sharing option is under Review tab [OK]

Hint: Sharing is under Review tab, not File or Home [OK]

Common Mistakes:

• Looking for sharing under File or Insert tabs
• Confusing Save As with sharing options
• Not enabling the checkbox for multiple users

3. If you share a workbook and two users edit the same cell at the same time, what happens?

medium

A. Excel shows a conflict message to choose which change to keep

B. Excel locks the cell and prevents any changes

C. Excel automatically merges both changes without asking

D. Excel deletes the cell content

Solution

1. Step 1: Understand cell conflict in shared workbooks

   When two users edit the same cell, Excel detects a conflict.

2. Step 2: Identify Excel's behavior on conflict

   Excel prompts users with a conflict message to decide which change to keep.

3. Final Answer:

   Excel shows a conflict message to choose which change to keep -> Option A

4. Quick Check:

   Conflicts prompt user choice [OK]

Hint: Conflicts prompt a message to resolve edits [OK]

Common Mistakes:

• Assuming Excel merges changes automatically
• Thinking Excel locks the cell permanently
• Believing Excel deletes data on conflict

4. You shared a workbook but your colleague cannot see your changes. What is the most likely reason?

medium

A. You forgot to save the workbook after editing

B. You did not protect the workbook with a password

C. Your colleague is using a different version of Excel

D. The workbook is not saved on a shared network or cloud location

Solution

1. Step 1: Check sharing location

   For sharing to work, the workbook must be saved on a shared network or cloud location accessible to all users.

2. Step 2: Identify common cause of changes not visible

   If the file is saved locally on your computer, others cannot see your changes.

3. Final Answer:

   The workbook is not saved on a shared network or cloud location -> Option D

4. Quick Check:

   Shared file location needed for collaboration [OK]

Hint: Save workbook on shared/cloud location for visibility [OK]

Common Mistakes:

• Assuming saving locally shares changes
• Blaming Excel version differences first
• Confusing password protection with sharing

5. You want to share a workbook with your team and track changes made by each user. Which steps should you follow?

hard

A. Save locally, share via email, and protect workbook with password

B. Save the workbook on OneDrive, use Share button, and enable Track Changes

C. Save on USB drive, share manually, and disable Track Changes

D. Save on desktop, use Share Workbook feature without Track Changes

Solution

1. Step 1: Save workbook on cloud for sharing

   Saving on OneDrive or similar cloud allows easy sharing and simultaneous editing.

2. Step 2: Use Share button and enable Track Changes

   Sharing via the Share button lets team edit together; enabling Track Changes logs edits by each user.

3. Final Answer:

   Save the workbook on OneDrive, use Share button, and enable Track Changes -> Option B

4. Quick Check:

   Cloud + Share + Track Changes = collaborative tracking [OK]

Hint: Use cloud save + Share + Track Changes for teamwork [OK]

Common Mistakes:

• Sharing via email causes version conflicts
• Not using cloud storage limits collaboration
• Disabling Track Changes loses edit history