This repository contains my Week 1 Internship Task for the Data Analyst Internship at Logic Stack, focused on Retail Sales Data Cleaning & Basic Analysis using Microsoft Excel.
The goal of this task was to take a raw, real-world retail sales dataset and walk through the complete beginner-to-intermediate Excel analytics workflow — understanding the data, cleaning and formatting it, validating its quality, applying formulas, building summary tables, creating charts and translating the numbers into clear business insights.
This is my first hands-on Data Analytics project, and it forms the foundation for the more advanced analysis work planned in the upcoming weeks of the internship.
💼 Internship: Logic Stack — Data Analysis Internship (Jun 2026)
🧩 Task: Week 1 — Retail Sales Data Cleaning & Basic Analysis in Excel
🛠️ Tool Used: Microsoft Excel
⏱️ Duration: 7 Days
| Detail | Description |
|---|---|
| 🏢 Company | Logic Stack |
| 👨💻 Role | Data Analyst Intern |
| 📅 Task | Week 1 Assignment |
| 🧠 Focus Area | Excel-based Data Cleaning & Analysis |
| 📂 Dataset | Retail Sales Transactions (1,000 records) |
Logic Stack's Data Analysis Internship is designed to build practical, job-ready data analytics skills — starting with the fundamentals of Excel before progressing into more advanced tools like SQL, Python and BI dashboards. This repository documents my Week 1 progress and deliverables.
- ✅ Beginner-friendly, real-world retail dataset (1,000 transactions)
- ✅ Full data understanding & column-level inspection
- ✅ Cleaned and properly formatted Excel Table
- ✅ Automated data quality validation using formulas
- ✅ Formula-driven KPIs (no hardcoded values)
- ✅ Category, Gender, and Age Group level breakdowns
- ✅ Native Excel charts (Bar, Column, Pie)
- ✅ Sorting & filtering demonstrations
- ✅ Clear, data-backed written observations
- ✅ Bonus: Customer Age Group segmentation
The objective of this task was to build practical fluency in:
- Opening and understanding a raw dataset before analyzing it
- Cleaning and formatting data for readability and consistency
- Performing data quality checks to catch blanks, duplicates and mismatches
- Applying core Excel formulas (
SUM,AVERAGE,MIN,MAX,COUNT,SUMIF,IF) - Using sorting and filtering to answer business questions
- Building clean, readable charts for non-technical stakeholders
- Writing simple, evidence-based observations rather than assumptions
The dataset (retail_sales_dataset.csv) contains 1,000 retail sales transactions with the following columns:
| Column Name | Description |
|---|---|
Transaction ID |
Unique ID for each transaction |
Date |
Date of the transaction |
Customer ID |
Unique ID of the customer |
Gender |
Gender of the customer |
Age |
Age of the customer |
Product Category |
Category of product purchased (Beauty, Clothing, Electronics) |
Quantity |
Number of items purchased |
Price per Unit |
Price of one unit |
Total Amount |
Total sales amount of the transaction |
flowchart LR
A[📥 Raw Dataset] --> B[🔍 Dataset Understanding]
B --> C[🧹 Data Cleaning & Formatting]
C --> D[✅ Data Quality Check]
D --> E[🧮 Basic Analysis & Formulas]
E --> F[🔽 Sorting & Filtering]
F --> G[📊 Charts & Visualization]
G --> H[📝 Observations & Insights]
In the Cleaned Data sheet, the raw dataset was transformed into an analysis-ready table:
- Converted the full data range into a native Excel Table for structured referencing and filtering
- Made all column headers bold with a clean, professional header style
- Adjusted column widths so every value is fully visible
- Formatted the
Datecolumn using a properyyyy-mm-dddate format - Formatted
Price per UnitandTotal Amountas currency - Froze the top row so headers stay visible while scrolling through 1,000 rows
- Added a Calculated Total column (
Quantity × Price per Unit) to cross-verify the data
A dedicated Data Quality Check sheet validates the dataset using live formulas:
| Check | Result |
|---|---|
| Blank cells in the dataset | ❌ None found |
| Duplicate transactions (Transaction ID) | ❌ None found |
Invalid Quantity values (≤ 0) |
❌ None found |
Invalid Price per Unit values (≤ 0) |
❌ None found |
Total Amount = Quantity × Price per Unit for every row |
✅ Matches 100% |
An Amount Check column flags every row as Correct or Check using:
=IF([@[Calculated Total]]=[@[Total Amount]],"Correct","Check")
Key metrics calculated using Excel formulas (SUM, AVERAGE, MIN, MAX, COUNT):
| Metric | Value |
|---|---|
| 💰 Total Sales Amount | $456,000.00 |
| 📈 Average Sales Amount | $456.00 |
| 🔽 Minimum Sales Amount | $25.00 |
| 🔼 Maximum Sales Amount | $2,000.00 |
| 📦 Total Quantity Sold | 2,514 units |
| 🎂 Average Customer Age | 41.4 years |
| 🧒 Youngest Customer | 18 years |
| 👴 Oldest Customer | 64 years |
| 🧾 Total Transactions | 1,000 |
| 🗂️ Unique Product Categories | 3 |
Category, Gender & Age Group breakdowns (via SUMIF):
|
Sales by Category
|
Sales by Gender
|
Bonus: Sales by Age Group
|
Three clean, business-ready charts were built in the Charts sheet:
| Chart Type | Insight Visualized | Preview |
|---|---|---|
| 📊 Bar Chart | Total Sales by Product Category |  |
| 📈 Column Chart | Total Quantity Sold by Product Category |  |
| 🥧 Pie Chart | Total Sales by Gender |  |
Each chart includes a clear title, properly labeled axes and a clean, minimal design with no unnecessary colors or effects.
The Cleaned Data sheet was sorted and filtered to answer key business questions:
- 🏆 Identified the transaction with the highest and lowest total amount
- 👗 Filtered transactions where
Product Category = Clothing - 🚺 Filtered transactions where
Gender = Female - 📦 Filtered transactions where
Quantity > 2 - ⬇️ Sorted the dataset from highest to lowest Total Amount
- 🎂 Sorted the dataset from youngest to oldest customer
Screenshots of these filtered/sorted views are available in the screenshots/ folder.
- 📱 Electronics is the highest performing sales category, generating $156,905 in total sales.
- 💄 Beauty is the lowest performing sales category, with $143,515 in total sales.
- 🚺 Female customers generated slightly higher total sales ($232,840) than male customers ($223,160).
- 🎂 The average customer age is 41.4 years, with customers ranging from 18 to 64 years old.
- 📦 A total of 2,514 items were sold across 1,000 transactions.
- 💵 The highest single transaction was $2,000, while the lowest was just $25.
- ⚖️ Average spending per transaction ($456) shows fairly consistent purchase behavior across the customer base.
- 👨🦳 The Senior Adult age group (41–60 years) contributed the highest sales among all age segments.
week-1-retail-sales-excel-analysis/
│
├── dataset/
│ └── retail_sales_dataset.csv
│
├── analysis/
│ └── Retail_Sales_Excel_Analysis.xlsx
│
├── screenshots/
│ ├── clothing-filter.png
│ ├── female-filter.png
│ ├── highest-sales-sort.png
│ ├── sales-by-category-chart.png
│ ├── quantity-by-category-chart.png
│ └── sales-by-gender-chart.png
│
├── README.md
└── LICENSE
No installation required — this is an Excel-based analysis project.
# 1. Clone the repository
git clone https://github.com/YasirAwan4831/week-1-retail-sales-excel-analysis.git
# 2. Open the analysis file in Microsoft Excel
cd week-1-retail-sales-excel-analysis/analysis
start Retail_Sales_Excel_Analysis.xlsxThen explore the sheets in order: Original Data → Cleaned Data → Dataset Understanding → Data Quality Check → Basic Analysis → Sorting & Filtering Screenshots → Charts → Observations.
Through this task, I strengthened my practical skills in:
- Structuring raw data into clean, analysis-ready Excel Tables
- Writing formula-driven (non-hardcoded) calculations for accuracy and reusability
- Validating data quality systematically rather than assuming clean data
- Using
SUMIF,IF, and lookup-style formulas for category-level analysis - Designing simple, readable, stakeholder-friendly charts
- Translating raw numbers into clear, written business observations
- Organizing and documenting a data project professionally on GitHub
- 🔄 Automate the cleaning workflow using Power Query
- 📊 Build an interactive Excel Dashboard with slicers
- 🐍 Recreate the analysis in Python (Pandas) for comparison
- 🗄️ Migrate the dataset into SQL for query-based analysis
- 📈 Add trend analysis across the
Datecolumn (monthly/seasonal patterns)
Special thanks to Logic Stack for designing a structured, hands-on Data Analyst Internship program that builds real-world Excel analytics skills from the ground up. This project is submitted as part of the Week 1 Internship Task.
This repository is shared for educational and portfolio purposes only.
The dataset and business context belong to the respective organization and are not licensed for redistribution or commercial use.
The project structure and analysis approach may be referenced for learning purposes only.
Muhammad Yasir is a Full Stack Web Developer, Data Analyst and AI Automation Enthusiast passionate about building scalable web applications, data-driven solutions, automation systems and modern software products with clean architecture and outstanding user experience.
Please consider giving this repository a Star if you found it helpful.
Crafted with precision and passion by Muhammad Yasir
Full Stack Web Developer • Data Analyst • AI & Automation Enthusiast • Open Source Contributor