Sharing workbooks in Excel - Dashboard Guide

Dashboard Mode - Sharing workbooks

Goal

See how sharing a workbook helps a team track sales data together and keeps everyone updated.

Sample Data

Salesperson	Region	Sales Q1	Sales Q2	Total Sales
Alice	East	100	150	=SUM(C2:D2)
Bob	West	200	180	=SUM(C3:D3)
Charlie	East	130	170	=SUM(C4:D4)
Diana	West	90	120	=SUM(C5:D5)
Eva	East	160	140	=SUM(C6:D6)

Dashboard Components

• KPI Card: Total Sales All Regions
  Formula: =SUM(E2:E6)
  Shows total sales from all salespeople.
• KPI Card: Average Sales Q2
  Formula: =AVERAGE(D2:D6)
  Shows average sales in Q2.
• Table: Sales by Region
  Formula for East region total: =SUMIF(B2:B6,"East",E2:E6)
  Formula for West region total: =SUMIF(B2:B6,"West",E2:E6)
  Shows total sales by each region.
• Chart: Salesperson Total Sales Bar Chart
  Data range: Salesperson names (A2:A6) and Total Sales (E2:E6)
  Visualizes each salesperson's total sales.

Dashboard Layout

+----------------------+----------------------+
| Total Sales All Reg. | Average Sales Q2      |
|      [KPI Card]      |      [KPI Card]       |
+----------------------+----------------------+
|                      Sales by Region Table                      |
|                          [Table]                              |
+---------------------------------------------------------------+
|                  Salesperson Total Sales Bar Chart             |
|                           [Chart]                             |
+---------------------------------------------------------------+

Interactivity

Add a filter for Region. When you select a region (East or West), the KPI cards, sales by region table, and bar chart update to show data only for that region. This helps the team focus on one region's sales at a time.

Self Check

If you add a filter for Region = East, which components update?

• The Total Sales All Regions KPI card updates to show total sales only for East.
• The Average Sales Q2 KPI card updates to average Q2 sales for East.
• The Sales by Region table updates to show only East region totals.
• The Salesperson Total Sales Bar Chart updates to show only salespeople in East region.

Key Result

A shared sales dashboard showing total and average sales with region filters for team collaboration.

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