MS Excel Interview Preparation Video 3: Top 10 Excel Interview Questions & Answers

This video is part of a broader MS Excel course:

• Video 1: Job opportunities requiring Excel skills & how to access Excel for free.
• Video 2: Complete Excel tutorial for beginners.

Objective

A quick overview of 10 commonly asked Excel interview questions, focusing on scenario-based tasks rather than just function names.

---

Key Points & Interview Questions

1. Merge First, Middle, and Last Name Columns into Full Name

• Functions Used: CONCAT, TEXTJOIN
• CONCAT: Combines cells (no delimiter by default). Add spaces manually if needed.
• TEXTJOIN: Specify delimiter (e.g., a space) and choose to ignore empty cells.
  • Syntax: =TEXTJOIN(" ", TRUE, A2, B2, C2)

2. Split Full Name into First and Last Name Columns

• Feature Used: Text to Columns (Data Tab)
  1. Select data.
  2. Go to Data > Text to Columns.
  3. Choose Delimited (use space as delimiter).
  4. Specify the destination for the output.
• Best Practice: Clarify the format of the names (spaces, commas, middle names) before providing a solution.

3. Keep Header Row Visible While Scrolling

• Feature Used: Freeze Panes
  • Go to View tab > Freeze Top Row or Freeze First Column as required.
  • Can also freeze multiple rows/columns.

4. Prevent Others from Editing a Worksheet

• Sheet Protection:
  • Go to Review Tab > Protect Sheet.
  • Set permissions and password.
  • To unprotect, use the same menu and enter the password.

5. VLOOKUP Returning #N/A Error

• Possible Reasons:
  1. Absolute Referencing Not Used: Table array range changes when copying formula. (Fix by adding $ or F4.)
  2. VLOOKUP Limitation: Lookup value must be in the leftmost column. Move the lookup column if not.
• General Tip: Learn about absolute/relative referencing and VLOOKUP limitations.

6. Sum All Scores for a Specific Student

• Function Used: SUMIF
  • Syntax: =SUMIF(range_of_names, "Harry", range_of_scores)

7. Highlight Scores Greater Than 80

• Feature Used: Conditional Formatting
  1. Select cells.
  2. Home Tab > Conditional Formatting > Highlight Cell Rules > Greater Than.
  3. Set color preference.

8. Sum Scores of Subjects Ending With "y"

• Function Used: SUMIF with Wildcards (*)
  • Syntax: =SUMIF(subjects_range, "*y", scores_range)
• Wildcards in Excel:
  • * (asterisk): Any number of characters.
  • ? (question mark): Any one character.
  • ~ (tilde): Treats next character as literal (e.g., ~* for an actual asterisk).

9. Prevent/Resolve #REF! Errors After Deleting Columns/Rows

• Cause: Formula references deleted cells.
• Prevention Steps:
  1. Check for dependencies: Formulas Tab > Trace Dependents.
  2. Use Show Formulas to reveal all formulas before deleting.

10. Analyzing Marketing Data for Campaign Decisions

• Feature Used: Pivot Tables
  • Capable of summarizing, analyzing, and comparing large datasets without changing original data.
• Prerequisites for Pivot Table:
  1. Correct and single header row.
  2. No blank rows/columns.
  3. Value fields formatted as numbers.
  4. Date fields formatted as dates.
  5. Remove duplicates.

---

Additional Common Topics

• Charts
• Removing duplicates
• Text functions
• Error types
• Sorting and filtering

---

Interview Preparation Tips

• Prepare for behavioral questions (e.g., "Tell me about yourself", "What are your strengths and weaknesses?", explaining employment gaps, etc.)
• Check the complete tutorial and interview prep courses linked in the video/channel.

---

Host: Tushin – Urban Fight YouTube Channel

Remember to like, subscribe, and check out other career and finance videos for well-researched, concise content.