Real-World Projects

Putting It All Together

Theory becomes useful when you apply it. This chapter walks through practical projects you can build and use immediately.

Each project combines multiple skills from this book.

Project 1: Personal Budget Tracker

What You'll Build

A monthly budget that tracks income, expenses, and savings automatically.

Structure

Sheet 1: Monthly Tracker

Sheet 2: Summary

• Total income (by month, by category)
• Total expenses (by month, by category)
• Net savings
• Budget vs. actual comparison

Sheet 3: Budget

• Target amounts by category

Key Formulas

Total income: =SUMIF(D:D, ">0", D:D) or use a table and =SUM(Table1[Income])

Total expenses: =SUM(E:E)

Expense by category: =SUMIF(B:B, "Food", E:E)

Budget variance: =[@Budget]-[@Actual]

Techniques Used

• Data validation (dropdown list for categories)
• SUMIF and SUMIFS for categorization
• Conditional formatting (red if over budget)
• Charts for visualization

AI Prompt to Help

Help me create a personal budget tracker in Excel.

I want to track:
- Income and expenses by date
- Categories for each transaction
- Monthly summary with budget vs. actual

Suggest the structure and key formulas I should use.

Project 2: Sales Dashboard

What You'll Build

A dashboard that summarizes sales data with key metrics and charts.

Structure

Sheet 1: Raw Data

Sheet 2: Dashboard

• KPI cards: Total Revenue, # Orders, Avg Order Size
• Sales by Region (chart)
• Sales by Product (chart)
• Monthly Trend (line chart)
• Top Salespeople table

Key Formulas

Total revenue: =SUM(Table1[Revenue])

Order count: =COUNTA(Table1[Date])

Average order size: =AVERAGE(Table1[Revenue])

Sales by region: Use a pivot table, then reference cells or GETPIVOTDATA.

Techniques Used

• Pivot tables for summarization
• Pivot charts for visualization
• Conditional formatting for KPI indicators
• Slicers for interactivity

AI Prompt to Help

I have sales data with Date, Salesperson, Region, Product, Quantity, Revenue.

Help me create a dashboard showing:
- Key metrics (total revenue, orders, average)
- Sales breakdown by region and product
- Monthly trend
- Top performers

Suggest the best approach using pivot tables and charts.

Project 3: Invoice Generator

What You'll Build

A reusable invoice template that calculates totals automatically.

Structure

Header section:

• Your company info
• Invoice number
• Date
• Client info

Line items table:

Summary:

• Subtotal
• Tax (percentage)
• Total due
• Payment terms

Key Formulas

Line total: =[@Quantity]*[@[Unit Price]]

Subtotal: =SUM(Table1[Total])

Tax: =Subtotal*TaxRate (TaxRate in a named cell)

Grand total: =Subtotal+Tax

Techniques Used

• Tables for easy data entry
• Named ranges for tax rate
• Cell protection (lock formulas, allow entry in specific cells)
• Print formatting

AI Prompt to Help

Help me create a professional invoice template in Excel.

I need:
- Company and client info sections
- Line items with quantity, price, and automatic total
- Subtotal, tax calculation, and grand total
- Professional formatting

Give me the structure and formulas.

Project 4: Project Timeline Tracker

What You'll Build

A project tracker with task status, dates, and visual timeline.

Structure

Visual timeline: Conditional formatting creates a simple Gantt chart.

Key Formulas

Duration: =[@End]-[@Start]

Days remaining: =[@End]-TODAY() (negative if overdue)

% Complete (if tracking): =[@[Actual Days]]/[@Duration]

Techniques Used

• Data validation for Status dropdown
• Conditional formatting for status colors
• Conditional formatting for timeline bars
• TODAY() for dynamic calculations

AI Prompt to Help

Help me create a project tracker with a Gantt-chart-style timeline.

I want to track:
- Tasks with owner, start date, end date
- Status (Not Started, In Progress, Complete)
- Visual timeline showing task bars

How do I create the visual timeline with conditional formatting?

Project 5: Data Analysis Template

What You'll Build

A reusable template for analyzing any dataset quickly.

Structure

Sheet 1: Raw Data Paste your data here.

Sheet 2: Analysis

• Basic stats (count, sum, average, min, max)
• Pivot table placeholder
• Charts

Sheet 3: Documentation

• Instructions for using the template
• Notes on the current dataset

Key Formulas

Count of rows (auto-expanding): =COUNTA(Data[Column1])

Summary statistics: Use AVERAGE, MIN, MAX, STDEV

Techniques Used

• Tables (auto-expand when data is added)
• Dynamic charts connected to tables
• Pivot tables for exploration
• Documentation sheet

Project 6: Expense Report

What You'll Build

A reusable expense report for submitting work expenses.

Structure

Summary:

• Total by category
• Grand total
• Approval signature line

Techniques Used

• Data validation for Category and Receipt dropdowns
• SUMIF for category totals
• Print area for clean printout
• Cell protection

Customizing These Projects

Starting Point

These are starting points. Customize based on your needs:

• Add/remove columns
• Change categories
• Adjust formatting
• Add more analysis

Scaling Up

As your skills grow:

• Add more automation
• Connect to external data
• Use Power Query for data transformation
• Add VBA for complex automation

AI Prompt: Custom Project

I need to build a spreadsheet for:

[Describe what you're tracking or analyzing]

Key requirements:
- [What data you'll input]
- [What outputs/calculations you need]
- [Any special features]

Help me:
1. Design the structure
2. Suggest key formulas
3. Recommend features that would make it more useful

What's Next

You've built real projects. For quick reference, check the formula reference.

Next chapter: Formula reference — quick lookup for common formulas.