Accounting Excel Template

Pin this to your board!

This free Accounting Excel Template is perfect for tracking your business income, expenses and bank balance and for viewing financial reports which update automatically when you enter your transactions into each month. 

This template has a similar layout as our very popular Excel Cashbook, but in this template you can record sales tax and track the balances of two different bank accounts and one credit card within each month.

If you are new to Excel I highly recommend you do this free 5 hr course (book with videos) by Mike Smart. Make sure to select the version that matches your version of Excel. It's well worth it.

Even if you have used Excel for years, I still recommend doing his free basic course as a refresher, you just might pick up some golden nuggets of information and shortcuts that you weren't aware of - I know I did. If you enjoy it you can then follow on with his courses for more advanced Excel skills at a low cost ($19.99). (Note, this is not an affiliate link, I do not make any money out of this if you purchase the advanced courses).

Download and save Accounting excel Template

You can look at screenshots further down this page before deciding if you want to download it.

Accounting Excel Template DownloadSelect / click on your month below

February

March

June

July

September

December

One
To download the template, select (click-on) the financial year-end month that applies to your business in the column on the left next to this one. 

If your financial year ends in March, download the March template. If your financial year ends in June, download the June template.   And so on.

If you know what your year is but it's not on the list, use the contact form and I will prepare and upload your year-end to this page

If you don’t know what your financial year end is then use the December template. 

Two
Once you have selected your month, it will automatically download into the Downloads folder on your computer. Go and check that folder to access the downloaded template.

Three
Open the template and save it by going into ‘File’, ‘Save Copy’ or 'Save As' in Excel and give it a name of your choice and save it to your digital filing system.

Please note, if you insert new rows into the monthly tabs, the sales tax report will not be accurate because the source data has not been set up to include new rows. You must manually unhide the Sales Tax Data sheet (there is no password to unhide it), insert the number of new rows required in the correct month, and enter the formulas (copy from ones already in there) into those rows to pick up data from the monthly tabs.

More detail on what this accounting excel template offers

If you want a template without sales tax you can try our Excel Cash Book which is also free.

  • It includes sales tax columns for both income and expenses – go to the Control page, enter your sales tax rates and then on the monthly tabs simply select the sales tax percent from the drop down box for each transaction that applies to you depending on what country you live in.  The drop down box is linked to the Rates box on the Control page.

  • You can run a sales tax report (using Pivot Tables) for the months of your choice. 

  • In each month you can enter transactions for two bank accounts and one credit card, for example:-
  1. a checking account
  2. a savings account
  3. a business credit card

This saves you having to have three separate workbooks for each bank account | credit card.

This Accounting Excel Template is just like a cashbook and the money received and money spent transactions should be a reflection of what happens on your bank account. 

Extra Helpful Instructions

Here are printable PDF instructions for updating the sales tax report :

  1. PIVOT TABLE DATA SOURCE LINK
    Often, when an Excel file containing a pivot table report is renamed, the pivot table information in the new file will still point to the original file data and not the new file data, so the link needs to be redirected to the new file. These instructions will tell you how to update it. 

    >>DOWNLOAD INSTRUCTIONS

  2. EXTRA ROWS IN DATA SOURCE
    The Sales Tax Report gets its information from one data source within the template. When you insert extra rows into each month because you have more transactions than the available rows in the template, the data source does not automatically update with these new rows. You must manually update the data source worksheet to include these new rows. These instructions will tell you how to do that. 


    >>DOWNLOAD INSTRUCTIONS

step by step using this template

  1. Download and save (the download links are up above).
  2. Explore the 20 different tabs / worksheets
  3. Look at the sample page
  4. Learn how Sales Tax in this template works
  5. Name your accounts on the Control tab
  6. Enter a pretend transaction in the first month 
  7. Check out how it affects the reports
  8. About inserting more rows and columns
  9. How the Opening and Closing Balances work
  10. Starting a fresh template in your second financial year.

When you look through the workbook, there are many cells that have a 0.00 value showing. These cells have pre-set formulas that we have entered there. Take care not to accidentally delete them.

Explore the tabs along the bottom

All the WorksheetsAccounting Excel Template Tabs/Worksheets
  • The first two tabs (also called Worksheets) About and Links pages contain helpful links to the Beginner Bookkeeping website. When you first download and open the template, you might get an Excel message about automatic updating of external links - it is these links Excel refers to - it is safe to Enable the Content.

  • The second tab is the Sample page which gives you an idea of how to enter transactions into the cells of the template.

  • The Control tab is where you update the names of all the accounts you want to have in the template – so you can name five different streams of income, and 20 different expense types. These accounts are also known as Ledger Accounts and are found on the Chart of Accounts.

  • The next 12 worksheets are months Jan to Dec (the order of the months will depend upon what Template you downloaded for your financial year-end). These monthly worksheets are where the action happens! Here you will enter all your daily transactions of money received and money spent.

  • Then you have the Inventory sheet where you can enter your opening and closing balances. We have a free Excel Inventory Template which will help you keep track of your daily stock levels and calculate what those opening and closing balances should be.

  • Next is the Profit and Loss report (aka Income Statement) – a very important report to tell you if your business is making a profit or a loss.

  • After that is the Balance Sheet which gives you a snapshot of the financial position of your business.

  • Finally, you get a Sales Tax Report so you can see the total Sales Tax figures for each tax rate on your Income and Expenses.

sample page

At the top of this page are the headings which apply to both bank accounts and the credit card in each month.

In the screenshot below you can see:-

  • Three Details columns – Date, Description and Reference - these are the same for both Money Received and Money Spent

Money In Columns

  • Four Sales Tax columns for the income
  • Three income columns (there are more on your working pages - unused columns have been hidden on this example page to keep it smaller.
  • Four Other Funds received columns
  • Total Money Received In

...then, within the template, scroll to the right-hand side to see the columns that relate to the Money Out section - see screenshot below.

Sample Page of workbookAccounting Excel Template Sample Page 'Money In' Section

Money Out Columns

  • Four Sales Tax columns for the expenses 
  • Four Cost of Goods Sold columns 
  • Four Expenses columns (there are many more on your working pages - unused columns have been hidden on this example to keep it smaller) 
  • Five Other Funds Paid Out columns and 
  • a Total Money Paid Out column
Money out columns and accounts.Accounting Excel Template Sample Page 'Money Out' Section


Funds Available

This column will show you how much money is in your bank accounts or credit card on any given day after you have entered your daily transactions.

It calculates automatically so you don’t have to. 

The Funds Available formula is the bank balance from the row (day) before, plus total money received in, minus total money paid out.

how the sales tax works

Sales Tax Columns

Income Transaction Amount

In this column you will enter the full amount of each transaction including sales tax.

Sales Tax Rate
In this column you can click on the selector arrow (this becomes visible when you click on the cell), and a list will pop up with the sales tax rates that you must enter into the Control sheet. Choose the rate that applies to your transaction. 

Sales Tax Amount
The cells in this column have a formula that will automatically calculate the sales tax amount based on the information you allocated in the first two columns discussed above.

Allocate to Income or Other Funds
The cells in this column also have a formula that will automatically calculate the amount excluding sales tax. You must manually enter the resulting amount into one of the Income or Other Funds Received columns.

It works the same with the Sales Tax for the expenses columns to the right of the spreadsheet.

Sales Tax Report

You must first refresh this page so that the report includes any new data you just entered. Instructions on how to refresh it are in the worksheet.

This report is in the tab next to the Balance Sheet. 

Once you have entered your transactions into the current month you are working on and all your sales tax rates, go to the Sales Tax Report.

You can select the month or months totals that you want to view by clicking on the small filter icon in Cell D4 and Cell G4 – select the months of your choice and click OK.

Sales Tax ReportAccounting Excel Template Sales Tax Report

NOTE: we do not teach how to process sales tax in your country because the sales tax process is different in every country. If you do not know what your sales tax rates are, you need to search the internet or ask your friends and business associates. We do however, teach you how to enter the rates and values into this template and to view the report.


control worksheet

Control AccountsAccounting Excel Template Control

Here, you enter the name of your business and the names of the headings (aka 'accounts)

You can re-name the headings titled ‘Head 1, Head 2’ and so on through to the expenses side (not seen in this screenshot) – these are your ‘accounts’. If you earn money from consulting, then you could rename Head 1 ‘Consulting Fees’. If you also earn money from selling products, then you could rename Head 2 ‘Product Sales’.

You can also re-name the pink or yellow shaded headings that already have names if you know what you are doing and want a different name for them. 

To rename any of the accounts, click in the header cell and type away, then hit enter on your keyboard. This will write-over the current wording and replace it with yours.

Enter in your Sales Tax Rates.

Note: You cannot use this control page to update the font, or column sizes or background shading for all the monthly tabs. You have to go into each monthly tab to do that.


When you enter information into the Business Name cells and the Heading Cells it will update all the same information in each month Jan to Dec. 

The Monthly tabs

The monthly worksheets are where the day to day action happens!

For this explanation, we will use the January month. Below is a screenshot - we can't fit the whole worksheet into the image so you can just simply download the Accounting Excel Template to look at it properly.

Blank Version of JanuaryAccounting Excel Template January Worksheet

You will see in red writing it says ‘Bank Account #1’ – you can rename this, if you haven’t done so already, to a name of your choice by going on the Control page – see Cell F11.

Explore this page by scrolling down and scrolling right so you can see all there is to see.  

Scroll all the way down to Row 172 – this is the final row and is the total of all three bank/credit accounts combined. Do not delete this row because it is linked to the P&L and Balance Sheet.

Bank Account #1 is color coded blue

Bank Account #2 is color coded green

Credit Card Account is color coded light orangy-pink (there must be a proper name for that color, but I don’t know!!)


Practice a pretend transaction

Enter a pretend transaction into the first bank account so you can see what happens on this page. 

  • Enter a date a Description an amount in the Income transaction column 
  • select a pretend sales tax rate
  • enter the amount that has to be allocated into one of the income columns 
  • scroll to the right and see that the Total Money Received In column (Column ‘U’) has updated as well as the ‘Funds Available column (Column ‘BF)
  • check out the P&L and Balance Sheets to see what has occurred
  • once you’ve played around a bit and become familiar with the layout, you can delete any pretend transactions you have entered (just be careful not to ‘delete’ the formulas in the columns that have formulas.)


Inserting Rows or columns

If you need more rows or columns you can insert some.

Inserting Rows Watch our videos to see how to insert rows and columns in Excel; this is based on our Excel Cashbook but is similar for this Accounting Excel Template. Make sure you copy and paste formulas from one of the original rows into your new rows that you have inserted. Make sure the totals along the bottom of the spreadsheet include the new rows – you can do this by updating the formula.

Inserting Columns – When adding more columns, you are in effect adding new accounts to your spreadsheet. The totals of these columns are linked to the Profit and Loss Report and Balance Sheet. When inserting new account columns, you need to link them to the P&L and the Balance Sheet manually – this requires inserting new rows into each report and linking the cells in each month on these reports to the totals on each monthly tab....

If inserting columns sounds too hard you could ask yourself “do I really need to insert new columns?” Are you instead able to combine certain income or expense types into one account column that already exists in the spreadsheet?

For example, stationery or printing costs could be combined into one column called “Printing & Stationery”.  Vehicle repairs and fuel purchases could be combined into “Vehicle Costs”.

the opening and closing balances

The closing balance in December is not linked to the opening balance in January – it would not make sense to link December 2017 to January 2017 as you can’t go backward in time, unless you have a time machine!

In the template ending in the month of December, the closing bank balances in Rows 61, 116 and 171 in January are linked to the opening bank balances in Rows 9, 64, 119 in February. 

The February closing bank balances are linked to the March opening balances. 

The March closing bank balances are linked to the April opening balances… and so it goes with the remaining months. This carries the correct bank balance through the template… and they are linked to the Balance Sheet.

Once you get to the end of December, it is time to start a new template for the new Financial Year (discussed below).

Note: This works much the same for the templates/year-ends March, June and September too.

starting a new financial year when your first one is finished

When December comes around (or whatever your year-end is), you will start a new accounting excel template for the new financial year (by downloading a current version of this Accounting Excel Template under Downloads near the top of this page.

You will manually enter December’s closing balance from the old accounting excel template into the opening balance of January in the new template.

If you like using the Balance Sheet, you must also manually enter in the December balances onto the new Balance Sheet in the Opening Balances column.



You are here:

Accounting Excel Template


GET FREE UPDATES

Round logo 150x150

This subscription adds you to our Newsletter which is sent out once a month. Please read our Privacy Policy regarding what we do with your data.

We won't send you spam or sell your data. Powered by Seva



Facebook Comments

Have your say about what you just read!
Leave me a comment in the box below.

Recent Articles

  1. 10 Free Bookkeeping Courses to Understand Business Accounts

    Aug 03, 18 11:45 PM

    These are the best free bookkeeping courses online for anyone in business who must understand business accounts. Learn the bookkeeping language, how to balance the books, how to manage finances, doubl…

    Read More

  2. Simple Cash Book Format Design for Quick Money Tracking

    Jul 31, 18 11:41 PM

    Cash book format instructions for a simple bookkeeping spreadsheet - so quick and easy to do for very small businesses like someone who runs a stall at a fair, or for a small non-profit club or lemona…

    Read More

  3. Bookkeeping Example of Business Transaction, Journal, Ledger, Report

    Jun 14, 18 10:07 PM

    Bookkeeping example of business transactions step-by-step entered into journals, ledgers, reports and cashbook for both hand-written bookkeeping and computerized bookkeeping

    Read More

Back to Top : Accounting Excel Template