Embedded Files
Turning raw spreadsheets into structured, queryable tables, then summarising them into PivotTables and charts that answer a specific business question.
What I did
What I did
Converted flat ranges into structured Excel Tables so formulas and PivotTables stay accurate as the data grows.
Cleaned hidden trailing spaces from numeric fields and confirmed data types before aggregating — text that looks numeric breaks a PivotTable silently, with no error raised.
Calculated totals and averages with SUM, AVERAGE and MAX, then extended the analysis independently using AVERAGEIF to compare average sale value by customer gender.
Banded regional sales volume into High / Medium / Low with SWITCH(TRUE, ...). Condition order carries the logic — reversing two thresholds returns the wrong band silently, with no error.
Built PivotTables across gender, age group, country and product category, and grouped transaction dates up to Year for an annual view.
Created PivotCharts — stacked columns for revenue by market, a pie chart with percentage labels for revenue share, and a dual-series line chart tracking revenue against profit across 2017 to 2021.
Applied conditional formatting to flag the highest and lowest values at a glance.
Findings
Findings
The United States is the most profitable market — £13.9m of £42.1m total profit, roughly a third of the whole, and the leading market in every age group and both genders.
Revenue concentrates heavily in Adults aged 35 to 64 at 50% and Young Adults aged 25 to 34 at 36%. The Seniors segment is effectively absent at under 1%.
Average sale value by gender differed by £1.12 — £455.43 against £456.55. Worth reporting as no meaningful difference rather than as a finding.
Repository
Repository
Page updated
Google Sites
Report abuse