Capstone: Build a Complete AI-Powered Spreadsheet System

You’ve learned to generate formulas, clean data, build lookups, create pivot summaries, design dashboards, and use built-in AI. Now it’s time to combine all of these skills into a real system.

In this capstone, you’ll build a Sales Tracking & Reporting System — the kind of spreadsheet that impresses managers and saves hours every week.

🔄 Quick Recall: Over the past seven lessons, you’ve built skills in formula generation (Lesson 2), data cleaning (Lesson 3), lookups (Lesson 4), pivot tables (Lesson 5), dashboards (Lesson 6), and built-in AI (Lesson 7). This capstone integrates all of them.

Your Capabilities Review

Here’s what you can now do with AI assistance:

Skill	What You Learned	AI Prompt Pattern
Formula generation	CONTEXT → TASK → CONSTRAINTS → OUTPUT	“In [platform], column A has [data]. Create a formula that [does X]”
Data cleaning	Duplicates → Formats → Blanks → Validate	“Standardize [column] to [format], flag duplicates by [criteria]”
Lookups	VLOOKUP, INDEX/MATCH, XLOOKUP	“Look up [value] in [sheet/range] and return [column]”
Pivot tables	QUERY function, traditional pivots	“Summarize [metric] grouped by [dimension], sorted by [order]”
Dashboards	KPIs, charts, conditional formatting	“Create [chart type] showing [data story]”
Built-in AI	Gemini multi-step, Copilot, COPILOT function	Direct manipulation and live AI formulas

Capstone Project: Sales Tracking System

✅ Quick Check: Before starting the build, list the four sheets you’ll create and what each one does. (Answer: Raw Data for data entry, Reference Data for lookups, Cleaned & Enriched Data for quality-checked records, and Dashboard for KPIs and charts.)

Build a 4-sheet system using AI prompts for every component:

Sheet 1: Raw Data

This is your data entry sheet. Columns: Order Date, Customer Name, Product ID, Quantity, Unit Price, Region, Sales Rep.

AI prompt to generate sample data:

“Generate 50 rows of realistic sales data with columns: Order Date (random dates in Jan-Mar 2026), Customer Name, Product ID (PROD-001 through PROD-010), Quantity (1-20), Unit Price (10-500), Region (North/South/East/West), Sales Rep (5 different names). Include some intentional data quality issues: 3 duplicate rows, 2 blank customer names, and mixed date formats.”

AI prompt to add calculated columns:

“Add a formula for column H (Total) that multiplies Quantity by Unit Price. Add a formula for column I (Quarter) that extracts the quarter from the Order Date.”

Sheet 2: Reference Data

A lookup table for product details. Columns: Product ID, Product Name, Category, Cost Price.

AI prompt for lookups:

“In the Raw Data sheet, create a formula for a new column J (Product Name) that looks up the Product ID in the Reference sheet column A and returns the Product Name from column B. Show ‘Unknown Product’ if not found.”

Sheet 3: Cleaned & Enriched Data

Apply your data cleaning workflow:

AI prompts (in order):

“Create a formula that flags duplicate rows based on Order Date + Customer Name + Product ID combination”
“Standardize all dates in column A to YYYY-MM-DD format”
“Flag rows where Customer Name is blank as ‘NEEDS REVIEW’”
“Create a validation column that checks: date is valid, quantity is positive, unit price is positive, region is one of North/South/East/West”

Sheet 4: Dashboard

Build the executive summary with three layers:

KPI row prompts:

“Create formulas for: Total Revenue, Number of Orders, Average Order Value, Top Region by Revenue, Month-over-Month Revenue Change (%)”

Chart prompts:

“Prepare a summary table for charting: monthly revenue totals for each region (months as rows, regions as columns)”

Conditional formatting prompt:

“Apply conditional formatting: green for regions above $10,000 total, red for regions below $5,000, yellow for in between”

Implementation Roadmap

If you want to apply this to your real work, here’s a step-by-step plan:

Identify your data source — What raw data do you work with regularly?
List your pain points — Which manual tasks take the most time?
Build the data foundation — Set up raw data and reference sheets
Automate cleaning — Create formulas that flag and fix common issues
Create summaries — Add QUERY formulas or pivot tables for analysis
Build the dashboard — KPIs, charts, and conditional formatting
Document everything — Note which AI prompts you used and what each formula does

Common Pitfalls to Avoid

Pitfall	Solution
Trusting AI formulas blindly	Always verify against known data
Over-complicating formulas	Ask AI for the simplest approach first
Hardcoding values	Use cell references so formulas stay flexible
Skipping documentation	Add a “Notes” sheet explaining each formula’s purpose
Building everything at once	Start small, test each component, then connect them

Prompt Library: Your Quick Reference

Keep these prompts handy for everyday spreadsheet work:

Formula generation: “In [platform], column [X] has [data type]. Create a formula that [calculation] where [condition].”

Data cleaning: “Standardize column [X] from [current formats] to [target format]. Flag/remove [issues].”

Lookups: “Look up [value] in [sheet] column [X], return column [Y]. Show [fallback] if not found.”

Summaries: “Summarize [metric] grouped by [dimension]. Filter where [condition]. Sort by [order].”

Charts: “Create a [chart type] showing [data story] from data in [range].”

Debugging: “This formula returns [error]: [formula]. My data has [description]. What’s wrong?”

Key Takeaways

Build spreadsheet systems in layers: raw data → reference data → cleaned data → dashboard
Use AI for every step: data generation, formula creation, cleaning, lookups, summaries, and visualization
Always verify AI output against known data before deploying
Document your AI prompts alongside your formulas for future maintenance
Start small and connect components incrementally — don’t build everything at once

Congratulations — you now have the skills to handle any spreadsheet task with AI assistance. What used to take hours of formula research and manual work now takes minutes of clear prompting.