This calculates the average sales per numeric record.
Or simply:
=AVERAGE(B2:B100)
Understanding both approaches helps you understand what Excel is actually calculating.
1️⃣3️⃣ Using Cell References Instead of Hardcoding
Avoid unnecessary hardcoding.
Instead of:
=SUM(B2:B100)_1.18
you could put the tax rate in another cell.
For example:
F1 = 18%
Then:
=SUM(B2:B100)_(1+$F$1)
Now if the tax rate changes, you only change F1.
This makes your analysis more flexible.
1️⃣4️⃣ Relative References
Consider:
=B2_C2
If you copy this formula to row 3, Excel changes it to:
=B3_C3
This is a relative reference.
It's extremely useful when applying the same calculation to many rows.
1️⃣5️⃣ Absolute References
Suppose:
F1 = 18%
You want to apply this percentage to every row.
Use:
=C2_$F$1
When copied down:
=C3_$F$1
=C4_$F$1
=C5_$F$1
F1 stays fixed.
The $ tells Excel:
1️⃣6️⃣ Mixed References
You may also encounter:
$A1
A$1
$A1
Column A is fixed, row can change.
A$1
Row 1 is fixed, column can change.
These become particularly useful when building complex Excel models.
🧪 Practical Example
Suppose you have:
Employee Sales
John 50,000
Sarah 75,000
Mike 60,000
David 90,000
Alice 45,000
You can calculate:
Total Sales
=SUM(B2:B6)
320,000
Average Sales
=AVERAGE(B2:B6)
64,000
Highest Sales
=MAX(B2:B6)
90,000
Lowest Sales
=MIN(B2:B6)
45,000
Number of Employees
=COUNT(B2:B6)
5
🎯 Mini Interview Challenge
Your interviewer gives you this dataset:
Employee Sales
John 45,000
Sarah 80,000
Mike 65,000
David 95,000
Alice 55,000
They ask:
Q1. What is total sales?
=SUM(B2:B6)
Q2. What is average sales?
=AVERAGE(B2:B6)
Q3. What is the highest sales?
=MAX(B2:B6)
Q4. What is the lowest sales?
=MIN(B2:B6)
Q5. How many employees have sales values?
=COUNT(B2:B6)
If you can answer these comfortably, you've covered the core of Excel Level 2.
🏆 Quick Recap
Double Tap ❤️ For Part-4
Or simply:
=AVERAGE(B2:B100)
Understanding both approaches helps you understand what Excel is actually calculating.
1️⃣3️⃣ Using Cell References Instead of Hardcoding
Avoid unnecessary hardcoding.
Instead of:
=SUM(B2:B100)_1.18
you could put the tax rate in another cell.
For example:
F1 = 18%
Then:
=SUM(B2:B100)_(1+$F$1)
Now if the tax rate changes, you only change F1.
This makes your analysis more flexible.
1️⃣4️⃣ Relative References
Consider:
=B2_C2
If you copy this formula to row 3, Excel changes it to:
=B3_C3
This is a relative reference.
It's extremely useful when applying the same calculation to many rows.
1️⃣5️⃣ Absolute References
Suppose:
F1 = 18%
You want to apply this percentage to every row.
Use:
=C2_$F$1
When copied down:
=C3_$F$1
=C4_$F$1
=C5_$F$1
F1 stays fixed.
The $ tells Excel:
Don't move this reference.
1️⃣6️⃣ Mixed References
You may also encounter:
$A1
A$1
$A1
Column A is fixed, row can change.
A$1
Row 1 is fixed, column can change.
These become particularly useful when building complex Excel models.
🧪 Practical Example
Suppose you have:
Employee Sales
John 50,000
Sarah 75,000
Mike 60,000
David 90,000
Alice 45,000
You can calculate:
Total Sales
=SUM(B2:B6)
320,000
Average Sales
=AVERAGE(B2:B6)
64,000
Highest Sales
=MAX(B2:B6)
90,000
Lowest Sales
=MIN(B2:B6)
45,000
Number of Employees
=COUNT(B2:B6)
5
🎯 Mini Interview Challenge
Your interviewer gives you this dataset:
Employee Sales
John 45,000
Sarah 80,000
Mike 65,000
David 95,000
Alice 55,000
They ask:
Q1. What is total sales?
=SUM(B2:B6)
Q2. What is average sales?
=AVERAGE(B2:B6)
Q3. What is the highest sales?
=MAX(B2:B6)
Q4. What is the lowest sales?
=MIN(B2:B6)
Q5. How many employees have sales values?
=COUNT(B2:B6)
If you can answer these comfortably, you've covered the core of Excel Level 2.
🏆 Quick Recap
"What is the total?" → SUM()
"What is the average?" → AVERAGE()
"What is the highest?" → MAX()
"What is the lowest?" → MIN()
"How many numeric records?" → COUNT()
"How many non-empty records?" → COUNTA()
"How many missing values?" → COUNTBLANK()
Double Tap ❤️ For Part-4
❤16
𝗪𝗢𝗥𝗞 𝗙𝗥𝗢𝗠 𝗛𝗢𝗠𝗘 𝗝𝗢𝗕 𝗢𝗣𝗣𝗢𝗥𝗧𝗨𝗡𝗜𝗧𝗬 😍
Company Name :- AI InsurTech Company
💼 𝗥𝗼𝗹𝗲: Backend Developer
💰 𝗦𝗮𝗹𝗮𝗿𝘆: ₹5 LPA
🏠 𝗪𝗼𝗿𝗸 𝗠𝗼𝗱𝗲: Work From Home
📍 𝗟𝗼𝗰𝗮𝘁𝗶𝗼𝗻: Hyderabad / Remote
🎓 𝗪𝗵𝗼 𝗖𝗮𝗻 𝗔𝗽𝗽𝗹𝘆?
✅ BTech/BE graduates
✅ Branches: CS, IT, AI, ML and Data-related streams
✅ Graduation Years: 2025 and 2026
🔗 𝗔𝗽𝗽𝗹𝘆 𝗡𝗼𝘄 👇:-
https://pdlink.in/4xIfsE4
⚡ Apply early and share this opportunity with your friends!
Company Name :- AI InsurTech Company
💼 𝗥𝗼𝗹𝗲: Backend Developer
💰 𝗦𝗮𝗹𝗮𝗿𝘆: ₹5 LPA
🏠 𝗪𝗼𝗿𝗸 𝗠𝗼𝗱𝗲: Work From Home
📍 𝗟𝗼𝗰𝗮𝘁𝗶𝗼𝗻: Hyderabad / Remote
🎓 𝗪𝗵𝗼 𝗖𝗮𝗻 𝗔𝗽𝗽𝗹𝘆?
✅ BTech/BE graduates
✅ Branches: CS, IT, AI, ML and Data-related streams
✅ Graduation Years: 2025 and 2026
🔗 𝗔𝗽𝗽𝗹𝘆 𝗡𝗼𝘄 👇:-
https://pdlink.in/4xIfsE4
⚡ Apply early and share this opportunity with your friends!
❤3
🗄️ How to Solve SQL Problems
If you are a beginner, don't try to write the entire SQL query immediately. The easiest approach is to break the problem into small steps.
📌 Step 1: Understand What the Question Is Asking
Read the question carefully and identify the final output.
Example:
Ask yourself:
👉 What do I need to display?
Answer:
Customer
Total Sales
📌 Step 2: Identify the Table
Find which table contains the required information.
Suppose you have:
sales
customer_id
product
quantity
price
You need the sales table.
📌 Step 3: Identify the Required Columns
For:
You need:
customer_id
quantity
price
Because: Sales = quantity × price
📌 Step 4: Decide Whether You Need Filtering
Ask:
For example:
Now you need a WHERE condition.
WHERE order_date >= '2026-01-01'
📌 Step 5: Decide Whether You Need GROUP BY
Look for words such as: Each customer, Each department, Per product, By region, By month
These usually indicate GROUP BY.
For example:
GROUP BY customer_id
📌 Step 6: Identify the Required Aggregate Function
Look for words like:
Total → SUM()
Average → AVG()
Count → COUNT()
Maximum → MAX()
Minimum → MIN()
For total sales:
SUM(quantity _ price)
📌 Step 7: Build the Query Step by Step
Instead of writing everything at once:
1.
SELECT customer_id FROM sales;
2.
Add the calculation:
SELECT customer_id, SUM(quantity _ price) AS total_sales FROM sales;
3.
Add grouping:
SELECT
customer_id,
SUM(quantity ** price) AS total_sales
FROM sales
GROUP BY customer_id;
Now the query is complete.
📌 Step 8: Check Whether You Need HAVING
Suppose the question changes to:
You cannot use WHERE on SUM(). Use HAVING:
SELECT
customer_id,
SUM(quantity ** price) AS total_sales
FROM sales
GROUP BY customer_id
HAVING SUM(quantity ** price) > 50000;
📌 Step 9: Check Whether You Need a JOIN
Suppose the question says:
You have:
customers: customer_id, customer_name
sales: customer_id, quantity, price
Now you need a JOIN.
SELECT
c.customer_name,
SUM(s.quantity ** s.price) AS total_sales
FROM customers c
JOIN sales s
ON c.customer_id = s.customer_id
GROUP BY c.customer_name;
📌 Step 10: Validate Your Answer
Before considering the problem solved, check:
✓ Did I use the correct table?
✓ Did I select the correct columns?
✓ Is my JOIN correct?
✓ Did I handle NULL values?
✓ Did I accidentally create duplicates?
✓ Did I use WHERE or HAVING correctly?
✓ Does the output actually answer the question?
🧠 Use This SQL Problem-Solving Framework
Whenever you get a SQL question, think:
1. What is being asked?
2. Which table(s) do I need?
3. Which columns do I need?
4. Do I need filtering?
5. Do I need a JOIN?
6. Do I need aggregation?
7. Do I need GROUP BY?
8. Do I need HAVING?
9.
Do I need a window function?
10. Validate the result
🔥 Double Tap ❤️ For More SQL Tips
If you are a beginner, don't try to write the entire SQL query immediately. The easiest approach is to break the problem into small steps.
📌 Step 1: Understand What the Question Is Asking
Read the question carefully and identify the final output.
Example:
Find the total sales for each customer.
Ask yourself:
👉 What do I need to display?
Answer:
Customer
Total Sales
📌 Step 2: Identify the Table
Find which table contains the required information.
Suppose you have:
sales
customer_id
product
quantity
price
You need the sales table.
📌 Step 3: Identify the Required Columns
For:
Find total sales for each customer.
You need:
customer_id
quantity
price
Because: Sales = quantity × price
📌 Step 4: Decide Whether You Need Filtering
Ask:
Do I need only certain rows?
For example:
Find total sales for customers who purchased in 2026.
Now you need a WHERE condition.
WHERE order_date >= '2026-01-01'
📌 Step 5: Decide Whether You Need GROUP BY
Look for words such as: Each customer, Each department, Per product, By region, By month
These usually indicate GROUP BY.
For example:
Find total sales for each customer.
GROUP BY customer_id
📌 Step 6: Identify the Required Aggregate Function
Look for words like:
Total → SUM()
Average → AVG()
Count → COUNT()
Maximum → MAX()
Minimum → MIN()
For total sales:
SUM(quantity _ price)
📌 Step 7: Build the Query Step by Step
Instead of writing everything at once:
1.
SELECT customer_id FROM sales;
2.
Add the calculation:
SELECT customer_id, SUM(quantity _ price) AS total_sales FROM sales;
3.
Add grouping:
SELECT
customer_id,
SUM(quantity ** price) AS total_sales
FROM sales
GROUP BY customer_id;
Now the query is complete.
📌 Step 8: Check Whether You Need HAVING
Suppose the question changes to:
Find customers whose total sales are greater than ₹50,000.
You cannot use WHERE on SUM(). Use HAVING:
SELECT
customer_id,
SUM(quantity ** price) AS total_sales
FROM sales
GROUP BY customer_id
HAVING SUM(quantity ** price) > 50000;
📌 Step 9: Check Whether You Need a JOIN
Suppose the question says:
Find the names of customers and their total sales.
You have:
customers: customer_id, customer_name
sales: customer_id, quantity, price
Now you need a JOIN.
SELECT
c.customer_name,
SUM(s.quantity ** s.price) AS total_sales
FROM customers c
JOIN sales s
ON c.customer_id = s.customer_id
GROUP BY c.customer_name;
📌 Step 10: Validate Your Answer
Before considering the problem solved, check:
✓ Did I use the correct table?
✓ Did I select the correct columns?
✓ Is my JOIN correct?
✓ Did I handle NULL values?
✓ Did I accidentally create duplicates?
✓ Did I use WHERE or HAVING correctly?
✓ Does the output actually answer the question?
🧠 Use This SQL Problem-Solving Framework
Whenever you get a SQL question, think:
1. What is being asked?
2. Which table(s) do I need?
3. Which columns do I need?
4. Do I need filtering?
5. Do I need a JOIN?
6. Do I need aggregation?
7. Do I need GROUP BY?
8. Do I need HAVING?
9.
Do I need a window function?
10. Validate the result
🔥 Double Tap ❤️ For More SQL Tips
❤17🔥2
☁️ 𝟰 𝗙𝗥𝗘𝗘 𝗚𝗼𝗼𝗴𝗹𝗲 𝗖𝗹𝗼𝘂𝗱 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 | 𝗕𝘂𝗶𝗹𝗱 𝗜𝗻-𝗗𝗲𝗺𝗮𝗻𝗱 𝗖𝗹𝗼𝘂𝗱 𝗦𝗸𝗶𝗹𝗹𝘀
Explore these Google Cloud learning resources covering cloud fundamentals, infrastructure, networking, security, data and AI/ML.
🔥 4 Courses to Explore:
1️⃣ Cloud Computing Fundamentals
2️⃣ Infrastructure in Google Cloud
3️⃣ Networking & Security in Google Cloud
4️⃣ Data, ML & AI in Google Cloud
🔗 𝗘𝗻𝗿𝗼𝗹𝗹 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘👇:-
https://pdlink.in/4zrksPn
🎯 Perfect for Students | Freshers | Developers | Cloud & DevOps Aspirants
Explore these Google Cloud learning resources covering cloud fundamentals, infrastructure, networking, security, data and AI/ML.
🔥 4 Courses to Explore:
1️⃣ Cloud Computing Fundamentals
2️⃣ Infrastructure in Google Cloud
3️⃣ Networking & Security in Google Cloud
4️⃣ Data, ML & AI in Google Cloud
🔗 𝗘𝗻𝗿𝗼𝗹𝗹 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘👇:-
https://pdlink.in/4zrksPn
🎯 Perfect for Students | Freshers | Developers | Cloud & DevOps Aspirants
🚀 𝗔𝗜 & 𝗠𝗮𝗰𝗵𝗶𝗻𝗲 𝗟𝗲𝗮𝗿𝗻𝗶𝗻𝗴 𝗙𝗥𝗘𝗘 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗖𝗼𝘂𝗿𝘀𝗲
🔥 Upgrade your skills and prepare for exciting career opportunities in AI!
✅ Beginner-friendly course
✅ Learn AI & Machine Learning fundamentals
✅ Gain practical, job-ready skills
✅ Earn a FREE certificate
✅ Boost your resume and LinkedIn profile
✅ Ideal for students, freshers and professionals
🔗 𝗘𝗻𝗿𝗼𝗹𝗹 𝗳𝗼𝗿 𝗙𝗥𝗘𝗘 👇:-
https://pdlink.in/4zrkYNg
⚡ Limited opportunity—start learning today!
🔥 Upgrade your skills and prepare for exciting career opportunities in AI!
✅ Beginner-friendly course
✅ Learn AI & Machine Learning fundamentals
✅ Gain practical, job-ready skills
✅ Earn a FREE certificate
✅ Boost your resume and LinkedIn profile
✅ Ideal for students, freshers and professionals
🔗 𝗘𝗻𝗿𝗼𝗹𝗹 𝗳𝗼𝗿 𝗙𝗥𝗘𝗘 👇:-
https://pdlink.in/4zrkYNg
⚡ Limited opportunity—start learning today!
❤1
🚀 Data Analyst Roadmap — Part 4
📊 Excel — Level 3: Conditional Functions
Now that you understand basic Excel formulas, the next step is learning how to make Excel make decisions based on conditions.
This is a very important skill for Data Analysts because real-world questions are rarely just:
Instead, you'll get questions like:
To answer these questions, you need conditional functions.
1️⃣ IF()
IF() is one of the most important Excel functions.
It allows Excel to make a decision.
Syntax
Think of it as:
Example
Suppose sales are in B2.
You want to classify employees:
Sales ≥ 50,000 → High
Sales < 50,000 → Low
If B2 is:
75,000
Result: High
If B2 is:
35,000
Result: Low
2️⃣ IF() in Real-World Data Analysis
Suppose you have:
Employee | Sales
John | 75,000
Sarah | 45,000
Mike | 90,000
David | 30,000
You can create a performance column:
Result:
Employee | Sales | Status
John | 75,000 | Target Achieved
Sarah | 45,000 | Target Not Achieved
Mike | 90,000 | Target Achieved
David | 30,000 | Target Not Achieved
This is called data categorization.
3️⃣ Multiple Conditions with Nested IF()
Sometimes you need more than two categories.
For example:
≥ 80,000 → Excellent
≥ 60,000 → Good
≥ 40,000 → Average
< 40,000 → Poor
You can use:
Excel checks the conditions from left to right.
Important: The order matters. You should generally check the highest threshold first.
4️⃣ IFS()
IFS() is a cleaner alternative when you have multiple conditions.
The first condition that evaluates to TRUE determines the result.
IF vs IFS
Use:
5️⃣ AND()
AND() checks whether all conditions are true.
Example
You want to identify employees who:
Belong to IT AND earn more than ₹80,000
Both conditions must be true.
6️⃣ Combining IF() + AND()
This is more useful in real analysis.
Meaning:
7️⃣ OR()
OR() checks whether at least one condition is true.
Example:
You want to identify employees who belong to either:
IT OR Finance
If either condition is true, the result is TRUE.
8️⃣ Combining IF() + OR()
📊 Excel — Level 3: Conditional Functions
Now that you understand basic Excel formulas, the next step is learning how to make Excel make decisions based on conditions.
This is a very important skill for Data Analysts because real-world questions are rarely just:
"What is the total?"
Instead, you'll get questions like:
"What are the total sales for the IT department?"
"How many employees earn more than ₹80,000?"
"What is the average sales for the North region?"
"Which employees achieved their target?"
To answer these questions, you need conditional functions.
1️⃣ IF()
IF() is one of the most important Excel functions.
It allows Excel to make a decision.
Syntax
=IF(condition, value_if_true, value_if_false)Think of it as:
If something is true → do this; otherwise → do that.
Example
Suppose sales are in B2.
You want to classify employees:
Sales ≥ 50,000 → High
Sales < 50,000 → Low
=IF(B2>=50000,"High","Low")If B2 is:
75,000
Result: High
If B2 is:
35,000
Result: Low
2️⃣ IF() in Real-World Data Analysis
Suppose you have:
Employee | Sales
John | 75,000
Sarah | 45,000
Mike | 90,000
David | 30,000
You can create a performance column:
=IF(B2>=50000,"Target Achieved","Target Not Achieved")Result:
Employee | Sales | Status
John | 75,000 | Target Achieved
Sarah | 45,000 | Target Not Achieved
Mike | 90,000 | Target Achieved
David | 30,000 | Target Not Achieved
This is called data categorization.
3️⃣ Multiple Conditions with Nested IF()
Sometimes you need more than two categories.
For example:
≥ 80,000 → Excellent
≥ 60,000 → Good
≥ 40,000 → Average
< 40,000 → Poor
You can use:
=IF(B2>=80000,"Excellent",IF(B2>=60000,"Good",IF(B2>=40000,"Average","Poor")))Excel checks the conditions from left to right.
Important: The order matters. You should generally check the highest threshold first.
4️⃣ IFS()
IFS() is a cleaner alternative when you have multiple conditions.
=IFS(
B2>=80000,"Excellent",
B2>=60000,"Good",
B2>=40000,"Average",
TRUE,"Poor"
)
The first condition that evaluates to TRUE determines the result.
IF vs IFS
Use:
IF() → simple decisionsIFS() → multiple conditions 5️⃣ AND()
AND() checks whether all conditions are true.
Example
You want to identify employees who:
Belong to IT AND earn more than ₹80,000
=AND(B2="IT",C2>80000)Both conditions must be true.
6️⃣ Combining IF() + AND()
This is more useful in real analysis.
=IF(AND(B2="IT",C2>80000),"Eligible","Not Eligible")Meaning:
If the employee is from IT AND salary is greater than ₹80,000, return "Eligible".
Otherwise: "Not Eligible"
7️⃣ OR()
OR() checks whether at least one condition is true.
Example:
You want to identify employees who belong to either:
IT OR Finance
=OR(B2="IT",B2="Finance")If either condition is true, the result is TRUE.
8️⃣ Combining IF() + OR()
=IF(
OR(B2="IT",B2="Finance"),
"Technical Department",
"Other"
)
This is extremely useful for business analysis.
1️⃣8️⃣ Understand IF vs IF Functions
This distinction is important.
IF()
Used to make a decision.
Example:
SUMIF()
Used to calculate a sum based on a condition.
Example:
COUNTIF()
Used to count records based on a condition.
Example:
AVERAGEIF()
Used to calculate an average based on a condition.
Example:
Think:
IF → Decision
SUMIF → Conditional Total
COUNTIF → Conditional Count
AVERAGEIF → Conditional Average
🧪 Practical Interview Challenge
Suppose you have:
Employee | Department | Salary
John | IT | 75,000
Sarah | HR | 60,000
Mike | IT | 82,000
David | Finance | 90,000
Alice | HR | 65,000
Your interviewer asks:
Q1. Is John earning more than ₹70,000?
Q2. How many employees are in IT?
Q3. What is the total IT salary?
Q4. What is the average IT salary?
Q5. How many IT employees earn more than ₹80,000?
Q6. What is the total salary of IT employees earning more than ₹70,000?
🏆 Key Lesson
Understand the question first.
"Should I classify this record?"
→ IF()
"How much in total?"
→ SUMIF() / SUMIFS()
"How many?"
→ COUNTIF() / COUNTIFS()
"What's the average?"
→ AVERAGEIF() / AVERAGEIFS()
One condition?
→ IF version
Multiple conditions?
→ IFS version
Double Tap ❤️ For Part-5
1️⃣8️⃣ Understand IF vs IF Functions
This distinction is important.
IF()
Used to make a decision.
Example:
=IF(C2>=50000,"High","Low")SUMIF()
Used to calculate a sum based on a condition.
Example:
=SUMIF(B2:B100,"IT",C2:C100)COUNTIF()
Used to count records based on a condition.
Example:
=COUNTIF(B2:B100,"IT")AVERAGEIF()
Used to calculate an average based on a condition.
Example:
=AVERAGEIF(B2:B100,"IT",C2:C100)Think:
IF → Decision
SUMIF → Conditional Total
COUNTIF → Conditional Count
AVERAGEIF → Conditional Average
🧪 Practical Interview Challenge
Suppose you have:
Employee | Department | Salary
John | IT | 75,000
Sarah | HR | 60,000
Mike | IT | 82,000
David | Finance | 90,000
Alice | HR | 65,000
Your interviewer asks:
Q1. Is John earning more than ₹70,000?
=IF(C2>70000,"Yes","No")Q2. How many employees are in IT?
=COUNTIF(B2:B6,"IT")Q3. What is the total IT salary?
=SUMIF(B2:B6,"IT",C2:C6)Q4. What is the average IT salary?
=AVERAGEIF(B2:B6,"IT",C2:C6)Q5. How many IT employees earn more than ₹80,000?
=COUNTIFS(B2:B6,"IT",C2:C6,">80000")Q6. What is the total salary of IT employees earning more than ₹70,000?
=SUMIFS(C2:C6,B2:B6,"IT",C2:C6,">70000")🏆 Key Lesson
Understand the question first.
"Should I classify this record?"
→ IF()
"How much in total?"
→ SUMIF() / SUMIFS()
"How many?"
→ COUNTIF() / COUNTIFS()
"What's the average?"
→ AVERAGEIF() / AVERAGEIFS()
One condition?
→ IF version
Multiple conditions?
→ IFS version
Double Tap ❤️ For Part-5
❤11👍1
🚀 𝗪𝗶𝗽𝗿𝗼 𝗘𝗹𝗶𝘁𝗲 𝗡𝗧𝗛 & 𝗧𝘂𝗿𝗯𝗼 𝗙𝗥𝗘𝗘 𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄 𝗞𝗶𝘁 💻🔥
Get access to a FREE interview preparation kit and prepare smarter for your upcoming assessment & interview rounds.
📚 Prepare For:-
✅ Technical Interview Questions
✅ Software Engineer Interview Rounds
✅ Interview Preparation Resources
🎯 Perfect for Students | Freshers | Engineering Graduates | Wipro Aspirants
🔗 𝗚𝗲𝘁 𝗙𝗥𝗘𝗘 𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄 𝗞𝗶𝘁 👇:-
https://pdlink.in/4zh9E6g
🔥 Start preparing early and improve your chances of cracking the Wipro hiring process!
Get access to a FREE interview preparation kit and prepare smarter for your upcoming assessment & interview rounds.
📚 Prepare For:-
✅ Technical Interview Questions
✅ Software Engineer Interview Rounds
✅ Interview Preparation Resources
🎯 Perfect for Students | Freshers | Engineering Graduates | Wipro Aspirants
🔗 𝗚𝗲𝘁 𝗙𝗥𝗘𝗘 𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄 𝗞𝗶𝘁 👇:-
https://pdlink.in/4zh9E6g
🔥 Start preparing early and improve your chances of cracking the Wipro hiring process!
❤1
📊 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()
❤2
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
❤3
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!
👍4
🚀 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
❤1
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.
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
❤3