Guys, let's continue our Data Analyst Interview Series.
Today, let's focus on real-world Excel case-study questions. These are important because interviewers often want to know how you approach a business problem, not just whether you know a formula.
1๏ธโฃ You receive a sales dataset with 1 million rows. How would you analyze it in Excel?
Sample Answer:
"First, I would understand the structure and business objective.
Since the dataset is large, I would avoid relying heavily on worksheet formulas. I would use Power Query to import and transform the data and, if appropriate, load it into the Data Model.
Then I would create measures or PivotTables for KPIs such as total sales, profit, order count, average order value, and sales growth.
Finally, I would validate the results against the source data before presenting the analysis."
2๏ธโฃ Sales have decreased by 20% this month. How would you investigate the reason?
Sample Answer:
"I would not assume that the overall decline has a single cause.
I would break the analysis down by:
โข Region
โข Product
โข Customer segment
โข Sales channel
โข Date
โข Salesperson
Then I would compare each segment with the previous period to identify where the decline is concentrated.
I would also check for data-quality issues, missing transactions, changes in business rules, and incomplete data before drawing a conclusion."
3๏ธโฃ How would you identify customers at risk of becoming inactive?
Sample Answer:
"First, I would define what 'inactive' means for the business, such as no purchase in the last 90 days.
Then I would calculate each customer's most recent transaction date and calculate the number of days since that transaction.
For example:
=TODAY()-Last_Purchase_Date