How To Use Excel for Accounting in 2023 [+Free Templates]
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 that you use Excel as your primary bookkeeping system. However, it’s a fantastic tool to supplement your bookkeeping platform by performing needed reconciliations and calculations.
We discuss how you can use Excel to enhance your accounting. We also provide several templates, including bank reconciliation, invoice tracker, bills tracker, loan amortization, and general ledger, 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.
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!
An A/R aging report is used by many businesses that invoice their clients to keep track of what payments are due to them. It shows when payments are owed, the amount due, and from which customer. It serves as an effective way to forecast cash flow and know exactly how much you’re owed at any given time.
You can use Excel to generate your invoices, especially if you want unlimited customization options, such as adding your logo and changing fonts. However, you may want to consider using a free invoice generator, which automates the invoicing process. Check out our recommendations for the top free invoice generators.
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.
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.
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. Learn more about it through our review of QuickBooks Online.
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.
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.
Using Excel as a General Ledger
Thank you for downloading!
The general ledger is the collection of all accounts used in the accounting system. All transactions during the period must be posted to an account in the general ledger. However, you can skip journal entries if you only have a few transactions per month and update the accounts in the general ledger.
We recommend using this template if you don’t need to keep detailed records of transactions. It will enable you to keep tabs of all accounts related to your business. To keep things organized, we recommend using one sheet per month so that you don’t mix previous and current month transactions in one sheet.
Instructions on how to manipulate the template are included in the Excel file. Download it and read the instructions carefully.
Importing Data From QuickBooks Online Into Excel
Exporting Reports to Excel in QuickBooks Online
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)
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.
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.
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.
