Data Analytics

🚀 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:

"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.

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

🚀 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:

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.

𝗚𝗼𝗼𝗴𝗹𝗲 𝗙𝗥𝗘𝗘 𝗔𝗜 & 𝗠𝗮𝗰𝗵𝗶𝗻𝗲 𝗟𝗲𝗮𝗿𝗻𝗶𝗻𝗴 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 🚀

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

𝗠𝗶𝗰𝗿𝗼𝘀𝗼𝗳𝘁 𝗮𝗻𝗱 𝗟𝗶𝗻𝗸𝗲𝗱𝗜𝗻 𝗙𝗥𝗘𝗘 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻𝘀🎓

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

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

🚀 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.

🎓 𝐀𝐜𝐜𝐞𝐧𝐭𝐮𝐫𝐞 𝐅𝐑𝐄𝐄 𝐂𝐞𝐫𝐭𝐢𝐟𝐢𝐜𝐚𝐭𝐢𝐨𝐧 𝐂𝐨𝐮𝐫𝐬𝐞𝐬 😍

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

𝗙𝗥𝗘𝗘 𝗚𝗲𝗻𝗔𝗜 + 𝗖𝗹𝗮𝘂𝗱𝗲 𝗢𝗻𝗹𝗶𝗻𝗲 𝗠𝗮𝘀𝘁𝗲𝗿𝗰𝗹𝗮𝘀𝘀😍

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!

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

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.

🚀 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:

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

🚀 𝗙𝗥𝗘𝗘 𝗗𝗮𝘁𝗮 𝗔𝗻𝗮𝗹𝘆𝘁𝗶𝗰𝘀 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗖𝗼𝘂𝗿𝘀𝗲! 📊

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!

Suppose you receive this dataset:

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

MID() extracts text from the middle of a string.

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

🚀 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:

=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()

𝗣𝗮𝘆 𝗔𝗳𝘁𝗲𝗿 𝗣𝗹𝗮𝗰𝗲𝗺𝗲𝗻𝘁—𝗕𝗲𝗰𝗼𝗺𝗲 𝗮 𝗙𝘂𝗹𝗹 𝗦𝘁𝗮𝗰𝗸 𝗗𝗲𝘃𝗲𝗹𝗼𝗽𝗲𝗿 𝘄𝗶𝘁𝗵 𝗚𝗲𝗻𝗔𝗜😍

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!

📊 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

🚀 𝗪𝗶𝗽𝗿𝗼 𝗘𝗹𝗶𝘁𝗲 𝗡𝗧𝗛 & 𝗧𝘂𝗿𝗯𝗼 𝗙𝗥𝗘𝗘 𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄 𝗞𝗶𝘁 💻🔥

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!

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: =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