Sometimes it’s nice to break up a single transaction into multiple categories.
- You get cashback at the grocery checkout and want to split the transaction between your Grocery and Cash categories
- You make a large purchase on Amazon and want to bucket the computer, dog food and bedding purchases by category (rather than having just one large, ambiguous line item)
- You consolidate a shared dinner with friends onto your credit card and receive cash to cover your friend’s portions
With the Transaction Splitter in the Tiller Labs add-on, it’s really easy to split transactions between multiple categories.
Transaction Splitter Features
- Breaks transactions into as many splits as you need
- Calculates & pre-populates remaining balance as splits are added
- Alerts when splits don’t total original line item
- Optionally adds
Note to memorialize split details
How To Use the Transaction Splitter
The Transaction Splitter is available within the Tiller Labs add-on.
You can use the workflow using the following steps:
- Open your Tiller spreadsheet.
- Navigate to your Transactions sheet. Select a cell in the transaction row you wish to split.
- Select Add-Ons → Tiller Labs → View Solutions.
- Once the sidebar loads, select Tools.
- Select Split Transaction. The add-on will pull in the selected transaction from the Transactions sheet.
- Select a split category from the dropdown and update the split amount.
- Click add-split to add a new split row if another split is needed.
- Repeat the split creation steps as many times as needed.
- Click Split Transaction to write the splits to your Transactions sheet.
- If you wish to create more splits, select a different row in the Transactions sheet then click “Get Selected Transaction” to pull it into the splitter.
- When you are finished creating splits, click “Done”.
How it works
- New splits will prepopulate with the remainder between the original transaction amount and the sum of your splits. For this reason, it usually works best to add known quantities as the first splits and let the splitter calculate the remainder for a catch-all final category. (For example, if I spend $105.61 at the grocery store with $40.00 cash back, it is best to enter the $40.00 split first, then let the Add Split operation calculate the balance so I can categorize what’s left as Groceries.)
- If you create more splits than you need in the sidebar, you can delete them by clicking the red-X icon.
- The workflow requires a Transactions sheet with the following columns
Transaction ID. The Transactions
Note column is optional.
- The workflow requires a Categories sheet with a
- If your Transactions sheet has a
Notes column, the splitter will memorialize splits by appending a note that looks like: $50.00 of $100.00 split from “Whole Foods” transaction on Dec 11, 2019
- The Tiller Labs Transaction Splitter is a refreshed build of the older Tiller Splitter in the G Suite Marketplace.
If you have a question or need help first search the community to see if someone has already asked and if not click here to quickly post a question about this tool in the Google Sheets category.
Be sure to customize the title of your post with keywords about the issue or question so others can easily find the Q&A in search.
Is there a way of making the colour of the split cells in the transactions sheet a different colour to show at a glance which transactions have been split?
Obviously I could do this manually but it would be nice to have the line items automatically colour when the split was performed with the splitter tool.
The Tiller Labs Splitter doesn’t do this out of the box but you should be able to build conditional formatting to accomplish this. For example, I’m noticing the
Note column is rendered with text like this:
$1,000.00 of $1,590.00 split from "External Deposit - Bill.com Inv #100" transaction on Dec 30, 2019
What if you created a conditional formatting rule to color the row if the
Note column contains the text “split from”?
Let me know if it works.
Attached are three screen shots - the transaction, the split, and the error message.
The transaction amount is $53.10
The split is $36.11 and $16.99 for a total of $53.10
The error message state that the split is short $.01.
Thanks for flagging this, @rgsidor. I just published a new build that implements some rounding that should help. Let me know if you see any improvement or additional issues.
Is it possible that the recently implemented rounding may have had some unintended consequences for split calculations? @randy
We received this report from a customer…
“I have a bug to report for the split transaction tool under Tiller Labs. It’s always off by a penny when I split a transaction into two.”
Here’s an example:
Expense amount: $206.95
Transaction Split #1
Expense Category: $6.94
Good question, @krista. While it’s certainly possible I introduced a bug, I was not able to reproduce this with the following steps:
- I replaced a transaction’s value with
- Ran the splitter.
- Set the first split to
- Clicked Add Split.
The first split populated properly for me at
Again, it is possible I introduced a bug, but I need steps to reproduce it.
Please share if you have better steps…
P.S. Is it possible the customer was running the old version? Or that they started with a transaction value that had a hidden thousandths component (e.g. the cell’s value was
Hey Randy, this seems like a great feature! I base my financial records in the Office 360 Excel and would LOVE to use this solution. I know it’s a Beta program, and everything I’ve used it for has been seamless so far. Are there any plans to add this feature and other solutions to Excel? If so, would it be anytime soon or should I not keep an eye out?
Most transactions have a sales tax that is a part of the transaction total. I personally would prefer not to split the sales tax into a separate category and it is also tedious to manually split the sales tax between the other categories. I also do not require the sales tax to be accurate as different items in one transaction could have different sales tax rates applied.
My recommendation (and how I have seen other budget apps solve for this issue) is to have a button that will let you split the remaining balance by the percentage that was split into each category. For example, if you have $5 remaining after splitting $40 into Groceries and $10 into Personal Care, then $4 would be added to Groceries and $1 would be added to Personal Care.
Is that possible?
Glad to hear you’re liking the Excel Beta tools, @madimitsvotai. I will let @heather, who prioritizes development work, speak to when splitter automation may come to Excel.
I see what you are getting at, @sadermine. Right now, when a balance remains, there is an alert box with the amount. To your point, I could envision a button appearing instead that says something like “Prorate Remaining $5.00”. Perhaps that could appear above the Add Split button.
I’m currently heads down on the Migration Helper, but I will think on this as a Splitter feature.
Thanks for chiming in, @sadermine.
Thank you for taking it into consideration @randy!
Can I safely add a “Notes” column to my transactions? I currently do not have one. When I try to insert a column I get the"Heads Up" message.
You certainly can add that column. I would recommend calling it Note instead of Notes, because other Tiller Sheets using recognize that header name. If there is a warning message about changing a header, just disable that protection for a few minutes.
Thanks for the update. So far I have not experienced any additional rounding issues.
I 2nd the “prorate” request. Also, would love to see the amount field within each category split be a calculated field so I don’t have to pull up a calculator when adding up line items on a receipt…or perhaps there is already a slick way I’m unaware of?
Keep up the great work…love the tool!
@twilsonfam & @sadermine,
Exciting news! I implemented the prorate-remainder feature you requested for the splitter today.
I just published it. Have a look and let me know what you think.
Thanks for the suggestion!
Where can I find the new “prorate-remainder” feature? I created a test transaction, highlighted it, opened the split transaction tool and I don’t see any new feature. Thanks!
@twilsonfam I was able to use the prorate-remainder feature when splitting my transaction. I purposely didn’t split the transaction evenly… before posting the split, I received a message to opt to prorate the remaining balance. Great add!!
Thanks for the tip @warren! I tried the prorate feature with several transactions and split scenarios…it works like a charm! Thanks @randy!