📄 Save or print this lesson:

← Module 07: Data Cleaning 🏠 Course Home Course Complete 🏆

Module 08 of 08 | Excel & Google Sheets for Data

🏆 Capstone Project: Monthly Sales Report

This is your final project. You'll take a realistic 50-row dataset — messy, like real data always is — and walk through the complete analysis workflow: import, inspect, clean, calculate, analyze, visualize, and write your findings.

⏰ ~60–90 minutes 🎓 All Levels 📋 Requires: All Modules 01–07

📌 Before You Start

What this project covers

This capstone simulates a real task you might get at work: "Here's the monthly sales data. Analyze it and tell me what happened." You'll use every skill from this course:

Formatting and navigation (Module 01)
Formulas: SUM, AVERAGE, SUMIF, IF (Modules 02–03)
Sort, Filter, Freeze (Module 04)
Pivot tables (Module 05)
Charts (Module 06)
Data cleaning: TRIM, PROPER, IFERROR, blanks (Module 07)

Create a new Google Sheets file called "Monthly Sales Report — Capstone". You'll work on multiple sheets within it.

📋 The Dataset — Monthly Sales Report (50 rows)

Enter this data into Sheet1 (rename it "Raw Data")

The intentionally messy entries are highlighted. Spot them as you type — you'll fix them in Step 2.

A — Date	B — Salesperson	C — Region	D — Product	E — Units	F — Price	G — Revenue	H — Returns
2024-01-05	Alice Chen	North	Laptop	2	600	1200	0
2024-01-07	alice chen	North	Webcam	5	79	395	0
2024-01-09	Bob Patel	South	Keyboard	10	50	500	50
2024-01-12	BOB PATEL	South	Mouse Pad	20	9	180	0
2024-01-15	Carol Rivera	East	Monitor	3	450	1350	450
2024-01-18	Carol Rivera	East	USB Hub	8	16	128	0
2024-01-20	David Kim	West	Laptop	1	600	600	0
2024-01-22	David Kim	West	Headphones	4	120	480	0
2024-01-25	Alice Chen	North	Keyboard	7	50	350	0
2024-01-28	Bob Patel	South	Monitor	2	450	900
2024-02-02	Carol Rivera	East	Laptop	4	600	2400	600
2024-02-05	Alice Chen	North	Webcam	10	79	790	0
2024-02-08	Bob Patel	South	Keyboard	15	50	750	0
2024-02-10	David Kim	West	Headphones	6	120	720	120
2024-02-13	Carol Rivera	East	Monitor	2	450	900	0
2024-02-16	Alice Chen	North	USB Hub	12	16	192	0
2024-02-18	Bob Patel	South	Mouse Pad	30	9	270	0
2024-02-20	David Kim	West	Laptop	3	600	1800	600
2024-02-23	Alice Chen	North	Keyboard	8	50	400	0
2024-02-25	Carol Rivera	East	Webcam	5	79	395	0
2024-03-01	Bob Patel	South	Headphones	7	120	840	0
2024-03-03	David Kim	West	USB Hub	15	16	240	0
2024-03-06	Alice Chen	North	Monitor	2	450	900	450
2024-03-09	Carol Rivera	East	Keyboard	20	50	1000	0
2024-03-12	carol rivera	East	Laptop	2	600	1200	0
2024-03-15	Bob Patel	South	Webcam	8	79	632	0
2024-03-18	David Kim	West	Mouse Pad	25	9	225	0
2024-03-20	Alice Chen	North	Headphones	4	120	480	0
2024-03-23	Bob Patel	South	USB Hub	18	16	288
2024-03-26	Carol Rivera	East	Monitor	1	450	450	0
2024-04-02	David Kim	West	Laptop	2	600	1200	0
2024-04-04	Alice Chen	North	Keyboard	12	50	600	0
2024-04-07	Bob Patel	South	Headphones	5	120	600	120
2024-04-10	Carol Rivera	East	Webcam	8	79	632	0
2024-04-13	David Kim	West	USB Hub	20	16	320	0
2024-04-16	ALICE CHEN	North	Monitor	3	450	1350	450
2024-04-18	Bob Patel	South	Laptop	4	600	2400	0
2024-04-21	Carol Rivera	East	Mouse Pad	35	9	315	0
2024-04-24	David Kim	West	Keyboard	10	50	500	0
2024-04-27	Alice Chen	North	Webcam	6	79	474	0
2024-05-01	Bob Patel	South	Monitor	2	450	900	0
2024-05-04	Carol Rivera	East	Headphones	6	120	720	0
2024-05-07	David Kim	West	Laptop	1	600	600	600
2024-05-10	Alice Chen	North	USB Hub	15	16	240	0
2024-05-13	Bob Patel	south	Keyboard	12	50	600	0
2024-05-16	Carol Rivera	East	Mouse Pad	40	9	360	0
2024-05-19	David Kim	West	Webcam	7	79	553	0
2024-05-22	Alice Chen	North	Monitor	1	450	450	0
2024-05-25	Bob Patel	South	Headphones	8	120	960	0
2024-05-28	Carol Rivera	East	Laptop	3	600	1800	600

Issues embedded in the data (highlighted red): inconsistent capitalization in Salesperson and Region columns, leading spaces, and two blank Returns cells.

🔧 Phase 1

Step 1: Inspect the Data Start here

Before touching anything, understand what you have.

Freeze row 1 (View → Freeze → 1 row). Scroll down to row 50 to see if your data is all there.
Check column widths — auto-fit all columns so you can read the data.
Use =COUNTA(B2:B51) to count how many Salesperson entries you have. Should be 50.
Use =COUNTA(H2:H51) to count Returns entries. If it's less than 50, you have blank cells.
Scroll through the Salesperson column. Notice the capitalization inconsistencies (alice chen, BOB PATEL, etc.).
Scroll through Region — notice "south" and " West" (with a leading space).
Write your observations in a comment or a separate notes area on the sheet.

🔧 Phase 2

Step 2: Clean the Data

Duplicate the "Raw Data" sheet. Rename the copy "Cleaned Data". Work on the copy.

Fix Salesperson (column B): Add a temporary column I. In I2, write =PROPER(TRIM(B2)). Copy down to I51. Then copy I2:I51, and Paste Special → Values into B2:B51. Delete column I.
Fix Region (column C): Repeat the same process with =PROPER(TRIM(C2)). This fixes "south" → "South" and " West" → "West".
Fix blank Returns (column H): In a helper column, use =IF(ISBLANK(H2), 0, H2). Paste values back. Delete helper column.
Verify with COUNTIF: Use =COUNTIF(B:B, "Alice Chen") — you should now get a consistent count for each salesperson name.
Check COUNTA(H2:H51) — should now be 50 (no blanks).

🔧 Phase 3

Step 3: Add Calculated Columns

On the "Cleaned Data" sheet:

Add column I: Net Revenue. In I1, type "Net Revenue". In I2, type =G2-H2. Copy down to I51.
Add column J: Month. In J1, type "Month". In J2, type =TEXT(A2, "MMMM"). Copy down. This extracts the month name from the date.
Format column I as currency (Format → Number → Currency). Format column J as plain text.
Your data now has 10 columns: Date, Salesperson, Region, Product, Units, Price, Revenue, Returns, Net Revenue, Month.

🔧 Phase 4

Step 4: Analyze with Formulas

Add a new sheet called "Analysis". Create a summary table with these calculations:

Total Revenue: =SUM('Cleaned Data'!G2:G51) Total Net Revenue: =SUM('Cleaned Data'!I2:I51) Total Returns: =SUM('Cleaned Data'!H2:H51) Avg Revenue/Sale: =AVERAGE('Cleaned Data'!G2:G51) Total Transactions: =COUNTA('Cleaned Data'!A2:A51) -- By Region -- North Revenue: =SUMIF('Cleaned Data'!C:C, "North", 'Cleaned Data'!I:I) South Revenue: =SUMIF('Cleaned Data'!C:C, "South", 'Cleaned Data'!I:I) East Revenue: =SUMIF('Cleaned Data'!C:C, "East", 'Cleaned Data'!I:I) West Revenue: =SUMIF('Cleaned Data'!C:C, "West", 'Cleaned Data'!I:I) -- By Salesperson -- Alice Chen Revenue: =SUMIF('Cleaned Data'!B:B, "Alice Chen", 'Cleaned Data'!I:I) Bob Patel Revenue: =SUMIF('Cleaned Data'!B:B, "Bob Patel", 'Cleaned Data'!I:I) Carol Rivera Revenue:=SUMIF('Cleaned Data'!B:B, "Carol Rivera", 'Cleaned Data'!I:I) David Kim Revenue: =SUMIF('Cleaned Data'!B:B, "David Kim", 'Cleaned Data'!I:I)

🔧 Phase 5

Step 5: Build a Pivot Table

Click inside the "Cleaned Data" sheet.
Insert → Pivot table → New sheet. Rename the sheet "Pivot — By Person & Month".
Add Salesperson to Rows.
Add Month to Columns.
Add Net Revenue to Values (Summarize by: SUM).
You now see each salesperson's net revenue broken down by month. Who had the best January? Who grew the most from January to May?
Add a slicer for Region to filter the pivot by region.

🔧 Phase 6

Step 6: Create Two Charts

Chart 1: Monthly Net Revenue Trend (Line Chart)

On the Analysis sheet, create a small summary table: Month (Jan–May) and total Net Revenue per month (use SUMIF with the Month column).
Select that table → Insert → Chart → Line chart.
Title: "Monthly Net Revenue Trend — Jan to May 2024".
Horizontal axis label: "Month". Vertical axis label: "Net Revenue ($)".

Chart 2: Net Revenue by Region (Bar Chart)

Use your Analysis sheet's "By Region" summary.
Select the Region + Net Revenue cells → Insert → Chart → Bar chart (or Column chart).
Title: "Net Revenue by Region — Jan to May 2024".
Add axis labels. Color the bars green (#43A047).

🖐 Phase 7 — Your Turn

Step 7: Write Your 3 Findings

Add a new sheet called "Findings". In a text area or a series of cells, write 3 specific, data-backed findings from your analysis. Each finding should:

State a specific observation ("The South region generated X% of total net revenue")
Reference the data ("Based on SUMIF analysis across 50 transactions")
Include a recommendation if applicable

📝 Example findings structure:

Finding 1: [Salesperson X] had the highest total net revenue of $[amount], which is [X]% above the average salesperson. Consider assigning them to the highest-value accounts.

Finding 2: The [Month] had the lowest net revenue at $[amount], which may be worth investigating.

Finding 3: Returns accounted for $[amount] of total revenue ($[gross]), representing [X]% return rate. The [Product] had the most returns.

Hint for return rate: =SUM(Returns)/SUM(Revenue) gives you the return rate as a decimal. Format it as percentage.

Hint for top salesperson: Use your Analysis sheet SUMIF values to compare. Or sort the pivot table by Total column.

💡 Sample Solution Notes (click to expand)

These are approximate expected values based on the dataset above. Your numbers may vary slightly if you entered data differently.

Cleaning

After PROPER(TRIM()) on Salesperson: all 4 names should be consistent across 50 rows.
Region "south" → "South"; " West" → "West".
Two blank Returns cells → filled with 0.
COUNTA(B2:B51) = 50, COUNTA(H2:H51) = 50 after cleaning.

Summary Formulas (approximate)

Total Gross Revenue: ~$31,969 Total Returns: ~$3,540 Total Net Revenue: ~$28,429 Average Revenue per Sale: ~$639 Transactions: 50

By Region (Net Revenue, approximate)

North: ~$7,681 South: ~$7,308 East: ~$7,755 West: ~$5,685

By Salesperson (Net Revenue, approximate)

Alice Chen: ~$7,471 Bob Patel: ~$7,538 Carol Rivera: ~$8,315 David Kim: ~$5,105

Sample Findings

Finding 1: Carol Rivera generated the highest net revenue (~$8,315), approximately 16% above the average salesperson revenue. Alice Chen and Bob Patel performed similarly at ~$7,500 each.

Finding 2: David Kim generated the lowest net revenue (~$5,105), which is about 37% below Carol. A review of his sales territory (West region, lowest revenue at ~$5,685) may be warranted.

Finding 3: Returns totaled ~$3,540 on gross revenue of ~$31,969 — an 11% return rate. Laptops and Monitors accounted for the majority of returns due to their high unit price. A product quality or fit-to-need review for those categories is recommended.

🧠 Brain Break

You Just Did What Analysts Do

What you completed in this capstone — inspect, clean, calculate, pivot, chart, and report — is exactly what a business data analyst does daily. The tools may become more powerful (SQL, Python, Tableau, Power BI), but the workflow stays the same.

Spreadsheet skills remain foundational because most data starts in spreadsheets, gets shared as spreadsheets, and gets communicated through spreadsheets. You now have those skills. From here, the natural next step is SQL for larger datasets and Python for automation — both courses are available on this site.

✅ Wrap Up

Course Complete — What You've Learned

Module 01: Navigating Google Sheets, cell addressing, formatting, keyboard shortcuts.
Module 02: SUM, AVERAGE, COUNT, MIN/MAX, relative vs. absolute references.
Module 03: IF, IFS, COUNTIF, SUMIF, VLOOKUP, XLOOKUP.
Module 04: Sort, Filter, Freeze rows, Data Validation, Named Ranges.
Module 05: Pivot tables — creating, configuring, grouping dates, slicers.
Module 06: Bar, line, pie, scatter charts — when and how to use each.
Module 07: TRIM, PROPER, CLEAN, IFERROR, ISBLANK, Remove Duplicates, Find & Replace.
Module 08: Full end-to-end analysis: inspect → clean → calculate → pivot → visualize → report.

🎓 Get Your Certificate

You completed the Excel & Google Sheets for Data course. Generate a free printable certificate to recognize your achievement.

Get Your Certificate →

← Back to Course Home