Scribd
Upload
9 views4 pages

Advanced Excel Test for Data Analysts

The document outlines an advanced Excel practical test for data analysts in a retail company, focusing on sales performance, employee records, and product information. It includes tasks involving date functions, VLOOKUP, XLOOKUP, INDEX + MATCH, and Pivot Tables, with specific requirements for each task. The test is designed to assess the ability to create dynamic calculations and automated outputs using Excel's advanced functions within a 90-minute timeframe.

Uploaded by

bba25f35
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
9 views4 pages

Advanced Excel Test for Data Analysts

The document outlines an advanced Excel practical test for data analysts in a retail company, focusing on sales performance, employee records, and product information. It includes tasks involving date functions, VLOOKUP, XLOOKUP, INDEX + MATCH, and Pivot Tables, with specific requirements for each task. The test is designed to assess the ability to create dynamic calculations and automated outputs using Excel's advanced functions within a 90-minute timeframe.

Uploaded by

bba25f35
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Advanced Excel Practical Test

Level: Hard / Application-Based


Time Allowed: 90 Minutes
Total Marks: 50
Instructions:

 Use Microsoft Excel.


 Do not hardcode results; use formulas only.
 Proper formatting is required where applicable.
 All calculations must be dynamic.

Scenario Overview (Business Context)


You are working as a Data Analyst in a retail company. The company wants to analyze sales
performance, employee records, and product information using Excel’s advanced functions.

You are provided with raw data, and management requires automated outputs using Date
Functions, VLOOKUP, XLOOKUP, INDEX, MATCH, and Pivot Tables.

Task 1: Date Functions (10 Marks)


Dataset (Sheet Name: Employee_Data)

Emp ID Name Join Year Join Month Join Day


E101 Ali 2022 6 15
E102 Sara 2023 1 10
E103 Ahmed 2021 11 25
E104 Hina 2020 3 5

Requirements:

A. Generate Joining Date

 In column F, generate the Joining Date using the DATE() function.


 Format the date as DD-MMM-YYYY.

B. Calculate Service Duration


 In column G, calculate total days of service from joining date till today using:
o TODAY()
o DAYS()

C. System Timestamp

 In cell I2, display the current date and time using the NOW() function.
 Mention whether this value is static or dynamic (comment in cell I3).

Task 2: VLOOKUP – Employee Grade System (8 Marks)


Dataset (Sheet Name: Grade_Table)

Grade Code Grade Bonus (%)


G1 A 20%
G2 B 15%
G3 C 10%
G4 D 5%

Additional Data (Employee_Data Sheet)

Emp ID Grade Code


E101 G1
E102 G2
E103 G3
E104 G2

Requirements:

 Use VLOOKUP to:


o Fetch Grade
o Fetch Bonus Percentage
 Ensure exact match is used.
 Handle errors gracefully if an invalid Grade Code is entered.

Task 3: XLOOKUP – Product Sales Analysis (10 Marks)


Dataset (Sheet Name: Products)
Product ID Product Name Unit Price
P101 Laptop 1200
P102 Mouse 25
P103 Keyboard 45
P104 Monitor 300

Sales Entry (Sheet Name: Sales)

Sale ID Product ID Quantity


S001 P103 10
S002 P101 2
S003 P105 5

Requirements:

 Use XLOOKUP to:


o Retrieve Product Name
o Retrieve Unit Price
 If Product ID does not exist, display "Invalid Product".
 Calculate Total Sale Amount = Quantity × Unit Price.

Task 4: INDEX + MATCH – Flexible Lookup (10 Marks)


Management wants a dynamic lookup system instead of VLOOKUP.

Requirements:

 Create a lookup cell where the user enters:


o Employee Name
o Required Field (Grade or Bonus %)
 Use INDEX + MATCH combination to:
o Return the correct value dynamically.
 The formula must work even if columns are rearranged.

Task 5: Pivot Table – Sales Performance Summary (12


Marks)
Dataset (Sheet Name: Regional_Sales)

Region Product Sales


North Laptop 3000
South Laptop 4500
East Mouse 800
West Keyboard 1200
North Mouse 600
South Keyboard 1500
East Monitor 2000

Requirements:

 Create a Pivot Table in a new worksheet.


 Show:
o Total Sales by Region
 Then modify Pivot Table to show:
o Region-wise Sales by Product
 Apply:
o Currency formatting
o Clear headings

Bonus Challenge (Optional – 5 Marks)


 Combine XLOOKUP + DATE + TODAY to:
o Identify employees with service greater than 1000 days
o Display status as "Senior Staff" or "Junior Staff"

You might also like