📊 Data Analyst Interview Series — Part 9

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

Let's cover 10 more important Excel interview questions, with a stronger focus on formulas, data analysis, and practical situations. 👇

1️⃣ What is the difference between SUMPRODUCT and SUMIFS?

Sample Answer:

"SUMIFS is useful when I need to sum values based on one or more criteria.

SUMPRODUCT can perform more flexible calculations by multiplying corresponding arrays and then summing the results.

For example, if I want total revenue:

=SUMPRODUCT(Quantity,Price)

This multiplies each quantity by its corresponding price and adds the results."

2️⃣ How would you find the percentage of total sales for each region?

Sample Answer:

"I would first calculate total sales for each region and then divide each region's sales by the overall sales."

=Region_Sales/Total_Sales

"If the total sales is stored in a fixed cell, I would use an absolute reference so that the denominator doesn't change when the formula is copied."

3️⃣ How would you identify the first purchase made by each customer?

Sample Answer:

"I would identify the minimum transaction date for each customer."

For example, with modern Excel:

=MINIFS(Transaction_Date,Customer_ID,A2)

"This gives the earliest transaction date associated with the customer."

4️⃣ How would you calculate the number of working days between two dates?

Sample Answer:

"I can use NETWORKDAYS to calculate the number of working days between two dates."

=NETWORKDAYS(Start_Date,End_Date)

"If the company has specific holidays, I can provide a holiday range as an additional argument."

=NETWORKDAYS(Start_Date,End_Date,Holidays)

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

Sample Answer:

"I can use the TEXT function to convert a date into a month name."

=TEXT(A2,"mmmm")

"This returns the full month name, such as January.

If I need the abbreviated month name, I can use:

=TEXT(A2,"mmm")"

6️⃣ How would you calculate year-over-year sales growth?

Sample Answer:

"I would compare the current year's sales with the previous year's sales."

=(Current_Year_Sales-Previous_Year_Sales)/Previous_Year_Sales

"For example, if sales increased from ₹80 lakh to ₹100 lakh:

Growth = (100 - 80) / 80 = 25%

I would also check that the comparison periods are equivalent."

7️⃣ What is a slicer in Excel?

Sample Answer:

"A slicer is an interactive filtering control commonly used with PivotTables and PivotCharts.

Instead of opening filter menus, users can click buttons to filter the data.

For example, a sales dashboard could have slicers for Region, Product Category, and Year."

8️⃣ How would you identify the month with the highest sales?

Sample Answer:

"I would first aggregate sales by month using a PivotTable or another appropriate method.

Then I would identify the maximum monthly sales value and retrieve the corresponding month.

For example, I could use MAX to find the highest value and XLOOKUP to return the associated month."

=MAX(B2:B13)

=XLOOKUP(MAX(B2:B13),B2:B13,A2:A13)

9️⃣ How would you compare two Excel datasets and identify missing records?

Sample Answer:

"I would identify a common unique key, such as Customer_ID or Transaction_ID, and compare the two datasets using XLOOKUP, COUNTIF, Power Query, or another suitable method.

For example:

=IF(COUNTIF(Sheet2!A:A,A2)=0,"Missing","Found")

This allows me to identify records present in the first dataset but missing from the second."

🔟 Your manager asks you to reduce a manual Excel report that takes 4 hours every week. What would you do?

Sample Answer:

"First, I would understand the current reporting process and identify which steps are repetitive.

I would then look for opportunities to automate data extraction, cleaning, calculations, and reporting.

For example, I could use Power Query for data preparation, PivotTables or the Data Model for analysis, and formulas or VBA only where appropriate.

DOUBLE TAP ❤️ FOR MORE