Assignments oF Class 9: Working with Multiple Worksheets and Workbooks:

Assignments  oF Class 9: Working with Multiple Worksheets and Workbooks:

Assignment 1: Creating and Navigating Multiple Worksheets

Objective: Create a workbook with multiple worksheets and practice navigation.

1. Instructions:
• Create a new Excel workbook.
• Add at least five worksheets with the following names:
• "Inventory"
• "Sales"
• "Expenses"
• "Profit Calculation"
• "Year Summary"
• Enter sample data in each sheet related to the respective topic (e.g., inventory counts, sales data, expense items).
2. Tasks:
• Rename one of the sheets.
• Delete one sheet.
• Navigate through the sheets using both the mouse and keyboard shortcuts.

Expected Outcome:

• A workbook with properly named sheets containing sample data, demonstrating understanding of navigation and management of sheets.

---

Assignment 2: Linking Data Between Worksheets

Objective: Learn how to link data from one worksheet to another.

1. Instructions:
• In the workbook created in Assignment 1, link data between the "Sales" and "Profit Calculation" sheets.
• Assume that the "Sales" sheet contains sales figures in cell B2 for Product A.
2. Tasks:
• In the "Profit Calculation" sheet, create a formula that links to the sales figures from the "Sales" sheet.
• Calculate profit by subtracting a fixed expense (let's say $100) from the sales figure in the "Profit Calculation" sheet.

Example Formula:

• For profit in cell B2 of the "Profit Calculation" sheet:

excel

Copy code

=Sales!B2 - 100

Expected Outcome:

• The "Profit Calculation" sheet should automatically update when the sales figures in the "Sales" sheet change.

---

Assignment 3: Consolidating Data from Multiple Worksheets

Objective: Use the Consolidate feature to summarize data from multiple sheets.

1. Instructions:
• Use the existing workbook and create sample data for each sheet:
• "Inventory": Item A: 50, Item B: 30
• "Sales": Product A: $200, Product B: $150
• "Expenses": Expense A: $100, Expense B: $50
2. Tasks:
• Create a new sheet called "Consolidated Summary".
• Use the Consolidate feature to summarize total inventory, sales, and expenses across the three sheets.
• For example, total inventory should sum the respective items across the sheets.

Steps to Consolidate:

• Go to the "Consolidated Summary" sheet.
• Click on the Data tab.
• Click on Consolidate.
• Choose Sum as the function.
• Select the range of data from each sheet and click Add for each range.
• Click OK.

Expected Outcome:

• A summary table in the "Consolidated Summary" sheet that reflects the total values from the "Inventory", "Sales", and "Expenses" sheets.

---

Assignment 4: Linking Data Between Workbooks

Objective: Learn how to link data from one workbook to another.

1. Instructions:
• Create two separate Excel workbooks:
• Workbook A named "SalesData.xlsx" with a sheet containing sales figures.
• Workbook B named "Analysis.xlsx" that will link to Workbook A.
• In "SalesData.xlsx", enter sample sales data (e.g., Sales for Q1, Q2, Q3).
2. Tasks:
• In "Analysis.xlsx", link to the sales figures from "SalesData.xlsx".
• Use a formula to calculate the total sales across the quarters in "Analysis.xlsx".

Example Formula:

• Assuming Q1 sales are in cell B2, Q2 in B3, and Q3 in B4 in "SalesData.xlsx":

= [SalesData.xlsx]Sheet1!B2 + [SalesData.xlsx]Sheet1!B3 + [SalesData.xlsx]Sheet1!B4

Expected Outcome:

• The "Analysis.xlsx" workbook should reflect the total sales from "SalesData.xlsx" and update dynamically when the sales figures change.

---

Submission Guidelines:

• Submit the completed Excel files for each assignment.
• Ensure all links and formulas are working correctly.
• Include a brief description of what you learned from each assignment.