Excel Practical Assignment
Part A – Formatting & Familiarity
1. Enter the following data in Excel:
Product Category Sales (₹) Quantity Region
Laptop Electronics 45,000 5 North
Phone Electronics 30,000 10 East
Chair Furniture 8,000 15 West
Table Furniture 12,000 7 South
Pen Stationery 500 50 North
Notebook Stationery 2,000 20 East
Tasks:
• Format the header row with bold text, yellow background, and borders.
• Format Sales column in currency (₹).
• Highlight any sales above ₹10,000 in light green using conditional formatting.
• Apply a table style to the data.
Part B – Formulas
1. Find the Total Sales using SUM.
2. Find the Average Quantity using AVERAGE.
3. Use COUNTIF to find how many products belong to Furniture.
4. Use IF to classify:
o “High Sale” if Sales ≥ ₹10,000
o “Low Sale” if Sales < ₹10,000
5. Use VLOOKUP to find the Sales of Notebook.
6. Use HLOOKUP (after transposing the table) to get the Quantity of Phone.
7. Use TRANSPOSE to convert the product list vertically to horizontally.
Part C – Graphs
1. Create a Bar Chart showing Product vs Sales.
2. Add Data Labels and Chart Title = “Product Sales Performance”.
Part D – Pivot Table
1. Insert a Pivot Table to show:
o Category-wise Total Sales.
o Region-wise Total Quantity.
Part E – Pivot Chart
1. Create a Pivot Chart from the Pivot Table showing Category vs Total Sales.
2. Format with different colors for each category.