Can You Do Bookkeeping in Excel? A 5-Sheet Template, Ready-Made Formulas, Excel's Limits and the Point to Switch to Bookkeeping Software
Can bookkeeping be done in Excel? A 5-sheet template, account and cash formulas, Excel's 7 limits, the hours lost to double entry and the threshold for switching to software.
Contents 11 sections
- Can bookkeeping be done in Excel?
- How do you set up a bookkeeping template in Excel?
- Which formulas calculate account, cash and inventory balances in Excel?
- What limits can Excel not overcome in bookkeeping?
- How many hours a month does double entry in Excel cost?
- When should you switch from Excel to bookkeeping software?
- How do you switch from Excel to bookkeeping software?
- What are the most common mistakes in bookkeeping with Excel?
- How does switching from Excel to bookkeeping software work with NetKasam?
- Conclusion: Fix the template first, then measure the hours
- Frequently Asked Questions About Bookkeeping in Excel
Most businesses starting bookkeeping take the first step in the same place: an empty Excel file. Invoices go on one sheet, customers on another, cash on a third. In the first months, this setup is enough, even fast. The problem is that the file quietly breaks down as the business grows: the same sale is written both in the invoice portal and in the spreadsheet, a formula slips to the wrong row, two people open the same file at the same time and one overwrites the other's entry. The honest answer to the question can bookkeeping be done in Excel is "yes, up to a point." What matters is knowing where that point is.
In this article, we first give you a properly built Excel bookkeeping template in five sheets with ready-made formulas; in other words, if you are going to stay with Excel, we show you the most robust way. Then we explain the seven limits Excel cannot overcome in bookkeeping, how many hours a month double entry costs with an example, and at what threshold switching to bookkeeping software makes sense. For the transactions bookkeeping covers, see the article what are bookkeeping transactions.
Can bookkeeping be done in Excel?
In short: Yes. If the number of invoices is small, one person does the recording and there is a single sales channel, orderly bookkeeping can be kept in Excel. What Excel cannot do is issue invoices, automatically update inventory and accounts from invoices and let more than one person work safely on the same records.
Excel is a spreadsheet; it does not know accounting rules. When you enter a sales invoice, the customer's balance owed does not increase, inventory does not decrease and, when a collection comes in, the balance does not close on its own. You have to build these links with formulas and check every month that those formulas have not broken. Software, on the other hand, comes with these links built in from the start.
Excel's advantages are real too: it costs nothing or very little, everyone knows it a bit and you can add any column you want. For a newly founded business issuing a few invoices a month, Excel is a reasonable start. The problem is not starting in Excel but staying there after passing the point where Excel is no longer enough.
How do you set up a bookkeeping template in Excel?
In short: A robust Excel template consists of five sheets: Accounts, Products, Transactions, Balances and Checks. All transactions are written row by row only on the Transactions sheet; the other sheets are fed from those rows with formulas. This way, every figure has a single source.
The most common template mistake is opening a separate sheet for each transaction type: "Invoices," "Collections," "Cash," "Bank." In this setup, a collection is written on both the collections sheet and the cash sheet, and sooner or later the two sheets stop matching. In the right setup, every transaction is a single row, and the "Type" and "Account" columns in the row tell you what that transaction affects.
| Sheet | Contents | Who writes it |
|---|---|---|
| Accounts | Account code, title, tax number, type (customer/supplier), opening balance | Manually, as new accounts come in |
| Products | Product code, name, unit, opening stock | Manually, as new products come in |
| Transactions | Date, document no, type, account code, product code, quantity, amount including VAT, VAT rate, account (cash/bank), due date | Manually, one row per transaction |
| Balances | Account balance, cash, bank, stock quantity | Formulas only |
| Control | Alerts for missing fields, duplicate document numbers, negative stock | Formulas only |
The "Type" column on the Transactions sheet should be selected from a fixed list (a drop-down list with Data Validation): Sale, Sales Return, Purchase, Purchase Return, Collection, Payment, Expense, Transfer. If free text is allowed, one row gets "Collection" and another "Collection " (with a trailing space), and the formula does not see the second one.
Which formulas calculate account, cash and inventory balances in Excel?
In short: Balances are calculated by summing the amounts on the Transactions sheet by type, account and cash/bank; for this, SUMIFS (ÇOKETOPLA in Turkish Excel) is used. On the Checks sheet, COUNTIFS (ÇOKEĞERSAY) catches duplicate and missing records.
In the formulas below, the columns of the Transactions sheet are assumed as follows: C = Type, D = Account code, E = Product code, F = Quantity, G = Amount including VAT, H = VAT rate, I = Cash/bank account, B = Document no. In Turkish Excel, arguments are separated by semicolons.
- Customer balance = Opening + SUMIFS(Transactions!G:G; Transactions!D:D; A2; Transactions!C:C; "Sale") − SUMIFS(…; "Sales Return") − SUMIFS(…; "Collection")
- Cash = Opening + SUMIFS(G:G; I:I; "Cash"; C:C; "Collection") − SUMIFS(G:G; I:I; "Cash"; C:C; "Payment") − SUMIFS(G:G; I:I; "Cash"; C:C; "Expense")
- Stock quantity = Opening + SUMIFS(F:F; E:E; A2; C:C; "Purchase") + SUMIFS(…; "Sales Return") − SUMIFS(…; "Sale")
- Amount excluding VAT = G2 / (1 + H2), for example 1,200 / 1.20 = 1,000
- Duplicate document alert = IF(COUNTIFS(B:B; B2; C:C; C2) > 1; "DUPLICATE"; "")
For a transfer (depositing cash into the bank), two rows are written on the Transactions sheet: an outflow of type "Payment" from cash and an inflow of type "Collection" to the bank, with the account code left blank on both. Alternatively, a "Transfer" type can be created and added separately to the cash and bank formulas; what matters is that the transfer does not mix into any account balance or the revenue total.
If you want to check VAT calculations without writing formulas, you can use the VAT calculator. For how to verify an account balance with the other party, see the article how to track a current account.
What limits can Excel not overcome in bookkeeping?
In short: Excel cannot issue invoices, so every sale is written twice; more than one person cannot work safely in the same file; there is no change history or permission control; inventory does not drop instantly across multiple sales channels; and closing collections against invoices and aging receivables is hard with formulas.
- It cannot issue invoices. e-Invoices and e-Archive invoices are issued in a separate system; the same sale is written there first and then in Excel. If the amount or date is changed on one side and forgotten on the other, the two records diverge. For invoice types, see the article what is an e-Invoice, what is an e-Archive invoice.
- Multiple users are not safe. When two people work on copies of the same file, "the last one to save wins"; the rows entered by the other may be lost. Even with shared editing, the same row can be written to at the same time.
- There is no permission control. Anyone who can open the file can change any cell; both the cash report and account opening balances are open to everyone.
- There is no change history. When an amount is changed or a row is deleted, you often cannot see who changed what and when.
- Inventory does not drop instantly across channels. If you sell the same product in your store, on your own site and on a marketplace, the stock in Excel is updated manually after the sale; in the meantime, the same product can be sold in two channels at once.
- Closing against invoices is hard. When a customer has paid two of their 3 invoices, tracking with formulas which invoice remains open and how many days it has been waiting gets complicated; receivables aging is usually done by hand. For the method, see how to track receivables.
- Formulas break silently. A row is added outside the range, a cell is overwritten by hand, a column is shifted; the formula does not show an error but sums incorrectly. That is why an Excel setup creates a separate monthly job called "formula maintenance."
How many hours a month does double entry in Excel cost?
In short: The cost of double entry is the number of invoices multiplied by the time it takes to transfer them to the spreadsheet; add to this formula maintenance and the time spent hunting for mismatched records at month end. In the sample business, these three items make up 18 of the 47 hours of monthly bookkeeping work.
Example: a small stationery wholesaler with one physical store and one marketplace store issues 300 sales invoices a month and processes 80 purchase invoices and 360 bank transactions. It keeps its books in Excel. Suppose the times were measured in the business over one week: issuing a sales invoice in the invoice system takes 3 minutes, transferring the same invoice to Excel 2 minutes, entering a purchase invoice 3 minutes and matching a bank transaction 1 minute. The figures were chosen only to illustrate the method.
| Task | Account | Excel setup | Software setup |
|---|---|---|---|
| Issuing sales invoices | 300 × 3 min | 15 hours | 15 hours |
| Transferring invoices to the spreadsheet | 300 × 2 min | 10 hours | None |
| Entering purchase invoices | 80 × 3 min | 4 hours | 4 hours |
| Bank matching | 360 × 1 min | 6 hours | 6 hours |
| Formula maintenance | Fixed monthly | 4 hours | None |
| Month-end checks and hunting for differences | Fixed monthly | 8 hours | 4 hours |
| Total | 47 hours | 29 hours |
The software column in the table is a cautious assumption: invoicing, purchase entry and bank matching times were left unchanged. The difference comes from only three items: invoices not being written a second time, formula maintenance disappearing and month-end difference hunting being cut in half because two separate records no longer diverge.
- Monthly bookkeeping in the Excel setup47 hours
- Writing invoices into the spreadsheet a second time−10 hours
- Formula maintenance−4 hours
- After double entry is removed33 hours
- Month-end difference hunting cut in half−4 hours
- Monthly bookkeeping in the software setup29 hours
Prepared with sample values. Additional gains, such as automatic invoicing of marketplace orders, are not included.
The 18-hour difference in the example is more than two working days of a full-time employee. More important than the hours is the risk of errors: in any setup where the same sale is written in two places, a month will inevitably come when the two records do not match, and that month's difference is hunted down by opening documents one by one.
- Issuing sales invoices15 hours31.9%
- Transferring invoices to the spreadsheet10 hours21.3%
- Month-end checks and hunting for differences8 hours17%
- Bank matching6 hours12.8%
- Entering purchase invoices4 hours8.5%
- Formula maintenance4 hours8.5%
Prepared with sample values. Items tied to double entry (transfer, formula maintenance, half of difference hunting) total 18 hours.
When should you switch from Excel to bookkeeping software?
In short: If two or more of the following signs apply to your business, it is time to switch: you issue invoices regularly, more than one person records entries, you sell through more than one channel, you sell on credit or the month-end check takes longer every month.
| Situation | Excel is enough | Software is needed |
|---|---|---|
| Invoicing | A few invoices a month | Regular invoices every day or every week |
| Person recording entries | One person | Two or more people |
| Sales channel | One channel | Store, website and marketplace together |
| Sale type | Cash sales | Credit sales, many customers |
| Inventory | Few products, or a service business that sells no products | Products with variants, more than one warehouse |
| Month-end | Records match on the first try | Hunting for differences takes hours every month |
The criteria in the table are guidance, not a hard limit. A practical test: in the last three months, how many months did a balance in your Excel file (cash, bank or a customer) not match reality, forcing you to hunt for the difference? If the answer is more than one, the problem is not attention but the setup.
How do you switch from Excel to bookkeeping software?
In short: The switch is made by choosing a cutover date: the account, cash, bank and inventory balances on that date are taken from Excel and entered into the software as opening balances, and every transaction after the cutover date is recorded only in the software. For a short time, the two setups can be run in parallel and the results compared.
- 01Cutover dateA clean date such as the start of a month is chosen; it should be the same day as in your certified public accountant's records.
- 02CleanupDuplicate accounts and products in Excel are merged and codes are made unique.
- 03List importAccount and product lists are imported into the software.
- 04Opening balancesAccount, cash, bank and inventory balances at the cutover date are entered and compared with Excel.
- 05Single recordAfter the cutover date, every transaction is made only in the software; Excel becomes a read-only archive.
The most common switching mistake is trying to re-enter all past months' transactions into the software without entering opening balances; it takes a long time and carries old errors into the new system. Past transactions stay in Excel as an archive, and only balances are opened in the software. Detailed steps are in the article how to transfer data when changing accounting software.
- 5-sheet Excel template; all transactions on one sheet
- 47 hoursof monthly bookkeeping work in the Excel setup
- 18 hoursspent on double entry, formula maintenance and hunting for differences
- 38%less time in the software setup (from 47 to 29 hours)
What are the most common mistakes in bookkeeping with Excel?
In short: The most common mistakes are opening a separate sheet for each transaction type, typing type and account codes as free text, overwriting formula cells by hand, working with copies of the file and not keeping backups.
- A separate sheet for each transaction. A collection is written on both the collections sheet and the cash sheet; the two sheets diverge. Use a single Transactions sheet.
- Free-text type and account. "Collection" and "collection " or "ABC Ltd" and "ABC Ltd. Şti." look like two different values to the formula. Use a drop-down list and account codes.
- Overwriting a formula by hand. The correct figure is typed by hand into a cell whose balance does not match; the formula is lost and the next month continues incorrectly. Lock the Balances sheet.
- Working with file copies. "muhasebe_son.xlsx," "muhasebe_son2.xlsx" and copies sent by email; no one knows which one is current.
- No backups. When the file on a single computer gets corrupted or the computer breaks down, months of records are lost.
- Mixing amounts including and excluding VAT. One column sums amounts including VAT, another excluding VAT; the sales total is inflated or understated. Choose a single amount type and separate VAT with a formula.
- Not comparing the invoice system with the spreadsheet. If the total in the invoice system and the sales total in Excel are not compared at month end, invoices that were not written a second time go unnoticed.
How does switching from Excel to bookkeeping software work with NetKasam?
In short: In NetKasam, e-Invoices and e-Archive invoices are issued in the same panel; an issued invoice updates accounts and inventory together, so double entry disappears. Customer and product lists are imported with the help of Excel, and reports can be exported in Excel format. Bookkeeping is always free.
Double entry, the source of the 18 hours in the example, is solved by issuing invoices in the same place as bookkeeping. An invoice issued with the free e-Invoice system is reflected directly in customer and supplier account and inventory records. The number of users is unlimited, different permissions can be assigned to each user and all actions are logged; this closes Excel's 2nd, 3rd and 4th limits.
When switching, you can import your customers and products from the file you use with the help of Excel; you can also export reports in Excel format. So Excel is not abandoned completely; it stops being a data entry tool and becomes an analysis tool. If you sell on marketplaces, orders are collected on one screen and invoiced automatically; this is an additional gain we did not count in the example. Plans and prices are on the free bookkeeping software page.
Frequently Asked Questions About Bookkeeping in Excel
Can bookkeeping be done in Excel?
Yes, when the number of invoices is small, one person does the recording and there is a single sales channel, orderly bookkeeping can be kept in Excel. Because Excel cannot issue invoices or automatically update accounts and inventory from invoices, the risk of double entry and errors grows as the business grows.
What sheets should an Excel bookkeeping template have?
Accounts, Products, Transactions, Balances and Checks. All transactions are written only on the Transactions sheet; balances and checks are calculated from this sheet with formulas.
Which formula calculates an account balance in Excel?
With SUMIFS (ÇOKETOPLA in Turkish Excel): the account's sales are added to the opening balance, and returns and collections are subtracted. For the formula to work correctly, the type and account code must be selected from a drop-down list.
Can you issue an e-Invoice with Excel?
No. e-Invoices and e-Archive invoices are issued through an e-Invoice system; Excel can only be a table that holds invoice details. That is why, in an Excel setup, every sale is written twice.
How long does switching from Excel to bookkeeping software take?
The scope depends on the number of accounts and products. When past transactions are not re-entered and only the balances at the cutover date are opened, the switch is usually completed in a few days; the longest job is cleaning up duplicate records in Excel.
Is Excel abandoned completely after switching to software?
No. Excel stops being a data entry tool and becomes a tool for analyzing reports. Past months remain in Excel as an archive.
Can data be imported into NetKasam from Excel?
Yes. You can import your customers and products from the file you use into NetKasam with the help of Excel; you can also export reports in Excel format.
Is NetKasam paid?
Bookkeeping is always free: e-Invoice, e-Archive invoice, customer and supplier accounts, inventory, cash, bank, expenses and collections are included in the free plan. Only e-Invoice credits and the marketplace and e-commerce integration plans are paid.
Conclusion: Fix the template first, then measure the hours
If you are going to stay with Excel, convert your file to the five-sheet setup this week: gather all transactions on one sheet, tie type and account codes to drop-down lists and calculate balances with formulas. Then, for one month, note the hours spent on double entry, formula maintenance and month-end difference hunting. If that number reaches several days of a full-time employee's time, you have passed the point where Excel is enough.
Bookkeeping Is Now Completely Free
Manage your bookkeeping and e-Invoice operations with NetKasam without paying a thing. Customer and supplier accounts, inventory, collections and more in one panel, completely free.