Excel Test (Practical – Solve in Excel)
Section A – Conditional Formatting (4 Questions)
1. You have a dataset of employee salaries in column C. Apply conditional formatting
to:
o Highlight all salaries above 80,000 in green.
o Highlight all salaries below 40,000 in red.
o Highlight the top 10% salaries using a data bar.
2. A student marks dataset contains columns: Name, Subject, Marks. Apply rules to:
o Highlight students who scored less than 35 in any subject.
o Use color scales to show low marks in red and high marks in green.
3. Sales data is given for 12 months in column B. Apply conditional formatting so that:
o The month with the highest sales appears in blue.
o The month with the lowest sales appears in orange.
4. A list of product IDs is in column A. Use conditional formatting to:
o Highlight duplicate product IDs.
o Highlight unique product IDs.
Section B – Aggregate Functions with Condition (4 Questions)
5. You are given sales data with columns: Region, Product, Sales Amount. Write
formulas to:
o Calculate the total sales for the “North” region.
o Calculate the total sales of the product “Laptop”.
o Calculate the total sales for “Laptop” in the “North” region.
6. A dataset contains columns: Employee, Department, Salary. Use formulas to:
o Find the average salary of the IT department.
o Find the maximum salary in HR department.
o Find the minimum salary in Finance department.
7. A customer dataset contains: Customer Name, City, Purchase Amount. Using
conditional aggregate functions:
o Count how many customers are from Delhi.
o Find the total purchases made in Mumbai.
o Find the average purchase amount of customers from Chennai.
8. A product sales dataset has: Product, Category, Quantity Sold. Use formulas to:
o Find how many times the product “Mobile” appears.
o Find the total quantity sold for “Electronics” category.
o Find the total sales where Category = Electronics AND Product = Mobile.
Section C – Pivot Table (4 Questions)
9. Create a Pivot Table from a dataset with fields: Region, Salesperson, Sales
Amount. Show:
o Total sales for each region.
o Sales contribution of each salesperson within their region.
10. You have sales data: Date, Product, Sales Amount. Using Pivot Table:
Group sales data by Month and show total monthly sales.
Show which product has the highest monthly sales.
11. You are given an HR dataset: Employee, Department, Gender, Salary. With a Pivot
Table:
Show the average salary by Department.
Further break it down by Gender within each Department.
12. A dataset has: Order ID, Customer, Region, Sales Amount. With Pivot Table:
Show total sales by Region.
Add a filter to display only sales made by a particular customer.
Section D – Pivot Chart (3 Questions)
13. Using a dataset with fields: Product, Region, Sales Amount, create a Pivot Table
and then a Pivot Chart to:
Compare sales by region in a Column Chart.
Add Region as a filter so the chart updates dynamically.
14. From a dataset: Month, Sales, Profit, create a Pivot Table and Pivot Chart to:
Show both Sales and Profit trends by Month.
Represent the data using a Line Chart.
15. You have a dataset of Department, Expenses, Year. Create a Pivot Table and then
a Pivot Chart to:
Show total expenses by Department.
Show the trend of expenses over years for each department in a Stacked Column
Chart.