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.

Practice

1. What is the main purpose of the Track Changes feature in Excel?

easy

A. To automatically save the workbook every minute

B. To add notes or questions without changing data

C. To protect the workbook with a password

D. To see edits made by others in a shared workbook

Solution

1. Step 1: Understand the function of Track Changes

   Track Changes highlights edits made by different users in a shared Excel file.

2. Step 2: Compare with other features

   Comments add notes without changing data, so they are different from Track Changes.

3. Final Answer:

   To see edits made by others in a shared workbook -> Option D

4. Quick Check:

   Track Changes = See edits [OK]

Hint: Track Changes shows edits; Comments add notes [OK]

Common Mistakes:

• Confusing Track Changes with Comments
• Thinking Track Changes protects the file
• Assuming Track Changes autosaves the file

2. Which of the following is the correct way to insert a comment in Excel?

easy

A. Right-click a cell and select 'Insert Comment'

B. Press Ctrl + T to add a comment

C. Use the formula =COMMENT() in a cell

D. Double-click a cell and type a comment directly

Solution

1. Step 1: Recall how to add comments

   Right-clicking a cell and choosing 'Insert Comment' is the standard method.

2. Step 2: Check other options

   Ctrl + T is for tables, =COMMENT() is not a valid formula, and double-click edits cell content.

3. Final Answer:

   Right-click a cell and select 'Insert Comment' -> Option A

4. Quick Check:

   Insert Comment = Right-click menu [OK]

Hint: Right-click cell to add comment quickly [OK]

Common Mistakes:

• Using wrong shortcut keys
• Trying to add comments with formulas
• Typing comments directly in the cell

3. If you enable Track Changes and someone edits cell B2 from 10 to 15, what will you see in the worksheet?

medium

A. The cell B2 will show 10 and the change will be hidden

B. The cell B2 will show 15 with a colored border and a comment about the change

C. The cell B2 will be locked and uneditable

D. The cell B2 will show 15 with no indication of change

Solution

1. Step 1: Understand Track Changes behavior

   When Track Changes is on, edited cells show the new value with a colored border and a note about who changed it.

2. Step 2: Eliminate incorrect options

   The old value is replaced, cells are not locked automatically, and changes are not hidden or invisible.

3. Final Answer:

   The cell B2 will show 15 with a colored border and a comment about the change -> Option B

4. Quick Check:

   Track Changes = Highlight edits [OK]

Hint: Track Changes highlights edited cells with border and info [OK]

Common Mistakes:

• Thinking old value stays visible
• Assuming cells lock after edit
• Believing changes are invisible

4. You tried to enable Track Changes but the option is grayed out. What could be the reason?

medium

A. The workbook is not shared or saved as a shared workbook

B. You have comments in the workbook

C. The workbook is protected with a password

D. You are using Excel Online which does not support Track Changes

Solution

1. Step 1: Check workbook sharing status

   Track Changes requires the workbook to be shared; if not shared, the option is disabled.

2. Step 2: Review other options

   Comments do not disable Track Changes; protection or Excel Online may affect it but sharing is the main cause.

3. Final Answer:

   The workbook is not shared or saved as a shared workbook -> Option A

4. Quick Check:

   Track Changes needs shared workbook [OK]

Hint: Track Changes needs shared workbook enabled [OK]

Common Mistakes:

• Confusing comments with sharing
• Ignoring workbook protection effects
• Not knowing Excel Online limitations

5. You want to review all changes made in a shared workbook and accept or reject them. Which steps should you follow?

hard

A. Use the formula =ACCEPTCHANGES() in a new cell

B. Right-click any cell and select 'Accept Changes' directly

C. Go to Review tab, click Track Changes > Highlight Changes, then click Accept/Reject Changes

D. Save the workbook as a new file to accept all changes automatically

Solution

1. Step 1: Locate Track Changes review options

   In the Review tab, Track Changes menu lets you highlight and manage changes.

2. Step 2: Use Accept/Reject Changes feature

   You can accept or reject changes after highlighting them; no formula or right-click option exists for this.

3. Final Answer:

   Go to Review tab, click Track Changes > Highlight Changes, then click Accept/Reject Changes -> Option C

4. Quick Check:

   Review tab > Track Changes > Accept/Reject [OK]

Hint: Use Review tab Track Changes to accept or reject edits [OK]

Common Mistakes:

• Trying to accept changes via formulas
• Expecting right-click menu for accepting changes
• Thinking saving file accepts changes automatically