To categorise hundreds of rows at once in a European-locale spreadsheet, use one IFS formula with REGEXMATCH or SEARCH, and first make sure your amounts imported as numbers, not text. Many exports use a decimal comma and thousand dots, and a sheet set to another locale will read them as text.
Why do amounts sometimes refuse to add up?
A cell showing 1.234,56 is a number in some locales and text in others. If the SUM of the amount column is 0 or too small, the cells are text. Fix it by importing with the right locale, or in Google Sheets by changing the sheet locale under settings. Do this first, or later totals will not mean anything.
What does a categorising formula look like?
In Google Sheets with a semicolon locale, with descriptions in column B, put this in C2 and fill down:
=IFS(REGEXMATCH(LOWER(B2);"market|grocer|bakery");"Groceries";REGEXMATCH(LOWER(B2);"rail|metro|bus|taxi");"Transport";REGEXMATCH(LOWER(B2);"pharm|clinic");"Health";TRUE;"Review")
IFS tests each pair in order and returns the first category whose test is true. The final TRUE with Review catches everything else. In a comma locale, replace each semicolon with a comma. Excel 2019 or later supports IFS too, but REGEXMATCH is Google Sheets only; in Excel use ISNUMBER(SEARCH("market";B2)) in its place.
What does a 200-row run look like?
| Category | Rows matched | Total |
|---|---|---|
| Groceries | 62 | €1,284.30 |
| Transport | 41 | €318.90 |
| Health | 12 | €176.00 |
| Review | 85 | €2,040.55 |
| All rows | 200 | €3,819.75 |
Check the totals: 1,284.30 + 318.90 + 176.00 + 2,040.55 equals 3,819.75, and 62 + 41 + 12 + 85 equals 200. If those do not add up, a row was dropped or counted twice.
How do you shrink the Review pile?
- Filter Review and sort by description.
- Find the repeated ones and add their keyword to the right branch of the formula.
- Reapply and recount.
- Stop when the leftovers are one-offs you will label by hand.
What about tidy-up afterwards?
Copy the category column and paste as values, so changing the formula later does not rewrite history. Keep the formula in a spare tab for next month.
Should you use Excel or Google Sheets?
Either works. Google Sheets has REGEXMATCH and easy sharing, which helps if two people edit the same household file. Excel has strong pivot tables and offline use. Pick the one you already open, and make sure the regional setting matches the way your bank writes numbers and dates, as that is the cause of most formula errors.
- Check the locale before importing.
- Check that dates sort as dates.
- Check that amounts sum to a number.
Dates deserve the same care as amounts. If a day of 03/04 is read as the third of April in one locale and the fourth of March in another, monthly totals shift. Test by sorting the date column; if it sorts oddly, the cells are text. Fix the locale or convert the dates before you categorise.
Keep a note of any keyword you add during a session and why. When a row is wrongly matched a month later, the note tells you which keyword is responsible. A short comment column beside the formula tab, with the keyword and the date, is enough, and it makes the sheet understandable to a partner who shares it.
An app option
SpendixAI supports bulk edit and delete on transactions, with deleted items kept in a recycle bin for 30 days. It is built around India and INR and is not claimed to serve euro workflows.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


