To build an expense report for one credit card over a date range, take that card's statement lines for exactly those dates, group them into categories, total each group, and check that the grand total matches the statement's own total for the period. Everything below is method only; local tax and reporting rules differ, so ask your accountant about those.
What do you need before you start?
- The card's transactions for the period, from the issuer's statement or a downloadable file.
- Start and end dates agreed with whoever will read the report.
- A short category list, agreed in advance.
- Receipts for any line the reader may question.
Keep a clear note of the date type. A transaction date is when you swiped; a posting date is when it reached the account. Pick one and use it for the whole report.
How do you build the report in a spreadsheet?
- Put the statement rows in columns: Date in A, Merchant in B, Amount in C, Category in D, Notes in E.
- Put the start date in G1 and the end date in G2.
- Filter rows with a date between G1 and G2, or use SUMIFS so the filter is automatic.
- For each category total, write =SUMIFS($C$2:$C$500, $D$2:$D$500, "Travel", $A$2:$A$500, ">="&$G$1, $A$2:$A$500, "<="&$G$2), changing "Travel" for each category.
- Add a grand total: =SUMIFS($C$2:$C$500, $A$2:$A$500, ">="&$G$1, $A$2:$A$500, "<="&$G$2).
- Compare that grand total with the statement's total for the period.
This works in both Excel and Google Sheets. The dates in column A must be real dates, not text, or the comparison silently ignores them.
What does a worked example look like?
Say the report covers 1 March to 31 March on one card.
| Category | Lines | Total |
|---|---|---|
| Travel | Flight $412.00, taxi $38.50 | $450.50 |
| Meals | Four client lunches | $187.25 |
| Software | Two subscriptions | $64.00 |
| Supplies | Printer paper and ink | $52.40 |
| Uncategorised | One unknown charge | $29.99 |
The grand total is $450.50 + $187.25 + $64.00 + $52.40 + $29.99 = $784.14. If the statement shows $784.14 in purchases for that range, the report ties out. If the statement shows $754.15, the difference is $29.99, so the unknown charge is not on that statement period, and you check its date.
Why would the total not match the statement?
- Cycle mismatch: statement cycles rarely run from the first to the last of a month.
- Pending transactions that post after your end date.
- A refund or credit that you left out.
- A fee or interest line that is not a purchase.
- A date typed as text and skipped by the formula.
Can an app do this faster?
If your card is an Indian one and the amounts are in rupees, SpendixAI can take the statement as PDF, CSV or Excel, keep the card as its own account so entries stay tied to it, and let you filter a date range and download CSV. It is built around India and INR, and this article's dollar figures are only an illustration of the method. Review imported rows before saving, because layouts differ.
How do you handle refunds and credits in the report?
Show them as negative lines inside the same category as the original purchase, so the category total is net. Say a $120.00 hotel charge is partly refunded by $30.00 later in the same range. The net lodging line is $120.00 - $30.00 = $90.00. If the refund arrives after your end date, leave it out and add a note that it is pending, so the reader knows the total is gross for now. Payments you make to the card are not expenses at all; leave them out of the report and keep them on the statement reconciliation instead.
Before you send the report
- Start and end dates are written at the top.
- Date type is stated.
- Total ties out to the statement or the gap is explained.
- Every uncategorised line has a note.
- Receipts are attached for unusual items.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


