Excel Interview Q & A
1. What are some common Excel functions you use?
➡️ SUM, IF, VLOOKUP, INDEX-MATCH, COUNTIF, CONCATENATE, and TEXT functions.
2. How do you handle duplicate data in Excel?
➡️ Use the ‘Remove Duplicates’ feature or formulas like COUNTIF to identify duplicates.
3. Explain the difference between absolute, relative, and mixed cell references.
➡️ Relative (A1) changes when copied, absolute (A1) stays fixed, mixed (A1 or A1) locks row or column.
4. How would you use a Pivot Table?
➡️ Summarize large data sets quickly by grouping, filtering, and calculating aggregates like sums or averages.
5. What’s the difference between a function and a formula?
➡️ Formula is any calculation entered in a cell; function is a predefined formula like SUM().
6. How do you apply conditional formatting?
➡️ Highlight cells based on criteria like value, text, or formulas to visualize trends or errors.
7. Can you explain what data validation is?
➡️ It restricts the type or range of data entered into a cell to ensure accuracy.
Top 10 Logical Excel Functions Every Data Analyst
1️⃣ IF
→ Returns a value based on a condition
=IF(A2>50, “Pass”, “Fail”)
2️⃣ AND
→ Checks if all conditions are TRUE
=IF(AND(A2>50, B2>50), “Pass”, “Fail”)
3️⃣ OR
→ Checks if at least one condition is TRUE
=IF(OR(A2>50, B2>50), “Pass”, “Fail”)
4️⃣ NOT
→ Reverses the logic of a condition
=IF(NOT(A2>50), “Fail”, “Pass”)
5️⃣ IFS
→ Handles multiple conditions without nesting IFs
=IFS(A2>90,”A”,A2>80,”B”,A2>70,”C”)
6️⃣ IFERROR
→ Handles errors and returns a custom value
=IFERROR(A2/B2, “Error”)
7️⃣ ISBLANK
→ Checks if a cell is empty
=IF(ISBLANK(A2), “Missing”, A2)
8️⃣ ISNUMBER
→ Checks if a value is numeric
=IF(ISNUMBER(A2), “Number”, “Text”)
9️⃣ XLOOKUP + IF
→ Combine to search and validate
=IF(XLOOKUP(“Apple”,A2:A10,B2:B10)=””, “Not found”, “Found”)
🔟 SWITCH
→ Replaces nested IFs with simpler syntax
=SWITCH(A2, “A”, “Excellent”, “B”, “Good”, “C”, “Average”)
Top 10 Text Functions in Excel Every Analyst
1️⃣ LEFT
→ Extracts characters from the start
=LEFT(A2, 5) — First 5 characters of A2
2️⃣ RIGHT
→ Extracts characters from the end
=RIGHT(A2, 3) — Last 3 characters of A2
3️⃣ MID
→ Extracts text from the middle
=MID(A2, 3, 4) — From 3rd character, take 4 letters
4️⃣ LEN
→ Counts the number of characters
=LEN(A2)
5️⃣ TRIM
→ Removes extra spaces from text
=TRIM(A2)
6️⃣ CONCAT / TEXTJOIN
→ Combines text from multiple cells
=CONCAT(A2,B2)
=TEXTJOIN(” “, TRUE, A2:C2)
7️⃣ UPPER / LOWER / PROPER
→ Changes text case
=UPPER(A2) → ALL CAPS
=LOWER(A2) → all lowercase
=PROPER(A2) → Capitalize Each Word
8️⃣ FIND / SEARCH
→ Find position of text
=FIND(“a”, A2)
=SEARCH(“a”, A2) — Not case-sensitive
9️⃣ REPLACE / SUBSTITUTE
→ Change part of text
=REPLACE(A2,1,5,”Hello”)
=SUBSTITUTE(A2,”old”,”new”)
🔟 TEXT
→ Format numbers/dates into text
=TEXT(A2,”dd-mm-yyyy”)
Top 10 Excel Functions Every Analyst – With Examples!
1️⃣ VLOOKUP() / XLOOKUP()
🔍 Find a value in a table
Example: =VLOOKUP(“John”, A2:C10, 2, FALSE) → Finds John’s data in 2nd column
Modern Excel: =XLOOKUP(“John”, A2:A10, B2:B10)
2️⃣ IF()
✅ Returns a value based on a condition
Example: =IF(B2>50, “Pass”, “Fail”)
3️⃣ SUMIFS() / COUNTIFS()
📊 Sum or count with conditions
Example: =SUMIFS(C2:C10, B2:B10, “North”) → Total sales in North region
4️⃣ INDEX() + MATCH()
🔄 Better than VLOOKUP for flexibility
Example: =INDEX(C2:C10, MATCH(“John”, A2:A10, 0))
5️⃣ TEXT()
📅 Format dates or numbers
Example: =TEXT(A2, “dd-mmm-yyyy”) → 13-Jun-2025
6️⃣ LEFT(), RIGHT(), MID()
✂️ Extract text from a cell
Example: =LEFT(A2, 5) → First 5 characters
Example: =MID(A2, 3, 4) → Starts at 3rd character, 4 characters long
7️⃣ CONCAT() / TEXTJOIN()
🔗 Join multiple cells into one
Example: =TEXTJOIN(” “, TRUE, A2, B2) → Joins A2 and B2 with a space
8️⃣ LEN() & TRIM()
🧹 Clean up text
Example: =LEN(A2) → Counts characters
Example: =TRIM(A2) → Removes extra spaces
9️⃣ NOW() / TODAY()
🕒 Insert current time/date
Example: =TODAY() → Returns current date
🔟 IFERROR()
🚫 Handle errors gracefully
Example: =IFERROR(A2/B2, “Error”) → Avoids #DIV/0