To flag unusually large expenses, work out the average and the standard deviation for each category, set a threshold of the average plus two standard deviations, and mark any transaction above it. In Excel that takes AVERAGEIFS for the mean and STDEV.S with an IF inside it for the spread. The flag is a prompt to take a second look, not a verdict that something is wrong.
What do the average and standard deviation tell you?
The average is the typical amount in a category. The standard deviation is the usual distance of a payment from that average. A category with a small spread, such as a fixed monthly bill, flags even a modest jump. A category with a large spread, such as dining out, needs a much bigger payment to stand out. Using the same ₹ cut-off for every category misses this, which is why the calculation is done per category.
How do you set up the category table?
Assume a sheet called Log with Date in column A, Category in column B and Amount in column C, rows 2 to 500. On a summary sheet, list categories in F2 downward and build three columns:
- G2, average: =AVERAGEIFS(Log!$C$2:$C$500, Log!$B$2:$B$500, $F2)
- H2, spread: =STDEV.S(IF(Log!$B$2:$B$500=$F2, Log!$C$2:$C$500))
- I2, threshold: =G2 + 2*H2
The IF inside STDEV.S passes only the matching amounts, and the false values it creates for other rows are ignored. In Excel 365 and 2021 this just works. In Excel 2019 and older, confirm H2 with Ctrl+Shift+Enter. In Google Sheets, wrap it as =ARRAYFORMULA(STDEV(IF(Log!$B$2:$B$500=$F2, Log!$C$2:$C$500))). Older Excel files may use STDEV in place of STDEV.S, which gives the same result.
How do you mark the large transactions?
In column D of the log, flag each row by comparing the amount with its own category's threshold:
=IF(C2>INDEX($I$2:$I$12, MATCH(B2, $F$2:$F$12, 0)), "Review", "")
Point the ranges at your actual category table. Filter column D for Review to see the list.
What does one category look like in numbers?
Say groceries has plenty of entries, with an average of ₹850 and a standard deviation of ₹300. The threshold is 850 plus 2 times 300, which is ₹1,450.
| Grocery payment | Above ₹1,450? | Possible explanation |
|---|---|---|
| ₹620 | No | Daily top-up |
| ₹1,380 | No | Weekly shop |
| ₹2,150 | Yes | Festival shopping or a bulk order |
| ₹1,460 | Yes, narrowly | Probably normal for a big week |
The ₹2,150 row is worth a glance. Maybe it was Diwali shopping, or maybe an item was billed twice. The ₹1,460 row passes the line by ₹10, so you might ignore it. That is judgement, and the formula only decides what to put in front of you.
When does the method mislead?
- Too little data. A category with four entries has a meaningless spread. Wait until you have a reasonable number of entries before trusting it.
- One huge payment distorts both the average and the spread, so it may hide itself. If a category has a few very large items, look at the median instead with =MEDIAN(IF(...)) entered the same way.
- Fixed bills. An electricity bill that rises in summer will be flagged for good reason, and the explanation is the weather.
- Mixed categories. If Shopping holds both ₹200 items and ₹40,000 appliances, split it into two categories first.
How often should you run it?
Once a month, after you finish logging, is enough. Filter for Review, tick off the ones you recognise and investigate the rest. If you keep seeing the same category flagged, the real answer is often that your habits changed and the old average is out of date, so consider using only the last six months of data to compute it.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


