Split one bank payment into categories without losing the original row

The bank row and the category rows do different jobs

Replacing one charge with several smaller bank transactions makes the original payment harder to find. Give that payment a stable reference, such as P001, so you can link each allocation back to it with the same reference. The original is the parent row; the allocations are its children. Those names describe a link, not extra money movements.

You'll need the original date, description, amount and sign, account, currency, and source location, with a receipt reference beside them. If you use a row number as the source reference, record it before sorting and keep the unchanged source file. Row 8 of a sorted sheet might be a different payment.

The parent is what you match against the bank or card statement, while the children supply the category breakdown. These are separate views of the same payment, so never add both sets together. For a spending summary across several purchases, use the children of split purchases and the original amounts of unsplit purchases, without counting the split parents again.

A merchant rule can suggest a category, but it can't establish which items were in this basket. For that, you need the receipt or other supporting detail. Our guide to reviewable categorization rules and the merchant spending summary help with whole-row labeling; a mixed receipt needs this separate allocation step.

A fictional $100 purchase, explained three ways

This synthetic example uses USD and an invented receipt, R01. A debit-card purchase at FICTIONAL WAREHOUSE appears as -100.00 in a bank export whose negative amounts mean money out. The receipt supports these three allocations. Their amounts already include any charges or discounts assigned to those items; there is no extra tax or fee to add afterward.

The category check is 54.25 + 31.75 + 14.00 = 100.00, while the bank export still contains one -100.00 payment. The worksheet deliberately displays allocations as positive costs rather than signed bank movements. Don't change the source sign to match the table.

Adding the parent's $100 cost to the $100 of allocations would report $200 of spending. Adding its signed -100.00 to the positive allocations would produce zero. Neither is a useful spending total. Keep the two views separate instead of trying to fix the result with a sign change.

A zero allocation difference only says the pieces add up. It doesn't show that the groceries were categorized correctly, that the receipt is authentic, or that an employer will approve the desk supplies. That's why you need to compare the allocations with the receipt as well as checking the total.

Two small sheets keep the source separate

The example uses an Excel workbook with sheets named Original and Allocations, so the source payment stays separate from its breakdown. Put each CSV block into its matching sheet starting at A1, with commas separating columns. These are fictional worksheet inputs, not a bank-import format. The formulas use English function names and decimal-point amounts.

The Original sheet has one payment in row 2. Your own working copy should retain any additional source columns, with clear account and currency labels so you don't combine unrelated exports.

ParentID,Date,Description,SignedAmountUSD,StatementRef,ReceiptRef
P001,2026-09-12,FICTIONAL WAREHOUSE,-100.00,September-row8,R01

All three rows on Allocations belong to P001. Their positive values in column C are the amounts assigned to categories.

ParentID,Category,AmountUSD,ReceiptItems
P001,Groceries,54.25,R01-items1to6
P001,Household supplies,31.75,R01-items7to9
P001,Desk supplies,14.00,R01-item10

The checks go in G2:G5 on Allocations, with your own labels beside them in column F. Enter only the formula after each cell label:

G2: =COUNT(Original!D2)
G3: =COUNT(C2:C4)
G4: =SUM(C2:C4)
G5: =ROUND(-Original!D2-G4,2)

You should get 1 numeric parent amount, 3 numeric allocation amounts, a $100.00 allocation total and a $0.00 remainder. Both numeric counts need to match before you rely on the total, because Microsoft's SUM reference, with update metadata dated June 25, 2026 says text in a range is ignored; its COUNT reference, with update metadata dated July 24, 2026 explains the numeric-cell check. A number that merely looks right on screen is not enough.

The allocations must be nonnegative, use no more than two decimal places, and each child must reference P001. These fixed ranges check one payment, not an entire statement, so extend both ranges if you add allocation rows. Another parent needs a separate group and check. G5 negates the parent only because this example's outgoing payment is negative; it isn't a universal card-export formula.

If G5 is $0.01, a cent remains unallocated; if it is -$0.01, the allocations exceed the payment by a cent. With receipt-based amounts, that's a reason to look for missing items, discounts or typing errors before changing a child. Making the last category a balancing figure just to get zero would hide the problem.

You'll need .xlsx to keep this working file, not CSV. Microsoft's file-format guidance, with update metadata dated August 5, 2026, explains that CSV saves the active sheet's displayed text and values, not this two-sheet workbook and its reusable setup.

An equal split can leave a cent over

The fictional household agrees to share only the $31.75 supplies portion equally among Rowan, Taylor and Sam. Dividing it by three gives $10.58333… each. Rounding each person independently to $10.58 allocates $31.74, leaving $0.01 without an owner.

For this three-person split, they agree that Rowan and Taylor get rounded shares and Sam gets the remainder. To record that on Allocations, put their names in H2:H4, the following formulas in I2:I4, and the remainder check in I5:

I2: =ROUND($C$3/3,2)
I3: =I2
I4: =ROUND($C$3-SUM(I2:I3),2)
I5: =ROUND($C$3-SUM(I2:I4),2)

I5 is $0.00: 10.58 + 10.58 + 10.59 = 31.75. Microsoft documents the two-decimal operation in its ROUND reference, with update metadata dated June 25, 2026. The choice of who carries the extra cent comes from the household, not that function. With a different total, the remainder share can be smaller: $31.76 would give Rowan and Taylor $10.59 each and Sam $10.58. This is a rounding rule for the stated three-way split, not a general allocation engine.

These person shares explain the $31.75 child; they aren't three more purchases to add to the category total. Nor do they establish who still owes money, because that depends on who paid and any repayments already made. If you're settling costs with one other person, use the shared expense splitter and agree what belongs in that calculation first.

In YNAB, split the existing transaction

YNAB has a native split, so you don't need to replace the payment with several imported rows. Its Split Transactions guide, updated July 28, 2026, documents these steps for the web app:

1. Open the account and double-click the existing transaction to edit it.
2. Choose the category field, then Split.
3. Keep the full payment in the top line, here a $100.00 outflow.
4. Select the categories on the split lines and enter $54.25, $31.75 and $14.00. Check that nothing remains to assign, then save or update.

The guide also documents individual payee, category and memo fields on split lines, giving you a place to record receipt references or explain an allocation. If an amount remains next to “Amount remaining to assign” when you click Save, YNAB documents a prompt to auto-distribute it or go back and assign it yourself. For nonzero split amounts, the guide describes distributing the remainder proportionally. When you know the receipt totals, enter them yourself: automatic distribution shouldn't be assumed to reproduce them. Our leftover-cent rule above is not a claim about YNAB's rounding algorithm.

Importing the worksheet won't create those splits. YNAB's File-Based Import guide, updated September 22, 2026, says categories and split transactions cannot be imported from the file. That's different from YNAB assigning a category itself: the same page says imported transactions are auto-categorized using the payee's usual category. Extra category columns won't make this worksheet import as native splits, so bring in the original payment if it isn't already present, then edit its split in YNAB. These instructions are checked against support documentation, not a live YNAB account test.

Request the relevant portion, not the whole charge

Suppose the employee in our fictional purchase wants to request the $14.00 desk-supply portion for review. The other $86.00 stays outside that request. Keeping the $100.00 source charge visible alongside the requested $14.00 and reference R01 item 10 lets the reviewer see how one became the other. Calling the category Office supplies isn't approval.

The expense report builder lets you select a charge and enter a requested portion, purpose, category and receipt reference. Each source row gets one report category; it isn't a multi-category receipt allocator. You'll still need the allocation schedule and receipt with the request, especially if a reviewer needs several categories from one charge. A typed receipt reference is not an attached receipt.

For US business records, the IRS recordkeeping page, reviewed or updated August 3, 2026, says supporting documents for expenses should identify the payee, amount paid, proof of payment and date incurred. They should also describe the item purchased or service received in a way that shows the amount was for a business expense. It explicitly notes that a combination of documents may be needed to support all those elements. Keeping the receipt and payment record together serves that purpose. It doesn't establish that a cost is deductible or that the documents meet an employer's policy.

If the receipt is missing, mark it missing and ask the merchant for a copy rather than guessing an item list from the bank description. Record the purpose and why you chose the requested portion, but leave its acceptance to the reviewer. A complete sum is different from complete evidence.

Recovering the payment won’t recover the receipt

For one payment, copying its details from the statement may be enough. If you have several, check whether your bank offers a CSV export first. When the records are only available as PDFs, AnyStatement can convert statement rows to Excel or CSV for review. That recovers the payment data, but you'll still need the shopping list, business purpose or agreed split separately.

What we checked

We opened the linked YNAB, IRS and Microsoft guidance on October 17, 2026. We checked the fictional CSV values, allocation sums, cent remainder and equivalent formula logic with Python Decimal, including missing numeric input and one-cent under- and over-allocation. We did not run this workbook in Excel or Google Sheets, test a live YNAB split, submit an expense report, or validate anyone's tax treatment. The checks establish the example's arithmetic, not the authenticity or acceptability of supporting documents.