split transactions

The Excel check-register template has no built-in split-transaction command, so keep the transaction total once in the register and record its category allocations on a separate Splits sheet linked by a transaction ID.^1^

1. Add an ID to the check register

Insert a column named Transaction ID in the main register. Give each transaction a unique ID, for example:

Keep 125.00 as the only payment entered in the main register. This lets the existing balance formula subtract the transaction once; a check register uses a running balance as entries are added.^2^

2. Create the split-detail sheet

1. Add a worksheet and name it Splits.
2. Enter these headings in row 1:

3. Select the data and press Ctrl+T, then confirm My table has headers.
4. On Table Design, change the table name to SplitTable.

The amounts for TX-001 total $125.00, matching the single payment in the register.

3. Check that each split matches its transaction total

Add a Split Total column to the main register and enter:

=SUMIFS(
  SplitTable[Amount],
  SplitTable[Transaction ID],
  [@[Transaction ID]]
)

SUMIFS adds values whose corresponding criteria range matches the specified transaction ID.^3^

If the register is already an Excel table, entering this formula once should fill the entire column automatically.^4^

Optionally add a Split Check column:

=IF(
  ROUND([@[Split Total]],2)=ROUND([@Payment],2),
  "OK",
  "Fix split"
)

Replace Payment with the actual heading of the template’s withdrawal or payment column. The expected result is OK when all category amounts equal the original transaction.

Important

• Do not enter each category as another full transaction in the main register; that would affect the balance more than once.
• For a transaction that is not split, continue using the register’s normal Category cell. Add rows to SplitTable only for split transactions.
• Back up the workbook before inserting columns, particularly if the template contains protected cells or existing formulas.

References

1. Entering Check Register Split Transactions - Microsoft Q&A
2. Calculate a running total in Excel
3. SUMIFS function
4. Use calculated columns in an Excel table | Microsoft Support