7 Lookup functions in Excel 🔍
A lookup function allows you to get an exact value from a range, based off of inputs.
This is one of the most popular function types in excel, and there are many different methods available…
Let’s explore the 7 most common:
1️⃣ =VLOOKUP
Everyone has heard of a VLOOKUP function…
and many are still using it…even though there are much better functions available
This works by taking a range of data, and finding your lookup value by analyzing a VERTICAL range
I’m not a fan of VLOOKUP because you can only search in one direction (vertically), and need to hardcode in the column index…but it’s still a popular one.
2️⃣ =HLOOKUP
This is pretty much the same as VLOOKUP, only you can now search HORIZONTALLY.
just like VLOOKUP, I feel that HLOOKUP is unnecessary, and limited in it’s functionality
3️⃣ =XLOOKUP
Over the last few years Microsoft released XLOOKUP - your solution to all of your woes with VLOOKUP and HLOOKUP.
Now, instead of being bound to just one direction, you can specify a range on both an X and Y axis to find your lookup value.
XLOOKUP is the new kid on the block and is highly encouraged to use instead of VLOOKUP and HLOOKUP…
just need to watch out for those who are still using an older version of Microsoft Excel, as you’ll run into a compatibility issue (since it’s a new function)
4️⃣ =GETPIVOTDATA
Many aren’t familiar with this one…but it’s super useful.
Here, you can specify the inputs to dynamically pull out your value from a Pivot Table.
If you’re unsure of how to use this, just point your = sign to a cell in a Pivot Table, and excel will automatically populate your formula
5️⃣ =INDEX
The INDEX function is one of my favorite…
and it’s really simple to understand.
Here you can point to a range with both an X and Y axis…
and simply input the coordinates for what value you want to find (ex: 3rd row, 2nd column)
6️⃣ =MATCH
The MATCH function is also a really good one…
it allows you to get the POSITION of a value in a range, as compared to the position of another value in a range.
I love using this one when getting the difference in dates from one period to another.
7️⃣ =INDEX/MATCH
OK…here’s my favorite method for looking up a value.
It in essence combines both the Index and the Match value, allowing you to have complete flexibility over your range with dynamic inputs.
Many feel that XLOOKUP is superior, and I won’t fight it - it’s all a matter of personal preference!
===
Whatever function you use for looking up a value, keep in mind the following:
→ use a function that allows you to search both an X and Y axis, instead of just one direction
→ set things up so that your inputs are dynamic, and can change, yielding different outputs
→ keep your syntax clean & easy to understand
Those are my tips for lookup functions, and 7 ones to use.
A lookup function allows you to get an exact value from a range, based off of inputs.
This is one of the most popular function types in excel, and there are many different methods available…
Let’s explore the 7 most common:
1️⃣ =VLOOKUP
Everyone has heard of a VLOOKUP function…
and many are still using it…even though there are much better functions available
This works by taking a range of data, and finding your lookup value by analyzing a VERTICAL range
I’m not a fan of VLOOKUP because you can only search in one direction (vertically), and need to hardcode in the column index…but it’s still a popular one.
2️⃣ =HLOOKUP
This is pretty much the same as VLOOKUP, only you can now search HORIZONTALLY.
just like VLOOKUP, I feel that HLOOKUP is unnecessary, and limited in it’s functionality
3️⃣ =XLOOKUP
Over the last few years Microsoft released XLOOKUP - your solution to all of your woes with VLOOKUP and HLOOKUP.
Now, instead of being bound to just one direction, you can specify a range on both an X and Y axis to find your lookup value.
XLOOKUP is the new kid on the block and is highly encouraged to use instead of VLOOKUP and HLOOKUP…
just need to watch out for those who are still using an older version of Microsoft Excel, as you’ll run into a compatibility issue (since it’s a new function)
4️⃣ =GETPIVOTDATA
Many aren’t familiar with this one…but it’s super useful.
Here, you can specify the inputs to dynamically pull out your value from a Pivot Table.
If you’re unsure of how to use this, just point your = sign to a cell in a Pivot Table, and excel will automatically populate your formula
5️⃣ =INDEX
The INDEX function is one of my favorite…
and it’s really simple to understand.
Here you can point to a range with both an X and Y axis…
and simply input the coordinates for what value you want to find (ex: 3rd row, 2nd column)
6️⃣ =MATCH
The MATCH function is also a really good one…
it allows you to get the POSITION of a value in a range, as compared to the position of another value in a range.
I love using this one when getting the difference in dates from one period to another.
7️⃣ =INDEX/MATCH
OK…here’s my favorite method for looking up a value.
It in essence combines both the Index and the Match value, allowing you to have complete flexibility over your range with dynamic inputs.
Many feel that XLOOKUP is superior, and I won’t fight it - it’s all a matter of personal preference!
===
Whatever function you use for looking up a value, keep in mind the following:
→ use a function that allows you to search both an X and Y axis, instead of just one direction
→ set things up so that your inputs are dynamic, and can change, yielding different outputs
→ keep your syntax clean & easy to understand
Those are my tips for lookup functions, and 7 ones to use.
👍9❤1🔥1
Many people pay too much to learn Excel, but my mission is to break down barriers. I have shared complete learning series to learn Excel from scratch.
Here are the links to the Excel series
Complete Excel Topics for Data Analyst: https://t.iss.one/sqlspecialist/547
Part-1: https://t.iss.one/sqlspecialist/617
Part-2: https://t.iss.one/sqlspecialist/620
Part-3: https://t.iss.one/sqlspecialist/623
Part-4: https://t.iss.one/sqlspecialist/624
Part-5: https://t.iss.one/sqlspecialist/628
Part-6: https://t.iss.one/sqlspecialist/633
Part-7: https://t.iss.one/sqlspecialist/634
Part-8: https://t.iss.one/sqlspecialist/635
Part-9: https://t.iss.one/sqlspecialist/640
Part-10: https://t.iss.one/sqlspecialist/641
Part-11: https://t.iss.one/sqlspecialist/644
Part-12:
https://t.iss.one/sqlspecialist/646
Part-13: https://t.iss.one/sqlspecialist/650
Part-14: https://t.iss.one/sqlspecialist/651
Part-15: https://t.iss.one/sqlspecialist/654
Part-16: https://t.iss.one/sqlspecialist/655
Part-17: https://t.iss.one/sqlspecialist/658
Part-18: https://t.iss.one/sqlspecialist/660
Part-19: https://t.iss.one/sqlspecialist/661
Part-20: https://t.iss.one/sqlspecialist/662
Bonus: https://t.iss.one/sqlspecialist/663
I saw a lot of big influencers copy pasting my content after removing the credits. It's absolutely fine for me as more people are getting free education because of my content.
But I will really appreciate if you share credits for the time and efforts I put in to create such valuable content. I hope you can understand.
You can join this telegram channel for more Excel Resources: https://t.iss.one/excel_analyst
Python Learning Series: https://t.iss.one/sqlspecialist/615
Complete SQL Topics for Data Analysts: https://t.iss.one/sqlspecialist/523
Complete Power BI Topics for Data Analysts: https://t.iss.one/sqlspecialist/588
Thanks to all who support our channel and share the content with proper credits. You guys are really amazing.
Hope it helps :)
Here are the links to the Excel series
Complete Excel Topics for Data Analyst: https://t.iss.one/sqlspecialist/547
Part-1: https://t.iss.one/sqlspecialist/617
Part-2: https://t.iss.one/sqlspecialist/620
Part-3: https://t.iss.one/sqlspecialist/623
Part-4: https://t.iss.one/sqlspecialist/624
Part-5: https://t.iss.one/sqlspecialist/628
Part-6: https://t.iss.one/sqlspecialist/633
Part-7: https://t.iss.one/sqlspecialist/634
Part-8: https://t.iss.one/sqlspecialist/635
Part-9: https://t.iss.one/sqlspecialist/640
Part-10: https://t.iss.one/sqlspecialist/641
Part-11: https://t.iss.one/sqlspecialist/644
Part-12:
https://t.iss.one/sqlspecialist/646
Part-13: https://t.iss.one/sqlspecialist/650
Part-14: https://t.iss.one/sqlspecialist/651
Part-15: https://t.iss.one/sqlspecialist/654
Part-16: https://t.iss.one/sqlspecialist/655
Part-17: https://t.iss.one/sqlspecialist/658
Part-18: https://t.iss.one/sqlspecialist/660
Part-19: https://t.iss.one/sqlspecialist/661
Part-20: https://t.iss.one/sqlspecialist/662
Bonus: https://t.iss.one/sqlspecialist/663
I saw a lot of big influencers copy pasting my content after removing the credits. It's absolutely fine for me as more people are getting free education because of my content.
But I will really appreciate if you share credits for the time and efforts I put in to create such valuable content. I hope you can understand.
You can join this telegram channel for more Excel Resources: https://t.iss.one/excel_analyst
Python Learning Series: https://t.iss.one/sqlspecialist/615
Complete SQL Topics for Data Analysts: https://t.iss.one/sqlspecialist/523
Complete Power BI Topics for Data Analysts: https://t.iss.one/sqlspecialist/588
Thanks to all who support our channel and share the content with proper credits. You guys are really amazing.
Hope it helps :)
👍6❤2😨1
🎃1
Here are 7 Excel hacks that may not be commonly known but can be very useful:
1. Flash Fill: Excel's Flash Fill feature allows you to automatically fill in values based on a pattern you establish. Simply start typing a pattern in a column adjacent to the data you want to fill, then press
2. Custom Views: You can save different views of your data by using the Custom Views feature. This is useful when you want to quickly switch between different layouts or settings. Go to
3. Data Validation Lists: You can create drop-down lists in cells to restrict input options. To do this, select the cells where you want the drop-down list, go to
4. Quick Analysis Tool: Excel's Quick Analysis tool provides a quick way to analyze and visualize your data. Simply select a range of data, and a small icon will appear at the bottom right corner. Click on it to access various analysis options.
5. Text to Columns: If you have data that needs to be split into separate columns (e.g., first name and last name), you can use the Text to Columns feature. Go to
6. Conditional Formatting with Formulas: While conditional formatting is commonly used, you can take it a step further by applying formatting based on specific formulas. This allows for more customized formatting rules. Go to
7. Dynamic Arrays: Excel's dynamic array functions (available in newer versions) allow you to work with arrays of data more efficiently. Functions like
These Excel hacks can help you work more effectively with your data and take advantage of some lesser-known features in Excel.
Credits: https://t.iss.one/free4unow_backup
ENJOY LEARNING 👍👍
1. Flash Fill: Excel's Flash Fill feature allows you to automatically fill in values based on a pattern you establish. Simply start typing a pattern in a column adjacent to the data you want to fill, then press
Ctrl + E to apply the Flash Fill feature.2. Custom Views: You can save different views of your data by using the Custom Views feature. This is useful when you want to quickly switch between different layouts or settings. Go to
View > Custom Views to set up and manage custom views.3. Data Validation Lists: You can create drop-down lists in cells to restrict input options. To do this, select the cells where you want the drop-down list, go to
Data > Data Validation, choose "List" as the validation criteria, and enter the list items.4. Quick Analysis Tool: Excel's Quick Analysis tool provides a quick way to analyze and visualize your data. Simply select a range of data, and a small icon will appear at the bottom right corner. Click on it to access various analysis options.
5. Text to Columns: If you have data that needs to be split into separate columns (e.g., first name and last name), you can use the Text to Columns feature. Go to
Data > Text to Columns and follow the instructions to split the data based on delimiters or fixed widths.6. Conditional Formatting with Formulas: While conditional formatting is commonly used, you can take it a step further by applying formatting based on specific formulas. This allows for more customized formatting rules. Go to
Home > Conditional Formatting > New Rule and select "Use a formula to determine which cells to format."7. Dynamic Arrays: Excel's dynamic array functions (available in newer versions) allow you to work with arrays of data more efficiently. Functions like
FILTER, SORT, and UNIQUE can help you manipulate data dynamically without needing to enter array formulas.These Excel hacks can help you work more effectively with your data and take advantage of some lesser-known features in Excel.
Credits: https://t.iss.one/free4unow_backup
ENJOY LEARNING 👍👍
👍3❤1🔥1😭1
Why is Excel Often the Starting Point for SQL ?
Here's how Excel can help you before you dive into SQL:
✔️ 𝐕𝐋𝐎𝐎𝐊𝐔𝐏 = 𝐒𝐐𝐋 𝐉𝐎𝐈𝐍𝐒
In Excel, we use VLOOKUP to bring together data from different sheets. It's just like using JOINS in SQL to get data from more than one table.
✔️ 𝐒𝐔𝐌 𝐚𝐧𝐝 𝐂𝐎𝐔𝐍𝐓 𝐟𝐨𝐫 𝐒𝐐𝐋 𝐐𝐮𝐞𝐫𝐢𝐞𝐬
Excel's SUM and COUNT functions are like practice for SQL queries. They help you add up and count things, which is what you often do in SQL.
✔️ 𝐅𝐈𝐋𝐓𝐄𝐑 𝐒𝐭𝐚𝐭𝐞𝐦𝐞𝐧𝐭𝐬 & 𝐖𝐇𝐄𝐑𝐄 𝐢𝐧 𝐒𝐐𝐋
Excel's 𝐅𝐈𝐋𝐓𝐄𝐑 statements let you make choices with your data. This is similar to using WHERE in SQL to pick specific data.
✔️ 𝐇𝐚𝐧𝐝𝐥𝐢𝐧𝐠 𝐃𝐚𝐭𝐞𝐬 𝐚𝐧𝐝 𝐓𝐞𝐱𝐭
Both Excel and SQL have ways to work with dates and text. Learning these in Excel first can make it easier when you switch to SQL.
✔️ 𝐏𝐢𝐯𝐨𝐭 𝐓𝐚𝐛𝐥𝐞𝐬 & 𝐆𝐑𝐎𝐔𝐏 𝐁𝐘 𝐢𝐧 𝐒𝐐𝐋
Ever used pivot tables in Excel? They're a good start for understanding the GROUP BY function in SQL, which helps you organize and summarize data.
✔️ 𝐗𝐋𝐎𝐎𝐊𝐔𝐏 & 𝐇𝐲𝐩𝐞𝐫𝐥𝐢𝐧𝐤𝐬
Excel's XLOOKUP and hyperlinks are like SQL's ways of finding and linking data. They give you a peek into how SQL finds and connects information.
Learning Excel first makes SQL easier to understand. It's not just about learning a tool, it's about getting ready for the bigger world of data!
You will be asked questions on SQL in interviews for sure! Make sure to practice 2-3 questions daily, it can't be mastered overnight!
Share our channel link with your true friends: https://t.iss.one/excel_analyst
Hope this helps you 😊
Here's how Excel can help you before you dive into SQL:
✔️ 𝐕𝐋𝐎𝐎𝐊𝐔𝐏 = 𝐒𝐐𝐋 𝐉𝐎𝐈𝐍𝐒
In Excel, we use VLOOKUP to bring together data from different sheets. It's just like using JOINS in SQL to get data from more than one table.
✔️ 𝐒𝐔𝐌 𝐚𝐧𝐝 𝐂𝐎𝐔𝐍𝐓 𝐟𝐨𝐫 𝐒𝐐𝐋 𝐐𝐮𝐞𝐫𝐢𝐞𝐬
Excel's SUM and COUNT functions are like practice for SQL queries. They help you add up and count things, which is what you often do in SQL.
✔️ 𝐅𝐈𝐋𝐓𝐄𝐑 𝐒𝐭𝐚𝐭𝐞𝐦𝐞𝐧𝐭𝐬 & 𝐖𝐇𝐄𝐑𝐄 𝐢𝐧 𝐒𝐐𝐋
Excel's 𝐅𝐈𝐋𝐓𝐄𝐑 statements let you make choices with your data. This is similar to using WHERE in SQL to pick specific data.
✔️ 𝐇𝐚𝐧𝐝𝐥𝐢𝐧𝐠 𝐃𝐚𝐭𝐞𝐬 𝐚𝐧𝐝 𝐓𝐞𝐱𝐭
Both Excel and SQL have ways to work with dates and text. Learning these in Excel first can make it easier when you switch to SQL.
✔️ 𝐏𝐢𝐯𝐨𝐭 𝐓𝐚𝐛𝐥𝐞𝐬 & 𝐆𝐑𝐎𝐔𝐏 𝐁𝐘 𝐢𝐧 𝐒𝐐𝐋
Ever used pivot tables in Excel? They're a good start for understanding the GROUP BY function in SQL, which helps you organize and summarize data.
✔️ 𝐗𝐋𝐎𝐎𝐊𝐔𝐏 & 𝐇𝐲𝐩𝐞𝐫𝐥𝐢𝐧𝐤𝐬
Excel's XLOOKUP and hyperlinks are like SQL's ways of finding and linking data. They give you a peek into how SQL finds and connects information.
Learning Excel first makes SQL easier to understand. It's not just about learning a tool, it's about getting ready for the bigger world of data!
You will be asked questions on SQL in interviews for sure! Make sure to practice 2-3 questions daily, it can't be mastered overnight!
Share our channel link with your true friends: https://t.iss.one/excel_analyst
Hope this helps you 😊
👍2❤1😁1🍓1
🖥 Website To Learn Programming & Data Analytics
1. Learn HTML :- html.com
2. Learn CSS :- css-tricks.com
3. Learn Tailwind CSS :- tailwindcss.com
4. Learn JavaScript :- imp.i115008.net/mgGagX
5. Learn Bootstrap :- getbootstrap.com
6. Learn DSA :- t.iss.one/dsabooks
7. Learn Git :- git-scm.com
8. Learn React :- react-tutorial.app
9. Learn API :- rapidapi.com/learn
10. Learn Python :- t.iss.one/pythondevelopersindia
11. Learn SQL :- t.iss.one/sqlspecialist
12. Learn Web3 :- learnweb3.io
13. Learn JQuery :- learn.jquery.com
14. Learn ExpressJS :- expressjs.com
15. Learn NodeJS :- nodejs.dev/learn
16. Learn MongoDB :- learn.mongodb.com
17. Learn PHP :- phptherightway.com/
18. Learn Golang :- learn-golang.org/
19. Learn Power BI :- t.iss.one/powerbi_analyst
20. Learn Data Analytics:- datasimplifier.com
21. Learn Excel:- https://t.iss.one/excel_data
Join for more free resources: https://t.iss.one/free4unow_backup
ENJOY LEARNING 👍👍
1. Learn HTML :- html.com
2. Learn CSS :- css-tricks.com
3. Learn Tailwind CSS :- tailwindcss.com
4. Learn JavaScript :- imp.i115008.net/mgGagX
5. Learn Bootstrap :- getbootstrap.com
6. Learn DSA :- t.iss.one/dsabooks
7. Learn Git :- git-scm.com
8. Learn React :- react-tutorial.app
9. Learn API :- rapidapi.com/learn
10. Learn Python :- t.iss.one/pythondevelopersindia
11. Learn SQL :- t.iss.one/sqlspecialist
12. Learn Web3 :- learnweb3.io
13. Learn JQuery :- learn.jquery.com
14. Learn ExpressJS :- expressjs.com
15. Learn NodeJS :- nodejs.dev/learn
16. Learn MongoDB :- learn.mongodb.com
17. Learn PHP :- phptherightway.com/
18. Learn Golang :- learn-golang.org/
19. Learn Power BI :- t.iss.one/powerbi_analyst
20. Learn Data Analytics:- datasimplifier.com
21. Learn Excel:- https://t.iss.one/excel_data
Join for more free resources: https://t.iss.one/free4unow_backup
ENJOY LEARNING 👍👍
👍7❤2😁1💋1
Ask smart questions
The right question can reveal more than a hundred answers. Make them think while you gather intel.
The right question can reveal more than a hundred answers. Make them think while you gather intel.
🤮1
10 Must-Have Excel Skills for Professionals
1.Pivot Table
2. Xlookup
3. Pivot Charts
4. Flash Fill
5. Quick Analysis
6. Power View
7. Conditional Formatting
8. Moving Columns into Rows
9. IF Formulas
10. Auditing Formulas
#excel
1.Pivot Table
2. Xlookup
3. Pivot Charts
4. Flash Fill
5. Quick Analysis
6. Power View
7. Conditional Formatting
8. Moving Columns into Rows
9. IF Formulas
10. Auditing Formulas
#excel
🔥5👍2❤1
50 essential Excel formulas
SUM: =SUM(A1:A5)
AVERAGE: =AVERAGE(A1:A10)
VLOOKUP: =VLOOKUP(B1, A2:D10, 3, FALSE)
IF: =IF(A1 > 10, "Yes", "No")
CONCATENATE (or CONCAT): =CONCATENATE(A1, " ", B1)
COUNT: =COUNT(A1:A10)
MAX: =MAX(A1:A10)
MIN: =MIN(A1:A10)
ROUND: =ROUND(A1, 2)
TRIM: =TRIM(A1)
LOWER: =LOWER(A1)
UPPER: =UPPER(A1)
LEFT: =LEFT(A1, 5)
RIGHT: =RIGHT(A1, 5)
MID: =MID(A1, 2, 3)
LEN: =LEN(A1)
FIND: =FIND("search_text", A1)
REPLACE: =REPLACE(A1, 3, 2, "new_text")
SUBSTITUTE: =SUBSTITUTE(A1, "old_text", "new_text")
INDEX: =INDEX(A1:A10, 3)
MATCH: =MATCH(B1, A1:A10, 0)
OFFSET: =OFFSET(A1, 1, 2)
SUMIF: =SUMIF(A1:A10, ">5")
COUNTIF: =COUNTIF(A1:A10, "apple")
AVERAGEIF: =AVERAGEIF(A1:A10, "<>0")
SUMIFS: =SUMIFS(A1:A10, B1:B10, "apple", C1:C10, ">5")
COUNTIFS: =COUNTIFS(A1:A10, ">5", B1:B10, "apple")
AVERAGEIFS: =AVERAGEIFS(A1:A10, B1:B10, "apple", C1:C10, ">5")
IFERROR: =IFERROR(A1/B1, "Error")
AND: =AND(A1>5, A1<10)
OR: =OR(A1="apple", A1="banana")
NOT: =NOT(A1="apple")
DATE: =DATE(2022, 12, 31)
TODAY: =TODAY()
NOW: =NOW()
DATEDIF: =DATEDIF(A1, A2, "D")
YEAR: =YEAR(A1)
MONTH: =MONTH(A1)
DAY: =DAY(A1)
EOMONTH: =EOMONTH(A1, 3)
NETWORKDAYS: =NETWORKDAYS(A1, A2)
WEEKDAY: =WEEKDAY(A1)
HLOOKUP: =HLOOKUP(B1, A1:D10, 3, FALSE)
MATCH: =MATCH(B1, A1:A10, 0)
INDEX-MATCH: =INDEX(A1:A10, MATCH(B1, C1:C10, 0))
TRANSPOSE: =TRANSPOSE(A1:D10)
PIVOT TABLE: =PIVOT_TABLE(A1:D10, "Sales", "Region", "Sum")
RANK: =RANK(A1, A1:A10, 1)
RAND: =RAND()
CHOOSE: =CHOOSE(B1, "Option 1", "Option 2", "Option 3")
Share our channel link with your true friends: https://t.iss.one/excel_analyst
Hope this helps you 😊
SUM: =SUM(A1:A5)
AVERAGE: =AVERAGE(A1:A10)
VLOOKUP: =VLOOKUP(B1, A2:D10, 3, FALSE)
IF: =IF(A1 > 10, "Yes", "No")
CONCATENATE (or CONCAT): =CONCATENATE(A1, " ", B1)
COUNT: =COUNT(A1:A10)
MAX: =MAX(A1:A10)
MIN: =MIN(A1:A10)
ROUND: =ROUND(A1, 2)
TRIM: =TRIM(A1)
LOWER: =LOWER(A1)
UPPER: =UPPER(A1)
LEFT: =LEFT(A1, 5)
RIGHT: =RIGHT(A1, 5)
MID: =MID(A1, 2, 3)
LEN: =LEN(A1)
FIND: =FIND("search_text", A1)
REPLACE: =REPLACE(A1, 3, 2, "new_text")
SUBSTITUTE: =SUBSTITUTE(A1, "old_text", "new_text")
INDEX: =INDEX(A1:A10, 3)
MATCH: =MATCH(B1, A1:A10, 0)
OFFSET: =OFFSET(A1, 1, 2)
SUMIF: =SUMIF(A1:A10, ">5")
COUNTIF: =COUNTIF(A1:A10, "apple")
AVERAGEIF: =AVERAGEIF(A1:A10, "<>0")
SUMIFS: =SUMIFS(A1:A10, B1:B10, "apple", C1:C10, ">5")
COUNTIFS: =COUNTIFS(A1:A10, ">5", B1:B10, "apple")
AVERAGEIFS: =AVERAGEIFS(A1:A10, B1:B10, "apple", C1:C10, ">5")
IFERROR: =IFERROR(A1/B1, "Error")
AND: =AND(A1>5, A1<10)
OR: =OR(A1="apple", A1="banana")
NOT: =NOT(A1="apple")
DATE: =DATE(2022, 12, 31)
TODAY: =TODAY()
NOW: =NOW()
DATEDIF: =DATEDIF(A1, A2, "D")
YEAR: =YEAR(A1)
MONTH: =MONTH(A1)
DAY: =DAY(A1)
EOMONTH: =EOMONTH(A1, 3)
NETWORKDAYS: =NETWORKDAYS(A1, A2)
WEEKDAY: =WEEKDAY(A1)
HLOOKUP: =HLOOKUP(B1, A1:D10, 3, FALSE)
MATCH: =MATCH(B1, A1:A10, 0)
INDEX-MATCH: =INDEX(A1:A10, MATCH(B1, C1:C10, 0))
TRANSPOSE: =TRANSPOSE(A1:D10)
PIVOT TABLE: =PIVOT_TABLE(A1:D10, "Sales", "Region", "Sum")
RANK: =RANK(A1, A1:A10, 1)
RAND: =RAND()
CHOOSE: =CHOOSE(B1, "Option 1", "Option 2", "Option 3")
Share our channel link with your true friends: https://t.iss.one/excel_analyst
Hope this helps you 😊
Telegram
MS Excel for Data Analysis
✅ 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
✅ Learn Tips & Tricks Used in Excel
✅ Become An Expert
✅ Use The Skills Learnt Here In Your Career
For promotions: @love_data
👍11❤10
Roadmap to learn Excel:
📂 Learn Basic Excel Functions
∟📂 Learn Data Entry & Formatting
∟📂 Learn Basic Formulas (SUM, AVERAGE, MIN, MAX)
∟📂 Learn Basic Cell Referencing (Relative, Absolute)
∟📂 Learn Sorting and Filtering Data
📂 Learn Intermediate Excel Functions
∟📂 Learn Logical Functions (IF, AND, OR)
∟📂 Learn Lookup & Reference Functions (VLOOKUP, HLOOKUP, INDEX, MATCH)
∟📂 Learn Date & Time Functions
∟📂 Learn Text Functions (CONCATENATE, TEXT, LEFT, RIGHT)
📂 Learn Advanced Excel Functions
∟📂 Learn Advanced Formulas (SUMIF, COUNTIF, SUMPRODUCT, ARRAY FORMULAS)
∟📂 Learn Pivot Tables and Pivot Charts
∟📂 Learn Power Query for Data Transformation
∟📂 Learn Data Validation
∟📂 Learn Conditional Formatting
📂 Learn Data Visualization in Excel
∟📂 Learn Basic Charts (Bar, Line, Pie, etc.)
∟📂 Learn Advanced Charts (Combo Charts, Waterfall, Funnel, etc.)
∟📂 Learn Dashboards and Reports
📂 Learn Automation with VBA (Optional)
∟📂 Learn VBA Basics
∟📂 Automate Repetitive Tasks
∟📂 Learn User-Defined Functions (UDFs)
📂 Build Projects
∟📂 Create Financial Models
∟📂 Build Data Analysis Dashboards
∟📂 Analyze Large Datasets (e.g., Sales, Marketing)
📂 ✅ Apply for Jobs
∟📂 Apply for Data Analyst Roles
∟📂 Highlight Excel Projects in Resume
Free Excel Resources: https://whatsapp.com/channel/0029VaifY548qIzv0u1AHz3i
React ❤️ for More 📊
📂 Learn Basic Excel Functions
∟📂 Learn Data Entry & Formatting
∟📂 Learn Basic Formulas (SUM, AVERAGE, MIN, MAX)
∟📂 Learn Basic Cell Referencing (Relative, Absolute)
∟📂 Learn Sorting and Filtering Data
📂 Learn Intermediate Excel Functions
∟📂 Learn Logical Functions (IF, AND, OR)
∟📂 Learn Lookup & Reference Functions (VLOOKUP, HLOOKUP, INDEX, MATCH)
∟📂 Learn Date & Time Functions
∟📂 Learn Text Functions (CONCATENATE, TEXT, LEFT, RIGHT)
📂 Learn Advanced Excel Functions
∟📂 Learn Advanced Formulas (SUMIF, COUNTIF, SUMPRODUCT, ARRAY FORMULAS)
∟📂 Learn Pivot Tables and Pivot Charts
∟📂 Learn Power Query for Data Transformation
∟📂 Learn Data Validation
∟📂 Learn Conditional Formatting
📂 Learn Data Visualization in Excel
∟📂 Learn Basic Charts (Bar, Line, Pie, etc.)
∟📂 Learn Advanced Charts (Combo Charts, Waterfall, Funnel, etc.)
∟📂 Learn Dashboards and Reports
📂 Learn Automation with VBA (Optional)
∟📂 Learn VBA Basics
∟📂 Automate Repetitive Tasks
∟📂 Learn User-Defined Functions (UDFs)
📂 Build Projects
∟📂 Create Financial Models
∟📂 Build Data Analysis Dashboards
∟📂 Analyze Large Datasets (e.g., Sales, Marketing)
📂 ✅ Apply for Jobs
∟📂 Apply for Data Analyst Roles
∟📂 Highlight Excel Projects in Resume
Free Excel Resources: https://whatsapp.com/channel/0029VaifY548qIzv0u1AHz3i
React ❤️ for More 📊
❤6👍6😁1
Roadmap to learn EXCEL
Step 1 - Master Basic Formulas
Step 2 - Data Visualization
Step 3 - Pivot Tables and Analysis
Step 4 - Advanced Functions
Step 5 - Automation with Macros
Step 6 - Power Query and Power Pivot
Step 7 - Collaboration and Sharing
Step 8 - Excel Tips and Tricks
.....read more
#excel
Step 1 - Master Basic Formulas
Step 2 - Data Visualization
Step 3 - Pivot Tables and Analysis
Step 4 - Advanced Functions
Step 5 - Automation with Macros
Step 6 - Power Query and Power Pivot
Step 7 - Collaboration and Sharing
Step 8 - Excel Tips and Tricks
.....read more
#excel
👍1
Here are some Excel shortcuts that are commonly used by data analysts:
1. Ctrl + C: Copy
2. Ctrl + V: Paste
3. Ctrl + X: Cut
4. Ctrl + Z: Undo
5. Ctrl + Y: Redo
6. Ctrl + S: Save
7. Ctrl + F: Find
8. Ctrl + H: Replace
9. Ctrl + Arrow Keys: Navigate to the edge of data
10. Ctrl + Shift + Arrow Keys: Select data range
11. Ctrl + Home: Go to cell A1
12. Ctrl + End: Go to last cell with data
13. Ctrl + Shift + L: Toggle filters
14. Alt + ; : Select visible cells only
15. F2: Edit active cell
16. Ctrl + Shift + Enter: Enter an array formula
17. Ctrl + D: Fill down
18. Ctrl + R: Fill right
19. Ctrl + 1: Format cells dialog box
20. Ctrl + Shift + 1, 2, 3, etc.: Apply different number formats
These shortcuts can significantly increase your efficiency when working with Excel as a data analyst.
One of the very important tool that a data analyst must be aware of is Excel
1. Ctrl + C: Copy
2. Ctrl + V: Paste
3. Ctrl + X: Cut
4. Ctrl + Z: Undo
5. Ctrl + Y: Redo
6. Ctrl + S: Save
7. Ctrl + F: Find
8. Ctrl + H: Replace
9. Ctrl + Arrow Keys: Navigate to the edge of data
10. Ctrl + Shift + Arrow Keys: Select data range
11. Ctrl + Home: Go to cell A1
12. Ctrl + End: Go to last cell with data
13. Ctrl + Shift + L: Toggle filters
14. Alt + ; : Select visible cells only
15. F2: Edit active cell
16. Ctrl + Shift + Enter: Enter an array formula
17. Ctrl + D: Fill down
18. Ctrl + R: Fill right
19. Ctrl + 1: Format cells dialog box
20. Ctrl + Shift + 1, 2, 3, etc.: Apply different number formats
These shortcuts can significantly increase your efficiency when working with Excel as a data analyst.
One of the very important tool that a data analyst must be aware of is Excel
👍5❤1
📊 Excel Hack of the Week
Did you know you can use Flash Fill in Excel to automatically clean and format data without writing formulas?
📝 How to Use Flash Fill?
1️⃣ Type the first correct value manually in the adjacent column.
2️⃣ Press Ctrl + E (or go to Data > Flash Fill).
3️⃣ Excel will recognize the pattern and fill in the rest automatically!
🔍 Example:
✅ Extract first names from "John Doe" → Type "John" → Press Ctrl + E → Done!
✅ Format phone numbers from "1234567890" to "(123) 456-7890" in seconds!
✅ Convert dates from "01-02-2024" to "February 1, 2024" instantly!
📌 Bonus: Try using Flash Fill for splitting names, fixing email formats, or even extracting numbers from text.
You can join @excel_data for free Excel Resources.
Like this post for more data analytics tricks 👍♥️
Share with credits: https://t.iss.one/sqlspecialist
Hope it helps :)
Did you know you can use Flash Fill in Excel to automatically clean and format data without writing formulas?
📝 How to Use Flash Fill?
1️⃣ Type the first correct value manually in the adjacent column.
2️⃣ Press Ctrl + E (or go to Data > Flash Fill).
3️⃣ Excel will recognize the pattern and fill in the rest automatically!
🔍 Example:
✅ Extract first names from "John Doe" → Type "John" → Press Ctrl + E → Done!
✅ Format phone numbers from "1234567890" to "(123) 456-7890" in seconds!
✅ Convert dates from "01-02-2024" to "February 1, 2024" instantly!
📌 Bonus: Try using Flash Fill for splitting names, fixing email formats, or even extracting numbers from text.
You can join @excel_data for free Excel Resources.
Like this post for more data analytics tricks 👍♥️
Share with credits: https://t.iss.one/sqlspecialist
Hope it helps :)
👍4
📊 Excel Hack of the Week
Did you know you can use Flash Fill in Excel to automatically clean and format data without writing formulas?
📝 How to Use Flash Fill?
1️⃣ Type the first correct value manually in the adjacent column.
2️⃣ Press Ctrl + E (or go to Data > Flash Fill).
3️⃣ Excel will recognize the pattern and fill in the rest automatically!
🔍 Example:
✅ Extract first names from "John Doe" → Type "John" → Press Ctrl + E → Done!
✅ Format phone numbers from "1234567890" to "(123) 456-7890" in seconds!
✅ Convert dates from "01-02-2024" to "February 1, 2024" instantly!
📌 Bonus: Try using Flash Fill for splitting names, fixing email formats, or even extracting numbers from text.
Like this post for more excel tricks 👍♥️
Share with credits: https://t.iss.one/sqlspecialist
Did you know you can use Flash Fill in Excel to automatically clean and format data without writing formulas?
📝 How to Use Flash Fill?
1️⃣ Type the first correct value manually in the adjacent column.
2️⃣ Press Ctrl + E (or go to Data > Flash Fill).
3️⃣ Excel will recognize the pattern and fill in the rest automatically!
🔍 Example:
✅ Extract first names from "John Doe" → Type "John" → Press Ctrl + E → Done!
✅ Format phone numbers from "1234567890" to "(123) 456-7890" in seconds!
✅ Convert dates from "01-02-2024" to "February 1, 2024" instantly!
📌 Bonus: Try using Flash Fill for splitting names, fixing email formats, or even extracting numbers from text.
Like this post for more excel tricks 👍♥️
Share with credits: https://t.iss.one/sqlspecialist
❤3👍1
Excel Cheat Sheet for Aspiring Data Analysts 📊
As an aspiring data analyst, mastering Excel is like having a superpower!
Here’s a cheat sheet to get you started:
✔️ VLOOKUP & HLOOKUP: Quickly find and retrieve data across your spreadsheets.
✔️ Pivot Tables: Summarize and analyze large data sets with ease.
✔️ IF Statements: Make decisions in your sheets with logical tests
✔️ INDEX & MATCH: More flexible data lookup compared to VLOOKUP.
✔️ Conditional Formatting: Highlight important data trends and outliers.
✔️ Data Validation: Control the type of data entered into your sheets.
✔️ SUMIF & COUNTIF: Add and count cells that meet specific criteria.
✔️ Macros: Automate repetitive tasks to save time.
With these tools in your arsenal, you’ll be able to turn raw data into actionable insights effortlessly.
Join for more Resources : https://t.iss.one/excel_data
Hope this helps you 😊
As an aspiring data analyst, mastering Excel is like having a superpower!
Here’s a cheat sheet to get you started:
✔️ VLOOKUP & HLOOKUP: Quickly find and retrieve data across your spreadsheets.
✔️ Pivot Tables: Summarize and analyze large data sets with ease.
✔️ IF Statements: Make decisions in your sheets with logical tests
✔️ INDEX & MATCH: More flexible data lookup compared to VLOOKUP.
✔️ Conditional Formatting: Highlight important data trends and outliers.
✔️ Data Validation: Control the type of data entered into your sheets.
✔️ SUMIF & COUNTIF: Add and count cells that meet specific criteria.
✔️ Macros: Automate repetitive tasks to save time.
With these tools in your arsenal, you’ll be able to turn raw data into actionable insights effortlessly.
Join for more Resources : https://t.iss.one/excel_data
Hope this helps you 😊
👍4❤1
Why is Excel Often the Starting Point for SQL ?
Here's how Excel can help you before you dive into SQL:
✔️ 𝐕𝐋𝐎𝐎𝐊𝐔𝐏 = 𝐒𝐐𝐋 𝐉𝐎𝐈𝐍𝐒
In Excel, we use VLOOKUP to bring together data from different sheets. It's just like using JOINS in SQL to get data from more than one table.
✔️ 𝐒𝐔𝐌 𝐚𝐧𝐝 𝐂𝐎𝐔𝐍𝐓 𝐟𝐨𝐫 𝐒𝐐𝐋 𝐐𝐮𝐞𝐫𝐢𝐞𝐬
Excel's SUM and COUNT functions are like practice for SQL queries. They help you add up and count things, which is what you often do in SQL.
✔️ 𝐅𝐈𝐋𝐓𝐄𝐑 𝐒𝐭𝐚𝐭𝐞𝐦𝐞𝐧𝐭𝐬 & 𝐖𝐇𝐄𝐑𝐄 𝐢𝐧 𝐒𝐐𝐋
Excel's 𝐅𝐈𝐋𝐓𝐄𝐑 statements let you make choices with your data. This is similar to using WHERE in SQL to pick specific data.
✔️ 𝐇𝐚𝐧𝐝𝐥𝐢𝐧𝐠 𝐃𝐚𝐭𝐞𝐬 𝐚𝐧𝐝 𝐓𝐞𝐱𝐭
Both Excel and SQL have ways to work with dates and text. Learning these in Excel first can make it easier when you switch to SQL.
✔️ 𝐏𝐢𝐯𝐨𝐭 𝐓𝐚𝐛𝐥𝐞𝐬 & 𝐆𝐑𝐎𝐔𝐏 𝐁𝐘 𝐢𝐧 𝐒𝐐𝐋
Ever used pivot tables in Excel? They're a good start for understanding the GROUP BY function in SQL, which helps you organize and summarize data.
✔️ 𝐗𝐋𝐎𝐎𝐊𝐔𝐏 & 𝐇𝐲𝐩𝐞𝐫𝐥𝐢𝐧𝐤𝐬
Excel's XLOOKUP and hyperlinks are like SQL's ways of finding and linking data. They give you a peek into how SQL finds and connects information.
Learning Excel first makes SQL easier to understand. It's not just about learning a tool, it's about getting ready for the bigger world of data!
You will be asked questions on SQL in interviews for sure! Make sure to practice 2-3 questions daily, it can't be mastered overnight!
Share our channel link with your true friends: https://t.iss.one/excel_analyst
Hope this helps you 😊
Here's how Excel can help you before you dive into SQL:
✔️ 𝐕𝐋𝐎𝐎𝐊𝐔𝐏 = 𝐒𝐐𝐋 𝐉𝐎𝐈𝐍𝐒
In Excel, we use VLOOKUP to bring together data from different sheets. It's just like using JOINS in SQL to get data from more than one table.
✔️ 𝐒𝐔𝐌 𝐚𝐧𝐝 𝐂𝐎𝐔𝐍𝐓 𝐟𝐨𝐫 𝐒𝐐𝐋 𝐐𝐮𝐞𝐫𝐢𝐞𝐬
Excel's SUM and COUNT functions are like practice for SQL queries. They help you add up and count things, which is what you often do in SQL.
✔️ 𝐅𝐈𝐋𝐓𝐄𝐑 𝐒𝐭𝐚𝐭𝐞𝐦𝐞𝐧𝐭𝐬 & 𝐖𝐇𝐄𝐑𝐄 𝐢𝐧 𝐒𝐐𝐋
Excel's 𝐅𝐈𝐋𝐓𝐄𝐑 statements let you make choices with your data. This is similar to using WHERE in SQL to pick specific data.
✔️ 𝐇𝐚𝐧𝐝𝐥𝐢𝐧𝐠 𝐃𝐚𝐭𝐞𝐬 𝐚𝐧𝐝 𝐓𝐞𝐱𝐭
Both Excel and SQL have ways to work with dates and text. Learning these in Excel first can make it easier when you switch to SQL.
✔️ 𝐏𝐢𝐯𝐨𝐭 𝐓𝐚𝐛𝐥𝐞𝐬 & 𝐆𝐑𝐎𝐔𝐏 𝐁𝐘 𝐢𝐧 𝐒𝐐𝐋
Ever used pivot tables in Excel? They're a good start for understanding the GROUP BY function in SQL, which helps you organize and summarize data.
✔️ 𝐗𝐋𝐎𝐎𝐊𝐔𝐏 & 𝐇𝐲𝐩𝐞𝐫𝐥𝐢𝐧𝐤𝐬
Excel's XLOOKUP and hyperlinks are like SQL's ways of finding and linking data. They give you a peek into how SQL finds and connects information.
Learning Excel first makes SQL easier to understand. It's not just about learning a tool, it's about getting ready for the bigger world of data!
You will be asked questions on SQL in interviews for sure! Make sure to practice 2-3 questions daily, it can't be mastered overnight!
Share our channel link with your true friends: https://t.iss.one/excel_analyst
Hope this helps you 😊
👍8🥰2❤1
💥TOOL TIPS:
HOW TO,
A. To insert multiple columns:
1. Select a number of column headers equal to the number of columns you want to insert.
2. Right-click any selected column header, and then click Insert.
B. To insert a row
1. Right-click a row header, and then click Insert.
C. To insert multiple rows
1. Select a number of row headers equal to the number of rows you want to insert.
2. Right-click any selected row header, and then click Insert.
D. To delete one or more columns
1. Select the column headers of the columns you want to delete.
2. Right-click any selected column header, and then click Delete.
E. To delete one or more rows
1. Select the row headers of the rows you want to delete.
2. Right-click any selected row header, and then click Delete.
F. To hide one or more columns
1. Select the column headers of the columns you want to hide
2. Right-click any selected column header, and then click Hide.
G. To hide one or more rows
1. Select the row headers of the rows you want to hide.
2. Right-click any selected row header, and then click Hide.
H. To unhide one or more columns
1. Select the column headers to the immediate left and right of the column or columns you want to unhide.
2. Right-click any selected column header, and then click Unhide.
Alternatively,
1. Press Ctrl+A to select the entire worksheet.
2. Right-click anywhere in the worksheet, and then click Unhide.
I. To unhide one or more rows
1. Select the row headers immediately above and below the row or rows you want to unhide.
2. Right-click any selected column header, and then click Unhide.
Alternatively,
1. Press Ctrl + A to select the entire worksheet
2. Right-click anywhere in the worksheet, and then click Unhide
HOW TO,
A. To insert multiple columns:
1. Select a number of column headers equal to the number of columns you want to insert.
2. Right-click any selected column header, and then click Insert.
B. To insert a row
1. Right-click a row header, and then click Insert.
C. To insert multiple rows
1. Select a number of row headers equal to the number of rows you want to insert.
2. Right-click any selected row header, and then click Insert.
D. To delete one or more columns
1. Select the column headers of the columns you want to delete.
2. Right-click any selected column header, and then click Delete.
E. To delete one or more rows
1. Select the row headers of the rows you want to delete.
2. Right-click any selected row header, and then click Delete.
F. To hide one or more columns
1. Select the column headers of the columns you want to hide
2. Right-click any selected column header, and then click Hide.
G. To hide one or more rows
1. Select the row headers of the rows you want to hide.
2. Right-click any selected row header, and then click Hide.
H. To unhide one or more columns
1. Select the column headers to the immediate left and right of the column or columns you want to unhide.
2. Right-click any selected column header, and then click Unhide.
Alternatively,
1. Press Ctrl+A to select the entire worksheet.
2. Right-click anywhere in the worksheet, and then click Unhide.
I. To unhide one or more rows
1. Select the row headers immediately above and below the row or rows you want to unhide.
2. Right-click any selected column header, and then click Unhide.
Alternatively,
1. Press Ctrl + A to select the entire worksheet
2. Right-click anywhere in the worksheet, and then click Unhide
👍7❤1👏1