Advanced Microsoft Excel Study Guide
This guide focuses on advanced Excel skills using practical examples across supermarket,
school, and hospital data. Each section includes data entry tasks, formula explanations, and
exercises.
Section 1: Supermarket Data
Data Entry Example:
Product | Quantity | Price | Total
Apple | 10 | 2.5 | =B2*C2
Banana | 20 | 1.5 | =B3*C3
Task:
1. Enter the data into an Excel sheet.
2. Use a formula to calculate the total price of each product.
3. Calculate the grand total using SUM.
Key Formulas:
1. SUM: Add all the total prices. Example: =SUM(D2:D10)
2. IF: Identify expensive items. Example: =IF(C2>2, 'Expensive', 'Cheap')
3. VLOOKUP: Search for a product by ID. Example: =VLOOKUP(101, A2:D10, 2, FALSE)
Exercise 1:
Calculate the total sales, identify expensive items, and find product details using formulas.
Section 2: School Data
Example data:
Student Name | Math | Science | Average
John Doe | 85 | 90 | =(B2+C2)/2
Key Formulas:
1. AVERAGE: Calculate student averages. Example: =AVERAGE(B2:D2)
2. MAX/MIN: Find highest and lowest marks. Example: =MAX(B2:D2), =MIN(B2:D2)
Exercise 2:
Analyze student performance, find top scorers, and highlight those scoring below 50 using
conditional formatting.
Section 3: Hospital Data
Example data:
Patient ID | Admission Fee | Medicine Fee | Total Cost
1001 | 500 | 200 | =B2+C2
Key Formulas:
1. CONCATENATE: Combine patient names and IDs. Example: =CONCATENATE(A2, ' - ', B2)
2. IF: Flag patients with bills above $500. Example: =IF(D2>500, 'High', 'Normal')
Exercise 3:
Calculate total costs for patients, categorize based on fees, and create a chart showing fee
distribution.
Advanced Topics:
1. Pivot Tables
2. Data Validation
3. Conditional Formatting
4. Advanced Charting
