Data Analytics

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 ----- 2.2 ₽ · /balance_help

🚀 𝗪𝗶𝗽𝗿𝗼 𝗘𝗹𝗶𝘁𝗲 𝗡𝗧𝗛 & 𝗧𝘂𝗿𝗯𝗼 𝗙𝗥𝗘𝗘 𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄 𝗞𝗶𝘁 💻🔥 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 ----- 2.27 ₽ · /balance_help

🚀 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"
)

🚀 𝗔𝗜 & 𝗠𝗮𝗰𝗵𝗶𝗻𝗲 𝗟𝗲𝗮𝗿𝗻𝗶𝗻𝗴 𝗙𝗥𝗘𝗘 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗖𝗼𝘂𝗿𝘀𝗲 🔥 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!

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

🗄️ 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 ----- 2.26 ₽ · /balance_help

𝗪𝗢𝗥𝗞 𝗙𝗥𝗢𝗠 𝗛𝗢𝗠𝗘 𝗝𝗢𝗕 𝗢𝗣𝗣𝗢𝗥𝗧𝗨𝗡𝗜𝗧𝗬 😍 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!

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 ----- 2.58 ₽ · /balance_help

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

𝗗𝗮𝘁𝗮 𝗦𝗰𝗶𝗲𝗻𝗰𝗲 𝗙𝗥𝗘𝗘 𝗢𝗻𝗹𝗶𝗻𝗲 𝗠𝗮𝘀𝘁𝗲𝗿𝗰𝗹𝗮𝘀𝘀 😍 💫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

🎓 𝟰 𝗙𝗥𝗘𝗘 𝗖𝗲𝗿𝘁𝗶𝗳𝗮𝘁𝗶𝗼𝗻𝘀 𝗧𝗼 𝗠𝗮𝘀𝘁𝗲𝗿 𝗜𝗻 𝟮𝟬𝟮𝟲 🚀 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!

📊 𝗪𝗮𝗻𝘁 𝘁𝗼 𝗕𝗲𝗰𝗼𝗺𝗲 𝗮 𝗣𝗿𝗼 𝗶𝗻 𝗗𝗮𝘁𝗮 𝗔𝗻𝗮𝗹𝘆𝘁𝗶𝗰𝘀? 🚀 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

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 ----- 2.37 ₽ · /balance_help

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

🚀 𝗗𝗮𝘁𝗮 𝗔𝗻𝗮𝗹𝘆𝘁𝗶𝗰𝘀 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗖𝗼𝘂𝗿𝘀𝗲 📊🔥 𝗕𝘂𝗶𝗹𝗱 𝗝𝗼𝗯-𝗥𝗲𝗮𝗱𝘆 𝗦𝗸𝗶𝗹𝗹𝘀 & 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

📊 𝟱 𝗕𝗲𝘀𝘁 𝗟𝗲𝗮𝗿𝗻𝗶𝗻𝗴 𝗥𝗲𝘀𝗼𝘂𝗿𝗰𝗲𝘀 𝗧𝗼 𝗠𝗮𝘀𝘁𝗲𝗿 𝗠𝗦 𝗘𝘅𝗰𝗲𝗹 𝗳𝗼𝗿 𝗙𝗥𝗘𝗘 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