Q.
You are provided with the following sample dataset containing sales data for a retail
company:
Product ID Product Name Region Category Sales (₹)
101 Laptop North Electronics 50,000
102 Smartphone South Electronics 30,000
103 Headphones East Accessorie 5,000
s
104 Printer West Electronics 20,000
105 Mouse North Accessorie 1,000
s
Perform the following tasks:
a) Use the VLOOKUP function to retrieve the product name for Product ID 103.
b) Create a Pivot Table to summarize total sales by region and product category.
c) Apply COUNTIF to count the number of sales transactions above ₹10,000.
d) Write a brief explanation of the importance of Pivot Tables in data analysis.
Q. 2
a) Explain the difference between Relative Reference and Absolute Reference in Excel
with examples.
b) Use the AVERAGEIF function to calculate the average sales for the Electronics
category.
Sample Data:
Use the dataset from Q.1.
Q. 3
a) Describe the steps to create a Pivot Table in Excel.
b) Apply the IF function to classify sales transactions as "High" or "Low" based on a
threshold of ₹15,000.
Sample Data:
Use the dataset from Q.1.
Q. 4
a) Discuss the significance of COUNTIFS function in Excel with an example.
b) Use the CONCAT function to combine Product Name and Region into a single
column.
Sample Data:
Use the dataset from Q.1.
Q. 5
a) Explain the concept of Excel Formulas and provide an example of a formula using
arithmetic operators.
b) Use the COUNTBLANK function to identify missing data in a dataset.
Sample Data:
A B C
1 3
2 4
5 6
Q. 6
a) Excel Syntax
b) Excel Ranges
c) Excel Arithmetic Operators
d) Excel Parentheses
Q. 7
You are provided with the following sample dataset containing employee performance
data:
Employee ID Name Department Rating Scor
e
001 John Sales 4 85
002 Alice HR 3 70
003 Bob Sales 5 90
004 Emma IT 2 60
005 Mike Sales 4 80
Perform the following tasks:
a) Use the AVERAGEIFS function to calculate the average score for employees in the
Sales department with a rating above 3.
b) Create a Pivot Table to summarize average scores by department and rating.
c) Apply COUNTIF to count the number of employees with a score above 80.
d) Write a brief explanation of the importance of AVERAGEIFS in data analysis.
Q. 8
a) Explain the concept of Excel Ranges and provide an example of how to use them in
a formula.
b) Use the SUMIF function to calculate the total scores for the Sales department.
Sample Data:
Use the dataset from Q.7.
Q. 9
a) Describe the steps to use the IFS function in Excel with an example.
b) Apply the LEFT function to extract the first three characters from the Employee ID
column.
Sample Data:
Use the dataset from Q.7.
Q. 10
a) Discuss the significance of COUNTBLANK function in Excel with an example.
b) Use the TRIM function to remove irregular spacing from a dataset.
Sample Data:
A B C
John Sale
s
Alice HR
Bob Sale
s
Q. 11
a) Explain the concept of Excel Formulas and provide an example of a formula using
arithmetic operators.
b) Use the COUNTA function to count the number of non-empty cells in a dataset.
Sample Data:
Use the dataset from Q.7.
Q. 12
Conditional Formatting
Absolute Reference
Relative Reference
Excel Parantheses
Q.13
Use the HLOOKUP function to find the rating of an employee from the following horizontal
dataset:
{"ID", "001", "002", "003"; "Rating", 4, 3, 5}
Q.14
Explain the difference between COUNTIF and COUNTIFS with examples.
Q.15
Write a formula using the IF and AND functions to check if an employee belongs to the
Sales department and has a score greater than 80.
Q.16
Using the dataset from Q.1, apply the MAXIFS function to find the highest score among
employees in the Sales department.
Q.17
Explain the use of the NOW() and TODAY() functions in Excel. Provide an example for
each.
Q.18
What is the use of TEXT function in Excel? Format today’s date as “15-Apr-2025” using
this function.
Q.19
Create a dynamic drop-down list using Data Validation for selecting department names:
Sales, HR, IT, Finance.
Q.20
Explain how to apply Conditional Formatting to highlight all cells with scores below 70 in
the dataset from Q.1.
Q.21
Write a formula using the MATCH and INDEX functions to find the score of an employee
named "Bob" from the dataset in Q.1.
Q.22
Demonstrate how to use the PROPER, UPPER, and LOWER functions with the name
“michael”.
Q.23
How do you protect a worksheet in Excel? Describe the steps and practical uses.
Q.24
Use the ROUND, ROUNDUP, and ROUNDDOWN functions to round the number 78.65 to the
nearest whole number, next whole number, and previous whole number respectively.
Q.25
Explain the difference between workbook and worksheet in Excel with examples.
