📗 Excel mistakes beginners should avoid:

1. Merging Cells
- Breaks sorting and filtering
- Causes errors in formulas
- Use Center Across Selection instead
- Keeps data structure clean

2. Hardcoding Values in Formulas
- Makes spreadsheets static
- Hard to update later
- Use cell references (e.g., =A1×B1)
- Change one cell to update everything

3. Not Using Excel Tables (Ctrl + T)
- Manual ranges don't expand
- Formulas don't auto-fill
- Tables provide dynamic ranges
- Makes Pivot Tables easier to update

4. Manual Data Cleaning
- Slow and prone to human error
- Use Power Query for repetitive tasks
- Use Find & Replace or Flash Fill
- Automate the boring stuff

5. Ignoring Absolute References ($)
- Formulas break when dragged
- Use $A$1 to lock a cell
- Press F4 to toggle references
- Essential for tax rates or constants

6. Overusing VLOOKUP
- Slow on large datasets
- Breaks if you insert a column
- Learn XLOOKUP or INDEX/MATCH
- More flexible and powerful

7. Not Handling Errors
- Sheets look messy with #N/A or #DIV/0!
- Confuses the end-user
- Wrap formulas in IFERROR
- Example: =IFERROR(A1/B1, 0)

8. Merging Data Manually
- Copy-pasting from different sheets
- High risk of misalignment
- Use Power Query to merge/append
- Keep a "Single Source of Truth"

9. Poor Formatting & Alignment
- Using too many colors and borders
- Numbers aligned as text
- Keep it professional and clean
- Use Conditional Formatting to highlight trends

10. Not Using Pivot Tables
- Calculating summaries with manual math
- Taking hours to do what takes seconds
- Pivot Tables are the heart of analysis
- Drag and drop to get instant insights

Double Tap ♥️ For More

📊 Excel Interview Questions — Part 1

1️⃣ What is the difference between VLOOKUP and XLOOKUP?

Sample Answer:

VLOOKUP searches for a value in the first column of a table and returns a value from a column to its right.

XLOOKUP is more flexible because it can search both left and right, supports exact matching by default, and allows a custom result when the value isn't found.

Example:

"=XLOOKUP(A2,E2:E10,F2:F10,"Not Found")"

💡 In modern Excel, I would generally prefer XLOOKUP over VLOOKUP.

2️⃣ What is the difference between COUNT, COUNTA and COUNTBLANK?

Sample Answer:

"COUNT" → Counts cells containing numbers.

"COUNTA" → Counts non-empty cells.

"COUNTBLANK" → Counts empty cells.

Example:

"=COUNT(A2:A100)"

"=COUNTA(A2:A100)"

"=COUNTBLANK(A2:A100)"

💡 This is commonly asked to test your understanding of different data types.

3️⃣ What is the difference between SUMIF and SUMIFS?

Sample Answer:

"SUMIF" is used when we have one condition.

"SUMIFS" is used when we have multiple conditions.

Example:

"=SUMIF(A2:A100,"East",C2:C100)"

This calculates sales for the East region.

For multiple conditions:

"=SUMIFS(C2:C100,A2:A100,"East",B2:B100,"Laptop")"

This calculates Laptop sales in the East region.

4️⃣ What is the difference between relative and absolute cell references?

Sample Answer:

A relative reference changes when a formula is copied.

Example:

"A2"

An absolute reference remains fixed.

Example:

"A2"

A mixed reference fixes either the row or column:

"$A2"
"A$2"

💡 The "F4" key can be used while editing a formula to cycle through reference types.

5️⃣ What is a PivotTable?

Sample Answer:

A PivotTable is an Excel feature used to quickly summarize and analyze large datasets.

For example, from a sales dataset, I can create a PivotTable to calculate:

→ Sales by region
→ Sales by product
→ Monthly revenue
→ Number of transactions

It allows me to analyze data without manually writing multiple formulas.

6️⃣ How would you remove duplicate records in Excel?

Sample Answer:

I can use Excel's Remove Duplicates feature.

First, select the dataset.

Then:

"Data → Remove Duplicates"

I select the columns that should be considered when identifying duplicates and click OK.

💡 I would usually keep a copy of the original dataset before removing duplicates.

7️⃣ What is IFERROR and why is it useful?

Sample Answer:

"IFERROR" allows me to replace an Excel error with a more meaningful result.

Example:

"=IFERROR(A2/B2,0)"

If the calculation produces an error, Excel returns "0" instead.

Another example:

"=IFERROR(XLOOKUP(A2,E:E,F:F),"Not Found")"

This prevents errors such as "#N/A" from appearing in a report.

8️⃣ How would you find duplicate values in Excel?

Sample Answer:

One common approach is Conditional Formatting.

I select the required range and use:

"Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values"

Another approach is using a formula such as:

"=COUNTIF(A2:A100,A2)>1"

If the result is TRUE, the value appears more than once.

9️⃣ How would you extract the month from a date?

Sample Answer:

I can use the "MONTH" function to return the month number.

"=MONTH(A2)"

For example:

"15-Sep-2026" → "9"

If I need the month name:

"=TEXT(A2,"mmmm")"

Result:

"September"

💡 The approach depends on whether I need the numeric month or the actual month name.

🔟 What is the difference between VLOOKUP and INDEX + MATCH?

Sample Answer:

VLOOKUP searches for a value in the first column and returns a value from a column to the right.

INDEX + MATCH is more flexible because the lookup column doesn't have to be the first column, and it can look in either direction.

Example:

"=INDEX(C2:C100,MATCH(A2,A2:A100,0))"

Here:

"MATCH" → Finds the position of the lookup value.

"INDEX" → Returns the value from that position.

Double Tap ❤️ For More