✅ Text Functions in Excel ✂️📄
Text functions help clean, extract, and format text data. Useful for cleaning names, splitting columns, or preparing reports.
1️⃣ LEFT, RIGHT, MID
⦁ LEFT(text, num_chars): Get characters from start
⦁ RIGHT(text, num_chars): Get characters from end
⦁ MID(text, start_num, num_chars): Extract middle part
Example:
2️⃣ LEN
Returns number of characters in a string.
3️⃣ FIND & SEARCH
Finds position of text.
⦁ FIND is case-sensitive
⦁ SEARCH is not
4️⃣ TRIM
Removes extra spaces.
5️⃣ CONCAT & TEXTJOIN
⦁ CONCAT: Joins values
⦁ TEXTJOIN: Joins with a delimiter
6️⃣ UPPER, LOWER, PROPER
⦁
⦁
⦁
7️⃣ SUBSTITUTE & REPLACE
⦁ SUBSTITUTE: Replace specific text
⦁ REPLACE: Replace based on position
8️⃣ VALUE & TEXT
⦁ VALUE("100") → Converts text to number
⦁ TEXT(1234.5, "#,##0.00") → 1,234.50
💡 These are essential for data cleaning, reporting, and automation in Excel.
💬 Tap ❤️ for more!
Text functions help clean, extract, and format text data. Useful for cleaning names, splitting columns, or preparing reports.
1️⃣ LEFT, RIGHT, MID
⦁ LEFT(text, num_chars): Get characters from start
⦁ RIGHT(text, num_chars): Get characters from end
⦁ MID(text, start_num, num_chars): Extract middle part
Example:
=LEFT("Excel", 2) → Ex =RIGHT("Excel", 2) → el =MID("Excel", 2, 2) → xc2️⃣ LEN
Returns number of characters in a string.
=LEN("Excel") → 53️⃣ FIND & SEARCH
Finds position of text.
⦁ FIND is case-sensitive
⦁ SEARCH is not
=FIND("x", "Excel") → 2 =SEARCH("X", "Excel") → 24️⃣ TRIM
Removes extra spaces.
=TRIM(" Excel ") → Excel5️⃣ CONCAT & TEXTJOIN
⦁ CONCAT: Joins values
⦁ TEXTJOIN: Joins with a delimiter
=CONCAT(A1, B1) → No space =TEXTJOIN(" ", TRUE, A1, B1) → Adds space6️⃣ UPPER, LOWER, PROPER
⦁
=UPPER("excel") → EXCEL⦁
=LOWER("EXCEL") → excel⦁
=PROPER("hello world") → Hello World7️⃣ SUBSTITUTE & REPLACE
⦁ SUBSTITUTE: Replace specific text
=SUBSTITUTE("2023-11-25", "-", "/") → 2023/11/25⦁ REPLACE: Replace based on position
=REPLACE("Excel", 2, 2, "++") → E++el8️⃣ VALUE & TEXT
⦁ VALUE("100") → Converts text to number
⦁ TEXT(1234.5, "#,##0.00") → 1,234.50
💡 These are essential for data cleaning, reporting, and automation in Excel.
💬 Tap ❤️ for more!
❤14
✅ Essential Date & Time Functions in Excel ⏰📅
Master these to manage schedules, deadlines, and timestamps with ease:
1️⃣ TODAY()
Returns the current date.
Use it to track deadlines dynamically.
Example:
2️⃣ NOW()
Returns current date and time.
Example:
3️⃣ DATEDIF(start_date, end_date, unit)
Calculates the difference between two dates.
Example:
4️⃣ DAY(), MONTH(), YEAR()
Extracts day, month, or year from a date.
Example:
5️⃣ TEXT(date, format)
Formats dates/times into readable text.
Example:
6️⃣ EDATE(start_date, months)
Adds months to a date.
Example:
7️⃣ WORKDAY(start_date, days)
Skips weekends (and holidays optionally).
Example:
8️⃣ NETWORKDAYS(start_date, end_date)
Counts working days between two dates.
Example:
💡 Use these to automate timelines, manage reporting dates, and reduce manual errors.
💬 Tap ❤️ for more!
Master these to manage schedules, deadlines, and timestamps with ease:
1️⃣ TODAY()
Returns the current date.
Use it to track deadlines dynamically.
Example:
=TODAY()2️⃣ NOW()
Returns current date and time.
Example:
=NOW()3️⃣ DATEDIF(start_date, end_date, unit)
Calculates the difference between two dates.
Example:
=DATEDIF(A1, B1, "D") → Days between A1 and B14️⃣ DAY(), MONTH(), YEAR()
Extracts day, month, or year from a date.
Example:
=DAY(A1), =MONTH(A1), =YEAR(A1)5️⃣ TEXT(date, format)
Formats dates/times into readable text.
Example:
=TEXT(A1, "dddd, mmm dd yyyy")6️⃣ EDATE(start_date, months)
Adds months to a date.
Example:
=EDATE(A1, 3) → 3 months later7️⃣ WORKDAY(start_date, days)
Skips weekends (and holidays optionally).
Example:
=WORKDAY(A1, 10)8️⃣ NETWORKDAYS(start_date, end_date)
Counts working days between two dates.
Example:
=NETWORKDAYS(A1, B1)💡 Use these to automate timelines, manage reporting dates, and reduce manual errors.
💬 Tap ❤️ for more!
❤14
✅ Top 50 Microsoft Excel Interview Questions 📊🧠
1. What is Excel and its key features?
2. Difference between Excel Workbook and Worksheet
3. What are cell references (Relative, Absolute, Mixed)?
4. Explain basic Excel formulas: SUM, AVERAGE, COUNT
5. What is the use of VLOOKUP and HLOOKUP?
6. Difference between VLOOKUP and INDEX-MATCH
7. What is a Pivot Table? How do you create one?
8. Explain conditional formatting with examples
9. What is Data Validation?
10. How do you remove duplicates in Excel?
11. How does IF function work?
12. What is the use of COUNTIF and SUMIF?
13. What is the difference between CONCATENATE and TEXTJOIN?
14. What are Named Ranges and why use them?
15. Explain how to use Filters and Advanced Filters
16. What are Charts in Excel? Types of charts
17. How do you create a dynamic chart?
18. What is the use of the MATCH function?
19. Explain the use of the TODAY() and NOW() functions
20. How do you use Excel for data cleaning?
21. What is a macro in Excel?
22. How to record a macro in Excel?
23. Difference between Excel formulas and functions
24. What is the use of the IFERROR function?
25. How do you protect cells or sheets in Excel?
26. How to use the TRIM and CLEAN functions?
27. What is Power Query in Excel?
28. What is Power Pivot?
29. How do you merge cells and center content?
30. What are Excel Tables and how are they useful?
31. What is the use of the TEXT function?
32. Explain how to sort data in Excel
33. What is Goal Seek in Excel?
34. What is Scenario Manager in Excel?
35. What are array formulas and how to use them?
36. What is the use of TRANSPOSE function?
37. Explain difference between workbook sharing and co-authoring
38. What is Flash Fill?
39. How do you import data from other sources into Excel?
40. How to link multiple sheets together?
41. What is the use of the INDIRECT function?
42. Explain the OFFSET function
43. What are Sparklines?
44. How to create a drop-down list?
45. How to use the RANK function in Excel?
46. How does Excel handle errors in formulas?
47. What is the use of the SUBSTITUTE and REPLACE functions?
48. How do you use Excel in financial modeling?
49. How do you create a dashboard in Excel?
50. What is the difference between CSV and XLSX?
💬 Tap ❤️ for the detailed answers!
1. What is Excel and its key features?
2. Difference between Excel Workbook and Worksheet
3. What are cell references (Relative, Absolute, Mixed)?
4. Explain basic Excel formulas: SUM, AVERAGE, COUNT
5. What is the use of VLOOKUP and HLOOKUP?
6. Difference between VLOOKUP and INDEX-MATCH
7. What is a Pivot Table? How do you create one?
8. Explain conditional formatting with examples
9. What is Data Validation?
10. How do you remove duplicates in Excel?
11. How does IF function work?
12. What is the use of COUNTIF and SUMIF?
13. What is the difference between CONCATENATE and TEXTJOIN?
14. What are Named Ranges and why use them?
15. Explain how to use Filters and Advanced Filters
16. What are Charts in Excel? Types of charts
17. How do you create a dynamic chart?
18. What is the use of the MATCH function?
19. Explain the use of the TODAY() and NOW() functions
20. How do you use Excel for data cleaning?
21. What is a macro in Excel?
22. How to record a macro in Excel?
23. Difference between Excel formulas and functions
24. What is the use of the IFERROR function?
25. How do you protect cells or sheets in Excel?
26. How to use the TRIM and CLEAN functions?
27. What is Power Query in Excel?
28. What is Power Pivot?
29. How do you merge cells and center content?
30. What are Excel Tables and how are they useful?
31. What is the use of the TEXT function?
32. Explain how to sort data in Excel
33. What is Goal Seek in Excel?
34. What is Scenario Manager in Excel?
35. What are array formulas and how to use them?
36. What is the use of TRANSPOSE function?
37. Explain difference between workbook sharing and co-authoring
38. What is Flash Fill?
39. How do you import data from other sources into Excel?
40. How to link multiple sheets together?
41. What is the use of the INDIRECT function?
42. Explain the OFFSET function
43. What are Sparklines?
44. How to create a drop-down list?
45. How to use the RANK function in Excel?
46. How does Excel handle errors in formulas?
47. What is the use of the SUBSTITUTE and REPLACE functions?
48. How do you use Excel in financial modeling?
49. How do you create a dashboard in Excel?
50. What is the difference between CSV and XLSX?
💬 Tap ❤️ for the detailed answers!
❤44👍4👏2
✅ Top Excel Interview Questions with Answers: Part-1 🧠
1. What is Excel and its key features?
Microsoft Excel is a spreadsheet software used to store, organize, calculate, and analyze data.
Key features include: formulas, functions, charts, Pivot Tables, data validation, conditional formatting, and macros/VBA.
2. Difference between Excel Workbook and Worksheet
A Workbook is the entire Excel file, which can contain multiple Worksheets (individual tabs). Each worksheet holds a grid of rows and columns for data entry.
3. What are cell references (Relative, Absolute, Mixed)?
• Relative (A1): Adjusts automatically when copied.
• Absolute ($A$1): Fixed cell reference, doesn’t change when copied.
• Mixed (A$1 or $A1): Either row or column stays fixed.
4. Explain basic Excel formulas: SUM, AVERAGE, COUNT
•
•
•
5. What is the use of VLOOKUP and HLOOKUP?
• VLOOKUP searches vertically down the first column of a range.
Example:
• HLOOKUP searches horizontally across the top row of a range.
6. Difference between VLOOKUP and INDEX-MATCH
• VLOOKUP only searches left to right and can break if columns are moved.
• INDEX-MATCH is more flexible and faster, allowing lookups in any direction.
7. What is a Pivot Table? How do you create one?
A Pivot Table summarizes large datasets by grouping and aggregating.
To create: Select data → Insert → PivotTable → Drag fields into Rows, Columns, Values.
8. Explain conditional formatting with examples
Conditional formatting applies color or icons based on rules.
Example: Highlight all cells > 100
Go to Home → Conditional Formatting → Highlight Cells Rules → Greater Than.
9. What is Data Validation?
It restricts what type of data can be entered in a cell.
Example: Allow numbers only from 1 to 100 using Data → Data Validation.
10. How do you remove duplicates in Excel?
Select the data → Go to Data tab → Click “Remove Duplicates” → Choose columns to check → Confirm.
Double Tap ♥️ For Part-2
1. What is Excel and its key features?
Microsoft Excel is a spreadsheet software used to store, organize, calculate, and analyze data.
Key features include: formulas, functions, charts, Pivot Tables, data validation, conditional formatting, and macros/VBA.
2. Difference between Excel Workbook and Worksheet
A Workbook is the entire Excel file, which can contain multiple Worksheets (individual tabs). Each worksheet holds a grid of rows and columns for data entry.
3. What are cell references (Relative, Absolute, Mixed)?
• Relative (A1): Adjusts automatically when copied.
• Absolute ($A$1): Fixed cell reference, doesn’t change when copied.
• Mixed (A$1 or $A1): Either row or column stays fixed.
4. Explain basic Excel formulas: SUM, AVERAGE, COUNT
•
=SUM(A1:A5) adds the values in cells A1 through A5.•
=AVERAGE(A1:A5) calculates the average.•
=COUNT(A1:A5) counts numeric entries only.5. What is the use of VLOOKUP and HLOOKUP?
• VLOOKUP searches vertically down the first column of a range.
Example:
=VLOOKUP(101, A2:C10, 2, FALSE) • HLOOKUP searches horizontally across the top row of a range.
6. Difference between VLOOKUP and INDEX-MATCH
• VLOOKUP only searches left to right and can break if columns are moved.
• INDEX-MATCH is more flexible and faster, allowing lookups in any direction.
7. What is a Pivot Table? How do you create one?
A Pivot Table summarizes large datasets by grouping and aggregating.
To create: Select data → Insert → PivotTable → Drag fields into Rows, Columns, Values.
8. Explain conditional formatting with examples
Conditional formatting applies color or icons based on rules.
Example: Highlight all cells > 100
Go to Home → Conditional Formatting → Highlight Cells Rules → Greater Than.
9. What is Data Validation?
It restricts what type of data can be entered in a cell.
Example: Allow numbers only from 1 to 100 using Data → Data Validation.
10. How do you remove duplicates in Excel?
Select the data → Go to Data tab → Click “Remove Duplicates” → Choose columns to check → Confirm.
Double Tap ♥️ For Part-2
❤43
✅ Top Excel Interview Questions with Answers: Part-2 🧠
11. How does IF function work?
The IF function checks a condition and returns one value if it's TRUE, another if FALSE.
Syntax: =IF(condition, value_if_true, value_if_false)
Example: =IF(A2>50, "Pass", "Fail")
12. What is the use of COUNTIF and SUMIF?
• COUNTIF counts cells based on a condition.
• SUMIF adds values that meet a condition.
Example:
=COUNTIF(A1:A10, ">50")
=SUMIF(B1:B10, "Apples", C1:C10)
13. What is the difference between CONCATENATE and TEXTJOIN?
• CONCATENATE joins multiple text values (older function).
• TEXTJOIN joins with a delimiter and can ignore empty cells (Excel 2016+).
Example:
=CONCATENATE(A1, " ", B1)
=TEXTJOIN(" ", TRUE, A1:C1)
14. What are Named Ranges and why use them?
A named range is a custom name for a cell or range, useful in formulas.
Example: Name cell A1 as Price, then use =Price*10 instead of =A1*10
15. Explain how to use Filters and Advanced Filters
• Filter: Use the “Filter” button in the Data tab to view specific rows.
• Advanced Filter: Apply criteria from a range to filter complex data.
16. What are Charts in Excel? Types of charts
Charts visualize data.
Types: Column, Bar, Pie, Line, Scatter, Area, Combo, etc.
Use: Select data → Insert → Choose chart type.
17. How do you create a dynamic chart?
Use Excel Tables or named ranges that auto-expand.
Tip: Use OFFSET with defined names to auto-update chart data.
18. What is the use of the MATCH function?
Returns the position of a value in a range.
Syntax: =MATCH(50, A1:A10, 0) → Returns position of 50.
19. Explain the use of the TODAY() and NOW() functions
• TODAY() returns the current date.
• NOW() returns current date and time.
Useful for timestamps or dynamic calculations.
20. How do you use Excel for data cleaning?
• Remove duplicates
• Use TRIM(), CLEAN() to fix formatting
• Use FIND, REPLACE, and Filters
• Use Power Query for more advanced cleanup
Double Tap ♥️ For Part-3
11. How does IF function work?
The IF function checks a condition and returns one value if it's TRUE, another if FALSE.
Syntax: =IF(condition, value_if_true, value_if_false)
Example: =IF(A2>50, "Pass", "Fail")
12. What is the use of COUNTIF and SUMIF?
• COUNTIF counts cells based on a condition.
• SUMIF adds values that meet a condition.
Example:
=COUNTIF(A1:A10, ">50")
=SUMIF(B1:B10, "Apples", C1:C10)
13. What is the difference between CONCATENATE and TEXTJOIN?
• CONCATENATE joins multiple text values (older function).
• TEXTJOIN joins with a delimiter and can ignore empty cells (Excel 2016+).
Example:
=CONCATENATE(A1, " ", B1)
=TEXTJOIN(" ", TRUE, A1:C1)
14. What are Named Ranges and why use them?
A named range is a custom name for a cell or range, useful in formulas.
Example: Name cell A1 as Price, then use =Price*10 instead of =A1*10
15. Explain how to use Filters and Advanced Filters
• Filter: Use the “Filter” button in the Data tab to view specific rows.
• Advanced Filter: Apply criteria from a range to filter complex data.
16. What are Charts in Excel? Types of charts
Charts visualize data.
Types: Column, Bar, Pie, Line, Scatter, Area, Combo, etc.
Use: Select data → Insert → Choose chart type.
17. How do you create a dynamic chart?
Use Excel Tables or named ranges that auto-expand.
Tip: Use OFFSET with defined names to auto-update chart data.
18. What is the use of the MATCH function?
Returns the position of a value in a range.
Syntax: =MATCH(50, A1:A10, 0) → Returns position of 50.
19. Explain the use of the TODAY() and NOW() functions
• TODAY() returns the current date.
• NOW() returns current date and time.
Useful for timestamps or dynamic calculations.
20. How do you use Excel for data cleaning?
• Remove duplicates
• Use TRIM(), CLEAN() to fix formatting
• Use FIND, REPLACE, and Filters
• Use Power Query for more advanced cleanup
Double Tap ♥️ For Part-3
❤32