Split categories

Question

How do I split a single transaction into multiple categories. For example if I am creating a checkbook register on excel.

column 1 = date

column 2 = check number

column 3 = name of place

column 4 = categories (drop down list)

column 5 = withdrawal amount

column 6 = deposit amount

column 7 = balance

column 8 = status (if check cleared bank yes or no)

let’s say

2/2 (date) #111 (check number) insurance (name of place) split (category) $100 (withdrawal amount)

instead of typing split for the category I want to be able to say 50 dollars for health insurance, 25 dollars for vision insurance, and 25 dollars for dental insurance.

Answer 1

Hi, K

Excel can handle the drop-down part, and it can also jump to another spot in the workbook with a hyperlink. A practical workaround is to keep the $100 transaction on your main register, add a hidden transaction ID, and put the split details on a second sheet, for example: ID | Category | Amount. Then your main register can still show one clean entry, while the other sheet stores Health $50, Vision $25, and Dental $25. You can total it back with a formula like =SUMIF(SplitDetails!A:A,A2,SplitDetails!C:C).

It is recommended that you also add an “Open split” hyperlink from the register row to that detail area so it feels like one transaction without making the main sheet messy.

Thank you for your patience in reading, I hope this information has been helpful to you. 

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment."    

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread. 

Answer 2

Excel does not support splitting a single cell into multiple cells within the same row the way Word tables do. A cell is the smallest unit of data, so a single transaction row cannot directly hold multiple category/amount pairs in one “Categories” cell.

To model a split transaction in Excel, use additional rows or additional columns:

Option 1 – One row per category (recommended for a register) For the $100 check that is split into 3 categories, enter 3 rows and use the same date, check number, and payee:

• Row 1: 2/2 | #111 | Insurance | Health insurance | 50 | | balance | status
• Row 2: 2/2 | #111 | Insurance | Vision insurance | 25 | | balance | status
• Row 3: 2/2 | #111 | Insurance | Dental insurance | 25 | | balance | status

The running balance formula in column 7 can simply subtract each withdrawal row in order. This keeps each category separate for later analysis (e.g., SUMIF by category).

Option 2 – Extra category/amount columns on the same row Keep one row per check, but add more columns, for example:

• Category 1, Amount 1
• Category 2, Amount 2
• Category 3, Amount 3

Then enter:

• Category 1 = Health insurance, Amount 1 = 50
• Category 2 = Vision insurance, Amount 2 = 25
• Category 3 = Dental insurance, Amount 3 = 25

The total withdrawal column can sum those amount columns (e.g., =SUM(F2:H2)), and the balance formula uses that total. This preserves a single “transaction row” but spreads the split across multiple cells.

Excel cannot split the contents of a single “Category” cell into multiple categories and amounts inside that same cell; instead, use extra rows or columns to hold the additional detail.

---

References:

• Split a cell in Excel
• Merge and unmerge cells in Excel
• Distribute the contents of a cell into adjacent columns
• Split text into different columns with the Convert Text to Columns Wizard