You do not need two sheets. Add a Type column with a dropdown of Business and Personal, total each with SUMIFS, and filter on Business when it is time to hand rows to your accountant. One log keeps the order of events intact, and the Type column does the separating for you.
How should I set up the columns?
Say column A is the date, B the description, C the amount, D the category, E the Type and F Bill kept, a Yes or No. The Type column is the one that counts. Select E2:E500 and add data validation as a list: in Excel it is Data, Data Validation, List, with the source Business,Personal; in Google Sheets it is Data, Data validation, Dropdown. Reject anything outside the list so a typo does not create a third kind of spending.
How do I total each type?
- Business: =SUMIFS($C$2:$C$500,$E$2:$E$500,"Business")
- Personal: =SUMIFS($C$2:$C$500,$E$2:$E$500,"Personal")
- Not yet marked: =SUM($C$2:$C$500)-SUMIFS($C$2:$C$500,$E$2:$E$500,"Business")-SUMIFS($C$2:$C$500,$E$2:$E$500,"Personal")
Add date conditions the same way as always: ,$A$2:$A$500,">="&$H$1,$A$2:$A$500,"<"&EDATE($H$1,1) for the month starting in H1. The third formula is your safety net. It should read zero, and anything else is a row that someone forgot to classify.
What about spending that is part business and part personal?
A phone or internet bill is a common example. Rather than guess a Type for the whole row, add a column G, Business share, with a percentage such as 60%, and a column H, Business amount, with =C2*G2. Say a bill is ₹1,200 and your own estimate of business use is 60%. The business amount is ₹720 and the personal remainder is ₹480. What share is reasonable, and whether it can be treated as a business cost, is for your CA to say, so record your reasoning in a notes column.
| Item | Amount (₹) | Type | Business share | Business amount (₹) |
|---|---|---|---|---|
| Domain and hosting renewal | 3,000 | Business | 100% | 3,000 |
| Home internet | 1,200 | Business | 60% | 720 |
| Family dinner | 2,400 | Personal | 0% | 0 |
| Client coffee meeting | 650 | Business | 100% | 650 |
The business amount column adds up to ₹3,000 + ₹720 + ₹650 = ₹4,370. Use =SUM(H2:H500) or =SUMIFS($H$2:$H$500,$E$2:$E$500,"Business").
How do I pull out only the business rows at filing time?
- Turn on Filter, set Type to Business, and the sheet shows only those rows.
- Select the visible cells and copy. Excel copies only the visible rows of a filtered list.
- Paste into a new workbook or tab and send it as it is.
In Excel 365 or 2021 and in Google Sheets, there is a formula route: =FILTER($A$2:$F$500,$E$2:$E$500="Business") lists the business rows and updates by itself. Older Excel versions do not have FILTER. Sort by date before sending, and keep the original untouched.
Which habits make this work?
- Mark Type on the day you enter the row, not at the end of the year.
- Keep the bill, and tick Bill kept, for any row that is marked Business.
- Pay business costs from a separate account or card if you can, because it makes the rows easier to recognise later.
- Reconcile once a month, comparing your Business total with the account.
If you use more than one bank account or card, add an Account column too, so you can match the sheet to each statement. Then a quick SUMIFS on Account shows what the statement should add up to, and a mismatch tells you a row is missing or typed wrongly, before it becomes a problem at filing time.
When a sheet stops being enough
If you want receipts kept with the transactions, and category and accounting settings in one place, SpendixAI keeps receipts with their transactions, lets you set up custom categories, and has an Export centre with templates for Tally, Zoho Books and QuickBooks, which may save retyping for your accountant. It does not compute tax or file anything.
Before you send anything
- The not-yet-marked cell reads zero.
- Every Business row has a bill or note.
- You have a dated copy of what you sent.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

