Real-World Projects
Build four complete projects from scratch — a budget tracker, sales dashboard, project timeline, and inventory system — and put every skill from this course into practice.
The difference between knowing Excel and being good at Excel is the number of real things you have built
I have interviewed over 100 candidates who listed "Advanced Excel" on their resume. My screening question is simple: "Tell me about something you built in Excel that solved a real problem." About 70% go quiet. They know formulas. They have never built anything.
The other 30% light up. "I built a budget tracker that saved our department $40K by spotting duplicate vendor charges." "I created an inventory system that replaced a $15,000-per-year software subscription." "I built a sales dashboard that our VP still uses every Monday."
Those are the people who get hired. This module is about joining that 30%.
You are going to build four complete projects that use every skill from this course — formulas, data analysis, pivot tables, charts, and automation. Each project solves a real business problem. By the end, you will have a portfolio of work you can show in interviews or use at your job tomorrow.
Project 1: Personal Budget Tracker
The problem: You want to track income and expenses, see where your money goes, and identify spending patterns — without paying for budgeting software.
Structure
| Sheet | Purpose | Key features |
|---|---|---|
| Transactions | Raw data entry | Date, Category, Description, Amount, Type (Income/Expense) |
| Summary | Monthly breakdown | Pivot table or SUMIFS by month and category |
| Dashboard | Visual overview | Charts, KPIs, trends |
| Categories | Reference data | Dropdown list for data validation |
Build steps
Step 1: Create the Categories sheet with your spending categories (Housing, Food, Transport, Entertainment, Utilities, Savings, Income). This becomes the source for data validation dropdowns.
Step 2: Build the Transactions sheet as a structured table (Ctrl+T). Columns: Date, Category (dropdown from Categories), Description, Amount, Type. Format Date as date, Amount as currency.
Step 3: On the Summary sheet, use =SUMIFS(Transactions[Amount], Transactions[Category], "Food", Transactions[Type], "Expense", MONTH(Transactions[Date]), 1) to calculate January food spending. Repeat for each category and month — or create a pivot table.
Step 4: Add KPI formulas: =SUMIFS(Amount, Type, "Income") for total income, =SUMIFS(Amount, Type, "Expense") for total expenses, income minus expenses for net savings.
Step 5: Build the Dashboard: a line chart for monthly spending trend, a bar chart for spending by category, and KPI cells showing total income, total expenses, savings rate (savings/income as percentage).
Step 6: Add conditional formatting: red for negative net savings months, green for positive. Add sparklines next to each category showing its monthly trend.
Key formulas used:
=SUMIFS()for conditional totals- Data validation lists for category dropdowns
=TEXT(A2, "MMMM")to extract month names- Conditional formatting for spending alerts
Budget Tracker Design
25 XPProject 2: Sales Dashboard
The problem: Your sales team generates hundreds of transactions per week. Leadership wants a single-page view showing performance by rep, region, and product — updated with one click.
Structure
| Sheet | Purpose | Key features |
|---|---|---|
| Raw Data | Transaction-level sales data | Structured table, auto-expanding |
| Pivot Analysis | Multiple pivot tables | By rep, by region, by product, by month |
| Dashboard | Executive summary | KPIs, charts, slicers |
Build steps
Step 1: Format Raw Data as a structured table named "SalesData." Columns: Date, Rep Name, Region, Product Category, Deal Size, Status (Won/Lost/Pending).
Step 2: Create Pivot Table 1: Rep Name in Rows, SUM of Deal Size in Values (filter to Status = "Won"). This shows revenue by rep.
Step 3: Create Pivot Table 2: Region in Rows, Product Category in Columns, COUNT of Status in Values (filter to "Won"). This shows win count by region and product.
Step 4: Create Pivot Table 3: Date grouped by Month in Rows, SUM of Deal Size in Values. This shows the monthly revenue trend.
Step 5: Build the Dashboard: KPIs at top (Total Revenue, Win Rate, Average Deal Size, Number of Deals). Line chart for monthly trend. Bar chart for top reps. Pie chart (5 regions max) for regional distribution.
Step 6: Add slicers for Region and Date (connected to all three pivot tables). Clicking "West" filters every chart simultaneously.
Key features used:
- Pivot tables with multiple calculation types (SUM, COUNT, AVERAGE)
- Slicers connected to multiple pivot tables
- Calculated field: Win Rate = Won / (Won + Lost)
- Refresh on open (PivotTable Options → Refresh when opening)
✗ Without AI
- ✗500-row spreadsheet of raw transactions
- ✗Manager manually calculates totals each week
- ✗No visibility into trends or rep performance
- ✗30 minutes to prepare weekly report
✓ With AI
- ✓One-page visual summary updated with one click
- ✓KPIs, trends, and breakdowns auto-calculated
- ✓Slicers let anyone filter by region or date
- ✓Refresh takes 2 seconds
There Are No Dumb Questions
"Should a sales dashboard be in Excel or a BI tool like Tableau?"
If the data lives in Excel and fewer than 20 people view the dashboard, Excel is fine. If the data comes from a database, updates in real-time, or needs to be shared with 100+ people via a web link, use Power BI or Tableau. Many companies start with Excel dashboards and migrate to BI tools as they scale.
"How do I keep the dashboard updated when new sales data comes in?"
If you use structured tables for your raw data, new rows are automatically included in the table range. Then refresh the pivot tables (right-click → Refresh All, or set auto-refresh on open). The charts linked to pivot tables update automatically when the pivots refresh.
Project 3: Project Timeline (Gantt Chart)
The problem: Your team needs a visual project timeline showing tasks, durations, dependencies, and status — without buying project management software.
Structure
| Column | Content | Format |
|---|---|---|
| A | Task Name | Text |
| B | Owner | Text (data validation dropdown) |
| C | Start Date | Date |
| D | End Date | Date |
| E | Duration (days) | =D2-C2 (auto-calculated) |
| F | % Complete | Percentage (0-100%) |
| G | Status | Data validation: Not Started, In Progress, Complete, Blocked |
| H onward | Gantt bars | Conditional formatting |
The Gantt chart trick
The visual timeline uses conditional formatting — no chart needed:
- Create date headers across row 1 starting from column H (one column per day or week)
- For each cell in the Gantt area, the conditional formatting formula is:
=AND(H$1>=$C2, H$1<=$D2)— this fills the cell if the date header falls between the task's start and end dates - Add a second conditional formatting rule for completion:
=AND(H$1>=$C2, H$1<=$C2+($E2*$F2))with a darker shade — this fills the completed portion
Key formulas used:
=NETWORKDAYS(C2, D2)for business days instead of calendar days=IF(F2=1, "Complete", IF(D2<today(), "Overdue", IF(C2<=TODAY(), "In Progress", "Not Started")))for auto-status- Conditional formatting for Gantt bars
=SUMPRODUCT((G2:G20="Complete")*1) / COUNTA(A2:A20)for overall project completion percentage
Build a Gantt Chart
50 XPProject 4: Inventory Management System
The problem: A small business tracks inventory in spreadsheets — incoming stock, outgoing orders, current levels, and reorder alerts. They need a system that prevents stockouts without overordering.
Structure
| Sheet | Purpose | Key features |
|---|---|---|
| Products | Master product list | SKU, Name, Category, Unit Cost, Reorder Point, Reorder Quantity |
| Transactions | Stock in/out log | Date, SKU, Type (In/Out), Quantity, Reference (PO# or Order#) |
| Current Stock | Live inventory levels | SUMIFS to calculate current quantity per product |
| Dashboard | Alerts and overview | Low stock warnings, category breakdown, transaction trends |
Key formulas
Current stock level: =SUMIFS(Transactions[Quantity], Transactions[SKU], A2, Transactions[Type], "In") - SUMIFS(Transactions[Quantity], Transactions[SKU], A2, Transactions[Type], "Out")
Reorder alert: =IF(CurrentStock < ReorderPoint, "REORDER NOW", IF(CurrentStock < ReorderPoint * 1.5, "Low Stock", "OK"))
Days until stockout (based on average daily usage):
=IFERROR(CurrentStock / (SUMIFS(Transactions[Quantity], Transactions[SKU], A2, Transactions[Type], "Out") / DATEDIF(MIN(Transactions[Date]), MAX(Transactions[Date]), "d")), "No usage")
There Are No Dumb Questions
"Can multiple people update the same inventory file simultaneously?"
With Excel on SharePoint/OneDrive, yes — co-authoring supports multiple simultaneous editors. With desktop Excel and a shared network drive, no — file locking prevents simultaneous edits and causes "file in use" errors. For multi-user inventory tracking, consider Google Sheets (built-in collaboration) or dedicated inventory software.
"How do I prevent someone from entering a negative quantity?"
Data validation. Select the Quantity column → Data → Data Validation → Whole number → Greater than 0. Add an error message: "Quantity must be a positive number." For the "Out" type, add a custom validation to check that the outgoing quantity does not exceed current stock.
Putting it all together — skills map
| Skill from this course | Budget Tracker | Sales Dashboard | Project Timeline | Inventory System |
|---|---|---|---|---|
| Cell references & navigation | x | x | x | x |
| SUM, AVERAGE, COUNTIF, SUMIFS | x | x | x | x |
| IF / IFS | x | x | x | |
| VLOOKUP / INDEX-MATCH | x | x | ||
| Data validation | x | x | x | x |
| Conditional formatting | x | x | x | x |
| Pivot tables & slicers | x | x | ||
| Charts & sparklines | x | x | x | x |
| Structured tables | x | x | x | x |
| Dynamic arrays (FILTER, SORT) | x | x | ||
| Error handling (IFERROR) | x | x | x | |
| Date functions | x | x | x | x |
Inventory System Design
25 XPKey takeaways
- Building real projects is what separates "I know Excel" from "I am good at Excel" — interviewers and managers notice the difference
- Budget tracker teaches SUMIFS, data validation, conditional formatting, and dashboard design for personal finance
- Sales dashboard combines pivot tables, slicers, KPIs, and charts into an executive-ready one-page view
- Project timeline (Gantt chart) uses conditional formatting creatively, date functions, and auto-status formulas to replace project management software
- Inventory system integrates SUMIFS, lookups, error handling, and alert logic into a complete business tool
- Start with one project, use it for a week, then build the next — repetition across projects is what makes the skills permanent
- Know when to graduate from Excel to dedicated tools — Excel is the right starting point, not always the right ending point
Knowledge Check
1.In the budget tracker project, why is it better to use a structured table (Ctrl+T) for the Transactions sheet instead of a plain range?
2.In the sales dashboard project, you connect a Region slicer to three pivot tables. What happens when someone clicks 'West' on the slicer?
3.In the Gantt chart project, the conditional formatting formula =AND(H$1>=$C2, H$1<=$D2) is applied to the timeline area. What does this formula do?
4.An Excel inventory system works well for a small business. At what point should they consider migrating to dedicated inventory software?