# Categorize expenses in Google Sheets

`=HUNCHPICK(text, options)` reads one transaction description and returns the budget category it fits, chosen from the list you write. Point it at a bank or card export and every row gets a category, ready for a pivot, a `SUMIF`, or a `QUERY`.

Updated 28 September 2026. Source: https://hunchsheet.app/formulas/categorize-expenses-google-sheets

## Why a keyword rule breaks on a bank feed

A rule like "if the description contains hotel, mark it travel" works until a statement shows "WEWORK 285 FULTON ST", which is rent for one company and a coworking day pass for another, or "AMAZON MKTPLACE PMTS", which could be a book, a monitor stand, or an office chair. A keyword rule needs the exact word; a category formula needs only the meaning, and a bank feed rarely repeats the same merchant wording for long.

## The formula

```
=HUNCHPICK(B2:B500, "software and subscriptions: SaaS tools, apps, cloud services|travel: flights, hotels, rideshare, parking, mileage|meals and entertainment: restaurants, coffee, client meals|office and supplies: equipment, furniture, stationery|advertising and marketing: ads, sponsorships, design tools|professional services: legal, accounting, contractors, consultants|rent and utilities: office rent, internet, power|other")
```
In Excel: `=HUNCH.PICK(B2:B500, "software and subscriptions: SaaS tools, apps, cloud services|travel: flights, hotels, rideshare, parking, mileage|meals and entertainment: restaurants, coffee, client meals|office and supplies: equipment, furniture, stationery|advertising and marketing: ads, sponsorships, design tools|professional services: legal, accounting, contractors, consultants|rent and utilities: office rent, internet, power|other")`

Each option carries its own definition after a colon, so `travel` and `meals and entertainment` do not fight over a rideshare receipt to a client dinner. The category names are clear enough that the default question works: no third argument needed. Full syntax is on the [HUNCHPICK reference](https://hunchsheet.app/docs/hunchpick).

## Real outputs

Nine lines from a card statement export, with the model's answers as returned on 28 September 2026.

| Transaction description | Category |
| --- | --- |
| SQ *BLUE BOTTLE COFFEE  SAN FRANCISCO CA | meals and entertainment |
| AWS.AMAZON.COM  SEATTLE WA | software and subscriptions |
| DELTA AIR 006 2341234567  ATLANTA GA | travel |
| GOOGLE *ADS  MOUNTAIN VIEW CA | advertising and marketing |
| WEWORK 285 FULTON ST  NEW YORK NY | rent and utilities |
| STAPLES STORE #4471  DENVER CO | office and supplies |
| LEGALZOOM.COM  LOS ANGELES CA | professional services |
| COMCAST CABLE COMM  PHILADELPHIA PA | rent and utilities |
| UBER TRIP HELP.UBER.COM | travel |

Every line lands on a specific category, none in other, because the merchant shorthand carries more signal than it looks like at first read: LEGALZOOM.COM reads as professional services and GOOGLE *ADS as advertising and marketing without anyone needing to expand the abbreviation first. WEWORK is read as rent and utilities, the coworking-membership reading rather than a one-off day pass, worth checking against your own vendor if the two ever land on the same statement.

## Where the category list runs out

A statement always has a few lines that do not match anything on the list cleanly. These are worth reading before you trust the column for a full quarter.

| Transaction description | Category |
| --- | --- |
| AMAZON MKTPLACE PMTS  AMZN.COM/BILL WA | other |
| PAYPAL *NETFLIX  4029357733 | software and subscriptions |
| CHEVRON 0091234  SAN JOSE CA | other |
| APPLE.COM/BILL  866-712-7753 CA | software and subscriptions |
| INDEED.COM  512-605-1739 | professional services |
| DOORDASH*THAI PLACE  SAN FRANCISCO CA | meals and entertainment |

AMAZON MKTPLACE PMTS and the Chevron gas station both land in other, exactly the generic-merchant and fuel-purchase cases the category list does not name directly. PAYPAL *NETFLIX lands on software and subscriptions, the closest available option for a subscription that is not SaaS; if personal-style subscriptions need their own line for expense policy reasons, add a category rather than leaving Netflix and a CRM bill in the same bucket. INDEED.COM reads as professional services over advertising and marketing, a reasonable pick for a recruiting cost but one worth its own definition if your team argues about where hiring spend belongs.

> Tip: Read whatever lands in `other` once a month. If the same kind of line keeps landing there, add a category for it instead of leaving the bucket to grow.

## Totals stay in normal formulas

The category column is text. Amounts, dates, and totals stay exactly where they already are, in `SUMIF` and `QUERY`, not in the model.

```
=SUMIF(C2:C500, "travel", D2:D500)
```

```
=QUERY(A1:D500, "select C, sum(D) where C is not null group by C order by sum(D) desc label sum(D) 'total'")
```

Column C is the category from `HUNCHPICK`, column D the amount from the export. Neither formula asks the model anything; they read the column it already filled. A pivot table on the same two columns gives the same total a different way, category down the rows, month across the columns, if that is the view your finance lead already trusts.

Keep the category column even after you build the summary. Next month's statement is new rows, and the summary formulas above pick up whatever the category column contains, no rebuilding required.

## Limits

- The model reads only the description text. It does not see the amount, the date, or your chart of accounts, so a $4 coffee and a $4,000 vendor payment with the same merchant name get the same category.
- A range formula categorizes about 1,000 rows before Google's 30-second custom function limit gets close. A full year of statements is a job for **Extensions › Hunch › Ask about selection**, which writes values instead of formulas.
- Category names come back exactly as written before the colon. If your accounting software needs a code instead of a name, keep the words as the option list and `VLOOKUP` the result against your chart of accounts.
- It does not know a charge repeats every month. If you want a separate recurring-subscription flag next to the category, that is a second formula, not a seventh category.

Copy [the template](https://hunchsheet.app/template) to try this on your own export. The [product categorization guide](https://hunchsheet.app/formulas/categorize-products-google-sheets) and the [general classification guide](https://hunchsheet.app/formulas/classify-text-google-sheets-formula) cover the same HUNCHPICK pattern on other kinds of text.

## Questions

### Does it use the amount to help decide the category?

No. It judges only the text you give it. Combine the category column with SUMIF or a pivot on the amount column for totals; the two never need to touch inside the formula.

### What about a merchant name with no useful words in it?

A code like a processor ID with no merchant name gives the model little to work with, and the read leans toward other or a low-confidence guess. Add a memo column if your export has one, and concatenate it into the text you send.

### Can it return my chart of accounts number instead of a category name?

Not directly. Keep the option list in words, then VLOOKUP the returned label against a small chart of accounts table to get the number your bookkeeping software expects.

### Will recategorizing next month's statement cost credits for merchants I already categorized?

Identical text within one call is free, and results stay cached for six hours, so reopening the same sheet does not spend anything. A new month is new transaction text, so it does cost one credit per row.

### Can I flag recurring subscriptions separately from the category?

Yes, a second column: =HUNCH(B2:B500, "Does this look like a recurring subscription charge rather than a one-time purchase?") returns a probability you can filter on, independent of which budget category the charge landed in.