🚀 Data Analyst Roadmap — Part 6
📊 Excel — Level 5: Text Functions for Data Cleaning & Transformation
As a Data Analyst, you'll rarely receive perfectly clean data.
You may encounter:
" John"
"John "
"JOHN"
"john"
"John Smith"
"John Smith"
You may also have data such as:
EMP-001-IND
Mumbai, India
john.smith@email.com
+91-9876543210
Before analyzing this data, you often need to clean, extract, combine, split, or standardize text.
That's why Excel's text functions are extremely useful.
1️⃣ TRIM()
What does it do?
TRIM() removes unnecessary spaces from text.
For example:
" John Smith "
becomes:
"John Smith"
Formula:
Why is this important?
Suppose you have:
IT
IT
IT
IT
They may look identical, but hidden spaces can cause lookup and filtering problems.
For example:
may not behave as expected if the underlying values contain unwanted spaces.
Data Analyst use cases:
Use TRIM() for:
• Customer names
• Department names
• Product names
• Country names
• Category values
2️⃣ CLEAN()
CLEAN() removes many non-printing characters from text.
Formula:
This can be useful when data is copied from:
• Websites
• External systems
• Reports
• PDFs
• Legacy applications
Sometimes invisible characters are present even though the text looks normal.
TRIM vs CLEAN:
TRIM() → Removes unnecessary spaces.
CLEAN() → Removes non-printing characters.
You can combine them:
This is a very useful basic data-cleaning pattern.
3️⃣ UPPER()
Converts text to uppercase.
Example:
india
becomes:
INDIA
Why use it?
Suppose your dataset contains:
India
india
INDIA
You can standardize them using:
Now they all become:
INDIA
4️⃣ LOWER()
Converts text to lowercase.
Example:
JOHN.SMITH@EMAIL.COM
becomes:
john.smith@email.com
This is particularly useful for standardizing:
• Email addresses
• Usernames
• IDs
• Text categories
——————————
5️⃣ PROPER()
Converts text into proper case.
Example:
john smith
becomes:
John Smith
And:
mumbai
becomes:
Mumbai
Important:
PROPER() is useful for presentation, but don't automatically use it for every dataset.
Some names, product codes, or abbreviations should remain uppercase.
For example:
IBM
SQL
USA
may become undesirable results if automatically converted to proper case.
6️⃣ LEN()
LEN() returns the number of characters in a text string.
Example:
A2 = "John"
Result:
4
Why is this useful?
It can help identify:
• Invalid IDs
• Incorrect phone numbers
• Unexpected text lengths
• Data-quality issues
For example:
You could check:
7️⃣ LEFT()
LEFT() extracts characters from the beginning of a text string.
Syntax:
Example:
EMP-001-IND
To extract the first three characters:
Result:
EMP
8️⃣ RIGHT()
RIGHT() extracts characters from the end of a text string.
Example:
EMP-001-IND
Formula:
Result:
IND
This can be useful for extracting:
• Country codes
• File extensions
• Product suffixes
• Transaction codes
9️⃣ MID()
📊 Excel — Level 5: Text Functions for Data Cleaning & Transformation
As a Data Analyst, you'll rarely receive perfectly clean data.
You may encounter:
" John"
"John "
"JOHN"
"john"
"John Smith"
"John Smith"
You may also have data such as:
EMP-001-IND
Mumbai, India
john.smith@email.com
+91-9876543210
Before analyzing this data, you often need to clean, extract, combine, split, or standardize text.
That's why Excel's text functions are extremely useful.
1️⃣ TRIM()
What does it do?
TRIM() removes unnecessary spaces from text.
For example:
" John Smith "
becomes:
"John Smith"
Formula:
=TRIM(A2)Why is this important?
Suppose you have:
IT
IT
IT
IT
They may look identical, but hidden spaces can cause lookup and filtering problems.
For example:
=XLOOKUP("IT",A2:A100,B2:B100)may not behave as expected if the underlying values contain unwanted spaces.
Data Analyst use cases:
Use TRIM() for:
• Customer names
• Department names
• Product names
• Country names
• Category values
2️⃣ CLEAN()
CLEAN() removes many non-printing characters from text.
Formula:
=CLEAN(A2)This can be useful when data is copied from:
• Websites
• External systems
• Reports
• PDFs
• Legacy applications
Sometimes invisible characters are present even though the text looks normal.
TRIM vs CLEAN:
TRIM() → Removes unnecessary spaces.
CLEAN() → Removes non-printing characters.
You can combine them:
=TRIM(CLEAN(A2))This is a very useful basic data-cleaning pattern.
3️⃣ UPPER()
Converts text to uppercase.
=UPPER(A2)Example:
india
becomes:
INDIA
Why use it?
Suppose your dataset contains:
India
india
INDIA
You can standardize them using:
=UPPER(A2)Now they all become:
INDIA
4️⃣ LOWER()
Converts text to lowercase.
=LOWER(A2)Example:
JOHN.SMITH@EMAIL.COM
becomes:
john.smith@email.com
This is particularly useful for standardizing:
• Email addresses
• Usernames
• IDs
• Text categories
——————————
5️⃣ PROPER()
Converts text into proper case.
=PROPER(A2)Example:
john smith
becomes:
John Smith
And:
mumbai
becomes:
Mumbai
Important:
PROPER() is useful for presentation, but don't automatically use it for every dataset.
Some names, product codes, or abbreviations should remain uppercase.
For example:
IBM
SQL
USA
may become undesirable results if automatically converted to proper case.
6️⃣ LEN()
LEN() returns the number of characters in a text string.
=LEN(A2)Example:
A2 = "John"
Result:
4
Why is this useful?
It can help identify:
• Invalid IDs
• Incorrect phone numbers
• Unexpected text lengths
• Data-quality issues
For example:
Employee IDs should always contain 6 characters.
You could check:
=IF(LEN(A2)=6,"Valid","Check")7️⃣ LEFT()
LEFT() extracts characters from the beginning of a text string.
Syntax:
=LEFT(text,num_chars)Example:
EMP-001-IND
To extract the first three characters:
=LEFT(A2,3)Result:
EMP
8️⃣ RIGHT()
RIGHT() extracts characters from the end of a text string.
Example:
EMP-001-IND
Formula:
=RIGHT(A2,3)Result:
IND
This can be useful for extracting:
• Country codes
• File extensions
• Product suffixes
• Transaction codes
9️⃣ MID()
❤3
MID() extracts text from the middle of a string.
Syntax:
Suppose:
EMP-001-IND
You want:
001
Use:
Result:
001
Because:
Start at character 5
Extract 3 characters
🔟 FIND()
FIND() tells you where one piece of text appears inside another.
Example:
john.smith@gmail.com
You can find the position of @:
This returns the position of the @ character.
Why is this useful?
You can use the position to extract:
• Email username
• Domain
• Product components
• Codes
• Identifiers
1️⃣1️⃣ SEARCH()
SEARCH() is similar to FIND() but has some differences.
For example:
Unlike FIND(), SEARCH() is not case-sensitive.
Simple distinction:
FIND() → Case-sensitive
SEARCH() → Not case-sensitive
This difference can matter when cleaning real-world data.
1️⃣2️⃣ SUBSTITUTE()
SUBSTITUTE() replaces specific text with another value.
Suppose:
A2 = Mumbai, India
You want to replace the comma with a hyphen.
Result:
Mumbai- India
You can also replace words.
Result:
Mumbai, IND
1️⃣3️⃣ CONCAT()
CONCAT() combines text.
Suppose:
First Name | Last Name
John | Smith
Formula:
Result:
John Smith
This is useful when you need to create:
• Full names
• IDs
• Labels
• Descriptions
1️⃣4️⃣ TEXTJOIN()
TEXTJOIN() is particularly useful when combining multiple values with a delimiter.
Example:
Suppose:
A2 = John
B2 = Smith
C2 = India
Formula:
Result:
John, Smith, India
The second argument:
TRUE
tells Excel to ignore empty cells.
1️⃣5️⃣ TEXTSPLIT()
Modern Excel includes TEXTSPLIT(), which is extremely useful for breaking text into multiple columns.
Suppose:
A2 = John,IT,Pune
Use:
Excel can split it into:
John | IT | Pune
This is particularly useful when data arrives in a delimited format.
1️⃣6️⃣ Extract an Email Username
Suppose:
A2 = john.smith@gmail.com
You want:
john.smith
Using modern Excel:
Result:
john.smith
1️⃣7️⃣ Extract an Email Domain
Using the same data:
john.smith@gmail.com
Use:
Result:
gmail.com
These modern text functions can make data preparation much easier.
1️⃣8️⃣ Combining Text Functions
The real power comes from combining functions.
Suppose your data contains:
" JOHN SMITH "
You want:
John Smith
You could use:
First:
TRIM() removes unnecessary spaces.
Then:
PROPER() formats the name.
Result:
John Smith
1️⃣9️⃣ Real-World Data Cleaning Example
Suppose your department column contains:
IT
IT
it
IT
It
These values may represent the same department.
You could standardize them with:
Results become:
IT
IT
IT
IT
IT
Now filtering, counting and lookups become much more reliable.
2️⃣0️⃣ Data Quality Check Using Text Functions
Suppose all employee IDs should contain exactly 6 characters.
You can use:
If:
A2 = EMP001
Result:
Valid
If:
A2 = EMP01
Result:
Check
This is a simple example of using Excel for data-quality validation.
🧪 Practical Interview Challenge
Syntax:
=MID(text,start_num,num_chars)
Suppose:
EMP-001-IND
You want:
001
Use:
=MID(A2,5,3)
Result:
001
Because:
Start at character 5
Extract 3 characters
🔟 FIND()
FIND() tells you where one piece of text appears inside another.
Example:
john.smith@gmail.com
You can find the position of @:
=FIND("@",A2)This returns the position of the @ character.
Why is this useful?
You can use the position to extract:
• Email username
• Domain
• Product components
• Codes
• Identifiers
1️⃣1️⃣ SEARCH()
SEARCH() is similar to FIND() but has some differences.
For example:
=SEARCH("india",A2)Unlike FIND(), SEARCH() is not case-sensitive.
Simple distinction:
FIND() → Case-sensitive
SEARCH() → Not case-sensitive
This difference can matter when cleaning real-world data.
1️⃣2️⃣ SUBSTITUTE()
SUBSTITUTE() replaces specific text with another value.
Suppose:
A2 = Mumbai, India
You want to replace the comma with a hyphen.
=SUBSTITUTE(A2,",","-")
Result:
Mumbai- India
You can also replace words.
=SUBSTITUTE(A2,"India","IND")
Result:
Mumbai, IND
1️⃣3️⃣ CONCAT()
CONCAT() combines text.
Suppose:
First Name | Last Name
John | Smith
Formula:
=CONCAT(A2," ",B2)
Result:
John Smith
This is useful when you need to create:
• Full names
• IDs
• Labels
• Descriptions
1️⃣4️⃣ TEXTJOIN()
TEXTJOIN() is particularly useful when combining multiple values with a delimiter.
Example:
Suppose:
A2 = John
B2 = Smith
C2 = India
Formula:
=TEXTJOIN(", ",TRUE,A2:C2)Result:
John, Smith, India
The second argument:
TRUE
tells Excel to ignore empty cells.
1️⃣5️⃣ TEXTSPLIT()
Modern Excel includes TEXTSPLIT(), which is extremely useful for breaking text into multiple columns.
Suppose:
A2 = John,IT,Pune
Use:
=TEXTSPLIT(A2,",")
Excel can split it into:
John | IT | Pune
This is particularly useful when data arrives in a delimited format.
1️⃣6️⃣ Extract an Email Username
Suppose:
A2 = john.smith@gmail.com
You want:
john.smith
Using modern Excel:
=TEXTBEFORE(A2,"@")
Result:
john.smith
1️⃣7️⃣ Extract an Email Domain
Using the same data:
john.smith@gmail.com
Use:
=TEXTAFTER(A2,"@")
Result:
gmail.com
These modern text functions can make data preparation much easier.
1️⃣8️⃣ Combining Text Functions
The real power comes from combining functions.
Suppose your data contains:
" JOHN SMITH "
You want:
John Smith
You could use:
=PROPER(TRIM(A2))
First:
TRIM() removes unnecessary spaces.
Then:
PROPER() formats the name.
Result:
John Smith
1️⃣9️⃣ Real-World Data Cleaning Example
Suppose your department column contains:
IT
IT
it
IT
It
These values may represent the same department.
You could standardize them with:
=UPPER(TRIM(A2))
Results become:
IT
IT
IT
IT
IT
Now filtering, counting and lookups become much more reliable.
2️⃣0️⃣ Data Quality Check Using Text Functions
Suppose all employee IDs should contain exactly 6 characters.
You can use:
=IF(LEN(A2)=6,"Valid","Check")
If:
A2 = EMP001
Result:
Valid
If:
A2 = EMP01
Result:
Check
This is a simple example of using Excel for data-quality validation.
🧪 Practical Interview Challenge
❤4
Suppose you receive this dataset:
Employee
john smith
SARAH JONES
mike brown
DAVID WILSON
Task 1 — Remove extra spaces
Task 2 — Convert to proper case
Task 3 — Count characters
Task 4 — Convert to uppercase
Task 5 — Extract the first 3 characters
🏆 Key Lesson
Text functions aren't just about manipulating words.
For a Data Analyst, they're data-cleaning tools.
When you receive messy data, think:
Remove unwanted spaces → Standardize → Extract → Replace → Combine → Validate
For example:
can turn:
" jOhN sMiTh "
into:
John Smith
That may look like a small task, but cleaning and standardizing data correctly is an important part of professional analytics.
Double Tap ❤️ For Part-7
Employee
john smith
SARAH JONES
mike brown
DAVID WILSON
Task 1 — Remove extra spaces
=TRIM(A2)Task 2 — Convert to proper case
=PROPER(TRIM(A2))Task 3 — Count characters
=LEN(A2)Task 4 — Convert to uppercase
=UPPER(A2)Task 5 — Extract the first 3 characters
=LEFT(A2,3)🏆 Key Lesson
Text functions aren't just about manipulating words.
For a Data Analyst, they're data-cleaning tools.
When you receive messy data, think:
Remove unwanted spaces → Standardize → Extract → Replace → Combine → Validate
For example:
=PROPER(TRIM(A2))can turn:
" jOhN sMiTh "
into:
John Smith
That may look like a small task, but cleaning and standardizing data correctly is an important part of professional analytics.
Double Tap ❤️ For Part-7
❤9
🚀 𝗙𝗥𝗘𝗘 𝗗𝗮𝘁𝗮 𝗔𝗻𝗮𝗹𝘆𝘁𝗶𝗰𝘀 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗖𝗼𝘂𝗿𝘀𝗲! 📊
Here’s a great chance to learn valuable skills and earn a FREE Certificate 🎓
✅ Beginner-friendly
✅ Learn Data Analytics skills
✅ Free certification
✅ Boost your resume & LinkedIn profile
✅ Great for students & job seekers
𝗘𝗻𝗿𝗼𝗹𝗹 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘👇 :-
https://pdlink.in/4qn5q94
📌 Start learning today & upgrade your career!
Here’s a great chance to learn valuable skills and earn a FREE Certificate 🎓
✅ Beginner-friendly
✅ Learn Data Analytics skills
✅ Free certification
✅ Boost your resume & LinkedIn profile
✅ Great for students & job seekers
𝗘𝗻𝗿𝗼𝗹𝗹 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘👇 :-
https://pdlink.in/4qn5q94
📌 Start learning today & upgrade your career!
👍5
🚀 Data Analyst Roadmap — Part 7
📅 Excel — Level 6: Date & Time Functions for Data Analysis
Dates are everywhere in data analytics.
Think about datasets containing: Order dates, Transaction dates, Employee joining dates, Invoice dates, Payment dates, Due dates, Delivery dates, Project start/end dates, Customer registration dates
A Data Analyst often needs to answer questions such as:
To answer these questions, you need to understand Excel's date and time functions.
1️⃣ How Excel Stores Dates
One important concept is that Excel stores dates as numbers internally.
For example, a date such as: 01-Jan-2026 is represented internally by a serial number.
This is why Excel can perform calculations such as: =B2-A2
If: A2 = 01-Jan-2026, B2 = 10-Jan-2026 the result can be: 9 meaning 9 days between the dates.
This is the foundation of date calculations in Excel.
2️⃣ TODAY()
TODAY() returns the current date. =TODAY()
For example, if today's date is August 25, 2026, Excel returns: 25-Aug-2026
The value automatically changes when the date changes.
Common uses: Employee tenure, Age calculations, Overdue invoices, Days remaining, Current reporting period, Aging analysis
3️⃣ NOW()
NOW() returns the current date and time. =NOW()
Example: 25-Aug-2026 01:38
The exact result depends on when Excel recalculates.
TODAY vs NOW:
TODAY() → Current date, NOW() → Current date + current time
4️⃣ DATE()
DATE() creates a valid Excel date from year, month and day. =DATE(2026,8,25) Result: 25-Aug-2026
This is useful when dates need to be constructed from separate columns.
For example: Year: 2026, Month: 8, Day: 25 - You can create the date with: =DATE(A2,B2,C2)
5️⃣ YEAR()
YEAR() extracts the year from a date. Suppose: A2 = 25-Aug-2026 Use: =YEAR(A2) Result: 2026
Common uses: Yearly reporting, Year-over-year analysis, Creating Year columns, Grouping transactions by year
6️⃣ MONTH()
MONTH() extracts the month number. =MONTH(A2)
For: 25-Aug-2026 the result is: 8 because August is the eighth month.
7️⃣ DAY()
DAY() extracts the day of the month. =DAY(A2)
For: 25-Aug-2026 result: 25
8️⃣ Create Year, Month and Day Columns
Suppose you have: Order Date - 15-Jan-2026, 20-Feb-2026, 10-Mar-2026
You can create: Year: =YEAR(A2), Month Number: =MONTH(A2), Day: =DAY(A2)
This can help you analyze data by different time periods.
9️⃣ EOMONTH()
EOMONTH() returns the last day of a month. Syntax: =EOMONTH(start_date,months)
Suppose: A2 = 15-Aug-2026
Use: =EOMONTH(A2,0) Result: 31-Aug-2026
Next month's end: =EOMONTH(A2,1) Result: 30-Sep-2026
Previous month's end: =EOMONTH(A2,-1) Result: 31-Jul-2026
🔟 Why EOMONTH() Is Useful
It's extremely useful for: Month-end reporting, Financial reporting, Invoice analysis, Aging reports, Monthly dashboards, Closing processes
For example: "Give me all transactions up to the end of the reporting month." EOMONTH() becomes very useful here.
1️⃣1️⃣ EDATE()
EDATE() moves a date forward or backward by a specified number of months.
Suppose: A2 = 25-Aug-2026
📅 Excel — Level 6: Date & Time Functions for Data Analysis
Dates are everywhere in data analytics.
Think about datasets containing: Order dates, Transaction dates, Employee joining dates, Invoice dates, Payment dates, Due dates, Delivery dates, Project start/end dates, Customer registration dates
A Data Analyst often needs to answer questions such as:
How many orders were placed in January?
How long did customers wait for delivery?
Which month had the highest sales?
How many days overdue are invoices?
How many years has an employee worked?
To answer these questions, you need to understand Excel's date and time functions.
1️⃣ How Excel Stores Dates
One important concept is that Excel stores dates as numbers internally.
For example, a date such as: 01-Jan-2026 is represented internally by a serial number.
This is why Excel can perform calculations such as: =B2-A2
If: A2 = 01-Jan-2026, B2 = 10-Jan-2026 the result can be: 9 meaning 9 days between the dates.
This is the foundation of date calculations in Excel.
2️⃣ TODAY()
TODAY() returns the current date. =TODAY()
For example, if today's date is August 25, 2026, Excel returns: 25-Aug-2026
The value automatically changes when the date changes.
Common uses: Employee tenure, Age calculations, Overdue invoices, Days remaining, Current reporting period, Aging analysis
3️⃣ NOW()
NOW() returns the current date and time. =NOW()
Example: 25-Aug-2026 01:38
The exact result depends on when Excel recalculates.
TODAY vs NOW:
TODAY() → Current date, NOW() → Current date + current time
4️⃣ DATE()
DATE() creates a valid Excel date from year, month and day. =DATE(2026,8,25) Result: 25-Aug-2026
This is useful when dates need to be constructed from separate columns.
For example: Year: 2026, Month: 8, Day: 25 - You can create the date with: =DATE(A2,B2,C2)
5️⃣ YEAR()
YEAR() extracts the year from a date. Suppose: A2 = 25-Aug-2026 Use: =YEAR(A2) Result: 2026
Common uses: Yearly reporting, Year-over-year analysis, Creating Year columns, Grouping transactions by year
6️⃣ MONTH()
MONTH() extracts the month number. =MONTH(A2)
For: 25-Aug-2026 the result is: 8 because August is the eighth month.
7️⃣ DAY()
DAY() extracts the day of the month. =DAY(A2)
For: 25-Aug-2026 result: 25
8️⃣ Create Year, Month and Day Columns
Suppose you have: Order Date - 15-Jan-2026, 20-Feb-2026, 10-Mar-2026
You can create: Year: =YEAR(A2), Month Number: =MONTH(A2), Day: =DAY(A2)
This can help you analyze data by different time periods.
9️⃣ EOMONTH()
EOMONTH() returns the last day of a month. Syntax: =EOMONTH(start_date,months)
Suppose: A2 = 15-Aug-2026
Use: =EOMONTH(A2,0) Result: 31-Aug-2026
Next month's end: =EOMONTH(A2,1) Result: 30-Sep-2026
Previous month's end: =EOMONTH(A2,-1) Result: 31-Jul-2026
🔟 Why EOMONTH() Is Useful
It's extremely useful for: Month-end reporting, Financial reporting, Invoice analysis, Aging reports, Monthly dashboards, Closing processes
For example: "Give me all transactions up to the end of the reporting month." EOMONTH() becomes very useful here.
1️⃣1️⃣ EDATE()
EDATE() moves a date forward or backward by a specified number of months.
Suppose: A2 = 25-Aug-2026
❤3
Six months later: =EDATE(A2,6) Result: 25-Feb-2027
Three months earlier: =EDATE(A2,-3) Result: 25-May-2026
Common uses: Contract expiry, Subscription dates, Loan schedules, Review dates, Employee milestones
1️⃣2️⃣ Date Subtraction
One of the simplest but most useful date calculations is: =B2-A2
Suppose: Start Date: 01-Aug-2026, End Date: 10-Aug-2026 - Formula: =B2-A2 Result: 9 days
This is useful for calculating: Delivery time, Processing time, Turnaround time, Resolution time, Payment delays
1️⃣3️⃣ Calculate Days Overdue
Suppose: Due Date: 20-Aug-2026
You want to know how many days overdue the payment is. You could use: =MAX(0,TODAY()-A2)
If today is after the due date, Excel calculates the overdue days. If the payment isn't overdue, it returns: 0
This is useful for invoice and payment analysis.
1️⃣4️⃣ DATEDIF()
DATEDIF() calculates the difference between two dates in different units.
For example: =DATEDIF(A2,B2,"Y") returns the number of complete years.
DATEDIF Units
"Y" - Complete years. =DATEDIF(A2,B2,"Y")
"M" - Complete months. =DATEDIF(A2,B2,"M")
"D" - Total days. =DATEDIF(A2,B2,"D")
1️⃣5️⃣ Employee Tenure Example
Suppose: Employee: John, Joining Date: 15-Jan-2022
To calculate completed years as of today: =DATEDIF(B2,TODAY(),"Y")
If today is after January 15, 2026, the result would be: 4 years
This is commonly used in HR analytics.
1️⃣6️⃣ Calculate Years and Months Together
You can combine DATEDIF calculations.
=DATEDIF(B2,TODAY(),"Y")&" Years "&DATEDIF(B2,TODAY(),"YM")&" Months"
Example result: 4 Years 7 Months - This can be useful in employee reports.
1️⃣7️⃣ NETWORKDAYS()
NETWORKDAYS() calculates the number of working days between two dates. It normally excludes: Saturday, Sunday
Example: =NETWORKDAYS(A2,B2)
This is very useful for: SLA analysis, Employee working days, Project duration, Processing time, Operational reporting
1️⃣8️⃣ NETWORKDAYS() with Holidays
Suppose your company holidays are listed in: H2:H10
You can use: =NETWORKDAYS(A2,B2,H2:H10)
Now Excel excludes: Weekends, Listed holidays
This is extremely useful for real-world business calculations.
1️⃣9️⃣ WORKDAY()
WORKDAY() calculates a future or previous working date.
Suppose a task starts on: 25-Aug-2026 and should take: 10 working days - Use: =WORKDAY(A2,10)
Excel returns the date after 10 working days, excluding weekends.
You can also provide holidays: =WORKDAY(A2,10,H2:H10)
2️⃣0️⃣ MONTH-END Reporting Example
Suppose you're preparing a monthly sales report. You have: Order Date, Sales - You need to identify the month-end date for every transaction. Use: =EOMONTH(A2,0)
You can then use that month-end field for reporting and grouping.
2️⃣1️⃣ Extract Month Name
MONTH() gives you a number. But sometimes you want: January instead of: 1
You can use: =TEXT(A2,"mmmm") Result: January
For abbreviated month: =TEXT(A2,"mmm") Result: Jan
2️⃣2️⃣ Extract Year-Month
For reporting, you may want: 2026-08 - You can use: =TEXT(A2,"yyyy-mm")
This is useful for: Monthly trends, Grouping, Reporting, Time-series analysis
2️⃣3️⃣ Important Date Problem: Dates Stored as Text
One common real-world problem is that something that looks like a date isn't actually stored as a date.
Three months earlier: =EDATE(A2,-3) Result: 25-May-2026
Common uses: Contract expiry, Subscription dates, Loan schedules, Review dates, Employee milestones
1️⃣2️⃣ Date Subtraction
One of the simplest but most useful date calculations is: =B2-A2
Suppose: Start Date: 01-Aug-2026, End Date: 10-Aug-2026 - Formula: =B2-A2 Result: 9 days
This is useful for calculating: Delivery time, Processing time, Turnaround time, Resolution time, Payment delays
1️⃣3️⃣ Calculate Days Overdue
Suppose: Due Date: 20-Aug-2026
You want to know how many days overdue the payment is. You could use: =MAX(0,TODAY()-A2)
If today is after the due date, Excel calculates the overdue days. If the payment isn't overdue, it returns: 0
This is useful for invoice and payment analysis.
1️⃣4️⃣ DATEDIF()
DATEDIF() calculates the difference between two dates in different units.
For example: =DATEDIF(A2,B2,"Y") returns the number of complete years.
DATEDIF Units
"Y" - Complete years. =DATEDIF(A2,B2,"Y")
"M" - Complete months. =DATEDIF(A2,B2,"M")
"D" - Total days. =DATEDIF(A2,B2,"D")
1️⃣5️⃣ Employee Tenure Example
Suppose: Employee: John, Joining Date: 15-Jan-2022
To calculate completed years as of today: =DATEDIF(B2,TODAY(),"Y")
If today is after January 15, 2026, the result would be: 4 years
This is commonly used in HR analytics.
1️⃣6️⃣ Calculate Years and Months Together
You can combine DATEDIF calculations.
=DATEDIF(B2,TODAY(),"Y")&" Years "&DATEDIF(B2,TODAY(),"YM")&" Months"
Example result: 4 Years 7 Months - This can be useful in employee reports.
1️⃣7️⃣ NETWORKDAYS()
NETWORKDAYS() calculates the number of working days between two dates. It normally excludes: Saturday, Sunday
Example: =NETWORKDAYS(A2,B2)
This is very useful for: SLA analysis, Employee working days, Project duration, Processing time, Operational reporting
1️⃣8️⃣ NETWORKDAYS() with Holidays
Suppose your company holidays are listed in: H2:H10
You can use: =NETWORKDAYS(A2,B2,H2:H10)
Now Excel excludes: Weekends, Listed holidays
This is extremely useful for real-world business calculations.
1️⃣9️⃣ WORKDAY()
WORKDAY() calculates a future or previous working date.
Suppose a task starts on: 25-Aug-2026 and should take: 10 working days - Use: =WORKDAY(A2,10)
Excel returns the date after 10 working days, excluding weekends.
You can also provide holidays: =WORKDAY(A2,10,H2:H10)
2️⃣0️⃣ MONTH-END Reporting Example
Suppose you're preparing a monthly sales report. You have: Order Date, Sales - You need to identify the month-end date for every transaction. Use: =EOMONTH(A2,0)
You can then use that month-end field for reporting and grouping.
2️⃣1️⃣ Extract Month Name
MONTH() gives you a number. But sometimes you want: January instead of: 1
You can use: =TEXT(A2,"mmmm") Result: January
For abbreviated month: =TEXT(A2,"mmm") Result: Jan
2️⃣2️⃣ Extract Year-Month
For reporting, you may want: 2026-08 - You can use: =TEXT(A2,"yyyy-mm")
This is useful for: Monthly trends, Grouping, Reporting, Time-series analysis
2️⃣3️⃣ Important Date Problem: Dates Stored as Text
One common real-world problem is that something that looks like a date isn't actually stored as a date.
❤2
For example: "25/08/2026" may be stored as text.
Then functions such as: =YEAR(A2) may not work as expected.
You need to ensure the value is converted into a genuine Excel date before performing calculations.
This is a crucial data-cleaning concept.
🧪 Practical Interview Challenge
Suppose you have:
Employee: John, Joining Date: 15-Jan-2022, End Date: 25-Aug-2026
Sarah, 20-Mar-2021, 25-Aug-2026
Mike, 10-Jul-2023, 25-Aug-2026
Q1. Extract the joining year: =YEAR(B2)
Q2. Extract the joining month: =MONTH(B2)
Q3. Calculate completed years: =DATEDIF(B2,C2,"Y")
Q4. Calculate total days: =C2-B2
Q5. Find month-end for joining month: =EOMONTH(B2,0)
Q6. Find six months after joining: =EDATE(B2,6)
Q7. Calculate working days: =NETWORKDAYS(B2,C2)
🏆 Key Lesson
Dates aren't just values displayed on a spreadsheet. They allow you to analyze time.
A Data Analyst should be able to answer:
When did it happen? How long did it take? How many working days did it take? Which month did it happen in? Which quarter/year did it happen in? Is it overdue? When will it be due?
Once you become comfortable with date functions, you'll be able to build much more useful analysis around trends, aging, SLAs, employee tenure, financial periods and time-based KPIs.
Double Tap ❤️ For Part-8
Then functions such as: =YEAR(A2) may not work as expected.
You need to ensure the value is converted into a genuine Excel date before performing calculations.
This is a crucial data-cleaning concept.
🧪 Practical Interview Challenge
Suppose you have:
Employee: John, Joining Date: 15-Jan-2022, End Date: 25-Aug-2026
Sarah, 20-Mar-2021, 25-Aug-2026
Mike, 10-Jul-2023, 25-Aug-2026
Q1. Extract the joining year: =YEAR(B2)
Q2. Extract the joining month: =MONTH(B2)
Q3. Calculate completed years: =DATEDIF(B2,C2,"Y")
Q4. Calculate total days: =C2-B2
Q5. Find month-end for joining month: =EOMONTH(B2,0)
Q6. Find six months after joining: =EDATE(B2,6)
Q7. Calculate working days: =NETWORKDAYS(B2,C2)
🏆 Key Lesson
Dates aren't just values displayed on a spreadsheet. They allow you to analyze time.
A Data Analyst should be able to answer:
When did it happen? How long did it take? How many working days did it take? Which month did it happen in? Which quarter/year did it happen in? Is it overdue? When will it be due?
Once you become comfortable with date functions, you'll be able to build much more useful analysis around trends, aging, SLAs, employee tenure, financial periods and time-based KPIs.
Double Tap ❤️ For Part-8
❤11
𝗙𝗥𝗘𝗘 𝗚𝗲𝗻𝗔𝗜 + 𝗖𝗹𝗮𝘂𝗱𝗲 𝗢𝗻𝗹𝗶𝗻𝗲 𝗠𝗮𝘀𝘁𝗲𝗿𝗰𝗹𝗮𝘀𝘀😍
Learn how to use 25+ powerful AI tools to automate your work, create professional content and save hours every week!
🎯 Perfect For:-
Freelancers • Working Professionals • Business Owners • Self-Employed Individuals
💡 No technical knowledge or prior experience required!
🔗 𝗥𝗲𝗴𝗶𝘀𝘁𝗲𝗿 𝗳𝗼𝗿 𝗙𝗥𝗘𝗘 👇:-
https://pdlinks.in/ai
⚡ Start using AI smarter—limited slots available!
Learn how to use 25+ powerful AI tools to automate your work, create professional content and save hours every week!
🎯 Perfect For:-
Freelancers • Working Professionals • Business Owners • Self-Employed Individuals
💡 No technical knowledge or prior experience required!
🔗 𝗥𝗲𝗴𝗶𝘀𝘁𝗲𝗿 𝗳𝗼𝗿 𝗙𝗥𝗘𝗘 👇:-
https://pdlinks.in/ai
⚡ Start using AI smarter—limited slots available!
❤2
🎓 𝐀𝐜𝐜𝐞𝐧𝐭𝐮𝐫𝐞 𝐅𝐑𝐄𝐄 𝐂𝐞𝐫𝐭𝐢𝐟𝐢𝐜𝐚𝐭𝐢𝐨𝐧 𝐂𝐨𝐮𝐫𝐬𝐞𝐬 😍
Boost your skills with 100% FREE certification courses from Accenture!
📚 FREE Courses Offered:
1️⃣ Data Processing and Visualization
2️⃣ Exploratory Data Analysis
3️⃣ SQL Fundamentals
4️⃣ Python Basics
5️⃣ Acquiring Data
𝐋𝐢𝐧𝐤 👇:-
https://pdlink.in/4yJKnBy
✅ Learn Online | 📜 Get Certified
Boost your skills with 100% FREE certification courses from Accenture!
📚 FREE Courses Offered:
1️⃣ Data Processing and Visualization
2️⃣ Exploratory Data Analysis
3️⃣ SQL Fundamentals
4️⃣ Python Basics
5️⃣ Acquiring Data
𝐋𝐢𝐧𝐤 👇:-
https://pdlink.in/4yJKnBy
✅ Learn Online | 📜 Get Certified
❤5
🚀 Data Analyst Roadmap — Part 9
📊 Excel — Level 8: PivotTables, PivotCharts & Interactive Analysis
Now that you understand Excel formulas and dynamic functions, it's time to learn one of the most important Excel features for Data Analysts: PivotTables.
A PivotTable allows you to take a large dataset and quickly summarize it without writing complicated formulas.
For example, imagine you have 50,000 sales transactions. Your manager asks: "Show me total sales by region, product category, and month." Doing this manually would take a lot of time. With a PivotTable, you can summarize the data in seconds.
1️⃣ What Is a PivotTable?
A PivotTable is an Excel tool that lets you summarize, group, compare and analyze large datasets.
Raw data example:
Order ID | Date | Region | Product | Sales | Profit
1001 | Jan | North | Laptop | 80,000 | 12,000
1002 | Jan | South | Mouse | 2,000 | 500
Instead of manually calculating totals, create a PivotTable.
2️⃣ Creating a PivotTable
First select your dataset.
Then: Insert → PivotTable → Usually select New Worksheet → OK.
You'll see four main areas: Rows, Columns, Values, Filters. These four areas are the foundation.
3️⃣ Understand the Rows Area
Rows determines what you want to group by.
Drag Region → Rows → You get North, South, West grouped.
4️⃣ Understand the Values Area
Values contains the calculation. Drag Sales → Values → Sum of Sales.
Region | Total Sales → North 155,000, South 92,000, West 5,000.
Now you've answered: "How much did each region sell?"
5️⃣ Understand the Columns Area
Allows you to compare categories horizontally. Region → Rows, Product → Columns, Sales → Values → You get Region x Product matrix.
6️⃣ Understand the Filters Area
Lets you filter entire PivotTable.
Region → Rows, Sales → Values, Year → Filters → Select 2026 to see only 2026 results.
7️⃣ The Four PivotTable Areas
Rows → What do I want to group by?
Columns → What do I want to compare across?
Values → What calculation do I want?
Filters → What do I want to filter?
8️⃣ Change the Calculation
Right-click value → Value Field Settings → Choose Sum, Count, Average, Max, Min, etc. e.g., "What is average sales per order?" → Change to Average.
9️⃣ Sum vs Count in PivotTables
Sum of Sales = 100,000, Count = 3, Average = 33,333.33.
Always make sure aggregation matches business question.
🔟 Show Values as % of Total
Right-click Sales values → Show Values As → % of Grand Total → North 50%, South 30%, West 20%.
Useful for contribution analysis.
1️⃣1️⃣ Group Dates in PivotTables
Right-click a date → Group → Years, Quarters, Months, Days.
Makes time-based analysis easier.
1️⃣2️⃣ Analyze Monthly Sales
Order Date → Rows, Sales → Values, Group by Months → Jan 120K, Feb 145K, Mar 170K etc.
1️⃣3️⃣ Analyze Sales by Region and Month
Rows → Region, Columns → Month, Values → Sales → Matrix to identify best/worst region and trends.
1️⃣4️⃣ Sorting PivotTable Results
Sort Largest → Smallest to make best performers stand out.
1️⃣5️⃣ Top 10 Analysis
Use Value Filters → Top 10 to show top 10 customers/products/regions.
1️⃣6️⃣ Slicers
Slicers make PivotTables interactive.
📊 Excel — Level 8: PivotTables, PivotCharts & Interactive Analysis
Now that you understand Excel formulas and dynamic functions, it's time to learn one of the most important Excel features for Data Analysts: PivotTables.
A PivotTable allows you to take a large dataset and quickly summarize it without writing complicated formulas.
For example, imagine you have 50,000 sales transactions. Your manager asks: "Show me total sales by region, product category, and month." Doing this manually would take a lot of time. With a PivotTable, you can summarize the data in seconds.
1️⃣ What Is a PivotTable?
A PivotTable is an Excel tool that lets you summarize, group, compare and analyze large datasets.
Raw data example:
Order ID | Date | Region | Product | Sales | Profit
1001 | Jan | North | Laptop | 80,000 | 12,000
1002 | Jan | South | Mouse | 2,000 | 500
Instead of manually calculating totals, create a PivotTable.
2️⃣ Creating a PivotTable
First select your dataset.
Then: Insert → PivotTable → Usually select New Worksheet → OK.
You'll see four main areas: Rows, Columns, Values, Filters. These four areas are the foundation.
3️⃣ Understand the Rows Area
Rows determines what you want to group by.
Drag Region → Rows → You get North, South, West grouped.
4️⃣ Understand the Values Area
Values contains the calculation. Drag Sales → Values → Sum of Sales.
Region | Total Sales → North 155,000, South 92,000, West 5,000.
Now you've answered: "How much did each region sell?"
5️⃣ Understand the Columns Area
Allows you to compare categories horizontally. Region → Rows, Product → Columns, Sales → Values → You get Region x Product matrix.
6️⃣ Understand the Filters Area
Lets you filter entire PivotTable.
Region → Rows, Sales → Values, Year → Filters → Select 2026 to see only 2026 results.
7️⃣ The Four PivotTable Areas
Rows → What do I want to group by?
Columns → What do I want to compare across?
Values → What calculation do I want?
Filters → What do I want to filter?
8️⃣ Change the Calculation
Right-click value → Value Field Settings → Choose Sum, Count, Average, Max, Min, etc. e.g., "What is average sales per order?" → Change to Average.
9️⃣ Sum vs Count in PivotTables
Sum of Sales = 100,000, Count = 3, Average = 33,333.33.
Always make sure aggregation matches business question.
🔟 Show Values as % of Total
Right-click Sales values → Show Values As → % of Grand Total → North 50%, South 30%, West 20%.
Useful for contribution analysis.
1️⃣1️⃣ Group Dates in PivotTables
Right-click a date → Group → Years, Quarters, Months, Days.
Makes time-based analysis easier.
1️⃣2️⃣ Analyze Monthly Sales
Order Date → Rows, Sales → Values, Group by Months → Jan 120K, Feb 145K, Mar 170K etc.
1️⃣3️⃣ Analyze Sales by Region and Month
Rows → Region, Columns → Month, Values → Sales → Matrix to identify best/worst region and trends.
1️⃣4️⃣ Sorting PivotTable Results
Sort Largest → Smallest to make best performers stand out.
1️⃣5️⃣ Top 10 Analysis
Use Value Filters → Top 10 to show top 10 customers/products/regions.
1️⃣6️⃣ Slicers
Slicers make PivotTables interactive.
👍4
Add slicer for Region → Clickable North/South/East/West → PivotTable updates. Easier for non-technical users.
1️⃣7️⃣ Multiple Slicers
Add Region, Category, Year slicers → User selects Region: North, Category: Electronics, Year: 2026 → Shows only relevant info. Foundation of interactive dashboard.
1️⃣8️⃣ PivotCharts
A chart connected to a PivotTable.
📈 Line Chart for sales by month,
📊 Column Chart for sales by region.
Automatically responds to filters and slicers.
1️⃣9️⃣ Choosing the Right Chart
Compare categories → Bar/Column Chart
Show trends over time → Line Chart
Show contribution → Bar or Pie/Donut for small categories
Analyze relationships → Scatter Plot
2️⃣0️⃣ Drill Down
Year → Quarter → Month → Day. Move from high-level view to detailed view.
2️⃣1️⃣ Drill Through to Source Data
Double-click a value to see underlying records contributing to that value. Useful for investigating unexpected numbers.
2️⃣2️⃣ Refreshing PivotTables
PivotTables don't auto-update. Right-click → Refresh or Data → Refresh All. Using Excel Table as source makes refresh easier.
2️⃣3️⃣ PivotTable Best Practice
Source data should have:
✅ Headers
✅ No blank rows
✅ Consistent data types
✅ One record per row
✅ One field per column
✅ No manually inserted totals.
🧪 Practical Interview Challenge
Q1. Total sales by region → Region → Rows, Sales → Values
Q2. Average profit by category → Category → Rows, Profit → Values → Average
Q3. Monthly sales trend → Order Date → Rows, Sales → Values, Group by Months
Q4. Top 10 products by sales → Product → Rows, Sales → Values, Value Filters → Top 10
Q5. Interactive regional report → PivotTable + PivotChart + Region Slicer
🎯 Mini Project: Build an Excel Sales Analysis Dashboard
KPIs: Total Sales, Total Profit, Total Orders, Average Order Value
Analysis:
📊 Sales by Region,
📈 Monthly Trend,
📊 Sales by Category,
🏆 Top 10 Products,
📊 Profit by Region
Interactive Controls: Slicers for Region, Category, Year
Double Tap ❤️ For Part-10
1️⃣7️⃣ Multiple Slicers
Add Region, Category, Year slicers → User selects Region: North, Category: Electronics, Year: 2026 → Shows only relevant info. Foundation of interactive dashboard.
1️⃣8️⃣ PivotCharts
A chart connected to a PivotTable.
📈 Line Chart for sales by month,
📊 Column Chart for sales by region.
Automatically responds to filters and slicers.
1️⃣9️⃣ Choosing the Right Chart
Compare categories → Bar/Column Chart
Show trends over time → Line Chart
Show contribution → Bar or Pie/Donut for small categories
Analyze relationships → Scatter Plot
2️⃣0️⃣ Drill Down
Year → Quarter → Month → Day. Move from high-level view to detailed view.
2️⃣1️⃣ Drill Through to Source Data
Double-click a value to see underlying records contributing to that value. Useful for investigating unexpected numbers.
2️⃣2️⃣ Refreshing PivotTables
PivotTables don't auto-update. Right-click → Refresh or Data → Refresh All. Using Excel Table as source makes refresh easier.
2️⃣3️⃣ PivotTable Best Practice
Source data should have:
✅ Headers
✅ No blank rows
✅ Consistent data types
✅ One record per row
✅ One field per column
✅ No manually inserted totals.
🧪 Practical Interview Challenge
Q1. Total sales by region → Region → Rows, Sales → Values
Q2. Average profit by category → Category → Rows, Profit → Values → Average
Q3. Monthly sales trend → Order Date → Rows, Sales → Values, Group by Months
Q4. Top 10 products by sales → Product → Rows, Sales → Values, Value Filters → Top 10
Q5. Interactive regional report → PivotTable + PivotChart + Region Slicer
🎯 Mini Project: Build an Excel Sales Analysis Dashboard
KPIs: Total Sales, Total Profit, Total Orders, Average Order Value
Analysis:
📊 Sales by Region,
📈 Monthly Trend,
📊 Sales by Category,
🏆 Top 10 Products,
📊 Profit by Region
Interactive Controls: Slicers for Region, Category, Year
Double Tap ❤️ For Part-10
❤9👍4
𝗠𝗶𝗰𝗿𝗼𝘀𝗼𝗳𝘁 𝗮𝗻𝗱 𝗟𝗶𝗻𝗸𝗲𝗱𝗜𝗻 𝗙𝗥𝗘𝗘 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻𝘀🎓
Want to strengthen your resume with career-focused professional skills? Explore these free learning paths from Microsoft and LinkedIn.
🔥 Courses Available:
📌 Project Management
📊 Business Analysis
💻 System Administration
📈 Data Analysis
🔗 𝗘𝗻𝗿𝗼𝗹𝗹 𝗳𝗼𝗿 𝗙𝗥𝗘𝗘 👇:-
https://pdlinks.in/micrlink
💡 Learn → Get Certified → Upgrade Your Resume → Boost Your Career
Want to strengthen your resume with career-focused professional skills? Explore these free learning paths from Microsoft and LinkedIn.
🔥 Courses Available:
📌 Project Management
📊 Business Analysis
💻 System Administration
📈 Data Analysis
🔗 𝗘𝗻𝗿𝗼𝗹𝗹 𝗳𝗼𝗿 𝗙𝗥𝗘𝗘 👇:-
https://pdlinks.in/micrlink
💡 Learn → Get Certified → Upgrade Your Resume → Boost Your Career
❤2👍1👏1
𝗚𝗼𝗼𝗴𝗹𝗲 𝗙𝗥𝗘𝗘 𝗔𝗜 & 𝗠𝗮𝗰𝗵𝗶𝗻𝗲 𝗟𝗲𝗮𝗿𝗻𝗶𝗻𝗴 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 🚀
Explore Google Cloud learning resources covering AI/ML fundamentals through practical and advanced concepts.
🚀 Learn AI → Practice ML → Build Skills → Become Career Ready
🔗 𝗘𝗻𝗿𝗼𝗹𝗹 𝗳𝗼𝗿 𝗙𝗥𝗘𝗘 👇:-
https://pdlinks.in/eb6
🚀 Learn AI → Practice ML → Build Skills → Become Career Ready
Explore Google Cloud learning resources covering AI/ML fundamentals through practical and advanced concepts.
🚀 Learn AI → Practice ML → Build Skills → Become Career Ready
🔗 𝗘𝗻𝗿𝗼𝗹𝗹 𝗳𝗼𝗿 𝗙𝗥𝗘𝗘 👇:-
https://pdlinks.in/eb6
🚀 Learn AI → Practice ML → Build Skills → Become Career Ready
👍2
🚀 Data Analyst Roadmap — Part 10
🧹 Excel — Level 9: Power Query for Data Cleaning & Transformation
So far, you've learned how to analyze data using Excel formulas and PivotTables.
But there's a major problem with real-world data:
You might receive a monthly Excel file with:
• Duplicate records
• Missing values
• Incorrect data types
• Extra spaces
• Inconsistent names
• Multiple files
• Unnecessary columns
• Data spread across different tables
Cleaning this manually every time is slow and error-prone.
That's where Power Query comes in.
1️⃣ What Is Power Query?
Power Query is a data preparation and transformation tool available in Excel and Power BI.
It allows you to: Connect → Extract → Transform → Load - This is commonly called ETL.
Extract: Get data from a source.
Transform: Clean and reshape the data.
Load: Bring the prepared data into Excel for analysis.
The biggest advantage is repeatability. Instead of cleaning the same file manually every month, you create a transformation process once and refresh it.
2️⃣ Why Should a Data Analyst Learn Power Query?
Imagine your company sends you this file every month:
January.xlsx, February.xlsx, March.xlsx, April.xlsx...
Every file contains 50,000 rows, extra spaces, duplicates, incorrect date formats.
Without Power Query, you repeat the same cleaning every month.
With Power Query: Refresh → Transformations run again
3️⃣ Where Do You Find Power Query?
In modern Excel: Data → Get & Transform Data
Options: From Table/Range, From Workbook, From Text/CSV, From Folder, From Web, From Database
4️⃣ Understand the Power Query Workflow
Data Source → Connect → Power Query Editor → Clean → Transform → Validate → Load → Excel / Data Model → Analysis
Power Query records the transformation steps.
5️⃣ Import Data from Excel & CSV
Excel: Data → Get Data → From File → From Excel Workbook → Select sheet → Open in Power Query Editor
CSV: Data → From Text/CSV → Preview delimiter, headers, data types → Transform Data
6️⃣ Power Query Editor
Left side: Queries
Middle: Data preview
Right side: Applied Steps
Example Applied Steps:
Source → Changed Type → Removed Columns → Filtered Rows → Removed Duplicates → Renamed Columns → Added Custom Column
7️⃣ Changing Data Types
Correct data types are critical.
Order ID → Whole Number, Order Date → Date, Sales → Decimal Number, Customer → Text
Use the data-type icon to change it.
8️⃣ Remove Duplicates
If Order ID should be unique, select the column and use: Remove Rows → Remove Duplicates
🔟 Important: Understand What a Duplicate Means
Don't automatically delete duplicates.
Ask: > Is this actually a duplicate?
Two records with same customer but different orders = Not a duplicate.
Same order appearing twice = Duplicate.
1️⃣1️⃣ Remove & Rename Columns
Remove unnecessary columns: Home → Remove Columns
Rename for clarity: CustNm → Customer Name, SlsAmt → Sales
1️⃣2️⃣ Filter Rows
Filtering in Power Query becomes part of the reusable query.
Example: Keep only orders from 2026, or North region, or Sales > 0
1️⃣3️⃣ Handle Missing Values
Never blindly replace missing values with zero.
🧹 Excel — Level 9: Power Query for Data Cleaning & Transformation
So far, you've learned how to analyze data using Excel formulas and PivotTables.
But there's a major problem with real-world data:
The data is often messy.
You might receive a monthly Excel file with:
• Duplicate records
• Missing values
• Incorrect data types
• Extra spaces
• Inconsistent names
• Multiple files
• Unnecessary columns
• Data spread across different tables
Cleaning this manually every time is slow and error-prone.
That's where Power Query comes in.
1️⃣ What Is Power Query?
Power Query is a data preparation and transformation tool available in Excel and Power BI.
It allows you to: Connect → Extract → Transform → Load - This is commonly called ETL.
Extract: Get data from a source.
Transform: Clean and reshape the data.
Load: Bring the prepared data into Excel for analysis.
The biggest advantage is repeatability. Instead of cleaning the same file manually every month, you create a transformation process once and refresh it.
2️⃣ Why Should a Data Analyst Learn Power Query?
Imagine your company sends you this file every month:
January.xlsx, February.xlsx, March.xlsx, April.xlsx...
Every file contains 50,000 rows, extra spaces, duplicates, incorrect date formats.
Without Power Query, you repeat the same cleaning every month.
With Power Query: Refresh → Transformations run again
3️⃣ Where Do You Find Power Query?
In modern Excel: Data → Get & Transform Data
Options: From Table/Range, From Workbook, From Text/CSV, From Folder, From Web, From Database
4️⃣ Understand the Power Query Workflow
Data Source → Connect → Power Query Editor → Clean → Transform → Validate → Load → Excel / Data Model → Analysis
Power Query records the transformation steps.
5️⃣ Import Data from Excel & CSV
Excel: Data → Get Data → From File → From Excel Workbook → Select sheet → Open in Power Query Editor
CSV: Data → From Text/CSV → Preview delimiter, headers, data types → Transform Data
6️⃣ Power Query Editor
Left side: Queries
Middle: Data preview
Right side: Applied Steps
Example Applied Steps:
Source → Changed Type → Removed Columns → Filtered Rows → Removed Duplicates → Renamed Columns → Added Custom Column
7️⃣ Changing Data Types
Correct data types are critical.
Order ID → Whole Number, Order Date → Date, Sales → Decimal Number, Customer → Text
Use the data-type icon to change it.
8️⃣ Remove Duplicates
If Order ID should be unique, select the column and use: Remove Rows → Remove Duplicates
🔟 Important: Understand What a Duplicate Means
Don't automatically delete duplicates.
Ask: > Is this actually a duplicate?
Two records with same customer but different orders = Not a duplicate.
Same order appearing twice = Duplicate.
1️⃣1️⃣ Remove & Rename Columns
Remove unnecessary columns: Home → Remove Columns
Rename for clarity: CustNm → Customer Name, SlsAmt → Sales
1️⃣2️⃣ Filter Rows
Filtering in Power Query becomes part of the reusable query.
Example: Keep only orders from 2026, or North region, or Sales > 0
1️⃣3️⃣ Handle Missing Values
Never blindly replace missing values with zero.
❤2
A missing salary doesn't mean Salary = 0, it means it wasn't provided.
1️⃣4️⃣ Replace Values
Standardize inconsistent entries:
North, NORTH, north, N → North
Use: Replace Values
1️⃣5️⃣ Trim and Clean Text
Transform " John Smith " → "John Smith"
Trim whitespace, Clean non-printing characters, Change case
1️⃣6️⃣ Split Columns
John-Smith → First Name: John, Last Name: Smith
Use: Split Column → By Delimiter → "-"
1️⃣7️⃣ Merge Columns
John + Smith → John Smith
Use: Merge Columns with space separator
2️⃣0️⃣ Add Custom Columns
Sales: 100,000, Cost: 70,000 → Profit = Sales - Cost = 30,000
Profit Margin = Profit / Sales
2️⃣1️⃣ Conditional Columns
IF Sales >= 100000 THEN "High" ELSE IF Sales >= 50000 THEN "Medium" ELSE "Low"
Similar to Excel's IF()
2️⃣2️⃣ Merge Queries (The most important concept)
Sales: Product ID, Sales
Products: Product ID, Product, Category
Use: Merge Queries → Match Product ID → This is like a JOIN in SQL.
SQL: SELECT * FROM Sales LEFT JOIN Products ON Sales.ProductID = Products.ProductID;
2️⃣3️⃣ Append Queries
Merge = Add columns by matching keys
Append = Add rows by stacking
Jan (1001, 1002) + Feb (1003, 1004) → 1001, 1002, 1003, 1004
2️⃣4️⃣ Group By
Region: North 50K, North 70K → Group by Region, Sum Sales → North 120K
Similar to SQL GROUP BY
2️⃣5️⃣ Pivot and Unpivot
This is critical for reports designed for humans:
Before:
Region | Jan | Feb | Mar
North | 50K | 60K | 70K
After Unpivot:
Region | Month | Sales
North | Jan | 50K
North | Feb | 60K
This structure is much better for analysis.
2️⃣6️⃣ Applied Steps = Your Superpower
Source → Changed Type → Removed Columns → Trimmed Text → Removed Duplicates → Filtered Rows → Added Profit → Merged Products
When new data arrives, just Refresh.
🧪 Practical Interview Challenge
Messy file with: Duplicate Order IDs, Extra spaces, Sales as text, Missing regions, Product info in another file
Strong approach:
1. Import into Power Query
2. Set correct data types
3. Trim and clean text
4. Investigate duplicates
5. Handle missing regions per business rules
6. Merge Product lookup table
7. Add Profit column
8. Filter invalid records
9. Review Applied Steps
10. Load cleaned dataset
🏆 Key Lesson
Instead of: > "How do I clean this file?"
Think: > "How do I build a repeatable process that cleans this type of data every time?"
That's the difference between manually manipulating spreadsheets and building a professional analytics workflow.
Remember:
Merge = Add columns by matching data
Append = Add rows
Group By = Summarize
Unpivot = Convert columns into rows
Applied Steps = Record your process
Refresh = Run the process again
Double Tap ❤️ For Part-11
1️⃣4️⃣ Replace Values
Standardize inconsistent entries:
North, NORTH, north, N → North
Use: Replace Values
1️⃣5️⃣ Trim and Clean Text
Transform " John Smith " → "John Smith"
Trim whitespace, Clean non-printing characters, Change case
1️⃣6️⃣ Split Columns
John-Smith → First Name: John, Last Name: Smith
Use: Split Column → By Delimiter → "-"
1️⃣7️⃣ Merge Columns
John + Smith → John Smith
Use: Merge Columns with space separator
2️⃣0️⃣ Add Custom Columns
Sales: 100,000, Cost: 70,000 → Profit = Sales - Cost = 30,000
Profit Margin = Profit / Sales
2️⃣1️⃣ Conditional Columns
IF Sales >= 100000 THEN "High" ELSE IF Sales >= 50000 THEN "Medium" ELSE "Low"
Similar to Excel's IF()
2️⃣2️⃣ Merge Queries (The most important concept)
Sales: Product ID, Sales
Products: Product ID, Product, Category
Use: Merge Queries → Match Product ID → This is like a JOIN in SQL.
SQL: SELECT * FROM Sales LEFT JOIN Products ON Sales.ProductID = Products.ProductID;
2️⃣3️⃣ Append Queries
Merge = Add columns by matching keys
Append = Add rows by stacking
Jan (1001, 1002) + Feb (1003, 1004) → 1001, 1002, 1003, 1004
2️⃣4️⃣ Group By
Region: North 50K, North 70K → Group by Region, Sum Sales → North 120K
Similar to SQL GROUP BY
2️⃣5️⃣ Pivot and Unpivot
This is critical for reports designed for humans:
Before:
Region | Jan | Feb | Mar
North | 50K | 60K | 70K
After Unpivot:
Region | Month | Sales
North | Jan | 50K
North | Feb | 60K
This structure is much better for analysis.
2️⃣6️⃣ Applied Steps = Your Superpower
Source → Changed Type → Removed Columns → Trimmed Text → Removed Duplicates → Filtered Rows → Added Profit → Merged Products
When new data arrives, just Refresh.
🧪 Practical Interview Challenge
Messy file with: Duplicate Order IDs, Extra spaces, Sales as text, Missing regions, Product info in another file
Strong approach:
1. Import into Power Query
2. Set correct data types
3. Trim and clean text
4. Investigate duplicates
5. Handle missing regions per business rules
6. Merge Product lookup table
7. Add Profit column
8. Filter invalid records
9. Review Applied Steps
10. Load cleaned dataset
🏆 Key Lesson
Instead of: > "How do I clean this file?"
Think: > "How do I build a repeatable process that cleans this type of data every time?"
That's the difference between manually manipulating spreadsheets and building a professional analytics workflow.
Remember:
Merge = Add columns by matching data
Append = Add rows
Group By = Summarize
Unpivot = Convert columns into rows
Applied Steps = Record your process
Refresh = Run the process again
Double Tap ❤️ For Part-11
❤8👍1
🚀 Data Analyst Roadmap — Part 11
🗄️ SQL — Level 1: SQL Fundamentals & Databases
You've completed the major Excel section of the roadmap.
Now we're moving to one of the most important skills for a Data Analyst: SQL
If Excel helps you analyze spreadsheet-based data, SQL helps you work directly with data stored in databases.
A Data Analyst should be able to use SQL to:
• Retrieve data
• Filter records
• Sort results
• Summarize information
• Join tables
• Find trends
• Calculate KPIs
• Investigate business problems
1️⃣ What Is SQL?
SQL stands for: Structured Query Language
It's a language used to communicate with relational databases.
For example, suppose a company stores millions of sales records in a database.
Instead of opening a huge spreadsheet, you can ask the database:
Or:
Or:
SQL allows you to ask these questions directly.
2️⃣ Why Is SQL Important for Data Analysts?
Imagine a company has:
50 million transactions.
Excel isn't the right tool for storing and querying all that information.
The data may be stored in a database such as:
• PostgreSQL
• MySQL
• Microsoft SQL Server
• Oracle Database
• Snowflake
• BigQuery
As a Data Analyst, you may connect to the database and use SQL to extract the data you need.
A typical workflow looks like:
Database
↓
SQL Query
↓
Required Data
↓
Analysis
↓
Dashboard / Report
↓
Business Decision
3️⃣ What Is a Database?
A database is a system used to store and manage data.
For example, an e-commerce company might have:
• Customers
• Products
• Orders
• Payments
• Employees
Each represents a different type of information.
Instead of putting everything into one enormous table, relational databases typically organize related information into separate tables.
4️⃣ What Is a Table?
A table is a structured collection of data organized into:
Rows + Columns
For example:
Customers
Customer_ID Customer_Name City
101 John Mumbai
102 Sarah Pune
103 Mike Delhi
Each row represents one customer.
Each column represents an attribute.
This should look familiar from Excel.
5️⃣ Rows vs Columns
Just like Excel:
Row
Represents a record.
Example:
101 | John | Mumbai
represents one customer.
Column
Represents an attribute.
For example:
• Customer_ID
• Customer_Name
• City
A useful rule:
6️⃣ What Is a Primary Key?
A Primary Key uniquely identifies each record in a table.
For example:
Customer_ID Customer_Name
101 John
102 Sarah
103 Mike
Here:
Customer_ID
can be the primary key.
Each customer should have a unique ID.
101 → John
102 → Sarah
103 → Mike
You shouldn't have two different customers with the same primary key.
7️⃣ What Is a Foreign Key?
A Foreign Key is a column used to establish a relationship between tables.
Suppose:
Customers
Customer_ID Customer_Name
101 John
102 Sarah
Orders
Order_ID Customer_ID Sales
5001 101 50,000
5002 102 70,000
5003 101 30,000
Here:
Customers.Customer_ID
is the primary key.
Orders.Customer_ID
can be a foreign key.
🗄️ SQL — Level 1: SQL Fundamentals & Databases
You've completed the major Excel section of the roadmap.
Now we're moving to one of the most important skills for a Data Analyst: SQL
If Excel helps you analyze spreadsheet-based data, SQL helps you work directly with data stored in databases.
A Data Analyst should be able to use SQL to:
• Retrieve data
• Filter records
• Sort results
• Summarize information
• Join tables
• Find trends
• Calculate KPIs
• Investigate business problems
1️⃣ What Is SQL?
SQL stands for: Structured Query Language
It's a language used to communicate with relational databases.
For example, suppose a company stores millions of sales records in a database.
Instead of opening a huge spreadsheet, you can ask the database:
"Give me all sales from the North region."
Or:
"What was total revenue last month?"
Or:
"Which 10 products generated the most revenue?"
SQL allows you to ask these questions directly.
2️⃣ Why Is SQL Important for Data Analysts?
Imagine a company has:
50 million transactions.
Excel isn't the right tool for storing and querying all that information.
The data may be stored in a database such as:
• PostgreSQL
• MySQL
• Microsoft SQL Server
• Oracle Database
• Snowflake
• BigQuery
As a Data Analyst, you may connect to the database and use SQL to extract the data you need.
A typical workflow looks like:
Database
↓
SQL Query
↓
Required Data
↓
Analysis
↓
Dashboard / Report
↓
Business Decision
3️⃣ What Is a Database?
A database is a system used to store and manage data.
For example, an e-commerce company might have:
• Customers
• Products
• Orders
• Payments
• Employees
Each represents a different type of information.
Instead of putting everything into one enormous table, relational databases typically organize related information into separate tables.
4️⃣ What Is a Table?
A table is a structured collection of data organized into:
Rows + Columns
For example:
Customers
Customer_ID Customer_Name City
101 John Mumbai
102 Sarah Pune
103 Mike Delhi
Each row represents one customer.
Each column represents an attribute.
This should look familiar from Excel.
5️⃣ Rows vs Columns
Just like Excel:
Row
Represents a record.
Example:
101 | John | Mumbai
represents one customer.
Column
Represents an attribute.
For example:
• Customer_ID
• Customer_Name
• City
A useful rule:
One row = one record
One column = one attribute
6️⃣ What Is a Primary Key?
A Primary Key uniquely identifies each record in a table.
For example:
Customer_ID Customer_Name
101 John
102 Sarah
103 Mike
Here:
Customer_ID
can be the primary key.
Each customer should have a unique ID.
101 → John
102 → Sarah
103 → Mike
You shouldn't have two different customers with the same primary key.
7️⃣ What Is a Foreign Key?
A Foreign Key is a column used to establish a relationship between tables.
Suppose:
Customers
Customer_ID Customer_Name
101 John
102 Sarah
Orders
Order_ID Customer_ID Sales
5001 101 50,000
5002 102 70,000
5003 101 30,000
Here:
Customers.Customer_ID
is the primary key.
Orders.Customer_ID
can be a foreign key.
❤2
This allows us to connect orders to customers.
8️⃣ Understanding Relationships
The relationship is:
Customers
Customer_ID
↓
Orders
One customer can have multiple orders.
For example:
John
↓
Order 5001
Order 5003
Order 5010
This is a:
One-to-Many relationship
It's one of the most important database concepts for Data Analysts.
9️⃣ What Is a Relational Database?
A relational database stores data in related tables.
For example:
Customers
↓
Orders
↓
Order Details
↓
Products
Instead of storing the customer's name repeatedly in every order, the database can store:
Customer_ID
and retrieve the customer information through relationships.
This helps reduce unnecessary duplication.
🔟 What Is SQL Syntax?
SQL queries generally consist of keywords and expressions.
For example:
SELECT *
FROM Customers;
This asks:
Let me break it down.
SELECT: Specifies what you want to retrieve.
FROM: Specifies the table.
Customers: The table you're querying.
1️⃣1️⃣ SELECT
SELECT is one of the first SQL commands you need to learn.
Suppose you have:
Employees
Employee_ID Name Department Salary
101 John IT 75,000
102 Sarah HR 60,000
103 Mike Finance 82,000
To retrieve all columns:
SELECT *
FROM Employees;
1️⃣2️⃣ Selecting Specific Columns
You don't always need every column.
Suppose you only want:
Name and Department
Use:
SELECT Name, Department
FROM Employees;
Result:
Name Department
John IT
Sarah HR
Mike Finance
This is generally better than using SELECT * when you only need specific fields.
1️⃣3️⃣ Why Avoid SELECT * in Production Queries?
You may see beginners writing:
SELECT *
FROM Employees;
all the time.
It's useful while learning and exploring data.
But in production queries, explicitly selecting the required columns is often better because:
• It makes the query clearer
• It avoids retrieving unnecessary data
• It can reduce data transfer
• It makes downstream dependencies more predictable
For example:
SELECT Employee_ID, Name, Salary
FROM Employees;
is more intentional.
1️⃣4️⃣ WHERE
WHERE filters records.
Suppose you want employees from IT.
SELECT *
FROM Employees
WHERE Department = 'IT';
Result:
Employee_ID Name Department Salary
101 John IT 75,000
The database only returns records satisfying the condition.
1️⃣5️⃣ Filtering Numeric Values
Suppose you want employees earning more than ₹70,000.
SELECT *
FROM Employees
WHERE Salary > 70000;
Result:
Employee_ID Name Department Salary
101 John IT 75,000
103 Mike Finance 82,000
1️⃣6️⃣ Comparison Operators
You should know these operators:
Operator Meaning
= Equal to
<> Not equal to
Examples:
WHERE Salary >= 80000
WHERE Department <> 'HR'
1️⃣7️⃣ AND
AND requires all conditions to be true.
Suppose you want:
IT employees earning more than ₹70,000.
SELECT *
FROM Employees
WHERE Department = 'IT'
AND Salary > 70000;
The record must satisfy both conditions.
Think:
IT
AND
Salary > 70,000
1️⃣8️⃣ OR
OR requires at least one condition to be true.
Suppose you want:
IT or Finance employees.
8️⃣ Understanding Relationships
The relationship is:
Customers
Customer_ID
↓
Orders
One customer can have multiple orders.
For example:
John
↓
Order 5001
Order 5003
Order 5010
This is a:
One-to-Many relationship
It's one of the most important database concepts for Data Analysts.
9️⃣ What Is a Relational Database?
A relational database stores data in related tables.
For example:
Customers
↓
Orders
↓
Order Details
↓
Products
Instead of storing the customer's name repeatedly in every order, the database can store:
Customer_ID
and retrieve the customer information through relationships.
This helps reduce unnecessary duplication.
🔟 What Is SQL Syntax?
SQL queries generally consist of keywords and expressions.
For example:
SELECT *
FROM Customers;
This asks:
Return all columns from the Customers table.
Let me break it down.
SELECT: Specifies what you want to retrieve.
FROM: Specifies the table.
Customers: The table you're querying.
1️⃣1️⃣ SELECT
SELECT is one of the first SQL commands you need to learn.
Suppose you have:
Employees
Employee_ID Name Department Salary
101 John IT 75,000
102 Sarah HR 60,000
103 Mike Finance 82,000
To retrieve all columns:
SELECT *
FROM Employees;
1️⃣2️⃣ Selecting Specific Columns
You don't always need every column.
Suppose you only want:
Name and Department
Use:
SELECT Name, Department
FROM Employees;
Result:
Name Department
John IT
Sarah HR
Mike Finance
This is generally better than using SELECT * when you only need specific fields.
1️⃣3️⃣ Why Avoid SELECT * in Production Queries?
You may see beginners writing:
SELECT *
FROM Employees;
all the time.
It's useful while learning and exploring data.
But in production queries, explicitly selecting the required columns is often better because:
• It makes the query clearer
• It avoids retrieving unnecessary data
• It can reduce data transfer
• It makes downstream dependencies more predictable
For example:
SELECT Employee_ID, Name, Salary
FROM Employees;
is more intentional.
1️⃣4️⃣ WHERE
WHERE filters records.
Suppose you want employees from IT.
SELECT *
FROM Employees
WHERE Department = 'IT';
Result:
Employee_ID Name Department Salary
101 John IT 75,000
The database only returns records satisfying the condition.
1️⃣5️⃣ Filtering Numeric Values
Suppose you want employees earning more than ₹70,000.
SELECT *
FROM Employees
WHERE Salary > 70000;
Result:
Employee_ID Name Department Salary
101 John IT 75,000
103 Mike Finance 82,000
1️⃣6️⃣ Comparison Operators
You should know these operators:
Operator Meaning
= Equal to
<> Not equal to
Greater than
< Less than
= Greater than or equal
<= Less than or equal
Examples:
WHERE Salary >= 80000
WHERE Department <> 'HR'
1️⃣7️⃣ AND
AND requires all conditions to be true.
Suppose you want:
IT employees earning more than ₹70,000.
SELECT *
FROM Employees
WHERE Department = 'IT'
AND Salary > 70000;
The record must satisfy both conditions.
Think:
IT
AND
Salary > 70,000
1️⃣8️⃣ OR
OR requires at least one condition to be true.
Suppose you want:
IT or Finance employees.
SELECT *
FROM Employees
WHERE Department = 'IT'
OR Department = 'Finance';
Both departments will be included.
1️⃣9️⃣ IN
When checking multiple values, IN makes your query cleaner.
Instead of:
WHERE Department = 'IT'
OR Department = 'Finance'
OR Department = 'HR'
you can write:
WHERE Department IN ('IT', 'Finance', 'HR');
This is easier to read and maintain.
2️⃣0️⃣ NOT IN
You can exclude multiple values.
SELECT *
FROM Employees
WHERE Department NOT IN ('HR', 'Finance');
This returns employees who aren't in those departments.
2️⃣1️⃣ BETWEEN
BETWEEN checks whether a value falls within a range.
For example:
SELECT *
FROM Employees
WHERE Salary BETWEEN 50000 AND 80000;
This returns salaries within the specified range.
For numeric data, this is often useful for:
• Salary ranges
• Sales ranges
• Age ranges
• Scores
• Transaction values
2️⃣2️⃣ LIKE
LIKE is used for pattern matching.
Suppose you want employees whose names start with J.
SELECT *
FROM Employees
WHERE Name LIKE 'J%';
% means:
So this could match:
• John
• James
• Jennifer
2️⃣3️⃣ LIKE with Wildcards
•
Starts with J
LIKE 'J%'
•
Ends with n
LIKE '%n'
•
Contains "oh"
LIKE '%oh%'
Wildcards are extremely useful when searching text data.
2️⃣4️⃣ DISTINCT
DISTINCT removes duplicate values from the result.
Suppose your employee table contains:
• IT
• HR
• IT
• Finance
• HR
• IT
Use:
SELECT DISTINCT Department
FROM Employees;
Result:
IT
HR
Finance
This is useful for discovering categories in a dataset.
2️⃣5️⃣ ORDER BY
ORDER BY sorts your results.
Suppose you want employees with the highest salary first.
SELECT *
FROM Employees
ORDER BY Salary DESC;
DESC means:
Descending
Highest → Lowest
2️⃣6️⃣ ASC
ASC means ascending.
SELECT *
FROM Employees
ORDER BY Salary ASC;
Lowest → Highest
Ascending is generally the default sort direction.
2️⃣7️⃣ LIMIT / TOP
The syntax depends on the database system.
In systems such as PostgreSQL and MySQL:
SELECT *
FROM Employees
ORDER BY Salary DESC
LIMIT 5;
This returns the top 5 employees by salary.
In SQL Server, you would commonly use:
SELECT TOP 5 *
FROM Employees
ORDER BY Salary DESC;
This is an important point:
2️⃣8️⃣ Aliases
Aliases give columns or tables temporary names within a query.
For example:
SELECT
Name AS Employee_Name,
Salary AS Annual_Salary
FROM Employees;
The result displays:
Employee_Name Annual_Salary
John 75,000
Sarah 60,000
Aliases make results easier to understand.
2️⃣9️⃣ SQL Comments
You can add comments to explain your queries.
For example:
-- Get employees earning more than 70,000
SELECT Name, Salary
FROM Employees
WHERE Salary > 70000;
Comments don't affect the query result.
They're useful when queries become complex.
🧪 Practical Interview Challenge
Suppose you have:
Employees
ID Name Department Salary
101 John IT 75,000
102 Sarah HR 60,000
103 Mike Finance 82,000
104 David IT 90,000
105 Alice HR 65,000
Q1. Retrieve all employees.
SELECT *
FROM Employees;
Q2. Retrieve only names and salaries.
SELECT Name, Salary
FROM Employees;
Q3. Find employees earning more than ₹70,000.
SELECT *
FROM Employees
WHERE Salary > 70000;
Q4. Find IT employees.
SELECT *
FROM Employees
WHERE Department = 'IT';
Q5. Find IT or Finance employees.
SELECT *
FROM Employees
WHERE Department IN ('IT', 'Finance');
Q6. Sort employees by salary from highest to lowest.
SELECT *
FROM Employees
ORDER BY Salary DESC;
Q7. Find the top 3 highest-paid employees.
PostgreSQL/MySQL:
SELECT *
FROM Employees
ORDER BY Salary DESC
LIMIT 3;
SQL Server:
SELECT TOP 3 *
FROM Employees
ORDER BY Salary DESC;
Q8. List unique departments.
SELECT DISTINCT Department
FROM Employees;
🏆 Double Tap ❤️ For More
FROM Employees
WHERE Department = 'IT'
OR Department = 'Finance';
Both departments will be included.
1️⃣9️⃣ IN
When checking multiple values, IN makes your query cleaner.
Instead of:
WHERE Department = 'IT'
OR Department = 'Finance'
OR Department = 'HR'
you can write:
WHERE Department IN ('IT', 'Finance', 'HR');
This is easier to read and maintain.
2️⃣0️⃣ NOT IN
You can exclude multiple values.
SELECT *
FROM Employees
WHERE Department NOT IN ('HR', 'Finance');
This returns employees who aren't in those departments.
2️⃣1️⃣ BETWEEN
BETWEEN checks whether a value falls within a range.
For example:
SELECT *
FROM Employees
WHERE Salary BETWEEN 50000 AND 80000;
This returns salaries within the specified range.
For numeric data, this is often useful for:
• Salary ranges
• Sales ranges
• Age ranges
• Scores
• Transaction values
2️⃣2️⃣ LIKE
LIKE is used for pattern matching.
Suppose you want employees whose names start with J.
SELECT *
FROM Employees
WHERE Name LIKE 'J%';
% means:
Any number of characters.
So this could match:
• John
• James
• Jennifer
2️⃣3️⃣ LIKE with Wildcards
•
Starts with J
LIKE 'J%'
•
Ends with n
LIKE '%n'
•
Contains "oh"
LIKE '%oh%'
Wildcards are extremely useful when searching text data.
2️⃣4️⃣ DISTINCT
DISTINCT removes duplicate values from the result.
Suppose your employee table contains:
• IT
• HR
• IT
• Finance
• HR
• IT
Use:
SELECT DISTINCT Department
FROM Employees;
Result:
IT
HR
Finance
This is useful for discovering categories in a dataset.
2️⃣5️⃣ ORDER BY
ORDER BY sorts your results.
Suppose you want employees with the highest salary first.
SELECT *
FROM Employees
ORDER BY Salary DESC;
DESC means:
Descending
Highest → Lowest
2️⃣6️⃣ ASC
ASC means ascending.
SELECT *
FROM Employees
ORDER BY Salary ASC;
Lowest → Highest
Ascending is generally the default sort direction.
2️⃣7️⃣ LIMIT / TOP
The syntax depends on the database system.
In systems such as PostgreSQL and MySQL:
SELECT *
FROM Employees
ORDER BY Salary DESC
LIMIT 5;
This returns the top 5 employees by salary.
In SQL Server, you would commonly use:
SELECT TOP 5 *
FROM Employees
ORDER BY Salary DESC;
This is an important point:
SQL is a language, but different database systems have slightly different syntax.
2️⃣8️⃣ Aliases
Aliases give columns or tables temporary names within a query.
For example:
SELECT
Name AS Employee_Name,
Salary AS Annual_Salary
FROM Employees;
The result displays:
Employee_Name Annual_Salary
John 75,000
Sarah 60,000
Aliases make results easier to understand.
2️⃣9️⃣ SQL Comments
You can add comments to explain your queries.
For example:
-- Get employees earning more than 70,000
SELECT Name, Salary
FROM Employees
WHERE Salary > 70000;
Comments don't affect the query result.
They're useful when queries become complex.
🧪 Practical Interview Challenge
Suppose you have:
Employees
ID Name Department Salary
101 John IT 75,000
102 Sarah HR 60,000
103 Mike Finance 82,000
104 David IT 90,000
105 Alice HR 65,000
Q1. Retrieve all employees.
SELECT *
FROM Employees;
Q2. Retrieve only names and salaries.
SELECT Name, Salary
FROM Employees;
Q3. Find employees earning more than ₹70,000.
SELECT *
FROM Employees
WHERE Salary > 70000;
Q4. Find IT employees.
SELECT *
FROM Employees
WHERE Department = 'IT';
Q5. Find IT or Finance employees.
SELECT *
FROM Employees
WHERE Department IN ('IT', 'Finance');
Q6. Sort employees by salary from highest to lowest.
SELECT *
FROM Employees
ORDER BY Salary DESC;
Q7. Find the top 3 highest-paid employees.
PostgreSQL/MySQL:
SELECT *
FROM Employees
ORDER BY Salary DESC
LIMIT 3;
SQL Server:
SELECT TOP 3 *
FROM Employees
ORDER BY Salary DESC;
Q8. List unique departments.
SELECT DISTINCT Department
FROM Employees;
🏆 Double Tap ❤️ For More
❤19
🚀 Data Analyst Roadmap — Part 12
🗄️ SQL — Level 2: Aggregate Functions, GROUP BY & HAVING
Now that you've learned SQL fundamentals, it's time to move from retrieving individual records to summarizing data.
This is one of the most important SQL skills for Data Analysts.
In real interviews and jobs, you'll frequently be asked questions like:
To answer these questions, you need:
Aggregate Functions + GROUP BY + HAVING
1️⃣ What Are Aggregate Functions?
Aggregate functions perform calculations across multiple rows and return a summarized result.
The most important ones are:
SUM()
COUNT()
AVG()
MIN()
MAX()
Think of them as the SQL equivalent of the basic Excel functions you learned earlier.
2️⃣ SUM()
SUM() calculates the total of a numeric column.
Suppose you have:
Order_ID: 1001, Sales: 50,000
Order_ID: 1002, Sales: 70,000
Order_ID: 1003, Sales: 30,000
Query:
Result:
Total_Sales = 150,000
Business question
Answer → SUM()
3️⃣ COUNT()
COUNT() counts records.
If there are 10,000 orders:
Total_Orders = 10,000
Why COUNT(*)?
COUNT(*) counts rows.
This is often useful when you want the total number of records.
4️⃣ COUNT(Column)
You can also count values in a specific column.
One important distinction:
COUNT(column) generally doesn't count NULL values.
Whereas:
COUNT(*)
counts rows regardless of whether individual columns contain NULLs.
5️⃣ COUNT(DISTINCT)
Suppose your Orders table contains:
Order 1001 → Customer 101
Order 1002 → Customer 102
Order 1003 → Customer 101
Order 1004 → Customer 103
There are:
4 orders
but only:
3 unique customers
Use:
Result:
3
This is extremely important in analytics.
6️⃣ AVG()
AVG() calculates the average.
Suppose salaries are:
50,000, 60,000, 70,000
Query:
Result:
60,000
Business questions
Answer → AVG()
7️⃣ MIN()
MIN() returns the smallest value.
Example result:
35,000
Useful for:
Minimum salary
Lowest sales
Earliest date
Lowest transaction value
8️⃣ MAX()
MAX() returns the largest value.
Result:
150,000
Useful for:
Highest salary
Highest sales
Largest transaction
Latest date
9️⃣ Using Multiple Aggregate Functions
You can use several aggregate functions in one query.
🗄️ SQL — Level 2: Aggregate Functions, GROUP BY & HAVING
Now that you've learned SQL fundamentals, it's time to move from retrieving individual records to summarizing data.
This is one of the most important SQL skills for Data Analysts.
In real interviews and jobs, you'll frequently be asked questions like:
What is the total sales by region?
What is the average salary by department?
How many customers are in each city?
Which products generated more than ₹10 lakh in sales?
To answer these questions, you need:
Aggregate Functions + GROUP BY + HAVING
1️⃣ What Are Aggregate Functions?
Aggregate functions perform calculations across multiple rows and return a summarized result.
The most important ones are:
SUM()
COUNT()
AVG()
MIN()
MAX()
Think of them as the SQL equivalent of the basic Excel functions you learned earlier.
2️⃣ SUM()
SUM() calculates the total of a numeric column.
Suppose you have:
Order_ID: 1001, Sales: 50,000
Order_ID: 1002, Sales: 70,000
Order_ID: 1003, Sales: 30,000
Query:
SELECT SUM(Sales) AS Total_Sales
FROM Orders;
Result:
Total_Sales = 150,000
Business question
What is our total revenue?
Answer → SUM()
3️⃣ COUNT()
COUNT() counts records.
SELECT COUNT(*) AS Total_Orders
FROM Orders;
If there are 10,000 orders:
Total_Orders = 10,000
Why COUNT(*)?
COUNT(*) counts rows.
This is often useful when you want the total number of records.
4️⃣ COUNT(Column)
You can also count values in a specific column.
SELECT COUNT(Customer_ID) AS Customer_Count
FROM Orders;
One important distinction:
COUNT(column) generally doesn't count NULL values.
Whereas:
COUNT(*)
counts rows regardless of whether individual columns contain NULLs.
5️⃣ COUNT(DISTINCT)
Suppose your Orders table contains:
Order 1001 → Customer 101
Order 1002 → Customer 102
Order 1003 → Customer 101
Order 1004 → Customer 103
There are:
4 orders
but only:
3 unique customers
Use:
SELECT COUNT(DISTINCT Customer_ID) AS Unique_Customers
FROM Orders;
Result:
3
This is extremely important in analytics.
6️⃣ AVG()
AVG() calculates the average.
Suppose salaries are:
50,000, 60,000, 70,000
Query:
SELECT AVG(Salary) AS Average_Salary
FROM Employees;
Result:
60,000
Business questions
What is the average order value?
What is the average employee salary?
What is the average product price?
Answer → AVG()
7️⃣ MIN()
MIN() returns the smallest value.
SELECT MIN(Salary) AS Minimum_Salary
FROM Employees;
Example result:
35,000
Useful for:
Minimum salary
Lowest sales
Earliest date
Lowest transaction value
8️⃣ MAX()
MAX() returns the largest value.
SELECT MAX(Salary) AS Maximum_Salary
FROM Employees;
Result:
150,000
Useful for:
Highest salary
Highest sales
Largest transaction
Latest date
9️⃣ Using Multiple Aggregate Functions
You can use several aggregate functions in one query.
SELECT
SUM(Sales) AS Total_Sales,
AVG(Sales) AS Average_Sales,
MIN(Sales) AS Minimum_Sales,
MAX(Sales) AS Maximum_Sales,
COUNT(*) AS Total_Orders
FROM Orders;
❤1
The process is:
WHERE → Filter rows
GROUP BY → Create groups
SUM → Calculate totals
HAVING → Filter groups
This sequence is fundamental to SQL analysis.
1️⃣9️⃣ ORDER BY with GROUP BY
You can sort aggregated results.
Suppose you want regions with the highest sales first:
Result:
North: 500,000, South: 350,000, West: 200,000, East: 150,000
2️⃣0️⃣ Top 3 Regions
You can combine:
GROUP BY + ORDER BY + LIMIT
For example, in PostgreSQL/MySQL:
This answers:
2️⃣1️⃣ GROUP BY Dates
Suppose you have:
Order_Date and Sales
You might want:
The exact date function varies by database system.
For example, in PostgreSQL:
Result:
2024: 8,500,000, 2025: 10,200,000, 2026: 12,400,000
2️⃣2️⃣ Grouping by Month
In PostgreSQL, you can use:
This creates monthly sales totals.
Different SQL platforms have different date functions, so always check the database you're working with.
2️⃣3️⃣ Calculate Average Order Value
A common business KPI is:
Average Order Value (AOV)
A simple version is:
If each row represents exactly one order.
If the table can contain multiple rows per order, however, you need to calculate the denominator based on distinct orders:
This distinction is extremely important.
2️⃣4️⃣ COUNT(DISTINCT) in Real Analytics
Suppose a customer places multiple orders:
Customer 101 → Orders 5001, 5002
Customer 102 → Order 5003
Customer 103 → Orders 5004, 5005
Total orders: 5
Unique customers: 3
Query:
Result: 3
This is commonly used for metrics such as:
Active customers
Unique users
Unique accounts
Distinct orders
Distinct products
2️⃣5️⃣ Conditional Aggregation
One powerful technique is combining CASE WHEN with aggregate functions.
For example:
This allows you to create customized metrics.
You'll use this technique much more in advanced SQL.
2️⃣6️⃣ Common SQL Analytical Pattern
A very common query structure is:
For example:
WHERE → Filter rows
GROUP BY → Create groups
SUM → Calculate totals
HAVING → Filter groups
This sequence is fundamental to SQL analysis.
1️⃣9️⃣ ORDER BY with GROUP BY
You can sort aggregated results.
Suppose you want regions with the highest sales first:
SELECT
Region,
SUM(Sales) AS Total_Sales
FROM Orders
GROUP BY Region
ORDER BY Total_Sales DESC;
Result:
North: 500,000, South: 350,000, West: 200,000, East: 150,000
2️⃣0️⃣ Top 3 Regions
You can combine:
GROUP BY + ORDER BY + LIMIT
For example, in PostgreSQL/MySQL:
SELECT
Region,
SUM(Sales) AS Total_Sales
FROM Orders
GROUP BY Region
ORDER BY Total_Sales DESC
LIMIT 3;
This answers:
"Which three regions generated the most sales?"
2️⃣1️⃣ GROUP BY Dates
Suppose you have:
Order_Date and Sales
You might want:
Total sales by year.
The exact date function varies by database system.
For example, in PostgreSQL:
SELECT
EXTRACT(YEAR FROM Order_Date) AS Sales_Year,
SUM(Sales) AS Total_Sales
FROM Orders
GROUP BY EXTRACT(YEAR FROM Order_Date)
ORDER BY Sales_Year;
Result:
2024: 8,500,000, 2025: 10,200,000, 2026: 12,400,000
2️⃣2️⃣ Grouping by Month
In PostgreSQL, you can use:
SELECT
DATE_TRUNC('month', Order_Date) AS Sales_Month,
SUM(Sales) AS Total_Sales
FROM Orders
GROUP BY DATE_TRUNC('month', Order_Date)
ORDER BY Sales_Month;
This creates monthly sales totals.
Different SQL platforms have different date functions, so always check the database you're working with.
2️⃣3️⃣ Calculate Average Order Value
A common business KPI is:
Average Order Value (AOV)
A simple version is:
SELECT
SUM(Sales) / COUNT(*) AS Average_Order_Value
FROM Orders;
If each row represents exactly one order.
If the table can contain multiple rows per order, however, you need to calculate the denominator based on distinct orders:
SELECT
SUM(Sales) / COUNT(DISTINCT Order_ID) AS Average_Order_Value
FROM Orders;
This distinction is extremely important.
2️⃣4️⃣ COUNT(DISTINCT) in Real Analytics
Suppose a customer places multiple orders:
Customer 101 → Orders 5001, 5002
Customer 102 → Order 5003
Customer 103 → Orders 5004, 5005
Total orders: 5
Unique customers: 3
Query:
SELECT COUNT(DISTINCT Customer_ID) AS Unique_Customers
FROM Orders;
Result: 3
This is commonly used for metrics such as:
Active customers
Unique users
Unique accounts
Distinct orders
Distinct products
2️⃣5️⃣ Conditional Aggregation
One powerful technique is combining CASE WHEN with aggregate functions.
For example:
Count how many orders were above ₹50,000.
SELECT
SUM(
CASE
WHEN Sales > 50000 THEN 1
ELSE 0
END
) AS High_Value_Orders
FROM Orders;
This allows you to create customized metrics.
You'll use this technique much more in advanced SQL.
2️⃣6️⃣ Common SQL Analytical Pattern
A very common query structure is:
SELECT
Dimension,
AGGREGATE_FUNCTION(Metric) AS KPI
FROM Table
WHERE Condition
GROUP BY Dimension
HAVING Aggregate_Condition
ORDER BY KPI DESC;
For example:
SELECT
Region,
SUM(Sales) AS Total_Sales
FROM Orders
WHERE Order_Date >= '2026-01-01'
GROUP BY Region
HAVING SUM(Sales) > 100000
ORDER BY Total_Sales DESC;
This produces a compact business summary.
For example:
Total_Sales: 15,000,000, Average_Sales: 75,000, Minimum_Sales: 1,000, Maximum_Sales: 500,000, Total_Orders: 200
🔟 Why Do We Need GROUP BY?
Aggregate functions give you an overall summary.
But what if the business asks:
You need to divide the data into groups.
That's what GROUP BY does.
1️⃣1️⃣ Basic GROUP BY
Suppose:
North: 50,000 and 70,000 → Total 120,000
South: 40,000 and 60,000 → Total 100,000
West: 80,000 → Total 80,000
Query:
Result:
North = 120,000, South = 100,000, West = 80,000
Now you've answered:
1️⃣2️⃣ GROUP BY Department
Suppose you have:
John - IT - 75,000
Sarah - HR - 60,000
Mike - IT - 82,000
David - Finance - 90,000
Alice - HR - 65,000
Query:
Result:
Finance: 90,000, HR: 62,500, IT: 78,500
1️⃣3️⃣ GROUP BY with COUNT()
Question:
Result:
IT: 2, HR: 2, Finance: 1
1️⃣4️⃣ GROUP BY with Multiple Columns
You can group by more than one column.
Suppose your sales data contains:
North Electronics: 80,000
North Furniture: 40,000
South Electronics: 70,000
South Furniture: 50,000
Query:
Result:
North Electronics = 80,000, North Furniture = 40,000, South Electronics = 70,000, South Furniture = 50,000
This lets you analyze combinations of dimensions.
1️⃣5️⃣ GROUP BY vs PivotTable
This is an important connection.
In Excel:
Region → Rows
Sales → Values
In SQL:
The analytical concept is very similar.
You're grouping records and calculating an aggregate.
1️⃣6️⃣ HAVING
Now suppose you want:
You can't simply use WHERE on the aggregate result.
You use: HAVING
Result:
North = 120,000
1️⃣7️⃣ WHERE vs HAVING
This is a very common SQL interview question.
WHERE
Filters individual rows before grouping.
Example:
HAVING
Filters groups after aggregation.
Example:
Remember:
1️⃣8️⃣ WHERE + GROUP BY + HAVING
You can use all three.
Question:
Conceptually:
For example:
Total_Sales: 15,000,000, Average_Sales: 75,000, Minimum_Sales: 1,000, Maximum_Sales: 500,000, Total_Orders: 200
🔟 Why Do We Need GROUP BY?
Aggregate functions give you an overall summary.
But what if the business asks:
"What are total sales for each region?"
You need to divide the data into groups.
That's what GROUP BY does.
1️⃣1️⃣ Basic GROUP BY
Suppose:
North: 50,000 and 70,000 → Total 120,000
South: 40,000 and 60,000 → Total 100,000
West: 80,000 → Total 80,000
Query:
SELECT
Region,
SUM(Sales) AS Total_Sales
FROM Orders
GROUP BY Region;
Result:
North = 120,000, South = 100,000, West = 80,000
Now you've answered:
"How much did each region sell?"
1️⃣2️⃣ GROUP BY Department
Suppose you have:
John - IT - 75,000
Sarah - HR - 60,000
Mike - IT - 82,000
David - Finance - 90,000
Alice - HR - 65,000
Query:
SELECT
Department,
AVG(Salary) AS Average_Salary
FROM Employees
GROUP BY Department;
Result:
Finance: 90,000, HR: 62,500, IT: 78,500
1️⃣3️⃣ GROUP BY with COUNT()
Question:
How many employees are in each department?
SELECT
Department,
COUNT(*) AS Employee_Count
FROM Employees
GROUP BY Department;
Result:
IT: 2, HR: 2, Finance: 1
1️⃣4️⃣ GROUP BY with Multiple Columns
You can group by more than one column.
Suppose your sales data contains:
North Electronics: 80,000
North Furniture: 40,000
South Electronics: 70,000
South Furniture: 50,000
Query:
SELECT
Region,
Category,
SUM(Sales) AS Total_Sales
FROM Orders
GROUP BY Region, Category;
Result:
North Electronics = 80,000, North Furniture = 40,000, South Electronics = 70,000, South Furniture = 50,000
This lets you analyze combinations of dimensions.
1️⃣5️⃣ GROUP BY vs PivotTable
This is an important connection.
In Excel:
Region → Rows
Sales → Values
In SQL:
SELECT
Region,
SUM(Sales)
FROM Orders
GROUP BY Region;
The analytical concept is very similar.
You're grouping records and calculating an aggregate.
1️⃣6️⃣ HAVING
Now suppose you want:
"Show only regions where total sales are greater than ₹100,000."
You can't simply use WHERE on the aggregate result.
You use: HAVING
SELECT
Region,
SUM(Sales) AS Total_Sales
FROM Orders
GROUP BY Region
HAVING SUM(Sales) > 100000;
Result:
North = 120,000
1️⃣7️⃣ WHERE vs HAVING
This is a very common SQL interview question.
WHERE
Filters individual rows before grouping.
Example:
SELECT *
FROM Orders
WHERE Region = 'North';
HAVING
Filters groups after aggregation.
Example:
SELECT
Region,
SUM(Sales) AS Total_Sales
FROM Orders
GROUP BY Region
HAVING SUM(Sales) > 100000;
Remember:
WHERE → Filter rows
HAVING → Filter groups
1️⃣8️⃣ WHERE + GROUP BY + HAVING
You can use all three.
Question:
Find regions where 2026 sales exceed ₹100,000.
Conceptually:
SELECT
Region,
SUM(Sales) AS Total_Sales
FROM Orders
WHERE Order_Date >= '2026-01-01'
AND Order_Date < '2027-01-01'
GROUP BY Region
HAVING SUM(Sales) > 100000;
❤1