EXCEL SKILLS ASSIGNMENT
Overview
In this assignment, you will build a sales dataset from scratch using Excel formulas and use it to practice essential
Excel skills, from data creation to analysis and visual reporting. You will work with lookup functions, revenue
calculations, age derivation, and pivot table analysis.
Part 1: Create the Dataset
Create a sales table with 100 rows of data. All data in the Country, Order Date, Date of Birth, Category, and
Revenue columns must be generated using a formula, no manual typing of values.
Column Name Data Type Values / Range
SL Serial numbers 1 to 100
Country Text Sweden, France, UK, Portugal, Spain
Order Date Date Year 2020 to 2025
Customer DOB Date Year 1970 to 2015
Category Text Books, Games, Clothing, Beauty, Electronics
Revenue (USD) Number $11 to $30
Customer Age Calculated Derived from Date of Birth using a formula
Year Calculated Extracted from Order Date using a formula
Note: Use a formula that generates random whole numbers within a defined range to fill the Revenue column and
to drive the random selection of Country and Category values.
Part 2: Lookup Functions (optional)
Using your dataset, create a separate reference table and demonstrate all three of the following lookup formulas.
Each should retrieve data from the reference table based on a search value.
Function Description
VLOOKUP Searches a column vertically to find a matching value and returns data from another column in the
same row.
Hint: search the serial number based on country name.
Part 3: SUM Formula
Use the SUM formula to calculate the total revenue from all 100 orders. Additionally, calculate the total revenue
broken down by:
- Country: total revenue per country using SUMIF
- Category: total revenue per product category using SUMIF
- Include the count of orders and average revenue for each group
Part 4: Calculate Customer Age
Add a new column called Customer Age to the dataset. Calculate each customer's current age based on their
Date of Birth column using an appropriate date formula. The age should update automatically based on today's
date.
Part 5: Pivot Table
Create a Pivot Table (on a separate sheet) to answer the following business questions. Rank the results where
applicable.
Analysis Description
Top Performing Country Which country generates the highest total revenue?
Top Selling Product Which product category brings in the most revenue?
Year-wise Revenue How much revenue was earned each year from 2020 to 2025?
Slicer for Year Add a slicer linked to the Year column to filter results interactively by selecting any year.
The Pivot Table should be clearly labelled and easy to read. Add a Slicer for the Year column so results can be
filtered interactively by selecting a specific year (2020 to 2025).
Deliverables
Sheet / Section What to Include
Sales Dataset 100 rows of formula-generated data with all required columns
Lookup Functions VLOOKUP
SUM/SUMIF Formula Total revenue across all 100 orders; breakdown by country and category
Customer Age Column Age calculated from Date of Birth using DATE formula
Pivot Table Analysis Top country, top product, year-wise revenue with Year slicer
Good luck!