Track changes and comments in Excel - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants you to prepare a project task list where team members can add comments and track changes made to task details. This will help in reviewing updates and feedback easily.

📊 Data: You have a list of project tasks with columns: Task ID, Task Name, Assigned To, Due Date, and Status.

🎯 Deliverable: Create an Excel sheet with the task list where changes are tracked and comments can be added to cells. Show how to enable and use these features.

Sample Data

Task ID	Task Name	Assigned To	Due Date	Status
1	Define Scope	Alice	2024-07-10	Not Started
2	Gather Requirements	Bob	2024-07-15	In Progress
3	Design Solution	Charlie	2024-07-20	Not Started
4	Develop Prototype	Diana	2024-07-25	Not Started
5	Test Prototype	Eva	2024-07-30	Not Started
6	Review Feedback	Frank	2024-08-05	Not Started
7	Finalize Report	Grace	2024-08-10	Not Started

1

Step 1: Enter the sample data into an Excel worksheet with columns: Task ID, Task Name, Assigned To, Due Date, Status.

Expected Result

Data table with 7 tasks and their details is visible in the worksheet.

2

Step 2: Enable Track Changes: Go to the Review tab, click on 'Track Changes' dropdown, then select 'Highlight Changes'. Check 'Track changes while editing. This also shares your workbook.' and click OK.

Expected Result

Excel starts tracking all changes made to the worksheet cells.

3

Step 3: Make a change to a task detail, for example, change the Status of Task ID 2 from 'In Progress' to 'Completed'.

Expected Result

The changed cell is highlighted with a blue triangle in the top-left corner indicating a change.

4

Step 4: Add a comment to a cell: Right-click the cell for Task ID 3's 'Assigned To' (Charlie), select 'New Comment', type 'Check availability for design review meeting', then click outside the comment box.

Expected Result

A red triangle appears in the top-right corner of the cell indicating a comment is present.

5

Step 5: View all changes: Go to Review tab, click 'Track Changes' dropdown, select 'Highlight Changes', check 'List changes on a new sheet', then click OK.

Expected Result

A new worksheet is created listing all changes made with details like who changed, when, and what was changed.

6

Step 6: To review comments, hover over the cell with the red triangle or go to Review tab and use 'Show Comments' to see all comments in the sheet.

Expected Result

Comments appear as pop-ups or in a pane, showing the notes added by users.

7

Step 7: Save the workbook to preserve tracked changes and comments for team review.

Expected Result

Workbook saved with all tracked changes and comments intact.

Final Result

Task ID | Task Name          | Assigned To | Due Date   | Status     
--------|--------------------|-------------|------------|------------
1       | Define Scope       | Alice       | 2024-07-10 | Not Started
2       | Gather Requirements| Bob         | 2024-07-15 | Completed *
3       | Design Solution    | Charlie *   | 2024-07-20 | Not Started
4       | Develop Prototype  | Diana       | 2024-07-25 | Not Started
5       | Test Prototype     | Eva         | 2024-07-30 | Not Started
6       | Review Feedback    | Frank       | 2024-08-05 | Not Started
7       | Finalize Report    | Grace       | 2024-08-10 | Not Started

* Cells with tracked changes or comments indicated by *

✓Tracked changes highlight all edits made to the task list, making it easy to see updates.

✓Comments allow team members to add notes or questions directly on relevant cells.

✓The 'List changes on a new sheet' feature provides a clear audit trail of all modifications.

✓Combining track changes and comments improves collaboration and review efficiency.

Bonus Challenge

Set up Excel to send an email notification automatically when a change is made to the task list.

Show Hint

Use Excel VBA macros with the Worksheet_Change event to trigger an email via Outlook.