How to Use Excel for Accounting [+Free Templates]

This article is part of a larger series on Accounting Software.

1. 1 Reconcile Bank Accounts
2. 2 Track Unpaid Invoices (A/R)
3. 3 Track Unpaid Bills (A/P)
4. 4 Track Cost of Goods Sold
5. 5 Create Amortization Schedules
6. 6 General Journal & General Ledger
7. 7 Importing Data From QuickBooks Online
8. 8 When to Use Bookkeeping Software
9. 9 FAQs
10. 10 Bottom Line

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 to Excel? Check out our list of the best small business accounting software.

Brainy has paid for this placement. However, our team of experts approved Brainy as an appropriate product and our content remains editorially independent.

Looking for an affordable bookkeeping solution? Visit Brainy today to work with experienced, licensed CPAs.

• Integrates with QuickBooks, Xero, and other platforms
• Available add-on services including A/R and A/P
• Free consultation and no onboarding/signup fees
• Pricing starts at $195/mo

Using Excel to Reconcile Bank Accounts

FILE TO DOWNLOAD OR INTEGRATE

Free Bank Reconciliation Template

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.

1. Balance per check register (tip: update first your check register before reconciliation).
2. Bank statement ending balance.
3. Deposits in transit, which are deposits you’ve recorded in your check register that aren’t shown on the bank statement.
4. 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)

FILE TO DOWNLOAD OR INTEGRATE

Free Invoice Tracker Template

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.

• 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)

FILE TO DOWNLOAD OR INTEGRATE

Free Bills Tracker Template

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 are on any past-due accounts.

• Bill Number
• Bill Date
• Status
• Vendor Company Name
• Bill Total

Using Excel to Track Cost of Goods Sold

FILE TO DOWNLOAD OR INTEGRATE

Free Inventory & COGS Tracker Template

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 on whether you’re using the average cost (AVCO) method or the 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 expenses 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.

1. 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.
2. Date: It refers to the date when payment is due.
3. Beginning Balance: It’s the balance of the loan at the beginning of the payment period.
4. 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.
5. Interest: It’s the amount of interest expense based on the beginning balance of the loan.
6. Principal Amortization: It’s the portion of installment payments that is deducted from the beginning balance of the loan.
7. Ending Balance: It’s the balance of the loan after deducting the principal amortization.

Using Excel as a General Journal & General Ledger

FILE TO DOWNLOAD OR INTEGRATE

General Journal & General Ledger Template

Thank you for downloading!

The general journal (GJ) and general ledger (GL) are the two most important records in accounting. All transactions in a business are recorded in the GJ and summarized in the GL. Without these two books, it’s almost impossible to have an organized accounting system.

We recommend using this template if you don’t need to keep detailed records of transactions. It will enable you to keep tabs on all accounts related to your business. If you don’t have accounting knowledge, you’ll have a hard time using this template. You must let someone with accounting experience use this sheet for tracking your accounts. 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: 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

• 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 formulas and functions

Frequently Asked Questions (FAQs)

Yes, but it’s not recommended. If you’re a very small business, using Excel is cost-effective. However, 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.