Assignments for Class 10: Excel Tables and Structured References

 Assignments for Class 10: Excel Tables and Structured References

---

Assignment 1: Creating and Formatting Tables

Objective: Convert a dataset into an Excel Table and format it.

Instructions:

1. Open a new Excel workbook.
2. Enter the following dataset in the first sheet:

Product	Amount	Quantity
A	250	15
B	300	7
C	400	20
D	150	12

3. Select the dataset and convert it into a table using the steps outlined in class.
4. Apply a style of your choice to the table to enhance its appearance.

Expected Outcome:

You should have a formatted table with a clear design that differentiates the headers from the data.

---

Assignment 2: Using Structured References in Formulas

Objective: Utilize structured references to perform calculations.

Instructions:

1. In the table created in Assignment 1, add the following data below your existing rows:

Product	Amount	Quantity
E	500	10
F	350	8

2. In a new cell below the table, calculate the following using structured references:
• Total Amount: Use the formula =SUM(Table1[Amount])
• Total Quantity: Use the formula =SUM(Table1[Quantity])
3. Additionally, calculate the Average Amount using the formula =AVERAGE(Table1[Amount]) in another cell.

Expected Outcome:

You should have the total amount, total quantity, and average amount calculated correctly using structured references.

---

Assignment 3: Sorting and Filtering Data

Objective: Apply sorting and filtering features to an Excel Table.

Instructions:

1. Using the table created in Assignment 1 (with all products), perform the following:
• Sort the table by the Amount column in descending order.
• Filter the table to display only products with a Quantity greater than 10.
2. Take a screenshot of the sorted and filtered table and paste it into a Word document.

Expected Outcome:

You should have a screenshot of the sorted and filtered table showing only the products with quantities greater than 10.

---

Assignment 4: Combining Tables and Using Structured References

Objective: Work with multiple tables and structured references.

Instructions:

1. Create a new sheet in the same workbook.
2. Enter the following dataset for another table:

Salesperson	Total Sales	Region
John	1000	North
Lisa	1500	South
Mike	1200	East
Sarah	1800	West

3. Convert this dataset into a table and name it Sales.
4. In the original table (from Assignment 1), add a new column titled Salesperson and fill it with names corresponding to the products (A - John, B - Lisa, C - Mike, D - Sarah, E - John, F - Lisa).
5. In a new cell, use structured references to calculate the total sales for John by referencing both tables:

=SUMIFS(Sales[Total Sales], Salesperson, "John")

Expected Outcome:

You should have an additional column in the first table and a correct calculation of total sales for John, showcasing the use of structured references across multiple tables.

---

Assignment 5: Reflection and Summary

Objective: Reflect on what you have learned about Excel Tables and structured references.

Instructions:

1. Write a brief summary (150-200 words) reflecting on the importance of using Excel Tables and structured references.
2. Include points on how these features can improve data management and analysis in Excel.

Expected Outcome:

You should provide a concise summary highlighting the benefits of Excel Tables and structured references.