Practical Questions on MS Excel
1. Data Entry and Formatting: Enter a list of 10 products, their quantities, and prices into an Excel sheet. Format the
table to show currency symbols, apply borders, and use bold headers.
2. Cell Referencing: What is the difference between relative, absolute, and mixed cell referencing? Use an example to
show how $A$1, A1, and A$1 behave when copying formulas to different cells.
3. Basic Arithmetic Operations: Given the sales data of five products, calculate the Total Sales, Average Sales,
Maximum Sales, and Minimum Sales using basic functions like SUM(), AVERAGE(), MAX(), and MIN().
4. Sorting and Filtering: Enter a list of 20 employees with columns for Name, Age, and Department. Sort the data in
ascending order based on Age and apply a filter to show only employees from a specific department.
5. IF Function: Given a list of students and their scores, use the IF() function to assign 'Pass' or 'Fail' to each student. A
score of 50 or more is considered passing.
6. VLOOKUP Function: Create a price list for 10 items in one sheet and a separate purchase order sheet. Use the
VLOOKUP() function to retrieve the price of each item automatically when entering the item name in the purchase order.
7. Nested IF Function: Using the same student data as in question 5, assign grades (A, B, C, D, Fail) based on the
following conditions:
- A: Score greater than or equal to 80
- B: Score greater than or equal to 60 but less than 80
- C: Score greater than or equal to 50 but less than 60
- D: Score greater than or equal to 40 but less than 50
- Fail: Score less than 40
8. SUMIF Function: You are given a list of sales transactions, each with a region (East, West, North, South) and the
sales amount. Use the SUMIF() function to calculate the total sales for the 'East' region.
9. Pivot Table: Create a pivot table from a dataset of sales orders with columns for Product, Region, Salesperson, and
Amount. Analyze the total sales for each region and each product.
10. Data Validation: Create a spreadsheet for entering employee data. Use Data Validation to ensure the 'Age' column
only accepts numbers between 18 and 60. Additionally, restrict the 'Department' column to a predefined list of
departments.
11. Conditional Formatting: In a sales report, highlight cells with sales amounts greater than $10,000 using conditional
formatting. Additionally, apply a color scale to visualize different levels of sales.
12. Chart Creation: Using a dataset with monthly sales figures for three products, create a line chart to display the sales
trend over time. Then, create a pie chart to show the proportion of total sales by product.
13. INDEX-MATCH Function: Use the INDEX() and MATCH() functions to retrieve data from a table based on a given
row and column criteria (e.g., finding the price of an item based on its name and category).
14. TEXT Function: Given a column of dates, use the TEXT() function to format the dates into different formats like
'MM/DD/YYYY' and 'Month YYYY'.
15. DATE and TIME Functions: Given a start date and a number of days, use the WORKDAY() function to find the end
date, excluding weekends and public holidays.
16. Concatenation: Combine first and last names from two columns into a single full name column using both the
CONCAT() or & operator.
17. Removing Duplicates: You are provided with a list of customer email addresses, some of which are duplicates.
Remove the duplicates while keeping one instance of each unique email.
18. Protecting Worksheets: Create a worksheet that allows only certain cells (like data entry fields) to be editable.
Protect the rest of the worksheet so that no changes can be made to it.
19. Recording a Macro: Record a macro to automate a repetitive task (e.g., formatting a table, applying filters, and
generating a report). Assign a shortcut key to run the macro.
20. Goal Seek: Given a formula for profit that subtracts costs from revenue, use Goal Seek to determine what the sales
price should be in order to achieve a target profit.
21. Budget Tracker: Design a simple personal budget tracker where users can input their expenses under various
categories (e.g., food, entertainment, utilities). Calculate the total expenses and visualize them using a bar chart.
22. Loan Payment Calculation: Use the PMT() function to calculate the monthly payments for a loan given the loan
amount, interest rate, and loan period.
23. Sharing and Collaboration: Show how to share a workbook with others and track changes made by different users.
24. Importing Data: Import a dataset from a CSV file into Excel, clean the data (e.g., remove empty rows, fix data types),
and then analyze it using Pivot Tables.
