"Excel Your Interview: Top 10 Must-Know Excel Questions"
1. What are the different data types in Excel?
- Text
- Number
- Date/Time
- Boolean (TRUE/FALSE)
- Error (e.g., #DIV/0!, #N/A)
2. What is the difference between absolute,
relative, and mixed cell references?
- Relative (A1): Changes when copied.
- Absolute ($A$1): Fixed reference.
- Mixed ($A1 or A$1): Either row or column is
fixed.
3. How do you use VLOOKUP and what are its
limitations?
- Syntax: =VLOOKUP(lookup_value,
table_array, col_index_num, [range_lookup])
- Limitations:
- Only looks to the right.
- Slower with large data.
- Breaks with column
insertions.
- Replaced often by XLOOKUP or INDEX-MATCH.
4. What is the difference between VLOOKUP, HLOOKUP,
INDEX-MATCH, and XLOOKUP?
- VLOOKUP: Vertical lookup (left to
right).
- HLOOKUP: Horizontal lookup (top to
bottom).
- INDEX-MATCH: More flexible, faster, can
look left.
- XLOOKUP: Most powerful, can search
both directions, return multiple columns, supports error handling.
5. What are Pivot Tables and when do you use them?
- Pivot Tables summarize large datasets
dynamically.
- Use them to:
- Aggregate (sum, average,
count)
- Group by categories
- Slice data using filters
and slicers
6. What are some commonly used Excel functions?
- Logical: IF, AND, OR, IFERROR
- Lookup: VLOOKUP, HLOOKUP, INDEX, MATCH, XLOOKUP
- Text: LEFT, RIGHT, MID, LEN, CONCATENATE, TEXTJOIN, TRIM
- Date/Time: TODAY, NOW, DATEDIF, EDATE, EOMONTH
- Math/Stat: SUM, AVERAGE, ROUND, COUNTIF, SUMIF, MAX, MIN
7. How do you remove duplicates in Excel?
- Select range > Go to Data
tab > Click Remove Duplicates
- Can also use =UNIQUE() in dynamic arrays (Excel
365/2021+)
8. How do you handle errors in Excel formulas?
- Use IFERROR(value,
value_if_error)
- Example: =IFERROR(A1/B1,
"Error!")
9. How do you create dynamic drop-down lists in
Excel?
- Use Data Validation
with a range or a named range
- For dynamic lists: Use OFFSET or UNIQUE with named ranges or tables
10. What are Excel Tables and their advantages?
- Created via Insert >
Table
- Benefits:
- Auto-expanding ranges
- Structured references
- Easier filtering/sorting
- Compatible with PivotTables
and Power Query
No comments:
Post a Comment