๐ Excel Basics #32 โ Data Validation
When multiple people enter data into an Excel sheet, incorrect or inconsistent entries can easily create data-quality problems.
For example:
โ Someone enters "Pending"
โ Someone enters "pending"
โ Someone enters "Pendng"
Data Validation helps control what users can enter into a cell.
๐ What is Data Validation?
Data Validation allows you to set rules that restrict or control the type of data entered into a cell.
Go to:
Data โ Data Validation
๐ 1. Create a Drop-Down List
One of the most common uses of Data Validation is creating a dropdown.
Example:
You want users to select only:
โข Pending
โข In Progress
โข Completed
Steps:
1๏ธโฃ Select the cells.
2๏ธโฃ Go to Data โ Data Validation.
3๏ธโฃ Under Allow, select List.
4๏ธโฃ Enter:
Pending,In Progress,Completed
5๏ธโฃ Click OK.
Now users can select a status from a dropdown instead of typing it manually.
๐ 2. Restrict Numbers
You can restrict users to entering numbers within a specific range.
Example:
Allow marks only between 0 and 100.
Go to:
Data Validation โ Allow โ Whole Number
Then set:
between โ 0 โ 100
If someone enters "150", Excel can reject the entry.
๐ 3. Restrict Dates
You can also control which dates users can enter.
Example:
Allow dates only between:
01-Jan-2026 and 31-Dec-2026
This is useful for project trackers, financial reports, and attendance sheets.
๐ 4. Restrict Text Length
You can limit the number of characters entered.
Example:
Employee ID must contain a maximum of 10 characters.
Go to:
Data Validation โ Allow โ Text Length
Then specify the required limit.
๐ 5. Create an Input Message
Data Validation can display instructions when a user selects the cell.
Example:
Input Message:
"Select a valid project status from the dropdown."
This helps users understand what they are expected to enter.
๐ 6. Create an Error Alert
You can decide what happens when someone enters invalid data.
Excel provides options such as:
Stop โ Prevent invalid entry.
Warning โ Warn the user but allow them to continue.
Information โ Display an informational message.
For important business data, Stop is usually the safest option.
๐ Real-World Example
Imagine a project tracker:
Employee | Status | Priority
Rahul | Completed | High
Priya | In Progress | Medium
Amit | Pending | Low
Instead of allowing users to type anything, create dropdowns for:
Status:
โข Pending
โข In Progress
โข Completed
Priority:
โข High
โข Medium
โข Low
This keeps the dataset consistent and easier to analyze.
๐ Common Mistakes
โ Allowing users to type values manually when a dropdown would be better.
โ Not setting an error alert.
โ Applying validation to only part of the required data range.
โ Using inconsistent values in the source list.
โ Best Practices
โข Use dropdowns for fixed categories.
โข Restrict numbers and dates where appropriate.
โข Add helpful input messages.
โข Use meaningful error messages.
โข Apply validation before distributing the workbook.
โข Keep the allowed values standardized.
๐ก Remember:
Data Validation doesn't just make Excel look professional.
It helps improve data quality by controlling what users can enter.
For data analysts, this is especially important because clean and consistent input data leads to more reliable analysis.
Double Tap โค๏ธ For More
When multiple people enter data into an Excel sheet, incorrect or inconsistent entries can easily create data-quality problems.
For example:
โ Someone enters "Pending"
โ Someone enters "pending"
โ Someone enters "Pendng"
Data Validation helps control what users can enter into a cell.
๐ What is Data Validation?
Data Validation allows you to set rules that restrict or control the type of data entered into a cell.
Go to:
Data โ Data Validation
๐ 1. Create a Drop-Down List
One of the most common uses of Data Validation is creating a dropdown.
Example:
You want users to select only:
โข Pending
โข In Progress
โข Completed
Steps:
1๏ธโฃ Select the cells.
2๏ธโฃ Go to Data โ Data Validation.
3๏ธโฃ Under Allow, select List.
4๏ธโฃ Enter:
Pending,In Progress,Completed
5๏ธโฃ Click OK.
Now users can select a status from a dropdown instead of typing it manually.
๐ 2. Restrict Numbers
You can restrict users to entering numbers within a specific range.
Example:
Allow marks only between 0 and 100.
Go to:
Data Validation โ Allow โ Whole Number
Then set:
between โ 0 โ 100
If someone enters "150", Excel can reject the entry.
๐ 3. Restrict Dates
You can also control which dates users can enter.
Example:
Allow dates only between:
01-Jan-2026 and 31-Dec-2026
This is useful for project trackers, financial reports, and attendance sheets.
๐ 4. Restrict Text Length
You can limit the number of characters entered.
Example:
Employee ID must contain a maximum of 10 characters.
Go to:
Data Validation โ Allow โ Text Length
Then specify the required limit.
๐ 5. Create an Input Message
Data Validation can display instructions when a user selects the cell.
Example:
Input Message:
"Select a valid project status from the dropdown."
This helps users understand what they are expected to enter.
๐ 6. Create an Error Alert
You can decide what happens when someone enters invalid data.
Excel provides options such as:
Stop โ Prevent invalid entry.
Warning โ Warn the user but allow them to continue.
Information โ Display an informational message.
For important business data, Stop is usually the safest option.
๐ Real-World Example
Imagine a project tracker:
Employee | Status | Priority
Rahul | Completed | High
Priya | In Progress | Medium
Amit | Pending | Low
Instead of allowing users to type anything, create dropdowns for:
Status:
โข Pending
โข In Progress
โข Completed
Priority:
โข High
โข Medium
โข Low
This keeps the dataset consistent and easier to analyze.
๐ Common Mistakes
โ Allowing users to type values manually when a dropdown would be better.
โ Not setting an error alert.
โ Applying validation to only part of the required data range.
โ Using inconsistent values in the source list.
โ Best Practices
โข Use dropdowns for fixed categories.
โข Restrict numbers and dates where appropriate.
โข Add helpful input messages.
โข Use meaningful error messages.
โข Apply validation before distributing the workbook.
โข Keep the allowed values standardized.
๐ก Remember:
Data Validation doesn't just make Excel look professional.
It helps improve data quality by controlling what users can enter.
For data analysts, this is especially important because clean and consistent input data leads to more reliable analysis.
Double Tap โค๏ธ For More
โค10๐1
๐ฃ๐ฎ๐ ๐๐ณ๐๐ฒ๐ฟ ๐ฃ๐น๐ฎ๐ฐ๐ฒ๐บ๐ฒ๐ป๐โ๐๐ฒ๐ฐ๐ผ๐บ๐ฒ ๐ฎ ๐๐๐น๐น ๐ฆ๐๐ฎ๐ฐ๐ธ ๐๐ฒ๐๐ฒ๐น๐ผ๐ฝ๐ฒ๐ฟ ๐๐ถ๐๐ต ๐๐ฒ๐ป๐๐๐
Curriculum designed and taught by alumni from IITs & leading tech companies.
๐ Placement Highlights:-
๐ฐ โน41 LPA highest salary
๐ โน7.4 LPA average salary
๐ 2,000+ students placed
๐ข 500+ partner companies
๐ ๐๐ฝ๐ฝ๐น๐ ๐ก๐ผ๐ ๐:-
https://pdlink.in/3SuUeuD
โก Take the first step toward your dream tech career today!
Curriculum designed and taught by alumni from IITs & leading tech companies.
๐ Placement Highlights:-
๐ฐ โน41 LPA highest salary
๐ โน7.4 LPA average salary
๐ 2,000+ students placed
๐ข 500+ partner companies
๐ ๐๐ฝ๐ฝ๐น๐ ๐ก๐ผ๐ ๐:-
https://pdlink.in/3SuUeuD
โก Take the first step toward your dream tech career today!
โค1
๐ 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