Available now! Telegram Research 2025 โ the year's key insights 
MS Excel for Data Analysis
Open in Telegram
โ Learn Basic & Advaced Ms Excel concepts for data analysis โ Learn Tips & Tricks Used in Excel โ Become An Expert โ Use The Skills Learnt Here In Your Career For promotions: @love_data
Show more๐ Analytical overview of Telegram channel MS Excel for Data Analysis
Channel MS Excel for Data Analysis (@excel_analyst) in the English language segment is an active participant. Currently, the community unites 71 146 subscribers, ranking 2 264 in the Education category and 4 548 in the India region.
๐ Audience metrics and dynamics
Since its creation on ะฝะตะฒัะดะพะผะพ, the project has demonstrated rapid growth, gathering an audience of 71 146 subscribers.
According to the latest data from 30 June, 2026, the channel demonstrates stable activity. Although there has been a change in the number of participants by 721 over the last 30 days and by 71 over the last 24 hours, overall reach remains high.
- Verification status: Not verified
- Engagement rate (ER): The average audience engagement rate is 2.81%. Within the first 24 hours after publication, content typically collects 1.33% reactions from the total number of subscribers.
- Post reach: On average, each post receives 1 999 views. Within the first day, a publication typically gains 943 views.
- Reactions and interaction: The audience actively supports content: the average number of reactions per post is 7.
- Thematic interests: Content is focused on key topics such as excel, cell, chart, pivot, row.
๐ Description and content policy
The author describes the resource as a platform for expressing subjective opinions:
โโ
Learn Basic & Advaced Ms Excel concepts for data analysis
โ
Learn Tips & Tricks Used in Excel
โ
Become An Expert
โ
Use The Skills Learnt Here In Your Career
For promotions: @love_dataโ
Thanks to the high frequency of updates (latest data received on 01 July, 2026), the channel maintains relevance and a high level of publication reach. Analytics show that the audience actively interacts with content, making it an important point of influence in the Education category.
71 146
Subscribers
+7124 hours
+2227 days
+72130 days
Data loading in progress...
Similar Channels
Tags Cloud
Incoming and Outgoing Mentions
---
---
---
---
---
---
Attracting Subscribers
July '26
July '26
+19
in 0 channels
June '26
+841
in 4 channels
Get PRO
May '26
+1 023
in 6 channels
Get PRO
April '26
+915
in 3 channels
Get PRO
March '26
+336
in 4 channels
Get PRO
February '26
+992
in 9 channels
Get PRO
January '26
+1 598
in 3 channels
Get PRO
December '25
+1 557
in 1 channels
Get PRO
November '25
+1 414
in 6 channels
Get PRO
October '25
+842
in 1 channels
Get PRO
September '25
+362
in 6 channels
Get PRO
August '25
+650
in 14 channels
Get PRO
July '25
+685
in 10 channels
Get PRO
June '25
+1 241
in 21 channels
Get PRO
May '25
+2 227
in 17 channels
Get PRO
April '25
+3 493
in 12 channels
Get PRO
March '25
+848
in 16 channels
Get PRO
February '25
+1 486
in 25 channels
Get PRO
January '25
+1 998
in 24 channels
Get PRO
December '24
+3 003
in 19 channels
Get PRO
November '24
+3 356
in 12 channels
Get PRO
October '24
+4 232
in 17 channels
Get PRO
September '24
+3 493
in 15 channels
Get PRO
August '24
+3 739
in 13 channels
Get PRO
July '24
+5 039
in 17 channels
Get PRO
June '24
+4 850
in 14 channels
Get PRO
May '24
+4 515
in 8 channels
Get PRO
April '24
+3 995
in 5 channels
Get PRO
March '24
+4 364
in 10 channels
Get PRO
February '24
+3 278
in 2 channels
Get PRO
January '24
+4 746
in 3 channels
Get PRO
December '23
+5 078
in 4 channels
| Date | Subscriber Growth | Mentions | Channels | |
| 01 July | +19 |
Channel Posts
๐ ๐๐ฟ๐ฒ๐ฒ ๐ฆ๐ค๐ ๐๐ฒ๐ฟ๐๐ถ๐ณ๐ถ๐ฐ๐ฎ๐๐ถ๐ผ๐ป ๐ณ๐ผ๐ฟ ๐๐ฎ๐๐ฎ ๐ฆ๐ฐ๐ถ๐ฒ๐ป๐ฐ๐ฒ ๐๐ป
This FREE SQL certification program is perfect for students, freshers, and aspiring data professionals ๐ฅ
๐ก Why Learn SQL?
โจ One of the Most In-Demand Tech Skills
โจ Essential for Data Analytics & Data Science
โจ Used by Top IT & Tech Companies
โจ Boosts Career Opportunities in 2026
๐ ๐๐ป๐ฟ๐ผ๐น๐น ๐๐ผ๐ฟ ๐๐ฅ๐๐๐:
https://pdlink.in/4vspUif
๐ฅ Start learning SQL today and prepare for high-paying careers in Data Analytics & Data Science.
| 2 | Sort by Sales (Highest First)
=SORT(A2:C5,3,-1)
๐ These formulas update automatically when new data is added.
โ ๏ธ 9. Common Error โ #SPILL!
You may see:
SPILL!
Causes
โข Cells in the spill range already contain data
โข Merged cells block the spill range
โข Not enough space for results
Fix
โ
Clear the blocking cells.
โ
Avoid merged cells where dynamic arrays are used.
๐ฏ Mini Practice Project
Create: Employee_Report.xlsx
Data
Employee Department Salary
Rahul Sales 50000
Priya HR 60000
Amit Sales 55000
Neha Finance 65000
Karan HR 58000
Tasks
โ
Show only HR employees
=FILTER(A2:C6,B2:B6="HR")
โ
List unique departments
=UNIQUE(B2:B6)
โ
Sort employees by salary (highest first)
=SORT(A2:C6,3,-1)
โ
Generate employee serial numbers
=SEQUENCE(5)
โ
Generate five random performance scores (1โ100)
=RANDARRAY(5,1,1,100,TRUE)
๐ End of Part 11
After completing this lesson, you should be able to:
โ
Use FILTER() to create dynamic reports
โ
Sort data using SORT()
โ
Remove duplicates with UNIQUE()
โ
Generate number sequences using SEQUENCE()
โ
Create random sample data using RANDARRAY()
โ
Understand and resolve #SPILL! errors
๐ Dynamic Array functions simplify formulas, improve readability, and are highly valued in modern Excel workflows.
โก๏ธ Double Tap โค๏ธ For Part-12 | 789 |
| 3 | Now, let's understand the next web development project:
๐ Excel Formulas Fundamentals โ Part 11
โก Dynamic Array Functions (FILTER, SORT, UNIQUE, SEQUENCE, RANDARRAY)
Dynamic Array functions are available in modern versions of Microsoft Excel (Microsoft 365 and Excel 2021+). They automatically return multiple results and "spill" them into adjacent cells, making formulas simpler and reducing the need for helper columns.
๐ These functions are becoming increasingly common in modern Excel interviews and workplace tasks.
๐ง 1. What are Dynamic Arrays?
A Dynamic Array formula can return multiple values from a single formula.
Example
=UNIQUE(A2:A10)
Instead of returning one value, Excel returns all unique values and automatically fills the cells below.
This automatic expansion is called a spill range.
๐ 2. FILTER() โ Filter Data Dynamically
FILTER() returns only the rows that meet a condition.
Syntax
=FILTER(array,include,[if_empty])
Example
Employee Department Salary
Rahul Sales 50000
Priya HR 60000
Amit Sales 55000
Neha Finance 65000
Formula:
=FILTER(A2:C5,B2:B5="Sales","No Data")
Result:
Employee Department Salary
Rahul Sales 50000
Amit Sales 55000
๐ Use Cases:
โข Interactive dashboards
โข Dynamic reports
โข Filtering records without using AutoFilter
๐ 3. SORT() โ Sort Data Automatically
SORT() sorts data without changing the original dataset.
Syntax
=SORT(array,[sort_index],[sort_order])
Example
=SORT(A2:C10,3,-1)
Meaning
โข Sort by the 3rd column (Salary)
โข -1 = Descending order
๐ Useful for displaying Top Performers dynamically.
๐ฏ 4. UNIQUE() โ Return Unique Values
UNIQUE() removes duplicate values and returns only distinct entries.
Syntax
=UNIQUE(array)
Example
Department
Sales
HR
Sales
Finance
HR
Formula:
=UNIQUE(A2:A6)
Result:
Sales
HR
Finance
๐ Common Uses:
โข Dropdown lists
โข Customer lists
โข Product categories
๐ข 5. SEQUENCE() โ Generate Number Series
SEQUENCE() creates a sequence of numbers automatically.
Syntax
=SEQUENCE(rows,[columns],[start],[step])
Example
=SEQUENCE(10)
Result:
1
2
3
4
5
6
7
8
9
10
Another Example
=SEQUENCE(5,1,100,10)
Result:
100
110
120
130
140
๐ Useful for creating serial numbers and sample datasets.
๐ฒ 6. RANDARRAY() โ Generate Random Numbers
RANDARRAY() creates an array of random numbers.
Syntax
=RANDARRAY(rows,[columns],[min],[max],[whole_number])
Example
=RANDARRAY(5,1,1,100,TRUE)
Result (changes every recalculation):
15
87
42
63
91
๐ Use Cases:
โข Sample data
โข Testing dashboards
โข Random selections
๐ 7. Combining Dynamic Array Functions
Dynamic Array functions can be combined for powerful analysis.
Example
Display unique departments in alphabetical order:
=SORT(UNIQUE(B2:B20))
Result:
Finance
HR
Sales
Example
Display only Sales employees sorted by salary:
=SORT(FILTER(A2:C20,B2:B20="Sales"),3,-1)
This filters Sales employees and sorts them by Salary in descending order.
๐ผ 8. Real-World Business Scenario
Sales Dashboard
Region Product Sales
East Laptop 50000
West Mobile 30000
East Tablet 40000
South Laptop 35000
Show Only East Region
=FILTER(A2:C5,A2:A5="East")
Show Unique Products
=UNIQUE(B2:B5) | 651 |
| 4 |  ๐ช๐ฎ๐น๐บ๐ฎ๐ฟ๐ ๐๐ฅ๐๐ ๐๐ป๐๐ฒ๐ฟ๐ป๐๐ต๐ถ๐ฝ ๐๐ฒ๐ฟ๐๐ถ๐ณ๐ถ๐ฐ๐ฎ๐๐ถ๐ผ๐ป ๐ฃ๐ฟ๐ผ๐ด๐ฟ๐ฎ๐บ | ๐๐ฝ๐ฝ๐น๐ ๐ก๐ผ๐!๐
Offering a FREE Advanced Software Engineering Job Simulation where you can work on practical tasks, enhance your coding skills, and earn a certificate to strengthen your resume.
๐ฏ Benefits:
โ
Free Certificate
โ
Real-World Software Engineering Tasks
โ
Self-Paced Learning
Don't miss this opportunity to boost your profile and get job-ready for top tech companies! ๐ฅ
๐๐ป๐ฟ๐ผ๐น๐น ๐๐ผ๐ฟ ๐๐ฅ๐๐๐:
https://pdlink.in/4vDJN5W
๐ข Share with your friends and classmates. | 871 |
| 5 |  ๐๐ฅ๐๐ ๐๐ & ๐ ๐ฎ๐ฐ๐ต๐ถ๐ป๐ฒ ๐๐ฒ๐ฎ๐ฟ๐ป๐ถ๐ป๐ด ๐ฅ๐ฒ๐๐ผ๐๐ฟ๐ฐ๐ฒ๐ | ๐ฐ ๐๐ฒ๐๐ ๐ฌ๐ผ๐๐ง๐๐ฏ๐ฒ ๐๐ต๐ฎ๐ป๐ป๐ฒ๐น๐ ๐
Learn Artificial Intelligence and Machine Learning for FREE from world-class creators
โ๏ธ 100% Free Learning
โ๏ธ Beginner to Advanced Content
โ๏ธ Real-World Coding Projects
โ๏ธ Learn from AI Experts
โ๏ธ Build a Strong Portfolio
โ๏ธ Stay Updated with the Latest AI Trends
๐ ๐๐ป๐ฟ๐ผ๐น๐น ๐๐ผ๐ฟ ๐๐ฅ๐๐๐:
https://pdlinks.in/aiml
๐Start Learning Today. Build AI Skills. Get Career Ready! | 1 136 |
| 6 | โ
Data Analytics Essentials
TECH SKILLS (NON-NEGOTIABLE)
1๏ธโฃ SQL
โข Joins, Group by, Window functions
โข Handle NULLs and duplicates
Example: LEFT JOIN fits a churn query to include non-churned users
2๏ธโฃ Excel
โข Pivot tables, Lookups, IF logic
โข Clean raw data fast
Example: Reconcile 50k rows in minutes using Pivot tables
3๏ธโฃ Power BI or Tableau
โข Data modeling, Measures, Filters
โข One dashboard, One question
Example: Sales drop by region and month dashboard
4๏ธโฃ Python
โข pandas for cleaning and analysis
โข matplotlib or seaborn for quick visuals
Example: Groupby revenue by cohort
5๏ธโฃ Statistics Basics
โข Mean vs median, Variance, Correlation
โข Know when averages lie
Example: Median salary explains skewed data
ย
SOFT SKILLS (DEAL BREAKERS)
1๏ธโฃ Business Thinking
โข Ask why before how
โข Tie insights to decisions
Example: High churn points to onboarding gaps
2๏ธโฃ Communication
โข Explain insights without jargon
โข One slide, One takeaway
Example: Revenue fell due to fewer repeat users
3๏ธโฃ Problem Framing
โข Convert vague asks into clear questions
โข Define metrics early
Example: What defines an active user?
4๏ธโฃ Attention to Detail
โข Validate numbers
โข Double check logic
โข Small errors kill trust
5๏ธโฃ Stakeholder Handling
โข Listen first
โข Clarify scope
โข Push back with data
๐ฏ Balance both tech and soft skills to grow faster as an analyst
Double Tap โฅ๏ธ For More | 1 259 |
| 7 |  ๐ป ๐ ๐ฎ๐๐๐ฒ๐ฟ ๐ฆ๐ค๐ ๐๐ข๐ฅ ๐๐ฅ๐๐ | ๐ฑ ๐๐บ๐ฎ๐๐ถ๐ป๐ด ๐ช๐ฒ๐ฏ๐๐ถ๐๐ฒ๐ ๐ง๐ผ ๐๐ฒ๐ฎ๐ฟ๐ป ๐ฆ๐ค๐ ๐
Want to become a Data Analyst, Data Scientist, or Software Engineer? Start by mastering SQLโone of the most in-demand skills in the tech industry!
These 5 FREE websites will help you learn SQL from scratch through interactive lessons, quizzes, and hands-on practice.
๐๐ข๐ง๐ค๐:-
https://pdlinks.in/qje
๐ Start Learning SQL Today and Build a Strong Foundation for Your Tech Career! | 1 226 |
| 8 | Average Laptop Sales
=AVERAGEIF(B2:B5,"Laptop",C2:C5)
๐ผ 9. Interview Tips
Q: When should you use SUMIF() instead of SUMIFS()?
Answer: Use SUMIF() when there is only one condition. Use SUMIFS() when multiple conditions need to be checked.
Q: What is the difference between COUNTIF() and COUNTIFS()?
Answer: COUNTIF() supports a single condition. COUNTIFS() supports multiple conditions.
Q: Where is SUMPRODUCT() commonly used?
Answer: Weighted averages, Revenue calculations, Inventory valuation, Financial models, Multi-condition calculations
๐ฏ Mini Practice Project
Create: Regional_Sales_Report.xlsx
Data: East Laptop 2 50000 100000, East Mobile 3 20000 60000, West Laptop 1 50000 50000, West Tablet 4 15000 60000
Tasks
โ
Total Sales in East
=SUMIF(A2:A5,"East",E2:E5)
โ
Total Laptop Sales
=SUMIF(B2:B5,"Laptop",E2:E5)
โ
Count Products Sold in West
=COUNTIF(A2:A5,"West")
โ
Average Sales in East
=AVERAGEIF(A2:A5,"East",E2:E5)
โ
Calculate Total Revenue Using Quantity ร Price
=SUMPRODUCT(C2:C5,D2:D5)
๐ These are some of the most frequently used Excel functions in interviews and day-to-day business analysis.
Join our telegram channel: https://t.me/excel_data
โก๏ธ Double Tap โค๏ธ For More | 1 277 |
| 9 | ๐ Excel Formulas Fundamentals โ Part 10
๐ Conditional Functions (SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF, AVERAGEIFS, SUMPRODUCT)
Conditional functions allow you to calculate, count, or average data based on one or more conditions. They are among the most commonly used functions by Data Analysts, Financial Analysts, and Business Analysts.
๐ These functions are frequently asked in Excel interviews and used in business reporting.
๐ง 1. SUMIF() โ Sum Based on One Condition
SUMIF() adds values that meet a single condition.
Syntax:
=SUMIF(range, criteria, sum_range)
Example:
Data: East 50000, West 30000, East 40000
Formula: =SUMIF(A2:A4,"East",B2:B4)
Result: 90000
๐ Use Cases:
Total sales by region, Total expenses by category, Revenue by product
๐ฏ 2. SUMIFS() โ Sum Based on Multiple Conditions
SUMIFS() adds values only when all conditions are met.
Syntax:
=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2)
Example:
Data: East Laptop 50000, East Mobile 30000, West Laptop 45000
Formula: =SUMIFS(C2:C4,A2:A4,"East",B2:B4,"Laptop")
Result: 50000
๐ Commonly used in dashboards and business reports.
๐ข 3. COUNTIF() โ Count Based on One Condition
Counts the number of cells that meet a condition.
Syntax:
=COUNTIF(range, criteria)
Example:
Status: Completed, Pending, Completed
Formula: =COUNTIF(A2:A4,"Completed")
Result: 2
๐ Use Cases:
Count completed tasks, Count active customers, Count employees in a department
๐ 4. COUNTIFS() โ Count Based on Multiple Conditions
Counts records that satisfy multiple conditions.
Syntax:
=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2)
Example:
Data: East Laptop, East Mobile, West Laptop
Formula: =COUNTIFS(A2:A4,"East",B2:B4,"Laptop")
Result: 1
๐ 5. AVERAGEIF() โ Average Based on One Condition
Calculates the average for values matching one condition.
Syntax:
=AVERAGEIF(range, criteria, average_range)
Example:
Data: East 50000, West 30000, East 40000
Formula: =AVERAGEIF(A2:A4,"East",B2:B4)
Result: 45000
๐ 6. AVERAGEIFS() โ Average Based on Multiple Conditions
Calculates the average when multiple conditions are satisfied.
Syntax:
=AVERAGEIFS(average_range, criteria_range1, criteria1, ...)
Example:
=AVERAGEIFS(C2:C5,A2:A5,"East",B2:B5,"Laptop")
๐ Useful for finding the average sales of a specific product in a specific region.
โก 7. SUMPRODUCT() โ Multiply and Sum Arrays
SUMPRODUCT() multiplies corresponding values in arrays and returns the sum.
Syntax:
=SUMPRODUCT(array1, array2)
Example:
Data: Quantity 2 Price 500, Quantity 3 Price 700, Quantity 1 Price 1000
Formula: =SUMPRODUCT(A2:A4,B2:B4)
Calculation: (2 ร 500) + (3 ร 700) + (1 ร 1000) = 4100
Result: 4100
๐ Useful for weighted calculations and financial analysis.
๐ข 8. Real-World Scenario โ Sales Dashboard
Data: East Laptop 50000, East Mobile 30000, West Laptop 45000, West Mobile 25000
Total Sales in East
=SUMIF(A2:A5,"East",C2:C5)
Laptop Sales in West
=SUMIFS(C2:C5,A2:A5,"West",B2:B5,"Laptop")
Number of Mobile Orders
=COUNTIF(B2:B5,"Mobile") | 985 |
| 10 |  ๐ ๐ก๐ฉ๐๐๐๐ ๐๐ฅ๐๐ ๐๐ ๐๐ฒ๐ฟ๐๐ถ๐ณ๐ถ๐ฐ๐ฎ๐๐ถ๐ผ๐ป ๐๐ผ๐๐ฟ๐๐ฒ๐ | ๐๐ฒ๐ฎ๐ฟ๐ป ๐๐ฟ๐ผ๐บ ๐๐ ๐๐ป๐ฑ๐๐๐๐ฟ๐ ๐๐ฒ๐ฎ๐ฑ๐ฒ๐ฟ๐
Want to build cutting-edge *AI skills* from one of the world's leading AI and GPU companies?
*NVIDIA* offers *FREE AI Certification Courses* to help students, freshers, developers, and professionals
๐ ๐๐ป๐ฟ๐ผ๐น๐น ๐๐ผ๐ฟ ๐๐ฅ๐๐๐:
https://pdlinks.in/nvdia
๐ Start Learning Today. Earn Your Certificate. Build Your Future in AI! | 974 |
| 11 | ๐ If your Excel version supports XLOOKUP, prefer it over VLOOKUP.
๐ผ 9. Real-World Business Scenario
Employee Database: E101 Rahul Sales 50000, E102 Priya HR 60000, E103 Amit IT 70000ย
Requirement: Enter an Employee ID and automatically return Name, Department, Salary
Using XLOOKUP:
=XLOOKUP(F2,A2:A4,B2:B4,"Not Found") for Name
=XLOOKUP(F2,A2:A4,C2:C4,"Not Found") for Department
=XLOOKUP(F2,A2:A4,D2:D4,"Not Found") for Salaryย
๐ This is a common interview and workplace scenario.
๐ฏ Mini Practice Project
Create: Employee_Lookup.xlsxย
Data: E101 Rahul Sales 50000, E102 Priya HR 60000, E103 Amit IT 70000, E104 Neha Finance 65000
Tasks:
โ
Find Salary using VLOOKUP
=VLOOKUP("E103",A2:D5,4,FALSE)
โ
Find Position using MATCH
=MATCH("Neha",B2:B5,0)
โ
Return Department using INDEX + MATCH
=INDEX(C2:C5,MATCH("E102",A2:A5,0))
โ
Return Salary using XLOOKUP
=XLOOKUP("E101",A2:A5,D2:D5,"Not Found")
๐ Mastering lookup functions is essential for Excel interviews and day-to-day data analysis.
Join our telegram channel: https://t.me/excel_data
โก๏ธ Double Tap โค๏ธ For More | 1 015 |
| 12 | ๐ Excel Formulas Fundamentals โ Part 9
๐ Lookup & Reference Functions (VLOOKUP, HLOOKUP, INDEX, MATCH, XLOOKUP)
Lookup functions are among the most frequently asked topics in Excel interviews. They help you search for data in one table and return related information from another.
๐ Common use cases:
Employee databases, Product catalogs, Customer records, Sales reports, Inventory management
๐ง 1. What is a Lookup Function?
A lookup function searches for a value and returns a related value from another row or column.
Example:
You have Employee ID, Name, Department data.
Rows: E101 Rahul Sales, E102 Priya HR, E103 Amit IT
Search for E102 and return HR.
๐ 2. VLOOKUP()
VLOOKUP stands for Vertical Lookup.
It searches the first column of a table and returns a value from another column in the same row.
Syntax:
=VLOOKUP(lookup_value,table_array,col_index_num,FALSE)
Example:
Data: E101 Rahul 50000, E102 Priya 60000, E103 Amit 70000
Formula: =VLOOKUP("E102",A2:C4,3,FALSE)
Result: 60000
Arguments explained:
lookup_value: Value to search
table_array: Data table
col_index_num: Column number to return
FALSE: Exact match
โ ๏ธ 3. Limitations of VLOOKUP
โ Can only search left to right
โ Column numbers must be counted manually
โ Breaks if columns are inserted or deleted
โ Slower on large datasets
๐ These limitations are why many professionals now prefer INDEX + MATCH or XLOOKUP.
๐ 4. HLOOKUP()
HLOOKUP stands for Horizontal Lookup.
It searches across the first row and returns a value from a specified row.
Syntax:
=HLOOKUP(lookup_value,table_array,row_index_num,FALSE)
Example:
Data across columns: Jan 10000, Feb 12000, Mar 15000
Formula: =HLOOKUP("Feb",A1:D2,2,FALSE)
Result: 12000
๐ Less commonly used because most business data is arranged vertically.
๐ฏ 5. MATCH()
MATCH returns the position of a value in a range.
Syntax:
=MATCH(lookup_value,lookup_array,0)
Example:
List: Rahul, Priya, Amit
Formula: =MATCH("Priya",A2:A4,0)
Result: 2
๐ It returns the position, not the actual value.
๐ 6. INDEX()
INDEX returns the value at a specified row and optionally column.
Syntax:
=INDEX(array,row_num)
Example:
Salary list: 50000, 60000, 70000
Formula: =INDEX(A2:A4,2)
Result: 60000
๐ 7. INDEX + MATCH (Powerful Combination)
This combination is more flexible than VLOOKUP.
Formula:
=INDEX(C2:C4,MATCH("E102",A2:A4,0))
Result: 60000
How It Works:
1. MATCH finds the row number of E102.
2. INDEX returns the salary from that row.
Advantages:
โ
Can look left or right
โ
Doesn't break when columns are inserted
โ
Faster on large datasets
โ
Easier to maintain
โญ 8. XLOOKUP()
XLOOKUP is the modern replacement for VLOOKUP.
Syntax:
=XLOOKUP(lookup_value,lookup_array,return_array,"Not Found")
Example:
=XLOOKUP("E102",A2:A4,C2:C4,"Not Found")
Result: 60000
XLOOKUP vs VLOOKUP:
Left Lookup: VLOOKUP No, XLOOKUP Yes
Right Lookup: Both Yes
Default Error Message: VLOOKUP No, XLOOKUP Yes
Column Number Required: VLOOKUP Yes, XLOOKUP No
Easier to Read: VLOOKUP No, XLOOKUP Yes | 957 |
| 13 |  ๐ง๐๐ฆ ๐๐ฅ๐๐ ๐๐ฒ๐ฟ๐๐ถ๐ณ๐ถ๐ฐ๐ฎ๐๐ถ๐ผ๐ป ๐ข๐ป ๐๐ฎ๐๐ฎ ๐ ๐ฎ๐ป๐ฎ๐ด๐ฒ๐บ๐ฒ๐ป๐ - ๐๐ป๐ฟ๐ผ๐น๐น ๐๐ผ๐ฟ ๐๐ฅ๐๐๐
TCS iON is offering a FREE Master Data Management Course with a Certificate,
โ
100% FREE Learning
โ
Certificate on Completion
โ
Self-Paced Online Course
โ
Beginner-Friendly Content
โ
Industry-Relevant Skills
โ
Resume & LinkedIn Profile Boost
๐ ๐๐ป๐ฟ๐ผ๐น๐น ๐๐ผ๐ฟ ๐๐ฅ๐๐๐:
https://pdlink.in/4jGFBw0
๐ Start Learning Today. Upskill for Free. Get Career Ready! | 1 211 |
| 14 | Excel Basics for Data Analytics
Excel sits at the start of most analysis work.
What you use Excel for
โข Cleaning raw data
โข Exploring patterns
โข Quick summaries for teams
Core concepts you must know
โข Data setup
โ Freeze header row. View โ Freeze Top Row.
โ Convert range to table. Ctrl + T.
โ Use proper headers. No merged cells. One value per cell.
โข Data cleaning
โ Remove duplicates. Data โ Remove Duplicates.
โ Trim extra spaces. =TRIM(A2)
โ Convert text to numbers. =VALUE(A2)
โ Fix date format. Format Cells โ Date.
โ Handle blanks. Filter blanks, fill or delete.
โ Find and replace. Ctrl + H.
โข Essential formulas
โ Math and counts
โช SUM. =SUM(A2:A100)
โช AVERAGE. =AVERAGE(A2:A100)
โช MIN. =MIN(A2:A100)
โช MAX. =MAX(A2:A100)
โช COUNT. Counts numbers.
โช COUNTA. Counts non blanks.
โช COUNTBLANK. Counts blanks.
โ Conditional formulas
โช IF. =IF(A2>5000,"High","Low")
โช IFS. Multiple conditions.
โช AND. =AND(A2>5000,B2="West")
โช OR. =OR(A2>5000,A2<1000)
โ Lookup formulas
โช XLOOKUP. =XLOOKUP(A2,Sheet2!A:A,Sheet2!B:B)
โช VLOOKUP. Old but common.
โช INDEX + MATCH. Powerful alternative.
โ Text formulas
โช LEFT. =LEFT(A2,4)
โช RIGHT. =RIGHT(A2,2)
โช MID. =MID(A2,2,3)
โช LEN. =LEN(A2)
โช CONCAT or TEXTJOIN.
โช LOWER, UPPER, PROPER.
โ Date formulas
โช TODAY. Current date.
โช NOW. Date and time.
โช YEAR, MONTH, DAY.
โช DATEDIF. Date difference.
โช EOMONTH. Month end.
โข Sorting and filtering
โ Sort by multiple columns.
โ Filter by value, color, condition.
โ Top 10 filter for quick insights.
โข Conditional formatting
โ Highlight duplicates.
โ Color scales for trends.
โ Rules for thresholds. Example. Sales > 10000 in green.
โข Pivot tables
โ Insert โ PivotTable.
โ Rows. Category or Product.
โ Values. Sum, Count, Average.
โ Filters. Date, Region.
โ Refresh after data update.
โข Charts you must know
โ Column. Comparison.
โ Bar. Ranking.
โ Line. Trends over time.
โ Pie. Share or percentage.
โ Combo. Actual vs target.
โข Data validation
โ Dropdown list. Data โ Data Validation โ List.
โ Prevent wrong entries.
โข Useful shortcuts
โ Ctrl + Arrow. Jump data.
โ Ctrl + Shift + Arrow. Select range.
โ Ctrl + 1. Format cells.
โ Ctrl + L. Apply filter.
โ Alt + =. Auto sum.
โ Ctrl + Z / Y. Undo redo.
โข Common analyst mistakes to avoid
โ Merged cells.
โ Hard coded totals.
โ Mixed data types in one column.
โ No backup before cleaning.
โข Daily practice task
โ Download any sales CSV.
โ Clean it.
โ Build one pivot table.
โ Create one chart.
Excel Resources: https://whatsapp.com/channel/0029VaifY548qIzv0u1AHz3i
Data Analytics Roadmap: https://whatsapp.com/channel/0029VaGgzAk72WTmQFERKh02/1354
Double Tap โฅ๏ธ For More | 1 146 |
| 15 |  ๐ ๐๐ฅ๐๐ ๐๐ฎ๐๐ฎ ๐๐ป๐ฎ๐น๐๐๐ถ๐ฐ๐ ๐๐ผ๐๐ฟ๐๐ฒ๐ | ๐ก๐ผ ๐๐
๐ฝ๐ฒ๐ฟ๐ถ๐ฒ๐ป๐ฐ๐ฒ ๐ก๐ฒ๐ฒ๐ฑ๐ฒ๐ฑ! ๐
Want to start a career in Data Analytics but don't know where to begin?
These 5 FREE beginner-friendly courses will help you learn the most in-demand data skills and build a strong foundation.
๐ ๐๐ป๐ฟ๐ผ๐น๐น ๐๐ผ๐ฟ ๐๐ฅ๐๐๐:
https://pdlink.in/3SOk64h
๐ Start Learning Today. Build Your Portfolio. Land Your Dream Data Job! | 1 249 |
| 16 | Keyboard #Shortcut Keys
Ctrl+A - Select All
Ctrl+B - Bold
Ctrl+C - Copy
Ctrl+D - Fill Down
Ctrl+F - Find
Ctrl+G - Goto
Ctrl+H - Replace
Ctrl+I - Italic
Ctrl+K - Insert Hyperlink
Ctrl+N - New Workbook
Ctrl+O - Open
Ctrl+P - Print
Ctrl+R - Fill Right
Ctrl+S - Save
Ctrl+U - Underline
Ctrl+V - Paste
Ctrl W - Close
Ctrl+X - Cut
Ctrl+Y - Repeat
Ctrl+Z - Undo
F1 - Help
F2 - Edit
F3 - Paste Name
F4 - Repeat last action
F4 - While typing a formula, switch between absolute/relative refs
F5 - Goto
F6 - Next Pane
F7 - Spell check
F8 - Extend mode
F9 - Recalculate all workbooks
F10 - Activate Menu bar
F11 - New Chart
F12 - Save As
Ctrl+: - Insert Current Time
Ctrl+; - Insert Current Date
Ctrl+" - Copy Value from Cell Above
Ctrl+โ - Copy Formula from Cell Above
Shift - Hold down shift for additional functions in Excelโs menu
Shift+F1 - Whatโs This?
Shift+F2 - Edit cell comment
Shift+F3 - Paste function into formula
Shift+F4 - Find Next
Shift+F5 - Find
Shift+F6 - Previous Pane
Shift+F8 - Add to selection
Shift+F9 - Calculate active worksheet
Shift+F10 - Display shortcut menu
Shift+F11 - New worksheet
Ctrl+F3 - Define name
Ctrl+F4 - Close
Ctrl+F5 - XL, Restore window size
Ctrl+F6 - Next workbook window
Shift+Ctrl+F6 - Previous workbook window
Ctrl+F7 - Move window
Ctrl+F8 - Resize window
Ctrl+F9 - Minimize workbook
Ctrl+F10 - Maximize or restore window
Ctrl+F11 - Inset 4.0 Macro sheet
Ctrl+F1 - File Open
Alt+F1 - Insert Chart
Alt+F2 - Save As
Alt+F4 - Exit
Alt+Down arrow - Display AutoComplete list
Alt+โ - Format Style dialog box
Ctrl+Shift+~ - General format
Ctrl+Shift+! - Comma format
Ctrl+Shift+@ - Time format
Ctrl+Shift+# - Date format
Ctrl+Shift+$ - Currency format
Ctrl+Shift+% - Percent format
Ctrl+Shift+^ - Exponential format
Ctrl+Shift+& - Place outline border around selected cells
Ctrl+Shift+_ - Remove outline border
Ctrl+Shift+* - Select current region
Ctrl++ - Insert
Ctrl+- - Delete
Ctrl+1 - Format cells dialog box
Ctrl+2 - Bold
Ctrl+3 - Italic
Ctrl+4 - Underline
Ctrl+5 - Strikethrough
Ctrl+6 - Show/Hide objects
Ctrl+7 - Show/Hide Standard toolbar
Ctrl+8 - Toggle Outline symbols
Ctrl+9 - Hide rows
Ctrl+0 - Hide columns
Ctrl+Shift+( - Unhide rows
Ctrl+Shift+) - Unhide columns
Alt or F10 - Activate the menu
Ctrl+Tab - In toolbar: next toolbar
Shift+Ctrl+Tab - In toolbar: previous toolbar
Ctrl+Tab - In a workbook: activate next workbook
Shift+Ctrl+Tab - In a workbook: activate previous workbook
Tab - Next tool
Shift+Tab - Previous tool
Enter - Do the command
Shift+Ctrl+F - Font Drop down List
Shift+Ctrl+F+F - Font tab of Format Cell Dialog box
Shift+Ctrl+P - Point size Drop down List
Ctrl + E - Align center
Ctrl + J - justify
Ctrl + L - alignย
Ctrl + R - align right
Alt + Tab - switch applications
Windows + P - Project screen
Windows + E - open file explorer
Windows + D - go to desktop
Windows + M - minimize all windows
Windows + S - search | 1 332 |
| 17 |  ๐ ๐๐ฅ๐๐ ๐ง๐ฎ๐๐ฎ ๐๐ฎ๐๐ฎ ๐๐ป๐ฎ๐น๐๐๐ถ๐ฐ๐ ๐ฉ๐ถ๐ฟ๐๐๐ฎ๐น ๐๐ป๐๐ฒ๐ฟ๐ป๐๐ต๐ถ๐ฝ | ๐ช๐ถ๐๐ต ๐๐ฒ๐ฟ๐๐ถ๐ณ๐ถ๐ฐ๐ฎ๐๐ฒ ๐
Here's an amazing opportunity to complete the FREE Tata Data Analytics Virtual Internship and earn a certificate that you can showcase on your Resume and LinkedIn.
โ
100% FREE
โ
Self-Paced & Online
โ
Beginner-Friendly
โ
Certificate on Completion
โ
Real Business Case Studies
โ
Resume & LinkedIn Boost
๐ ๐๐ป๐ฟ๐ผ๐น๐น ๐๐ผ๐ฟ ๐๐ฅ๐๐๐:
https://pdlink.in/4eybW8J
๐ Upskill Today. Build Your Portfolio. Get Career Ready! | 1 680 |
| 18 | โ
Search within text using FIND() and SEARCH()
๐ These text functions are among the most frequently used in real-world data cleaning and preparation.
Double Tap โค๏ธ For More | 1 724 |
| 19 | ๐ Excel Formulas Fundamentals โ Part 8
๐ Text Functions for Data Cleaning & Manipulation
In real-world datasets, text data is often inconsistent. Names may have extra spaces, different letter cases, or be combined into one field.
Text functions help you clean, extract, combine, and standardize text efficiently.
๐ These functions are heavily used by Data Analysts, Business Analysts, HR teams, and Finance professionals.
๐ง 1. LEN() โ Count Characters
LEN() returns the total number of characters in a cell, including spaces.
Syntax: =LEN(text)
Example: =LEN(A2)
If A2 contains Deepak
Result: 6
๐ Use Cases: Validate IDs, Check password length, Verify product codes
โฌ
๏ธ 2. LEFT() โ Extract Characters from the Left
Extracts a specified number of characters from the beginning of a text string.
Syntax: =LEFT(text,num_chars)
Example: =LEFT(A2,3)
If A2 = "Laptop"
Result: Lap
๐ Useful for extracting prefixes or department codes.
โก๏ธ 3. RIGHT() โ Extract Characters from the Right
Returns characters from the end of a text string.
Syntax: =RIGHT(text,num_chars)
Example: =RIGHT(A2,4)
If A2 = "INV2026"
Result: 2026
๐ Useful for extracting years, invoice numbers, or suffixes.
๐ 4. MID() โ Extract Characters from the Middle
Extracts text starting from a specified position.
Syntax: =MID(text,start_num,num_chars)
Example: =MID(A2,3,4)
If A2 = "EMP12345"
Result: P123
๐ Useful for extracting parts of employee IDs or product codes.
โ๏ธ 5. TRIM() โ Remove Extra Spaces
Removes leading, trailing, and extra spaces between words.
Syntax: =TRIM(A2)
Example:
Before: John Doe
After: John Doe
๐ Essential for cleaning imported data.
๐ 6. UPPER(), LOWER(), PROPER()
These functions standardize text case.
UPPER()
=UPPER(A2)
Result: JOHN DOE
LOWER()
=LOWER(A2)
Result: john doe
PROPER()
=PROPER(A2)
Result: John Doe
๐ Useful for customer names, addresses, and reports.
๐ 7. CONCAT() and & โ Combine Text
Using &
=A2&" "&B2
If A2 = John, B2 = Doe
Result: John Doe
Using CONCAT()
=CONCAT(A2," ",B2)
๐ Combines multiple text values into one.
๐ 8. TEXTJOIN()
Joins text with a chosen delimiter.
Syntax: =TEXTJOIN(", ",TRUE,A2:A5)
Example:
Names: Rahul, Priya, Amit
Result: Rahul, Priya, Amit
๐ Useful for generating email lists or summaries.
๐ 9. SUBSTITUTE()
Replaces specific text with new text.
Syntax: =SUBSTITUTE(text,old_text,new_text)
Example: =SUBSTITUTE(A2,"Yes","Approved")
If A2 = "Yes"
Result: Approved
๐ Great for standardizing values.
๐ ๏ธ 10. REPLACE()
Replaces text based on position.
Syntax: =REPLACE(old_text,start_num,num_chars,new_text)
Example: =REPLACE("ABC123",1,3,"XYZ")
Result: XYZ123
๐ Useful when the position of the text is fixed.
๐ 11. FIND() and SEARCH()
Both functions return the position of text inside another text string.
FIND() Case-Sensitive
=FIND("Excel",A2)
SEARCH() Case-Insensitive
=SEARCH("excel",A2)
Example: If A2 = "Learn Excel Today"
Result: 7
๐ Useful for checking whether a keyword exists in a text string.
๐ฏ Mini Practice Project
Create: Customer_Data.xlsx
Data:
First Name | Last Name | Full Name | Customer ID
john | doe | CUST-2026-001
Tasks:
โ
Convert names to Proper Case
=PROPER(A2)
โ
Create Full Name
=A2&" "&B2
โ
Remove Extra Spaces
=TRIM(C2)
โ
Extract Year from Customer ID
=MID(D2,6,4)
โ
Count Characters in Customer ID
=LEN(D2)
๐ End of Part 8
After completing this lesson, you should be able to:
โ
Extract text using LEFT(), RIGHT(), and MID()
โ
Clean data using TRIM()
โ
Standardize text with UPPER(), LOWER(), and PROPER()
โ
Combine text using CONCAT(), TEXTJOIN(), and &
โ
Replace and modify text using SUBSTITUTE() and REPLACE() | 1 784 |
| 20 |  ๐ ๐ฃ๐๐ ๐ถ๐ ๐ผ๐ณ๐ณ๐ฒ๐ฟ๐ถ๐ป๐ด ๐ฎ ๐๐ฅ๐๐ ๐ฃ๐ผ๐๐ฒ๐ฟ ๐๐ ๐๐ฒ๐ฟ๐๐ถ๐ณ๐ถ๐ฐ๐ฎ๐๐ถ๐ผ๐ป ๐ฃ๐ฟ๐ผ๐ด๐ฟ๐ฎ๐บ
This helps tolearn data visualization, dashboard creation, KPI analysis, and business intelligence skills that companies actively look for.
โ
Free Certificate
โ
Self-Paced Learning
โ
Hands-On Power BI Projects
โ
Beginner Friendly
โ
Resume & LinkedIn Boost
Don't miss this opportunity to add an in-demand skill to your profile and stand out from the crowd! ๐ผ๐ฅ
๐ ๐๐ป๐ฟ๐ผ๐น๐น ๐๐ผ๐ฟ ๐๐ฅ๐๐๐:
https://pdlink.in/4g5sKFa
Share with yours friends who wants to start a career in Data Analytics | 1 479 |
