📊 Data Analyst Interview Series — Part 7

Guys, let's continue our Data Analyst Interview Series.

Let's go deeper into Excel with scenario-based questions. These questions test whether you can actually use Excel to solve business problems, not just remember formulas.

1️⃣ How would you find the highest-selling product in each region?

Sample Answer:

"I would first summarize sales by Region and Product using a PivotTable. Then I would compare the sales within each region and identify the product with the highest value.

For a dynamic solution, I could also use functions such as MAXIFS, FILTER, and XLOOKUP depending on the Excel version."

2️⃣ How would you calculate the percentage contribution of each product to total sales?

Sample Answer:

"I would calculate the total sales for each product and divide it by the overall sales."

=Product_Sales/Total_Sales

"If the total sales value is stored in a fixed cell, I would use an absolute reference."

=B2/$B$10

"I would then format the result as a percentage."

3️⃣ How would you identify customers whose sales have decreased compared with the previous month?

Sample Answer:

"I would first create a monthly sales summary by customer. Then I would compare the current month's sales with the previous month's sales.

A negative difference would indicate a decrease."

=Current_Month_Sales-Previous_Month_Sales

"I could then use conditional formatting to highlight customers with negative changes."

4️⃣ How would you calculate a rolling 3-month average?

Sample Answer:

"A rolling average calculates the average over a moving window of time.

For example, a 3-month rolling average can be calculated using the current month and the previous two months."

=AVERAGE(B2:B4)

"As I move down the dataset, the range moves forward by one month."

5️⃣ How would you calculate employee tenure in Excel?

Sample Answer:

"If I have an employee's joining date, I can calculate their tenure using DATEDIF or by calculating the difference between the joining date and today's date."

For example:

=DATEDIF(A2,TODAY(),"Y")

"This returns the completed number of years."

6️⃣ How would you identify overdue payments?

Sample Answer:

"I would compare the due date with the current date and check whether the payment status is still pending."

For example:

=IF(AND(B2<TODAY(),C2="Pending"),"Overdue","On Time")

"I could then use conditional formatting to highlight overdue transactions."

7️⃣ How would you identify the top 10% of customers by sales?

Sample Answer:

"I would first calculate total sales by customer. Then I could use a percentile-based approach or rank customers based on sales.

For example, I could calculate the 90th percentile and identify customers whose sales are greater than or equal to that threshold."

=PERCENTILE.INC(B2:B100,0.9)