Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Money in Excel - How can I split a transaction?

Copper Contributor

If I make a purchase with cash back, I would like to split the transaction as, say, groceries and petty cash. Another example might be buying prescriptions at a drug store along with household items. Since I want to separate deductible transactions from the other, how can I split transactions?

6 Replies


With your permission, if I can recommend you, add a file (without sensitive data) to your project.

Explain your plans in relation to this file. So you can get a solution that is tailored to your needs much faster.

At the same time, it is much easier for someone who wants to help to understand the subject.

A win-win situation for everyone.

Please no Picture, even if it is said that a picture can say a thousand words, it is certainly not in the case of Excel, on the contrary in some cases.


* Knowing the Excel version and operating system would also be an advantage.


Thank you for your understanding and patience



I know I don't know anything (Socrates)

ups now i see it is Money in excel, sry im not the right person for that.

Thx for your Time

Money in Excel allows manual transactions, so you could use that. Create two transactions, one positive and one negative to shift the expense into the correct category. Let's say you spent $100 at the drug store, $70 on health products and $30 on groceries. Mark the -$100 transaction that downloaded in the Shopping category. Create a manual transaction for +$30 also in Shopping, and a second manual transaction for -$30 in Groceries.

@David_Goodhand - Not a very good work around.  I want the split transactions totaled.

Ie.  Charge Card bill is 1000.00 I want be able to assign category to any item in the charge card bill, so I can see a report on these categories 

Charge Card 1,000

  Electric Bill 200.00

  Groceries 100.00

  Clothing 100.00

   Misc  600.00

I want to be able to see my monthy spend for all categories above.


@dkcarmona Add your charge card as an account in MIE.  MIE will import each card transaction individually, allowing you to assign individual transactions to the appropriate category as you do with your bank transactions - no splitting required for nearly all charge transactions.  There may still be other situations which require a manual adjustment, but your charge card transactions should import and categorize with ease.

The proposed solutions in this thread are time-consuming, cumbersome, and error prone. Split transactions are *extremely* common, and are supported by just about every other financial application in existence. You guys need a *MUCH* better answer than this.