Excel Scenario-Based Questions for Interview & Practice 🧠📊 📌… — Data Analyst Interview Resources

Data Analyst Interview Resources@dataanalystinterview · 52.6K subscribers Open in TelegramOpen

✅ Excel Scenario-Based Questions for Interview & Practice 🧠📊

📌 Scenario 96

Question: You have a dataset with thousands of rows and want to quickly identify the highest sales transaction for each region. How would you do it?

Answer: Use "MAXIFS()".

Example:

"=MAXIFS($B$2:$B$1000,$A$2:$A$1000,D2)"

Where "A" contains Region, "B" contains Sales, and "D2" contains the region you want to analyze.

📊 Scenario 97

Question: Your manager wants to calculate the number of unique products sold in each region. How would you do it in modern Excel?

Answer: Use "FILTER()", "UNIQUE()", and "COUNTA()".

Example:

"=COUNTA(UNIQUE(FILTER(B2:B1000,A2:A1000=D2)))"

This counts distinct products for the region specified in "D2".

📅 Scenario 98

Question: You have a monthly sales report and want users to select a month from a dropdown and automatically display the corresponding sales. How would you do it?

Answer: Create a dropdown using Data Validation and use "XLOOKUP()".

Example:

"=XLOOKUP(E2,A2:A13,B2:B13,"Not Found")"

Where "E2" contains the selected month.

📈 Scenario 99

Question: Your Excel report contains formulas that should not be visible to users, but users still need to enter data into specific cells. How would you protect the workbook?

Answer:

1. Select input cells → Format Cells → Protection → Unlock them.

2. Keep formula cells locked.

3. Go to Review → Protect Sheet.

4. Set a password if required.

This allows users to edit only the designated input cells.

🔍 Scenario 100

Question: Your manager gives you a large, messy dataset containing duplicates, missing values, inconsistent formats, and multiple files. You need to create a clean, refreshable report. What approach would you take?

Answer: Use a combination of Power Query, Excel Tables, Pivot Tables, and Data Validation.

A practical workflow would be:

➡️ Import and combine files using Power Query

➡️ Remove duplicates and handle missing values

➡️ Standardize data formats

➡️ Load the cleaned data into an Excel Table

➡️ Build Pivot Tables/Pivot Charts for analysis

➡️ Add Slicers for interactive filtering

➡️ Refresh the report whenever new data is received

💬 Double Tap ♥️ For More!

t.me/dataanalystinterview/2389