Data Analysis with Excel & Power BI
Beginner → Pro
Practice-ready
Bangla toggle
Default language: English · Toggle for casual Bangla
What you'll learn
- Excel Basics: formulas, tables, sorting, filtering
- Lookup & Logic:
IF,XLOOKUP, text/date tricks - Analysis: Pivot Tables, charts, data cleaning
- Power BI: Get Data, Power Query, visuals, publishing
- DAX: measures for KPIs (Revenue, AOV, YoY growth)
Level 1 — Excel Basics
A. Build your first table
Create a table like this (or open students_marks.csv):
| Student | Subject | Marks |
|---|---|---|
| Arafat | Math | 75 |
| Samira | English | 65 |
| Rafi | Science | 82 |
| Esha | Math | 55 |
| Mahin | English | 92 |
Task 1:
Tip: Press Ctrl+1 to format numbers.
=SUM(C2:C6)
Total marks
=AVERAGE(C2:C6)
Average marks
B. Sort & Filter
Turn range into a Table: Ctrl+T → check “My table has headers”. Now use header arrows to filter.
C. Must-know formulas
SUM,AVERAGE,MAX,MINROUND(number, digits),TEXT(value,"0.00")- Date:
TODAY(),MONTH(date),YEAR(date)
Level 2 — Logic & Lookup
A. Pass/Fail with IF
In D2:
=IF(C2>=60,"Pass","Fail")
then fill down
B. XLOOKUP (better than VLOOKUP)
Open sales_detail.csv and products_lookup.csv. Suppose you only have SKU and want ProductName/Category.
=XLOOKUP([@SKU], Products[SKU], Products[ProductName])
Product name
=XLOOKUP([@SKU], Products[SKU], Products[Category])
Category
C. Useful text & date tricks
PROPER("mahIN")→ Mahin,TRIM(" AAZ ")→ AAZDATEVALUE("2025-06-30"),TEXT(A2,"yyyy-mm")→ nice month labels
Level 3 — Analysis in Excel
A. Clean data fast
- Remove Duplicates: Data → Remove Duplicates
- Find/Replace: Ctrl+H
- Split columns: Data → Text to Columns
B. Pivot Table (your best friend)
- Select the Sales table → Insert → PivotTable
- Place OrderDate in Rows (Excel will auto group by month) and Revenue in Values (Sum)
- Add Category to Columns to compare categories by month
Task 3: Create a Pivot: Month vs Category (Sum of Revenue). Add a Slicer for Region.
C. Charts that matter
- Column/Line for monthly trend
- Stacked column for Category mix
- KPI Card (big number): just format a cell with the main metric
Level 4 — Power BI (from zero)
A. Get Data
- Power BI Desktop → Get Data > Text/CSV → load
sales_detail.csv - Repeat for
products_lookup.csv - Model view: relate Sales[SKU] → Products[SKU] (Many-to-One)
B. Power Query cleaning
- Home → Transform Data (Power Query)
- Change OrderDate type to Date; split OrderDate into Year, Month (Add Column → Date → Year/Month)
- Trim & Clean text columns (Transform → Format)
- Close & Apply
C. Core visuals
- Clustered Column: Axis = Month, Value = Revenue
- Stacked Column: Axis = Month, Legend = Category, Value = Revenue
- Card: Field = Total Revenue (measure below)
- Slicers: Region, PaymentMethod, Category
D. DAX – essential measures
Total Revenue = SUM(SalesDetail[Revenue])
Total Orders = DISTINCTCOUNT(SalesDetail[OrderID])
AOV = DIVIDE([Total Revenue],[Total Orders])
Revenue Last Year = CALCULATE([Total Revenue], DATEADD('SalesDetail'[OrderDate], -1, YEAR))
YoY % = DIVIDE([Total Revenue]-[Revenue Last Year],[Revenue Last Year])
E. Publish & share
- Home → Publish (sign in to Power BI Service)
- Get a share link or embed in your site/app (respect access rules)
Level 5 — Pro Practice & Portfolio
A. Business-style report
- KPIs: Revenue, Orders, AOV, Return % (if you add a returns table later)
- Trends: Revenue by Month, Region map, Payment mix
B. Scenario prompts
- “Which region grew the fastest last month?”
- “Which category has the highest AOV?”
- “What’s our discount impact on revenue?”
Portfolio Task: Export a PDF of your dashboard + 3 bullet insights. Add to your LinkedIn/website.
Progress
Made for Scaled learners • Keep practicing with the sample files above.
