Week 1 of the Leep Talent Data Technician Skills Bootcamp (Level 3)
This section showcases the Excel skills I developed during the first week of my bootcamp, working across real-world datasets covering retail sales, student performance, HR payroll, and bike sales.
| Task | Topic | Skills |
|---|---|---|
| Day 2 · Task 1 | Retail Sales Analysis | Tables, Filters, SUM, AVERAGE |
| Day 2 · Task 2 | Student Performance Analysis | Sorting, MAX, Conditional Formatting |
| Day 2 · Task 3 | Student Marks Dashboard | Dashboard Design, Cell Referencing |
| Day 3 · Task 1 | Bike Sales Pivot Table | PivotTables, Data Grouping |
| Day 3 · Task 2 | SWITCH Function & Pivot Table | SWITCH, PivotTables, Categorisation |
| Day 3 · Task 3 | Data Visualisation | Line Chart, Column Chart, Pie Chart |
Dataset: retail_sales_dataset.xlsx
File: retail_sales_dataset_Master.xlsx
A retail transaction dataset containing 1,000 records across columns including Transaction ID, Date, Customer ID, Gender, Age, Product Category (Beauty, Clothing, Electronics), Quantity, Price per Unit, Total Sales, and a calculated Commission column. The dataset spans a full calendar year and reflects a diverse customer base aged 18–64.
In my own words: This dataset captures a year's worth of retail transactions across three product categories, giving enough variety to practise real data preparation and summary calculations a retail analyst would perform daily.
Organisation type: Online retailer or high-street retail chain
A business like this needs commission summaries and sales totals to manage staff incentives, track category performance, and plan inventory. Without accurate aggregation, bonus payments and stock decisions would be based on incomplete figures.
- Structured the data — Converted columns A–H into a named Excel Table using
Ctrl+T, making the data filterable and formula-aware across future updates. - Filtered and sorted — Applied a filter to sort the
Agecolumn from largest to smallest to identify the oldest customer segment at a glance. - Calculated commission total — Used the
SUMfunction in cellP10to total all commission values across the dataset. - Calculated average commission — Used the
AVERAGEfunction in cellP11to find the mean commission per transaction.
Screenshot 1 — Named Table (Columns A–H structured as an Excel Table)
Screenshot 2 — Age Filter (Sorted Largest to Smallest)
Screenshot 3 — SUM Function in Cell P10
Screenshot 4 — AVERAGE Function in Cell P11
- Finding 1: The commission total across all 1,000 transactions sits at approximately £6,840, with an average commission per transaction of around £6.84 — reflecting the flat 1.5% rate applied to all sales.
- Finding 2: The oldest age group (64) has a notable spread across all three product categories, suggesting the dataset does not skew toward a younger demographic and is representative of a broad customer base.
What this means for the business: With commission totals calculable at a glance, a retail manager can reconcile staff incentive payments weekly without manually tallying individual rows — reducing both time and error.
This task demonstrates that I can structure raw data professionally, apply core Excel functions accurately, and produce the kind of reliable summary figures a retail analyst would be expected to deliver as a first step in any data workflow.
Dataset: student.csv
A classroom dataset recording student IDs, names, and marks across multiple subjects. The dataset was used to identify top performers, calculate averages, and apply conditional formatting to highlight score distributions.
In my own words: This dataset gives a snapshot of student academic performance across subjects, making it a useful exercise in ranking, aggregation, and visual data highlighting — skills that apply directly to any HR or performance-tracking context.
Organisation type: School, college, or training provider
Educational institutions need to identify high and low performers quickly to inform intervention strategies, report to governors, or allocate additional support resources. A data technician would be expected to surface these insights clearly and without manual row-by-row inspection.
- Filtered and sorted — Applied filters to display the best-performing student in each subject by sorting scores from highest to lowest per column.
- Calculated averages — Used the
AVERAGEfunction across each student's marks to populate a newAveragecolumn (Column E). - Found the highest score — Used the
MAXfunction in Column F to identify the single highest mark recorded across all subjects. - Ranked by average — Re-sorted the dataset by the Average column to identify the overall top student.
- Ranked by highest score — Re-sorted by the highest individual score to surface the strongest single-subject performer.
- Applied conditional formatting — Used colour scales to visually distinguish the highest and lowest average scores across the class.
Screenshot 1 — Filter Applied: Best Student per Subject
Screenshot 2 — AVERAGE Formula in Column E
Screenshot 3 — MAX Function in Column F
Screenshot 4 — Conditional Formatting: Highest and Lowest Averages
- Finding 1: The
MAXfunction immediately surfaced the highest individual score, identifying which student excelled in a particular subject without manually scanning rows. - Finding 2: Conditional formatting made the distribution of performance visible at a glance — revealing whether the class performance was clustered or spread, which impacts teaching decisions.
What this means for the business: A school administrator can use this view to identify students who need targeted support or recognition within seconds, rather than reviewing spreadsheets manually. This kind of quick insight is what data tools exist to deliver.
This task shows I can clean, sort, and analyse tabular data using core Excel functions, and that I understand how to make data readable for a non-technical audience through formatting — a key skill for any analyst presenting work to stakeholders.
Dataset: student.csv
The same student marks dataset used in Task 2, extended into a simple dashboard that brings together multiple functions and visual elements into a single view.
Organisation type: School or training provider
A dashboard gives a teacher or head of year an at-a-glance view of class performance without navigating multiple worksheets. Dashboards are the standard deliverable format when presenting data to a non-technical audience.
- Built a summary dashboard — Combined AVERAGE, MAX, and cell referencing to pull key figures into a clean summary area.
- Used cell referencing — Dynamically linked the Top Student ID and Name from the data to the dashboard using cell references rather than hardcoded values, so the dashboard updates automatically when data changes.
- Sorted to surface top performers — Sorted the data so the highest-scoring student appeared at the top of the ranked list visible in the dashboard view.
Screenshot 1 — Student Marks Dashboard Overview
Screenshot 2 — All Functions Visible in Formula Bar
- Finding 1: Using cell references rather than hardcoded values means the dashboard remains accurate even when new data is added — this is good practice that separates a reliable dashboard from a one-off report.
What this means for the business: A teacher can update marks and immediately see the rankings refresh — no reformatting or manual updates needed.
This task demonstrates that I can move beyond individual functions and think about how to present data in a format that is useful to an end user — a core part of the analyst role.
Dataset: Day_3_Task_1_Bike_Sales_Pivot_Lab_to_use.xlsx
File: Day_3_Task_1_Bike_Sales_Pivot_Lab_to_use.xlsx
A global bike sales dataset covering multiple countries (Australia, Canada, France, Germany, United Kingdom, United States), with fields including Order Quantity, Unit Cost, Unit Price, Profit, Cost, and Revenue. Customer demographics include Age Group and Gender, enabling multi-dimensional analysis via PivotTables.
In my own words: This dataset gives a multi-country view of bike sales profitability broken down by customer demographics — the kind of data a retail chain or manufacturer would use to understand which markets and segments are most valuable.
Organisation type: Sports retailer or product manufacturer with international sales
A business selling products across multiple countries needs to know which markets are generating profit vs. just volume. PivotTables allow an analyst to slice this data quickly by country, age group, and gender without writing a single formula.
- Created a PivotTable — Inserted a PivotTable from the Bike Sales data, placing
Countryin Rows andAge Groupin Columns, withProfitas the summarised value. - Reviewed the output — Examined the initial table to understand which countries had data in each age group category.
- Rearranged the table — Experimented with swapping rows and columns to see the data from different angles.
- Refined and filtered — Applied filters to isolate specific age groups and countries.
- Created a PivotChart — Generated a chart directly from the PivotTable to visualise profit by market segment.
- Switched to Profit view — Amended the value field from Sales to Profit to shift the analysis from volume to value.
| Question | Answer |
|---|---|
| In which markets does Germany have customers? | Germany only has customers in the Adults (35–64) age group |
| What country has sales in all markets? | Australia and the United Kingdom — they are the only countries with non-zero sales across all age groups |
| Most profitable segment? | Adults aged 35–64, particularly women in the United States and Australia, generate the highest profit figures |
| Any other findings? | Australia has the broadest demographic reach, with customers in every age group |
Screenshot 1 — Initial PivotTable Created
Screenshot 2 — PivotTable Refined (Profit View)
Screenshot 3 — PivotChart
- Finding 1: Adults aged 35–64 are the most profitable customer segment across almost all markets, with women in Australia and the US contributing the most profit.
- Finding 2: Germany has sales exclusively in the 35–64 age group, suggesting either a targeted market focus or a gap in reaching younger demographics.
What this means for the business: A bike retailer could use this analysis to prioritise marketing spend toward the 35–64 female demographic in high-value markets, and to consider whether Germany's limited demographic reach represents a growth opportunity.
This task shows I can use PivotTables to answer specific business questions quickly and accurately — a core skill for any data analyst role where stakeholders expect rapid insight from large datasets.
Dataset: Day_3_task_2_Switch__PivotTable-_Task_2_to_use.xlsx
File: Day_3_task_2_Switch__PivotTable-_Task_2_to_use.xlsx
A sales performance dataset tracking product sales volumes (Laptops, Smartphones, Printers) across six English counties (Yorkshire, Cornwall, Lancashire, Essex, Durham, Greater Manchester). The dataset required data cleaning before analysis — specifically removing trailing spaces in the Sales Volume column and confirming numeric data types.
In my own words: This compact dataset captures regional product performance in England and needed light cleaning before analysis — a realistic reflection of how data often arrives in practice, where small formatting issues block calculations.
Organisation type: Technology distributor or regional sales team
A company distributing products across UK regions needs to know which areas are performing strongly and which products are underperforming. Categorising sales volume into High/Medium/Low makes this accessible to non-technical stakeholders who don't need to read raw numbers.
- Cleaned the data — Removed trailing spaces from the Sales Volume column and confirmed the values were stored as numbers rather than text, so calculations would work correctly.
- Applied the SWITCH function — Added a new
Product Categorycolumn using the formula=SWITCH(TRUE, C2>600, "High", C2>=300, "Medium", "Low")to classify each row by sales volume. - Created a PivotTable — Summarised sales by County (Rows) and Product (Columns) using Sales Volume as the aggregated value.
- Filtered for High performers — Applied a filter to show only High-volume rows, making it easy to identify the strongest county-product combinations.
=SWITCH(TRUE, C2 > 600, "High", C2 >= 300, "Medium", "Low")
Screenshot 1 — SWITCH Function Applied (Product Category Column)
Screenshot 2 — PivotTable: Sales by County and Product
Screenshot 3 — Filtered View: High Sales Volume Only
- Finding 1: Essex (Printers, 800) and Cornwall (Laptops, 700) are the highest-volume county-product combinations, both categorised as "High" — these are the markets a sales team would prioritise.
- Finding 2: Smartphones underperform relative to Laptops and Printers across most counties, with only Greater Manchester reaching the Medium threshold.
What this means for the business: A regional sales manager can use this categorised view to focus resources on High-volume areas and investigate why Smartphones are underperforming — without needing to interpret raw numbers.
This task demonstrates that I can clean data before analysis, apply logical functions to generate new categorical fields, and combine those fields with PivotTables to surface actionable business insights — a complete mini-analysis workflow.
Dataset: Day_3_Task_3_Bike_Sales_Visualisations_Lab_Task_3.xlsx
File: Day_3_Task_3_Bike_Sales_Visualisations_Lab_Task_3.xlsx
A structured bike sales dataset containing three pre-built worksheets designed for visualisation practice: Revenue and Profit by Year (2017–2021), Product Revenue by Country, and Revenue by Age Group. The dataset was used to produce three distinct chart types following a structured lab brief.
In my own words: This dataset is structured specifically to demonstrate how different chart types suit different kinds of data — trends over time, category comparisons, and proportional breakdowns — which maps directly to how analysts choose visuals in real reporting.
Organisation type: Sporting goods manufacturer or cycling retailer
Visualisations are the primary way a data analyst communicates findings to non-technical stakeholders. Choosing the right chart type for the right data is as important as the analysis itself — a poorly chosen chart obscures the message, even when the underlying data is correct.
Part 1 — Line Chart (Revenue vs. Profit, 2017–2021)
- Selected the
Revenue and Profit by Yearworksheet (cells A3:C8) - Inserted a Line with Markers chart
- Formatted the vertical axis to display USD currency with zero decimal places
- Added the chart title "Revenue vs. Profits"
- Renamed legend entries to "Annual Profit" and "Annual Revenue"
- Repositioned the legend to the right
- Added axis titles: "Year" (horizontal) and "US Dollars" (vertical)
Part 2 — Column Chart (Product Revenue by Country)
- Selected the
Product Revenue by Countryworksheet (cells A3:E10) - Inserted a Stacked Column chart
- Added chart title "Product Revenue by Country"
- Formatted vertical axis as Currency with zero decimal places
- Repositioned legend to the right
- Added axis titles: "Country" (horizontal) and "US Dollars" (vertical)
Part 3 — Pie Chart (Revenue by Age Group)
- Selected the
Revenue by Age Groupworksheet (cells A3:B7) - Inserted a 2D Pie chart
- Added chart title "Revenue Comparison by Age Group"
- Repositioned legend to the right
- Added data labels showing Category Name and Percentage
Screenshot 1 — Line Chart: Revenue vs. Profits (2017–2021)
Screenshot 2 — Column Chart: Product Revenue by Country
Screenshot 3 — Pie Chart: Revenue Comparison by Age Group
- Finding 1: Revenue and profit both increased consistently from 2017 to 2021, with revenue growing from approximately $10M to $30M — indicating strong and sustained business growth over the five-year period.
- Finding 2: Adults aged 35–64 account for 50% of total revenue by age group, with Young Adults (25–34) at 36% — together these two segments represent 86% of all revenue, making them the clear priority demographic.
What this means for the business: A marketing team can use the age group breakdown to focus campaign spend on the 25–64 age range, which accounts for the vast majority of revenue. The year-on-year revenue trend also gives leadership confidence to invest in expansion.
This task shows I understand which chart type suits which data story — trends use line charts, category comparisons use bar or column charts, and proportional breakdowns use pie charts. Selecting and formatting charts appropriately is a skill employers look for when analysts need to present findings to a board or management team.
- Microsoft Excel 365 (Online and Desktop)
- Functions:
SUM,AVERAGE,MAX,SWITCH - Features: Excel Tables, Filters, Sorting, Conditional Formatting, PivotTables, PivotCharts, Data Labels, Axis Formatting
| File | Description | Source |
|---|---|---|
retail_sales_dataset_Master.xlsx |
1,000 retail transactions with commission | Bootcamp (Kaggle) |
student.csv |
Student marks across multiple subjects | Bootcamp |
Human_Resources-v1.xlsx |
Payroll and headcount data | Bootcamp |
Day_3_Task_1_Bike_Sales_Pivot_Lab_to_use.xlsx |
Global bike sales with profit/revenue | Bootcamp (Kaggle) |
Day_3_task_2_Switch__PivotTable-_Task_2_to_use.xlsx |
English county product sales volumes | Bootcamp |
Day_3_Task_3_Bike_Sales_Visualisations_Lab_Task_3.xlsx |
Bike sales structured for visualisation | Bootcamp (Kaggle) |