Automate Expense Tracking for Freelancers in Sheets

Separate your data flow into 3 tabs: Import, Expenses, and Summary. This sounds obvious but most broken expense trackers fail because someone pasted a CSV export directly on top of live formulas. That deletes the formulas, the data is now raw, and nothing aggregates correctly.

Bank exports, Stripe payouts, and accounting tool dumps don't arrive in the same column order every month. Mercury puts the date in column A; your accounting software might put it in column D. That sequence can also shift when you change export settings or add a new data field.

The fix is a header-row lookup that finds the right column by name, not by position. In the Expenses tab, define 3 helper cells that store the column index for each field you need:

=MATCH("Date", Import!$1:$1, 0)
=MATCH("Description", Import!$1:$1, 0)
=MATCH("Amount", Import!$1:$1, 0)

Name these cells col_date, col_desc, col_amount using the Name Box. Then in the Expenses rows, pull data dynamically:

=INDEX(Import!$A:$Z, ROW(A2), col_date)
=INDEX(Import!$A:$Z, ROW(A2), col_desc)
=INDEX(Import!$A:$Z, ROW(A2), col_amount)

Now when your export arrives with Description in column C instead of column B next month, nothing breaks. The header-row lookup finds it and every downstream formula adjusts automatically.

Raw vendor strings from bank exports are hostile: trailing spaces, mixed case, extra whitespace from OCR fields, encoding artifacts. Two transactions from the same vendor can look completely different to a SEARCH formula if one has a stray space.

In the Expenses tab, add a Vendor_Clean column that applies three transforms in sequence:

=TRIM(UPPER(SUBSTITUTE(C2, "  ", " ")))

TRIM strips leading and trailing whitespace. UPPER eliminates case mismatch. SUBSTITUTE collapses double spaces that sometimes appear in exported memo fields. This single column is what every downstream formula will reference — the raw import description stays untouched in its own column as an audit trail.

For date normalization, add a Date_Clean column. Mixed date formats — MM/DD/YYYY vs YYYY-MM-DD vs "16 Apr 2026" — are common when pulling from multiple sources. Force them to a standard value:

=IF(ISNUMBER(D2), D2, DATEVALUE(D2))

The Rules tab holds a 2-column table: Keyword in column A, Category in column B. This is the only place you touch when a new vendor appears or you want to reclassify a category.

A few design rules that matter. Keywords should match the most distinctive part of the vendor string — not "GOOGLE" (too broad, catches Google Drive, Google Ads, and Google Cloud as the same thing) but "GOOGLE CLOUD" or "GOOGLE ADS" specifically. Cap any given category's rule count at whatever it takes to be accurate; there's no artificial limit. A payment processor that generates 7 different string variants for the same vendor needs 7 keyword rows.

Keep Keywords sorted by specificity within each category — more specific patterns first. The categorizer returns the first match, so "FACEBOOK ADS" should appear before "FACEBOOK" if both are in your table.

The category formula scans your Rules table for any keyword that appears inside the cleaned vendor string. In the Expenses tab, add a Category column:

=IFERROR(INDEX(Rules!$B$2:$B$100, MATCH(TRUE, ISNUMBER(SEARCH(Rules!$A$2:$A$100, E2)), 0)), "Uncategorized")

Enter this with Ctrl+Shift+Enter to make it an array formula (in newer Sheets, you can also wrap the inner expression in ARRAYFORMULA). The formula returns the first category whose keyword appears anywhere in the cleaned vendor string — partial matches work, so "GOOGLE CLOUD STORAGE" correctly hits the "GOOGLE CLOUD" rule even though they're not identical.

The "Uncategorized" fallback is more important than it looks. If you return "" for unmatched rows, those transactions silently disappear from SUMIFS totals — SUMIFS skips blank cells. Using an explicit string means unmatched expenses appear as their own category in your summary, where you can see exactly what hasn't been mapped yet. That visibility is what tells you when your Rules table needs updating.

The Summary tab aggregates totals by category and month. Set up a grid with categories down column A and months across row 1 (formatted as the first day of each month, e.g., 2026-01-01). Then for each cell in the grid:

=SUMIFS(Expenses!$F:$F, Expenses!$G:$G, $A2, Expenses!$H:$H, ">="&B$1, Expenses!$H:$H, "<"&EDATE(B$1, 1))

Where column F is Amount, column G is Category, and column H is Date_Clean. The EDATE(B$1, 1) bounds the date range to the calendar month without hardcoding end dates.

Add a Total row at the bottom (SUM of each month column) and a Budget row where you hardcode your planned spend per category. The variance is just Actuals minus Budget:

=B3 - B2

Apply conditional formatting to variance cells: red for over-budget, green for under. This is a useful check, but it's not the final output — it's an input layer.

This is the step most expense tracker guides skip. The Summary grid from Step 6 is useful for personal bookkeeping, but it's not a financial model — it's a flat table. To make it useful as an input to a burn model or investor-facing P&L, you need a separate P&L tab that treats the Summary as an Actuals layer.

Set up the P&L tab with your full line-item structure in column A, aligned to how costs appear in a real income statement:

Each Actuals cell in the P&L pulls from Summary using a direct reference or a SUMIFS that maps P&L line items to Summary categories:

=SUMIFS(Summary!$B:$B, Summary!$A:$A, "Software")

For categories that roll up into a P&L line (e.g., "Hosting & Infrastructure" might combine Summary rows for AWS, Google Cloud, and Cloudflare), use multiple SUMIFS:

=SUMIFS(Summary!B:B,Summary!A:A,"Software") + SUMIFS(Summary!B:B,Summary!A:A,"Hosting")

This is the architecture: the categorizer populates Summary, Summary is the Actuals input layer, and the P&L tab is the model. Revenue goes into P&L directly or from its own feed. From here, your monthly actuals flow naturally into burn rate and runway calculations — for that structure, see how to calculate runway for a startup and startup burn rate dashboard.

The array formula in Step 5 runs an N × M comparison on every recalculation — every row in Expenses scanned against every row in the Rules table. On 500 expense rows against a 50-row rules table, that's 25,000 lookups per recalculate. On 10,000 rows, it becomes 500,000, and you'll notice the sheet freezing for several seconds every time a cell changes.

For datasets over 2,000 rows, move categorization to Apps Script. The logic is identical, but it runs once and writes results in a single batch:

function categorizeExpenses() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const expenses = ss.getSheetByName('Expenses');
  const rules = ss.getSheetByName('Rules');

  const expData = expenses.getDataRange().getValues();
  const ruleData = rules.getRange(2, 1, rules.getLastRow() - 1, 2).getValues();

  const categories = [];

  for (let i = 1; i < expData.length; i++) {
    const vendor = String(expData[i][4]).toUpperCase(); // Vendor_Clean column
    let matched = 'Uncategorized';

    for (const [keyword, category] of ruleData) {
      if (vendor.includes(String(keyword).toUpperCase())) {
        matched = category;
        break;
      }
    }
    categories.push([matched]);
  }

  // Single batch write — never write inside the loop
  expenses.getRange(2, 6, categories.length, 1).setValues(categories);
}

The critical rule: never call setValue() inside a loop. Each individual write triggers a round-trip to the Sheets API. On 10,000 rows, writing inside the loop can take 15+ minutes. The batch setValues() at the end runs the same operation in under 10 seconds.

Wire this to a trigger under Extensions → Apps Script → Triggers to run automatically on edit, or set it to fire when the Import tab changes. As of April 2026, Apps Script execution time is capped at 6 minutes for consumer accounts and 30 minutes for Workspace accounts — the batch write pattern keeps you comfortably inside both limits even at Google Sheets' maximum of 10 million cells.