Session 15 · Phase 2: Excel

Excel Assignment — End-to-End Report

The Excel capstone: take a messy sales dataset and turn it into a clean, summarized, visualized report a stakeholder can act on.

⏱ ~2.5 hrs 🧠 Assignment 🎯 Highest priority

The Scenario

You're a data analyst. A business manager emails you a messy spreadsheet of last quarter's sales and asks: "Clean this up and give me a report showing sales by region and product, and which products are most profitable."

💡

The analyst workflow (use this for every Excel task): Understand → Explore → Standardize → Clean → Deliver. Say it in interviews — it shows you have a repeatable process, not just scattered skills.

The Dataset (with deliberate messiness)

Here's the raw data you received — note the problems: duplicate rows, extra spaces, and an inconsistent city value.

Region	Full Name	Product	Sales	Cost
North	Aarav Sharma	Pen	100	60
South	Priya Verma	Notebook	150	90
North	Aarav Sharma	Pen	100	60
South	Rahul Mehta	Eraser	80	40
East	Sneha Iyer	Marker	50	30
north	Priya Verma	Notebook	200	120
West	Vikram Singh	Pen	120	70

Problems to fix: a duplicate row, "north" vs "North", and leading/trailing spaces in a name.

The Assignment — 10 Steps to a Finished Report

Step 1. Clean the data: remove the duplicate row. Data cleaning

Select the data → Data → Remove Duplicates → tick all columns (or the key columns) → OK. The duplicate "Aarav Sharma / Pen" row is deleted.

Step 2. Standardize "north" → "North" using Find & Replace. Data cleaning

Ctrl+H → Find "north" → Replace "North" → Replace All. (Also standardize any other case inconsistencies.)

Step 3. Remove extra spaces in names using TRIM. Data cleaning

Add a column: =TRIM(B2) → copy down → paste as values. " Vikram Singh " becomes "Vikram Singh".

Step 4. Split "Full Name" into First and Last name (Text to Columns). Data cleaning

Select "Full Name" → Data → Text to Columns → Delimited → Space → Finish.

Step 5. Add a "Profit" column = Sales − Cost. Formulas

In a new column: =D2-E2 → copy down.

Step 6. Flag each row as "High" (Sales ≥ 150) or "Low" using IF. Formulas

=IF(D2 >= 150, "High", "Low")

Step 7. Total sales by region using SUMIF (or a Pivot Table). Analysis

=SUMIF(A2:A7, "North", D2:D7)   -- North total sales

Step 8. Build a Pivot Table: sales by region (Rows) × product (Columns). Pivot

Select data → Insert → PivotTable → Region to Rows, Product to Columns, Sales to Values (SUM).

Step 9. Add a Calculated Field "Margin %" = Profit / Sales. Pivot

PivotTable Analyze → Fields, Items & Sets → Calculated Field → Name "Margin %", Formula =Profit/Sales → format as percent.

Step 10. Visualize and deliver: a chart + conditional formatting + a one-line recommendation. Deliver

Insert a bar chart of sales by region, apply conditional formatting to highlight top products, and write a one-line recommendation — e.g. "Notebooks drive the most sales; Pens have the highest margin, so focus promotions there."

📋 Stable content — Reviewed: August 2026

Interview Questions — End-to-End Excel Cases

These scenario questions test whether you have a repeatable process. Self-test before revealing.

IQ1. Walk me through how you'd approach an end-to-end Excel analysis task.

Model answer: "I use a five-step workflow — Understand the business question, Explore the data, Standardize formats, Clean it, then Deliver a report. That keeps me from jumping to formulas before I know what the stakeholder actually needs."

IQ2. What's your data-cleaning workflow before analysis?

Model answer: "First remove duplicates, then standardize inconsistent values with Find & Replace, TRIM extra spaces, split combined columns with Text to Columns, and handle missing values based on why they're missing. Only then do I analyze."

IQ3. How do you validate that your report numbers are correct?

Model answer: "I cross-check totals against the raw data with a simple SUM or COUNT, verify my pivot table is using the correct range and is refreshed, and make sure filters/duplicates aren't skewing the result."

IQ4. How would you build a monthly sales report in Excel?

Model answer: "Clean the data, build a pivot table with month in rows and sales in values, add a calculated field for profit or margin, then a chart and a few conditional-formatting highlights, and write a one-line summary of the insight."

IQ5. A pivot table total doesn't match the raw data. What do you check?

Model answer: "First, is the pivot table refreshed and does it cover the full data range? Then I check for duplicates, missing rows, and whether filters are excluding anything. I'd validate with a direct SUM."

IQ6. How do you decide between a formula and a pivot table?

Model answer: "For a quick, one-off calculation a formula like SUMIF is fine. For summarizing by many categories, changing views dynamically, or building a dashboard, a pivot table is faster and less error-prone."

IQ7. How do you make a report "stakeholder-ready"?

Model answer: "I make the key number obvious, use clean charts, highlight exceptions with conditional formatting, and write a plain-English takeaway. The audience shouldn't have to hunt for the insight."

IQ8. Why is data cleaning done before any analysis?

Model answer: "Because dirty data produces wrong conclusions — duplicates inflate totals, inconsistent values break grouping, and stray spaces split categories. Garbage in, garbage out."

💡

The interview-winning phrase: "Understand → Explore → Standardize → Clean → Deliver." It shows you think in a process, which is exactly what interviewers want from an analyst.

Key Takeaways

Always clean before you analyze — garbage in, garbage out.

Use the workflow: Understand → Explore → Standardize → Clean → Deliver.

Validate every total against source data before presenting.

Pivot tables summarize dynamically; formulas are for one-off calcs.

You've completed the full Excel stack — ready for any Excel round.

The Scenario

The Dataset (with deliberate messiness)

The Assignment — 10 Steps to a Finished Report

Interview Questions — End-to-End Excel Cases

Key Takeaways

Objective Questions — Test Your Understanding