Level 1 — Power BI Fundamentals

Desktop, Service, Reports, Dashboards, Workspaces, Data sources, Import mode, DirectQuery, Semantic models

Level 2 — Power Query

Data cleaning, transformations, merge, append, group, pivot/unpivot, conditional/custom columns, data types

🧮 STEP 8 — DAX

SUM, COUNT, COUNTROWS, DISTINCTCOUNT, AVERAGE, MIN, MAX

CALCULATE, FILTER, ALL, ALLSELECTED, REMOVEFILTERS, VALUES, SELECTEDVALUE

SUMX, AVERAGEX, COUNTX, MINX, MAXX

Time Intelligence: TOTALYTD, TOTALMTD, TOTALQTD, SAMEPERIODLASTYEAR, DATEADD, DATESYTD, DATESMTD

Measures: YTD, MTD, QTD, Previous Year, YoY %, Running Total, Rolling 12M, Market Share, Contribution %

🏗️ STEP 9 — Data Modeling

Fact tables, Dimension tables, Star schema, Snowflake schema, Relationships, Cardinality, Cross-filter direction, Active/Inactive relationships, Role-playing dimensions, Date tables

🎨 STEP 10 — Power BI Visualization

Cards, Tables, Matrix, Bar, Column, Line, Area, Scatter, Map, Treemap, Waterfall, KPI, Decomposition Tree, Drill-through, Tooltips, Bookmarks, Buttons, Slicers

Data storytelling: What happened? Why? Where? Who/What? What next?

🐍 STEP 11 — Python for Data Analysis

⏱️ Time: 3–4 weeks

Basics: Variables, Data Types, Lists, Tuples, Sets, Dicts, If/Else, Loops, Functions, Lambda, Exception Handling

NumPy: Arrays, Indexing, Vectorization, Math operations

Pandas: DataFrame, Series, read_csv(), read_excel(), head(), info(), describe(), loc[], iloc[], groupby(), merge(), concat(), pivot_table(), sort_values(), drop_duplicates(), fillna(), dropna(), apply()

Visualization: Matplotlib, Seaborn: Bar, Line, Histogram, Scatter, Box, Heatmap

🎯 Python Project

Customer Sales & Churn Analysis: Cleaning, EDA, Segmentation, Revenue analysis, Churn patterns, Visuals, Recommendations

🧹 STEP 12 — Data Cleaning

Missing values, duplicates, wrong data types, outliers, inconsistent categories, invalid dates, bad formats, negative values, duplicate transactions, data integrity

Practice in: Excel → Power Query → SQL → Python

🏢 STEP 13 — Business & Domain Knowledge

Sales: Revenue, AOV, Conversion Rate, Growth, Gross Margin

Marketing: CAC, CTR, CPC, ROAS, Retention

Product: DAU, MAU, Retention, Churn, Activation, Engagement

Finance: Revenue, Profit, EBITDA, Cost, Margin, Budget vs Actual, Forecast

Operations: SLA, Productivity, Turnaround Time, Error Rate, Capacity, Utilization

🤖 STEP 14 — AI for Data Analysts in 2026

Use AI for: SQL help, DAX help, Excel formulas, Python debugging, Data cleaning, Documentation, Storytelling, Root-cause analysis, Hypotheses, Analysis plans

Limitations: Hallucinations, Incorrect SQL, Wrong assumptions, Data privacy, Poor context

Mindset: AI augments analysts, doesn't replace thinking

☁️ STEP 15 — Cloud & Data Platforms

Azure, AWS, Google Cloud, Databricks, Snowflake

Concepts: Data warehouse, Data lake, Lakehouse, ETL, ELT, Pipelines, Batch processing, APIs

📁 STEP 16 — Build a Portfolio

Project 1 — Sales Analytics: Excel + SQL + Power BI → Revenue, Profit, Products, Regions, Customers, Trends

Project 2 — Customer Churn: SQL + Python + Power BI → Churn rate, Segments, Retention, Revenue at risk

Project 3 — Financial Analysis: Excel + Power BI → P&L, Budget vs Actual, Variance, Trends

Project 4 — E-commerce Analytics: SQL + Python + Power BI → Orders, Conversion, AOV, CLV

Project 5 — HR Analytics: Excel + SQL + Power BI → Headcount, Attrition, Salary, Tenure

🧠 STEP 17 — Explain Your Projects

Business Problem → Data → Cleaning → Transformation → Analysis → Visualization → Insights → Recommendations → Impact

💼 STEP 18 — Build Your Resume

🔎 STEP 19 — LinkedIn & GitHub

LinkedIn: Headline, About, Skills, Projects, Certifications, Posts on SQL, Power BI, Excel, Projects, Insights

GitHub: SQL projects, Python notebooks, Docs, Screenshots, Data dictionaries, README

🎤 STEP 20 — Interview Preparation

Excel: XLOOKUP, INDEX/MATCH, SUMIFS, COUNTIFS, PivotTables, Power Query

SQL: Joins, Aggregations, CTEs, Subqueries, Window functions, Ranking, Running totals

Power BI: DAX, CALCULATE, Data modeling, Relationships, Time intelligence

Python: Pandas, GroupBy, Merge, EDA

Business Cases: Sales drop, Churn increase, Revenue up but profit down, KPI anomaly

🗓️ Double Tap ❤️ For Detailed Explanation

💻 𝗠𝗮𝘀𝘁𝗲𝗿 𝗦𝗤𝗟 𝗳𝗼𝗿 𝗙𝗥𝗘𝗘 | 𝟱 𝗕𝗲𝘀𝘁 𝗬𝗼𝘂𝗧𝘂𝗯𝗲 𝗖𝗵𝗮𝗻𝗻𝗲𝗹𝘀 🚀

Want to learn SQL from scratch to advanced level without spending anything? These 5 YouTube channels offer tutorials, practical examples and problem-solving content.

🔥 Learn → Practice → Build Projects → Prepare for SQL Interviews

🔗 𝗘𝗻𝗿𝗼𝗹𝗹 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘👇:- 

https://pdlink.in/4wCjU6x

📊 Perfect for Students | Freshers | Data Analyst Aspirants | SQL Beginners

𝗙𝗥𝗘𝗘 𝗠𝗮𝘀𝘁𝗲𝗿𝗰𝗹𝗮𝘀𝘀 𝗢𝗻 𝗟𝗮𝘁𝗲𝘀𝘁 𝗧𝗲𝗰𝗵𝗻𝗼𝗹𝗼𝗴𝗶𝗲𝘀 😍
- AI
- Data Analytics
- Data Science
- CloudComputing
- Cyber Security
​
💫Build a Future Ready Career in the AI Era
​
💫Learn the Skills, Hiring Trends, and Preparation Strategies That Matter
​
𝗥𝗲𝗴𝗶𝘀𝘁𝗲𝗿 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘 👇:-
​
https://pdlink.in/45w4ztg
​
(Only few slots left )
​
Date & Time :- 18th August 2026 & 7PM

🚀 Data Analyst Roadmap — Part 1

🧠 Understanding the Data Analyst Role

Before learning Excel, SQL, Power BI, Python, or any other tool, you need to understand what a Data Analyst actually does.

Many beginners make the mistake of starting with tools.

They learn: Excel → SQL → Power BI → Python

But they don't understand why they're using these tools.

A good Data Analyst doesn't simply know how to write SQL or create dashboards.

A good Data Analyst knows how to turn a business problem into a data-driven answer.

1️⃣ What is Data Analytics?

Data Analytics is the process of examining data to find: Patterns, Trends, Relationships, Problems, Opportunities, Insights

The ultimate goal is to help an organization make better decisions using data.

Simple way to remember it:

Raw Data → Clean Data → Analysis → Insights → Decision

For example:

A company has thousands of sales transactions.

Raw data alone doesn't tell the business much.

After analyzing it, you might discover:

"Sales increased by 12%, but profit decreased by 5% because high-volume products had significantly lower margins."

That's a useful business insight.

2️⃣ What Does a Data Analyst Actually Do?

A Data Analyst can be involved in several stages of the data lifecycle.

📥 Step 1 — Collect Data

Data can come from: Databases, Excel files, CSV files, APIs, CRM systems, ERP systems, Cloud platforms, Business applications

Example: A sales analyst might receive data from a company's CRM and transactional database.

🧹 Step 2 — Clean the Data

Real-world data is rarely perfect.

You may encounter: Missing values, Duplicate records, Incorrect dates, Wrong data types, Spelling inconsistencies, Invalid transactions, Outliers, Duplicate customers

Example: India, India, india, INDIA, Ind ia all represent the same country but appear as different values.

A Data Analyst needs to identify and fix such problems before performing analysis.

🔄 Step 3 — Transform the Data

Sometimes the data needs to be converted into a useful structure.

Examples: Order Date → Month/Quarter/Year, Sales - Cost = Profit, Profit / Sales × 100 = Profit Margin %

This is where tools like SQL, Excel Power Query, Python and Power BI become extremely useful.

🔍 Step 4 — Analyze the Data

Now you start asking questions:

What are our total sales? Which product sells the most? Which region is underperforming? Why did sales decline? Which customers are most valuable?

This is where analytical thinking becomes more important than simply knowing a tool.

📊 Step 5 — Visualize the Data

Once you have analyzed the data, you need to communicate the findings.

You might create: Charts, Reports, Dashboards, KPI cards, Tables, Interactive visualizations

Tools: Excel → Power BI → Tableau

💡 Step 6 — Generate Insights

A visualization isn't automatically an insight.

❌ "North region sales are ₹10 crore." → That's a metric.

✅ "North region sales declined 18% over the last quarter, primarily driven by a decline in enterprise customers." → Tells what happened and why it matters.

🎯 Step 7 — Support Business Decisions

The final goal is action.

"Enterprise customers in the North region have declining purchase frequency.

The business should investigate customer retention and pricing issues in this segment."

3️⃣ A Real-World Example

Manager: "Sales dropped 15% last month. Find out why."

A beginner opens Power BI and creates a chart.

An analyst breaks down the problem: 

1. Did sales actually decline? Compare Current Month vs Previous Month 

2. Where did the decline happen? Region, Country, Department, Sales channel 

3. Which products caused the decline? 

4. Did the number of orders decrease? Check Order Volume 

5. Did customers spend less? Check Average Order Value 

6. Did existing customers stop purchasing? Analyze retention and frequency 

7. Was the decline caused by pricing? Compare Price → Quantity → Revenue → Profit

Result: "Sales declined 15%, mainly because enterprise orders in the North region decreased by 30%. Product A accounted for nearly 60% of the decline."

That's what Data Analytics is about.

4️⃣ The 4 Types of Data Analytics

🟢 Descriptive Analytics: What happened? → "Revenue decreased 10% in Q2."

🟡 Diagnostic Analytics: Why did it happen? → "Revenue decreased because customer orders declined in the North region."

🔵 Predictive Analytics: What might happen next? → "Based on current trends, revenue could decline further next quarter."

🟣 Prescriptive Analytics: What should we do? → "Increasing retention efforts for high-value customers could reduce the expected revenue loss."

As a Data Analyst, you'll spend a lot of time on descriptive and diagnostic analytics.

5️⃣ Data Analyst vs Data Scientist vs Data Engineer

📊 Data Analyst: Focus on Business questions, Reporting, Dashboards, KPIs, Trends, Insights.

Tools: Excel, SQL, Power BI, Tableau, Python

🤖 Data Scientist: Focus on Machine Learning, Predictive modeling, Statistical modeling, Forecasting

⚙️ Data Engineer: Focus on Data pipelines, ETL/ELT, Data warehouses, Data lakes, Data platforms

6️⃣ The Most Important Skill: Analytical Thinking

You can learn SQL syntax, DAX, Power BI. But you still need to learn how to think about data.

Ask: What happened? → Where did it happen? → Why did it happen? → How significant is it? → What should we do?

This mindset separates someone who knows analytics tools from someone who can actually work as an analyst.

🎯 Your First Practice Exercise

Dataset: Customer ID, Order ID, Order Date, Product, Category, Region, Quantity, Sales, Cost, Profit

Manager: "Give me an overview of business performance." 

Before opening any tool, write 10 questions: 

1. What is total revenue? 

2. What is total profit? 

3. What is the profit margin? 

4. Which products generate the most revenue? 

5. Which products generate the most profit? 

6. Which regions perform best? 

7. What is the monthly sales trend? 

8. Who are the highest-value customers? 

9. What is the average order value? 

10. What factors are driving changes in revenue?

🏆 Remember this framework:

Business Problem → Analytical Questions → Collect Data → Clean Data → Transform Data → Analyze Data → Visualize → Find Insights → Recommend Action → Business Decision

💡 Excel, SQL, Power BI and Python are tools.

Your real value as a Data Analyst comes from your ability to ask the right questions, analyze the data correctly, explain what you found, and connect it to a business decision.

Double Tap ❤️ For Part-2

📊 𝟱 𝗕𝗲𝘀𝘁 𝗟𝗲𝗮𝗿𝗻𝗶𝗻𝗴 𝗥𝗲𝘀𝗼𝘂𝗿𝗰𝗲𝘀 𝗧𝗼 𝗠𝗮𝘀𝘁𝗲𝗿 𝗠𝗦 𝗘𝘅𝗰𝗲𝗹 𝗳𝗼𝗿 𝗙𝗥𝗘𝗘

Excel is one of the most valuable workplace skills — start learning for FREE today!

✅ Beginner Friendly
✅ Learn at Your Own Pace
✅ Improve Excel & Data Analysis Skills
✅ Useful for Jobs & Interviews
✅ Completely FREE Resources

🔗 𝗘𝗻𝗿𝗼𝗹𝗹 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘👇:- 

https://pdlink.in/3UkOmoa

🎓 Perfect for Students | Freshers | Data Analyst Aspirants | Working Professionals

🚀 𝗗𝗮𝘁𝗮 𝗔𝗻𝗮𝗹𝘆𝘁𝗶𝗰𝘀 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗖𝗼𝘂𝗿𝘀𝗲 📊🔥

𝗕𝘂𝗶𝗹𝗱 𝗝𝗼𝗯-𝗥𝗲𝗮𝗱𝘆 𝗦𝗸𝗶𝗹𝗹𝘀 & Learn the tools companies actually use and prepare for high-growth Data Analyst opportunities.

💼 60+ Hiring Drives Every Month
🤝 500+ Hiring Partners
👨‍🏫 1-on-1 Expert Mentorship
📝 Resume & Interview Preparation
🚀 Dedicated Placement Assistance

🔗 𝗕𝗼𝗼𝗸 𝗮 𝗙𝗥𝗘𝗘 𝗖𝗮𝗿𝗲𝗲𝗿 𝗖𝗼𝘂𝗻𝘀𝗲𝗹𝗹𝗶𝗻𝗴👇:-

https://pdlink.in/45vk5ph

🎓 Perfect for Students | Freshers | Working Professionals | Career Switchers

🚀 Data Analyst Roadmap — Part 2

📊 Excel Basics

Excel is one of the most important foundational tools for a Data Analyst. Before learning advanced formulas, PivotTables, Power Query, or dashboards, you need to understand how Excel works and how to structure data correctly.

1️⃣ What is Excel?

Microsoft Excel is a spreadsheet application used to:

• Store data

• Organize information

• Perform calculations

• Clean data

• Analyze data

• Create reports

• Build dashboards

• Visualize trends

For a Data Analyst, Excel is much more than a place to enter numbers.

You can use it to answer questions such as:

Which product generated the highest revenue?

Which region is underperforming?

What is the average order value?

How has sales changed month over month?

2️⃣ Understand Workbooks and Worksheets

📁 Workbook

An Excel file is called a workbook.

Example: Sales_Analysis.xlsx

A workbook can contain multiple worksheets.

📄 Worksheet

A worksheet is an individual sheet inside the workbook.

For example: Sales, Customers, Products, Summary, Dashboard

Common structure:

Raw_Data → Cleaned_Data → Analysis → Dashboard

3️⃣ Understand Rows and Columns

Rows: Run horizontally. Identified by numbers: 1, 2, 3, 4, 5

Columns: Run vertically. Identified by letters: A, B, C, D, E

Together, they create cells.

4️⃣ Understand Cells

A cell is the intersection of a row and a column.

Examples: A1, B2, C5, D10

If you put Sales in cell C2, then C2 contains the value.

Formula example: =B2+C2 adds the values in B2 and C2.

5️⃣ Understand Cell Ranges

A range is a group of cells.

A1:A10 means cells A1 through A10

A1:C10 means the entire area from A1 to C10

Ranges are extremely important because most Excel functions operate on ranges.

Example: =SUM(B2:B100) adds all values from B2 through B100.

6️⃣ Learn the Correct Data Structure

This is one of the most important concepts for a Data Analyst.

One row = One record

One column = One attribute

Example:

Order ID | Customer | Product | Region | Sales

1001 | John | Laptop | North | 80000

1002 | Sarah | Mouse | South | 2000

1003 | Mike | Keyboard | West | 5000

This structure makes the data easy to: Filter, Sort, Analyze, Summarize, Create PivotTables, Import into Power BI, Load into databases

7️⃣ Avoid Bad Data Structures

Beginners often format datasets like reports.

Bad: January/North 50000/South 60000 then February below it

Good: Month | Region | Sales with January North 50000, January South 60000, etc.

Now Excel can easily answer: sales by month, sales by region, best performing month.

8️⃣ Learn Sorting

Sorting changes the order in which your data is displayed.

Numbers: Smallest → Largest or Largest → Smallest

Text: A → Z or Z → A

Dates: Oldest → Newest or Newest → Oldest

Example: 50,000 transactions → Sort Sales → Largest to Smallest to find biggest sales.

9️⃣ Learn Filtering

Filtering allows you to temporarily display only the records you need.

Example: Filter Department = IT → only IT employees show

Filter Sales > 60000 or Department = IT AND Sales > 60000

Filtering is one of the first techniques you'll use when exploring data.

🔟 Understand Data Types

Text: John, India, Laptop

Numbers: 100, 5000, 99.5

Dates: 18-Aug-2026, 01-Jan-2026

Percentages: 15%, 25%

Currency: ₹50,000, $2,000

Correct data types are important. If 50000 is stored as text, calculations may fail.

1️⃣1️⃣ Learn Formatting

Format: Numbers, Currency, Percentages, Dates, Decimal places, Font, Alignment, Borders, Column widths, Row heights

Remember: Formatting should improve readability, not hide poor data structure.

1️⃣2️⃣ Learn Freeze Panes

When working with large datasets, freeze headers.

Use: View → Freeze Panes

Keeps Order ID | Customer | Product | Sales | Date visible while scrolling.

1️⃣3️⃣ Learn Find & Replace

Useful for correcting inconsistent data.

Example: India, INDIA, india → standardize to India

Particularly useful when cleaning manually maintained Excel files.

1️⃣4️⃣ Learn Data Validation

Controls what users can enter into a cell.

Create dropdowns: IT, HR, Finance, Sales, Marketing

Reduces spelling inconsistencies like Finance, finance, FINANCE, Finanace

Especially useful for input templates.

1️⃣5️⃣ Learn Excel Tables

Shortcut: Ctrl + T

Benefits: Automatic filtering, Structured references, Automatic expansion, Easier formulas, Better formatting, Easier PivotTable creation

Tables are particularly useful when your dataset keeps growing.

🧪 Practice Exercise

Create a dataset with: Order ID, Order Date, Customer, Product, Category, Region, Quantity, Sales. Enter at least 20 records.

Task 1: Sort Sales from highest to lowest

Task 2: Filter only the North region

Task 3: Filter sales greater than ₹50,000

Task 4: Freeze the header row

Task 5: Convert the dataset into an Excel Table

Task 6: Create a dropdown for Region using Data Validation

🏆 Key Lesson

Good analysis starts with good data structure.

Before learning complicated formulas, learn how to organize your data correctly.

A Data Analyst should be able to look at an Excel sheet and immediately recognize:

Is this data structured properly for analysis?

That skill will help you later with SQL, Power BI, Python, and virtually every other analytics tool.

Excel Resources: https://whatsapp.com/channel/0029VbCWL6v3mFY2BHby4y3P

Double Tap ❤️ For Part-3

📊 𝗪𝗮𝗻𝘁 𝘁𝗼 𝗕𝗲𝗰𝗼𝗺𝗲 𝗮 𝗣𝗿𝗼 𝗶𝗻 𝗗𝗮𝘁𝗮 𝗔𝗻𝗮𝗹𝘆𝘁𝗶𝗰𝘀? 🚀

Learning Excel, SQL and Power BI is only the beginning. To stand out as a Data Analyst, focus on practical experience, visibility and networking.

🔥 4 Ways to Level Up Your Data Analytics Career:

💡 Master the Skills → Build Projects → Create Your Portfolio → Get Noticed

🔗 𝗖𝗵𝗲𝗰𝗸 𝘁𝗵𝗲 𝗖𝗼𝗺𝗽𝗹𝗲𝘁𝗲 𝗚𝘂𝗶𝗱𝗲 👇

https://pdlink.in/4cIfLqn

🎯 Perfect for Students | Freshers | Data Analyst Aspirants | Career Switchers

🎓 𝟰 𝗙𝗥𝗘𝗘 𝗖𝗲𝗿𝘁𝗶𝗳𝗮𝘁𝗶𝗼𝗻𝘀 𝗧𝗼 𝗠𝗮𝘀𝘁𝗲𝗿 𝗜𝗻 𝟮𝟬𝟮𝟲 🚀

Want to build job-ready skills and strengthen your resume? Start learning these in-demand technologies for FREE! 🔥

📊 𝗗𝗮𝘁𝗮 𝗔𝗻𝗮𝗹𝘆𝘁𝗶𝗰𝘀 :- https://pdlink.in/4qn5q94

💫 𝗔𝗜 & 𝗠𝗮𝗰𝗵𝗶𝗻𝗲 𝗟𝗲𝗮𝗿𝗻𝗶𝗻𝗴 :- https://pdlink.in/4zrkYNg

☁️ 𝗖𝗹𝗼𝘂𝗱 𝗖𝗼𝗺𝗽𝘂𝘁𝗶𝗻𝗴 :- https://pdlink.in/4wzy6Ny

🛡️ 𝗖𝘆𝗯𝗲𝗿 𝗦𝗲𝗰𝘂𝗿𝗶𝘁𝘆 :- https://pdlink.in/4xMJNl5

🔁 𝗦𝗵𝗮𝗿𝗲 this with your friends and classmates!

𝗗𝗮𝘁𝗮 𝗦𝗰𝗶𝗲𝗻𝗰𝗲 𝗙𝗥𝗘𝗘 𝗢𝗻𝗹𝗶𝗻𝗲 𝗠𝗮𝘀𝘁𝗲𝗿𝗰𝗹𝗮𝘀𝘀 😍

💫Kickstart Your Data Science Career

💫Join this Masterclass for an expert-led session on Data Science

Eligibility :- Students ,Freshers & Working Professionals

𝗥𝗲𝗴𝗶𝘀𝘁𝗲𝗿 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘 👇:-

https://pdlink.in/4xOh5jA

(Only few slots left )

Date & Time :- 21st August 2026 & 7PM

🚀 Data Analyst Roadmap — Part 3

📊 Excel — Level 2: Essential Formulas

Now that you understand Excel's basic structure, the next step is learning the formulas that every Data Analyst should know.

For every function, understand:

What does it do? → When should I use it? → What problem does it solve?

1️⃣ SUM()

SUM() adds numbers together.

Syntax

=SUM(number1, [number2], ...)

Example

Suppose:

Product Sales

Laptop 80,000

Mouse 2,000

Keyboard 5,000

To calculate total sales:

=SUM(B2:B4)

Result: 87,000

2️⃣ AVERAGE()

AVERAGE() calculates the arithmetic mean.

=AVERAGE(B2:B4)

For:

80,000

2,000

5,000

the result is: 29,000

Business example

What is the average order value?

If each row represents an order:

=AVERAGE(SalesColumn)

This gives you the average sales amount per order.

3️⃣ MIN()

Returns the smallest numeric value.

=MIN(B2:B100)

Example:

50,000

25,000

80,000

10,000

Result:

10,000

Common analytical uses

• Lowest sales

• Lowest salary

• Minimum transaction value

• Earliest numeric measurement

4️⃣ MAX()

Returns the largest numeric value.

=MAX(B2:B100)

Example:

50,000

25,000

80,000

10,000

Result:

80,000

Common use

Find the highest sales transaction.

=MAX(SalesRange)

5️⃣ COUNT()

COUNT() counts cells containing numbers.

Example:

Sales

50,000

60,000

70,000

—

80,000

=COUNT(A2:A6)

Result:

4

The blank cell isn't counted.

COUNT() counts numeric values, not all non-empty cells.

6️⃣ COUNTA()

COUNTA() counts non-empty cells.

Example:

Employee

John

Sarah

Mike

David

=COUNTA(A2:A5)

Result:

4

It can count text, numbers, dates, etc., as long as the cell isn't empty.

7️⃣ COUNTBLANK()

Counts empty cells.

=COUNTBLANK(A2:A100)

This is particularly useful for data-quality checks.

Example

Suppose you have 100 customer records and 7 customers have missing email addresses.

=COUNTBLANK(EmailColumn)

Result:

7

That immediately tells you something about data completeness.

8️⃣ ROUND()

Data often contains too many decimal places.

For example:

83.456789

You may want:

83.46

Use:

=ROUND(A2,2)

The 2 means two decimal places.

Examples

=ROUND(A2,0)

Rounds to a whole number.

=ROUND(A2,1)

Rounds to one decimal place.

=ROUND(A2,2)

Rounds to two decimal places.

9️⃣ ROUNDUP()

ROUNDUP() always rounds away from zero.

Example:

=ROUNDUP(83.451,2)

Result:

83.46

Compare this with ROUND() where the result depends on the next digit.

This can be useful when business rules require conservative upward rounding.

🔟 ROUNDDOWN()

ROUNDDOWN() always rounds toward zero.

=ROUNDDOWN(83.459,2)

Result:

83.45

Understanding the difference between:

ROUND → ROUNDUP → ROUNDDOWN

is useful when working with financial and operational calculations.

1️⃣1️⃣ SUM vs COUNT vs AVERAGE

This is a common beginner confusion.

Suppose:

Sales:

10,000

20,000

30,000

SUM

=SUM(A2:A4)

Result:

60,000

COUNT

=COUNT(A2:A4)

Result:

3

AVERAGE

=AVERAGE(A2:A4)

Result:

20,000

Remember:

SUM → Total

COUNT → Number of numeric records

AVERAGE → Mean

1️⃣2️⃣ Combining Functions

The real power of Excel comes from combining functions.

For example, suppose you want:

Total sales divided by number of orders.

You could write:

=SUM(B2:B100)/COUNT(B2:B100)

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:

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

𝗪𝗢𝗥𝗞 𝗙𝗥𝗢𝗠 𝗛𝗢𝗠𝗘 𝗝𝗢𝗕 𝗢𝗣𝗣𝗢𝗥𝗧𝗨𝗡𝗜𝗧𝗬 😍

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!

🗄️ 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:

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

☁️ 𝟰 𝗙𝗥𝗘𝗘 𝗚𝗼𝗼𝗴𝗹𝗲 𝗖𝗹𝗼𝘂𝗱 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 | 𝗕𝘂𝗶𝗹𝗱 𝗜𝗻-𝗗𝗲𝗺𝗮𝗻𝗱 𝗖𝗹𝗼𝘂𝗱 𝗦𝗸𝗶𝗹𝗹𝘀

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!

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

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

IFS() → 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: =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