Co-authoring in real time in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are a project coordinator at a consulting firm.

📋 Request: Your manager wants a shared project timeline that multiple team members can update at the same time without losing data.

📊 Data: You have a list of project tasks, assigned team members, start dates, end dates, and status updates.

🎯 Deliverable: Create a shared Excel workbook with a project timeline table that supports real-time co-authoring and shows the latest updates from all team members.

Sample Data

Task	Assigned To	Start Date	End Date	Status
Research	Alice	2024-06-01	2024-06-05	In Progress
Design	Bob	2024-06-06	2024-06-10	Not Started
Development	Charlie	2024-06-11	2024-06-20	Not Started
Testing	Dana	2024-06-21	2024-06-25	Not Started
Deployment	Eva	2024-06-26	2024-06-30	Not Started

1

Step 1: Open Excel and create a new workbook. Enter the project timeline data as shown in the sample data table.

Manually enter data in cells A1:E6 matching the sample data.

Expected Result

A table with columns Task, Assigned To, Start Date, End Date, Status filled with the sample data.

2

Step 2: Format the data range as an Excel Table to enable easy filtering and sorting.

Select A1:E6, then go to Insert > Table, and confirm 'My table has headers'.

Expected Result

Data is formatted as a table with filter dropdowns on each column header.

3

Step 3: Save the workbook to OneDrive or SharePoint to enable cloud storage and sharing.

Click File > Save As > OneDrive or SharePoint location, then save the file.

Expected Result

Workbook is saved online and ready for sharing.

4

Step 4: Share the workbook with your team members by clicking Share and inviting them via email with edit permissions.

Click Share button, enter team members' emails, set permission to 'Can edit', then send invite.

Expected Result

Team members receive an email link to open and edit the workbook simultaneously.

5

Step 5: Test co-authoring by having two or more team members open the workbook at the same time and update different task statuses.

No formula needed; just observe real-time updates.

Expected Result

Changes made by one user appear instantly on others' screens without conflicts.

6

Step 6: Add a formula to calculate the duration of each task in days.

In cell F2, enter =[@[End Date]]-[@[Start Date]] and fill down the column.

Expected Result

Column F shows the number of days between start and end dates for each task.

7

Step 7: Rename the new column header in F1 to 'Duration (days)'.

Manually type 'Duration (days)' in cell F1.

Expected Result

Table now includes a Duration (days) column with calculated values.

Final Result

Project Timeline Table (Shared Workbook)

+------------+------------+------------+------------+-------------+----------------+
| Task       | Assigned To| Start Date | End Date   | Status      | Duration (days)|
+------------+------------+------------+------------+-------------+----------------+
| Research   | Alice      | 2024-06-01 | 2024-06-05 | In Progress | 4              |
| Design     | Bob        | 2024-06-06 | 2024-06-10 | Not Started | 4              |
| Development| Charlie    | 2024-06-11 | 2024-06-20 | Not Started | 9              |
| Testing    | Dana       | 2024-06-21 | 2024-06-25 | Not Started | 4              |
| Deployment | Eva        | 2024-06-26 | 2024-06-30 | Not Started | 4              |
+------------+------------+------------+------------+-------------+----------------+

Bonus Challenge

Create a conditional formatting rule that highlights tasks with status 'In Progress' in green and 'Not Started' in red.

Show Hint

Use Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. For example, formula =[$E2]="In Progress" with green fill.