Creating a Budget Tracker with Automatic Categories in Excel

Tracking spending is tedious when you manually categorize every transaction. What if Excel could recognize "Starbucks" as "Food & Dining" and "Shell Gas" as "Transportation" automatically? Let's build exactly that.

The Problem

Budget tracking fails for most people because:

• Manually categorizing every expense is exhausting
• Categories are inconsistent (is "Coffee" food or entertainment?)
• Monthly summaries require formula updates
• It's hard to see where money actually goes

The Solution

We'll create a budget tracker with:

• Automatic categorization based on vendor name
• Real-time spending summaries by category
• Visual progress bars showing budget vs. actual
• A category lookup table you can customize

What You'll Need

• Excel 2016 or later
• A list of your typical expenses
• About 45 minutes to build

Step 1: Create the Category Lookup Table

This table tells Excel how to categorize vendors.

On a sheet named "Categories":

Format as Table:

1. Select your data
2. Press Ctrl+T
3. Name it "CategoryLookup" (Table Design tab)

Tips:

• Use partial keywords: "Shell" matches "Shell Gas Station"
• Add new vendors as you encounter them
• Categories can repeat with the same budget

Step 2: Create the Transactions Sheet

On a sheet named "Transactions":

Format Columns:

• Date: Date format
• Description: Text
• Amount: Currency
• Category: This will be auto-populated!

Convert to Table:

1. Select your headers and one empty row
2. Press Ctrl+T
3. Name it "Transactions"

Step 3: Build the Auto-Categorization Formula

In the Category column, enter this formula:

Formula:

=IFERROR(
    INDEX(CategoryLookup[Category],
        MATCH(TRUE,
            ISNUMBER(SEARCH(CategoryLookup[Keyword], [@Description])),
            0
        )
    ),
    "Uncategorized"
)

How it works:

• SEARCH(CategoryLookup[Keyword], [@Description]) - Looks for each keyword in the description
• ISNUMBER(...) - Returns TRUE if keyword is found
• MATCH(TRUE, ..., 0) - Finds the first TRUE (first matching keyword)
• INDEX(CategoryLookup[Category], ...) - Returns the corresponding category
• IFERROR(..., "Uncategorized") - Default for no match

In Excel 365/2021, use this simpler version:

=LET(
    desc, [@Description],
    matches, FILTER(CategoryLookup[Category], 
        ISNUMBER(SEARCH(CategoryLookup[Keyword], desc)), ""),
    IF(matches="", "Uncategorized", INDEX(matches, 1))
)

Step 4: Create the Summary Dashboard

On a sheet named "Dashboard":

Header Section:

• Cell A1: "Budget Tracker"
• Cell A2: Current month formula: =TEXT(TODAY(), "MMMM YYYY")

Summary Table: Create this structure starting at A5:

Step 5: Add Summary Formulas

Budget Column (B6 for Food & Dining):

=IFERROR(
    INDEX(CategoryLookup[Monthly Budget], 
        MATCH(A6, CategoryLookup[Category], 0)
    ),
    0
)

Spent Column (C6):

=SUMIFS(
    Transactions[Amount],
    Transactions[Category], A6,
    Transactions[Date], ">="&DATE(YEAR(TODAY()), MONTH(TODAY()), 1),
    Transactions[Date], "<="&EOMONTH(TODAY(), 0)
)

This sums transactions in the current month for this category.

Remaining Column (D6):

% Used Column (E6):

Format as percentage.

Total Row:

• Budget: =SUM(B6:B12)
• Spent: =SUM(C6:C12)
• Remaining: =SUM(D6:D12)
• % Used: =IFERROR(C13/B13, 0)

Step 6: Add Visual Progress Bars

Use conditional formatting to create in-cell progress bars:

1. Select the % Used column (E6:E12)
2. Home > Conditional Formatting > Data Bars
3. Choose a style

Custom Data Bars (Recommended):

1. Conditional Formatting > New Rule
2. Select "Data Bar"
3. Set Minimum: Number = 0
4. Set Maximum: Number = 1
5. Choose bar color
6. Check "Show Bar Only" if you want just the bar

Color by Status: Add another rule:

• Green: < 80% of budget
• Yellow: 80-100% of budget
• Red: > 100% of budget

1. Select E6:E12
2. Conditional Formatting > New Rule > Use formula:
3. =E6>1 → Red fill
4. Add another rule: =AND(E6>=0.8, E6<=1) → Yellow fill
5. Default green for under 80%

The Complete Solution

Full Formula Reference

Common Variations

Variation 1: Multi-Keyword Categories

For descriptions that might match multiple keywords, use priority:

=IFERROR(
    SWITCH(TRUE,
        ISNUMBER(SEARCH("Uber Eats", [@Description])), "Food & Dining",
        ISNUMBER(SEARCH("Uber", [@Description])), "Transportation",
        "Uncategorized"
    ),
    "Uncategorized"
)

Variation 2: Historical View

Add a month selector to view past months:

=SUMIFS(
    Transactions[Amount],
    Transactions[Category], A6,
    Transactions[Date], ">="&DATE(SelectedYear, SelectedMonth, 1),
    Transactions[Date], "<="&EOMONTH(DATE(SelectedYear, SelectedMonth, 1), 0)
)

Variation 3: Year-to-Date Summary

=SUMIFS(
    Transactions[Amount],
    Transactions[Category], A6,
    Transactions[Date], ">="&DATE(YEAR(TODAY()), 1, 1),
    Transactions[Date], "<="&TODAY()
)

Variation 4: Spending Trend Chart

Create a PivotTable from Transactions:

• Rows: Category
• Columns: Date (grouped by Month)
• Values: Sum of Amount

Insert a line chart to visualize spending trends.

Pro Tips

• Bulk import: Copy bank statement data directly into Transactions table
• Sort by Uncategorized: Find expenses that need new keywords
• Regular updates: Add new keywords when you see "Uncategorized"
• Use Data Validation: Create dropdowns for manual category override
• Backup your keywords: The CategoryLookup table is your customization—protect it!

Adding a Manual Override

Sometimes auto-categorization is wrong. Add an override column:

In Transactions, add "Override" column. Modify Category formula:

=IF(
    [@Override]<>"",
    [@Override],
    IFERROR(
        INDEX(CategoryLookup[Category],
            MATCH(TRUE, ISNUMBER(SEARCH(CategoryLookup[Keyword], [@Description])), 0)
        ),
        "Uncategorized"
    )
)

Now you can manually set a category when needed, and it takes priority.

Troubleshooting

Taking It Further

Level Up Your Tracker:

1. Bank Integration: Use Power Query to import CSV exports automatically
2. Goals Tracking: Add savings goals with progress visualization
3. Forecasting: Project month-end spending based on daily average
4. Alerts: Conditional formatting when approaching budget limits
5. Mobile Entry: Use Microsoft Forms → Power Automate → Excel

Conclusion

A budget tracker only works if it's easy to use. By automating categorization, you remove the biggest friction point. Enter a transaction, and Excel handles the rest—sorting it into the right category and updating your summary instantly.

The key is building a good keyword list over time. Every "Uncategorized" expense is an opportunity to make your tracker smarter. After a few months, 95% of your transactions will categorize themselves.

Take control of your spending without the tedium. Let Excel do the boring work.

Your budget, automated.