MS Excel for Data Analysis

🚀 Excel Interview Questions with Answers — Part 10

91. How would you build a monthly sales report from raw transaction data?

Step-by-Step Process:

1. Import raw sales data

2. Clean data using:

- Remove duplicates

- Handle blanks

- Standardize formats

3. Convert data into an Excel Table

4. Create PivotTables for:

- Total Sales

- Region-wise Sales

- Product Performance

- Monthly Trends

5. Add charts and KPIs

6. Use slicers for filtering

7. Format dashboard professionally

Common KPIs:

✅ Revenue

✅ Profit

✅ Growth %

✅ Top Products

✅ Sales by Region

Example Formula:

=SUM(Sales[Revenue])

📌 Final output usually includes:

• Dashboard

• PivotTables

• Summary charts

• Insights for management

92. How would you design a budget or expense tracker?

Important Sections:

Section : Purpose

Income : Salary/earnings

Expenses : Spending categories

Savings : Remaining balance

Goals : Financial targets

Features to Include:

✅ Dropdown categories

✅ Monthly summaries

✅ Conditional formatting

✅ Charts

✅ Expense alerts

Example Formula:

=SUM(B2:B20)

Remaining Balance Formula:

Balance = Income - Expenses

Excel Formula:

=B1-B2

📌 Useful for personal finance and business budgeting.

93. How would you analyze customer retention or RFM-style data?

RFM Analysis:

Metric : Meaning

Recency : Last purchase date

Frequency : Purchase count

Monetary : Total spending

Steps:

1. Clean customer transaction data

2. Calculate:

- Days since last purchase

- Total purchases

- Total revenue

3. Score customers

4. Segment into groups

Example:

Segment : Meaning

Champions : Frequent high spenders

At Risk : Inactive customers

Loyal : Regular customers

📌 Commonly used in marketing analytics.

94. How would you create an attendance or performance tracking sheet?

Features:

✅ Employee/student names

✅ Attendance status

✅ Performance metrics

✅ Monthly summaries

✅ Conditional formatting

Example Attendance Formula:

=COUNTIF(B2:B31,"Present")

Performance Status:

=IF(C2>=90,"Excellent","Needs Improvement")

Dashboard Additions:

✅ Attendance %

✅ Trend charts

✅ KPI cards

📌 Widely used in HR and education reporting.

95. How would you handle a messy Excel file shared by your team?

Cleaning Process:

1. Create backup copy

2. Remove duplicates

3. Standardize formats

4. Fix inconsistent values

5. Remove blank rows/columns

6. Validate formulas

7. Convert ranges into tables

Useful Functions:

Function : Purpose

TRIM : Remove spaces

PROPER : Standardize names

SUBSTITUTE : Replace values

IFERROR : Handle errors

Advanced Option:

Use Microsoft Power Query for large messy datasets.

📌 Data cleaning is one of the most important analyst skills.

96. How do you ensure your formulas and tables are easy to understand and maintain?

Best Practices:

✅ Use meaningful headers

✅ Use named ranges

✅ Avoid hardcoded values

✅ Add comments/notes

✅ Keep formulas simple

✅ Use consistent formatting

Example:

Instead of:

=A10.18

Use:

=A1Tax_Rate

Additional Tips:

✅ Separate raw data from reports

✅ Use tables instead of random ranges

✅ Document assumptions clearly

❤4

2.7K views16:09