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