I could then categorize customers into groups such as Active, At Risk, and Inactive and summarize them using a PivotTable."

4️⃣ How would you identify products with high sales but low profit?

Sample Answer:

"I would calculate both revenue and profit for each product.

Then I would compare products using a PivotTable or scatter chart.

For example, a product could have high revenue but a low profit margin because of high discounts or costs.

I would investigate the underlying factors rather than judging product performance based only on revenue."

5️⃣ You find that 10% of transactions have missing customer IDs. What would you do?

Sample Answer:

"First, I would determine why the customer IDs are missing.

I would check whether the information exists in another source or whether the records can be matched using another reliable identifier.

If they cannot be recovered, I would assess how those transactions affect the analysis.

I would not simply delete the records because doing so could change the total sales or other KPIs."

6️⃣ A stakeholder asks for a dashboard with 20 different KPIs. What would you do?

Sample Answer:

"I would first understand what decisions the stakeholder needs to make using the dashboard.

Then I would prioritize the KPIs based on business importance and frequency of use.

I would avoid putting every available metric on one screen because excessive information can make a dashboard difficult to use.

I would separate critical KPIs from supporting metrics and use filters or drill-downs where appropriate."

7️⃣ Two departments provide different versions of the same Excel report. How would you determine which one is correct?

Sample Answer:

"I would compare the underlying data sources, business definitions, calculation logic, filters, date ranges, and refresh times.

I would identify the exact point where the two reports start producing different results.

Then I would reconcile the numbers against the authoritative source or agreed business definition.

The goal would be to establish one consistent definition rather than simply choosing the report with the expected number."

8️⃣ How would you detect unusual transactions in Excel?

Sample Answer:

"I would first define what constitutes an unusual transaction based on the business context.

Possible approaches include identifying values outside expected thresholds, unusually large transactions, unusual dates, abnormal frequencies, or significant deviations from historical patterns.

I could use conditional formatting, statistical measures, PivotTables, or formulas to flag potential anomalies.

I would then investigate the flagged records rather than automatically treating them as errors."

9️⃣ Your Excel dashboard works correctly today but breaks whenever new data is added. How would you fix it?

Sample Answer:

"I would investigate whether the issue is caused by fixed ranges, formulas, PivotTable sources, or inconsistent incoming data.

I would convert the source data into an Excel Table or use Power Query so that the data range expands automatically.

I would also make the transformation and calculation logic refreshable and test the dashboard with new records before considering it stable."

🔟 What makes a good Excel dashboard?

Sample Answer:

"A good dashboard should be clear, relevant, accurate, and easy to use.

I would focus on:

• Important KPIs

• Clear visual hierarchy

• Appropriate charts

• Consistent formatting

• Useful filters and slicers

• Minimal unnecessary information

• Clear labels and units

• Fast refresh and reliable calculations

Most importantly, the dashboard should help the user answer a business question or make a decision rather than simply display data."

📌 Double Tap ❤️ For More