How To Use Excel for Accounting in 2023 [+Free Templates]
Tim is a Certified QuickBooks Time (formerly TSheets) Pro, QuickBooks ProAdvisor, and CPA with 25 years of experience. He brings his expertise to Fit Small Business’s accounting content.
Eric is a staff writer at Fit Small Business and CPA focusing on accounting content. He spends most of his time researching and studying to give the best answer to everyone.
This article is part of a larger series on Accounting Software.
Microsoft Excel is an invaluable tool for accountants to perform complex mathematical calculations that traditional bookkeeping software doesn’t provide. We don’t recommend you use Excel as your primary bookkeeping system as there are several free accounting software to choose from.
However, it’s a fantastic tool to supplement your bookkeeping system by performing needed reconciliations and calculations. We discuss how you can use Excel to enhance your accounting and fill in the gaps left by many free software and provide templates to assist you.
Are you looking for alternatives? Check out our list of the best small business accounting software.
Using Excel to Reconcile Bank Accounts
Thank you for downloading!
A bank reconciliation is essential to keep your bank and book balances on the same page. Due to timing differences, bank and book balances don’t always agree. Most accounting software has bank reconciliation features, but they always come with the paid or premium version.
We prepared our Excel bank reconciliation template in case you want to use free accounting software that lacks bank reconciliation features. With our file, you can reconcile the check register balance and bank statement balances by adjusting them for deposits in transit and outstanding checks.
Reconciling bank accounts in Microsoft Excel is possible and doable only if you have a few transactions to process. Using Excel for bank reconciliation becomes challenging and inefficient if you have a high volume of transactions. Hence, we recommend choosing accounting software if you need to reconcile many transactions from different accounts. Using Excel may significantly affect the efficiency of the accounting department and cause unseen errors during the process.
How To Use Our Bank Reconciliation Template
To prevent any problems with Excel formulas in the sheet, you only need to fill out boxes in blue. You’ll need to gather the following information:
- Balance per check register (Tip: Update first your check register before reconciliation).
- Bank statement ending balance.
- Deposits in transit, which are deposits you’ve recorded in your check register that aren’t shown on the bank statement.
- Outstanding checks which are checks recorded in your check register that aren’t shown on your bank statement.
The template matches the adjusted bank balance with the balance per check register. If it’s not equal, the template will show you the difference. Once reconciled, you’ll see the word “RECONCILED.”
Using Excel to Track Unpaid Invoices (A/R)
Thank you for downloading!
Microsoft Excel can track amounts and due dates for customer receivables. We’ve created an Excel spreadsheet that can provide invoice information per customer as well as an A/R aging report. This can serve as a good invoice database if you don’t need to process many invoices monthly.
We don’t recommend creating invoices in Excel because there are many high quality free invoice generators like Zoho Invoice. Aside from invoice creation, you can send them to customer emails within the app.
How To Use Our Invoice & A/R Tracker Template
After downloading the template, go directly to the CustomerList sheet. Replace all the information in the sheet with your actual customers. Once done, go to the InvoiceData sheet.
Items you can delete and replace with actual data in the InvoiceData sheet:
- Invoice Number
- Invoice Date
- Status
- Company Name
- Invoice Total
The Customer Name and Days Overdue columns cannot be replaced and deleted—these cells contain the formulas. After updating the sheet, go to the Customer Tracker or A/R Aging sheets. Right-click on the table and click Refresh to reflect your changes.
Using Excel to Track Unpaid Bills (A/P)
Thank you for downloading!
The process of tracking how much money is owed to vendors, when the bills are due, and when they have been paid is known as A/P. If you aren’t diligent with paying your vendors on time, you can end up with bad credit and a lack of supplies, among other things. Creating an A/P aging file in Excel will help you monitor your payable accounts so that you know which bills you must pay first and how behind you’re on any past-due accounts.
How To Use Our Bill & A/P Tracker Template
After downloading the template, go directly to the VendorList sheet. Replace all the information in the sheet with actual vendors, then go to the BillData sheet.
Items you can delete and replace with actual data in the BillData sheet:
- Bill Number
- Bill Date
- Status
- Vendor Company Name
- Bill Total
Using Excel to Track Cost of Goods Sold
Thank you for downloading!
The main challenge of inventory accounting is determining the cost of inventory on-hand at the end of the year versus the cost of inventory sold during the year, also known as cost of goods sold (COGS). Tracking COGS is crucial in determining net income. With our COGS tracker, you can compute the most recent COGS balance of a particular inventory item and determine individual inventory gross profits or cost per unit.
The computation of inventory ending balance and COGS depends if you’re using the average cost (AVCO) method or first in, first out (FIFO) method. For our free template, we use the AVCO method because implementing the FIFO method in Excel requires complex functions.
How To Use Our Inventory & COGS Tracker Template
The template above uses the AVCO method of inventory costing. You should only fill out the yellow cells, and Excel will compute the rest. Don’t touch the white cells because these contain formulas that compute certain values. Remember that this template assumes that you’re only tracking one inventory product. If you have several inventory products, copy the sheet and rename it, such as sheet 1 = “Light Bulbs.”
Tracking inventory in a spreadsheet is extremely time-consuming, especially if you have a lot of different products to track. QuickBooks Online Plus will track inventory automatically and split costs between COGS and ending inventory.
Using Excel to Create Amortization Schedules
Thank you for downloading!
A loan amortization schedule shows you the schedule of payments, the amount of interest you pay, and the amount that gets deducted from the principal balance. You can use the amortization table to record monthly interest expense and update loans payable in the books. Most accounting software doesn’t have an amortization schedule feature. Our Excel template can help you prepare journal entries to update account balances.
How To Use Our Loan Amortization Template
The template is a dynamic amortization table, and it adjusts based on the number of payment periods and terms. All you need to do is fill out the yellow cells and Excel will generate the table automatically. Take note that the maximum loan term in this template is 40 years. The amortization table contains the following information:
- Period: It is the ordinal number corresponding to the number of payment periods. For example, period 1 pertains to the first payment while period 6 pertains to the sixth payment.
- Date: It refers to the date when payment is due.
- Beginning Balance: It’s the balance of the loan at the beginning of the payment period.
- Payments: It’s the installment payment you need to pay based on the payment frequency. This cell changes dynamically if you choose a payment frequency.
- Interest: It’s the amount of interest expense based on the beginning balance of the loan.
- Principal Amortization: It’s the portion of installment payments that is deducted from the beginning balance of the loan.
- Ending Balance: It’s the balance of the loan after deducting the principal amortization.
Importing Data From QuickBooks Online Into Excel
QuickBooks Online can export any of its reports to Excel, which is useful as you can pull sales data, banking data, and invoices from many sources into one central workbook to support your accounting activities. Exporting the report is simple and can be done with the following steps:
Exporting Reports to Excel in QuickBooks Online
Step 1: Select Reports in the left sidebar.
Step 2: Search for and select the report that you want to export.
Step 3: Scroll to the top of the report to adjust the time period, accounting method, and other preferences, if desired.
Step 4: Click the Export icon in the top right corner of the report, then select Export to Excel. You also have the option to save the report as a PDF.
Step 5: Save the report in a location that you’ll be able to find easily, such as your desktop.
Here’s a sample Profit and Loss report exported to Excel:
Sample Profit and Loss (P&L) Statement exported to Excel from QuickBooks Online
If you’re missing data when you open the report in Excel, it’s likely that the file is in a protected view. Select Enable Editing in Excel to view the full report.
When To Use Bookkeeping Software
While there are many accounting uses for Excel, we don’t recommend using it as your primary bookkeeping software to track your cash flow and classify your income and expenses. While it can be done, it’s much simpler to use one of the many free accounting software that are available in both cloud and desktop formats.
Basic bookkeeping software makes it easy to maintain detailed and accurate books. In addition to automating bookkeeping tasks, such as sending invoices (A/R) and paying bills (A/P), many programs use double-entry accounting, enable you to generate financial reports, provide some form of technical support, integrate with business apps, and can scale with your business.
Unlike bookkeeping software, Excel won’t be able to:
- Generate reports based on the data from your books automatically
- Connect to your business bank accounts or credit cards and import and sort transactions automatically
- Create workflows to simplify accounting tasks because you still have to input formula and functions
Frequently Asked Questions (FAQs)
Can I use Excel for bookkeeping?
Yes, but it’s not recommended. If you’re a very small business, using Excel is cost-effective. But for small businesses with complex processes, Excel can be counterproductive and difficult to use. We recommend getting dedicated accounting software instead.
How do accountants use Excel?
Accountants and bookkeeping professionals use Excel to perform complex calculations, like amortization tables and depreciation schedules. They also use it to compute product cost, allocate overhead, and perform budgeting and forecasting.
Is QuickBooks better than Excel?
Overall, QuickBooks is better than Excel for accounting. It’s also easier to use because you don’t need to create formulas to perform calculations and process data. Excel requires users to have in-depth knowledge of formulas and even a little bit of programming to create automations.
Bottom Line
While using Excel for accounting tasks can be beneficial and inexpensive, it can also be a time-consuming process with the potential for errors. If you’re looking for a simpler and much more efficient way to manage your accounting, we recommend exploring different accounting software solutions to identify the one that’ll work best for you. Check out our list of the best small business accounting software for guidance.
