Forwarded from Data Analytics
If you dream of becoming a data analyst, let 2025 be the year you make it happen.
Work hard, stay focused, and change your life.
Happy New Year! May this year bring you success and new opportunities 💪
Work hard, stay focused, and change your life.
Happy New Year! May this year bring you success and new opportunities 💪
👍30❤4🎉4👏2
Top 10 Excel Interview Questions with Answers 😄👇
Free Resources to learn Excel: https://t.iss.one/excel_analyst
1. Question: What is the difference between CONCATENATE and "&" in Excel?
Answer: CONCATENATE and "&" both combine text, but "&" is more concise. For example,
2. Question: How can you freeze rows and columns simultaneously in Excel?
Answer: Use the "Freeze Panes" option under the "View" tab. Select the cell below and to the right of the rows and columns you want to freeze, and then click on "Freeze Panes."
3. Question: Explain the VLOOKUP function and when would you use it?
Answer: VLOOKUP searches for a value in the first column of a range and returns a corresponding value in the same row from another column. It's useful for looking up information in a table based on a specific criteria.
4. Question: What is the purpose of the IFERROR function?
Answer: IFERROR is used to handle errors in Excel formulas. It returns a specified value if a formula results in an error, and the actual result if there's no error.
5. Question: How do you create a PivotTable, and what is its purpose?
Answer: To create a PivotTable, select your data, go to the "Insert" tab, and choose "PivotTable." It summarizes and analyzes data in a spreadsheet, allowing you to make sense of large datasets.
6. Question: Explain the difference between relative and absolute cell references.
Answer: Relative references change when you copy a formula to another cell, while absolute references stay fixed. Use a
7. Question: What is the purpose of the INDEX and MATCH functions?
Answer: INDEX returns a value in a specified range based on the row and column number, while MATCH searches for a value in a range and returns its relative position. Combined, they provide a flexible way to look up data.
8. Question: How can you find and remove duplicate values in Excel?
Answer: Use the "Remove Duplicates" feature under the "Data" tab. Select the range containing duplicates, go to "Data" -> "Remove Duplicates," and choose the columns to check for duplicates.
9. Question: Explain the difference between a workbook and a worksheet.
Answer: A workbook is the entire Excel file, while a worksheet is a single sheet within that file. Workbooks can contain multiple worksheets.
10. Question: What is the purpose of the COUNTIF function?
Answer: COUNTIF counts the number of cells within a range that meet a specified condition. For example,
Share with credits: https://t.iss.one/sqlspecialist
Hope it helps :)
Free Resources to learn Excel: https://t.iss.one/excel_analyst
1. Question: What is the difference between CONCATENATE and "&" in Excel?
Answer: CONCATENATE and "&" both combine text, but "&" is more concise. For example,
=A1&B1 achieves the same result as =CONCATENATE(A1, B1).2. Question: How can you freeze rows and columns simultaneously in Excel?
Answer: Use the "Freeze Panes" option under the "View" tab. Select the cell below and to the right of the rows and columns you want to freeze, and then click on "Freeze Panes."
3. Question: Explain the VLOOKUP function and when would you use it?
Answer: VLOOKUP searches for a value in the first column of a range and returns a corresponding value in the same row from another column. It's useful for looking up information in a table based on a specific criteria.
4. Question: What is the purpose of the IFERROR function?
Answer: IFERROR is used to handle errors in Excel formulas. It returns a specified value if a formula results in an error, and the actual result if there's no error.
5. Question: How do you create a PivotTable, and what is its purpose?
Answer: To create a PivotTable, select your data, go to the "Insert" tab, and choose "PivotTable." It summarizes and analyzes data in a spreadsheet, allowing you to make sense of large datasets.
6. Question: Explain the difference between relative and absolute cell references.
Answer: Relative references change when you copy a formula to another cell, while absolute references stay fixed. Use a
$ symbol to make a reference absolute (e.g., $A$1).7. Question: What is the purpose of the INDEX and MATCH functions?
Answer: INDEX returns a value in a specified range based on the row and column number, while MATCH searches for a value in a range and returns its relative position. Combined, they provide a flexible way to look up data.
8. Question: How can you find and remove duplicate values in Excel?
Answer: Use the "Remove Duplicates" feature under the "Data" tab. Select the range containing duplicates, go to "Data" -> "Remove Duplicates," and choose the columns to check for duplicates.
9. Question: Explain the difference between a workbook and a worksheet.
Answer: A workbook is the entire Excel file, while a worksheet is a single sheet within that file. Workbooks can contain multiple worksheets.
10. Question: What is the purpose of the COUNTIF function?
Answer: COUNTIF counts the number of cells within a range that meet a specified condition. For example,
=COUNTIF(A1:A10, ">50") counts the cells in A1 to A10 that are greater than 50.Share with credits: https://t.iss.one/sqlspecialist
Hope it helps :)
👍19❤4🔥2👏1
Microsoft Excel users write code all the time.
Even if they don't think of it this way.
Consider the following Excel formula:
=IF(ResellerSales[@SalesAmount] < 1000,
"Yes",
"No")
This knowledge can help you learn Python.
🧵👇
Even if they don't think of it this way.
Consider the following Excel formula:
=IF(ResellerSales[@SalesAmount] < 1000,
"Yes",
"No")
This knowledge can help you learn Python.
🧵👇
👍4
Now consider the same thing in Python:
where(ResellerSales["SalesAmount"] < 1000,
"Yes",
"No")
Looks very similar, no?
Here's the dirty little secret in real-world DIY data science.
Excel code is very similar to Python.
But it goes beyond that...
So many of the things that you do in Microsoft Excel translate to what you do in Python:
Filtering data tables
Combining data tables
Pivoting data tables
Visualizing data tables
Your Excel skills are the gateway in 2025.
If you know Excel, it's easy for you to use Pandas library in Python for data manipulation
Here is a free pdf to help you learn Pandas quickly: https://t.iss.one/pythondevelopersindia/881
Hope this helps you
#excel #python
where(ResellerSales["SalesAmount"] < 1000,
"Yes",
"No")
Looks very similar, no?
Here's the dirty little secret in real-world DIY data science.
Excel code is very similar to Python.
But it goes beyond that...
So many of the things that you do in Microsoft Excel translate to what you do in Python:
Filtering data tables
Combining data tables
Pivoting data tables
Visualizing data tables
Your Excel skills are the gateway in 2025.
If you know Excel, it's easy for you to use Pandas library in Python for data manipulation
Here is a free pdf to help you learn Pandas quickly: https://t.iss.one/pythondevelopersindia/881
Hope this helps you
#excel #python
👍12❤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
👍20❤4
Data people, repeat after me:
Excel is not a database.
Excel is not a database.
Excel is not a database.
Excel is not a database.
Excel is not a database.
Excel is not a database.
Excel is not a database.
Excel is not a database.
Excel is not a database.
Excel is not a database.
Excel is not a database.
Excel is not a database.
Excel is not a database.
Excel is not a database.
Excel is not a database.
Excel is not a database.
Excel is not a database.
Excel is not a database.
Excel is not a database.
Excel is not a database.
👍32😁23👏4❤3
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 😊
👍38❤9😁1
👉VLOOKUP & INDEX/MATCH:
😍 Instead of using just VLOOKUP for searching data, try INDEX and MATCH. It’s more flexible and can search to the left of the lookup value, which VLOOKUP cannot.
😇 Example: =INDEX(A2:A10, MATCH("Search Value", B2:B10, 0))
😍 Instead of using just VLOOKUP for searching data, try INDEX and MATCH. It’s more flexible and can search to the left of the lookup value, which VLOOKUP cannot.
😇 Example: =INDEX(A2:A10, MATCH("Search Value", B2:B10, 0))
👍14
VLOOKUP vs HLOOKUP:
VLOOKUP looks for values in a column (vertical lookup).
HLOOKUP looks for values in a row (horizontal lookup).
Need to look across both axes?
Use XLOOKUP.
#exceltips
VLOOKUP looks for values in a column (vertical lookup).
HLOOKUP looks for values in a row (horizontal lookup).
Need to look across both axes?
Use XLOOKUP.
#exceltips
👍13❤2🔥2
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_data
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
I'll now start with learning series on SQL Interviews & Tableau.
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_data
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
I'll now start with learning series on SQL Interviews & Tableau.
Thanks to all who support our channel and share the content with proper credits. You guys are really amazing.
Hope it helps :)
👍25❤2
Join this channel to get free Excel Resources 👇👇
https://t.iss.one/excel_data
https://t.iss.one/excel_data
👍7