How to download and use Excel accounting template

Key points of this article

Before downloading, open it to check the formula, number of worksheets and whether it contains macros. Do not fill in the real data directly.
The income and expenditure detailed table only records the running water, and the monthly summary is automatically calculated from the detailed table using formulas, without manually filling in the total amount.
Categories are fixed with drop-down lists, which can reduce statistical deviations caused by mixing "catering" and "eating".
Forms containing bank card numbers and identity information are only saved locally and are not uploaded to online conversion or unfamiliar websites.
Save a copy every month and check whether the summary matches the bank statement after one month.

Many people search for "free Excel accounting templates" because they want to save the time of building a spreadsheet from scratch. Templates can indeed solve most structural problems, but the quality of online templates varies greatly: some only have a general table, some formula references are misplaced, some require macros to be enabled, and some transfer data to online websites. If you choose the wrong template, you may have to rework your accounts in the next few months.

This article is written for readers who want to use Excel to manage personal or family income and expenses. You'll learn how to tell if a template is reliable, what to check first after downloading it, how to fill out each worksheet, and how to have monthly totals calculated automatically. After reading, you can use a clearly structured template to start accounting, and you can also judge whether the forms given by others are worth using.

How to choose a free Excel accounting template? One sentence conclusion

When picking a template, structure and formula are more important than appearance. A useful accounting sheet must be able to distinguish at least three things: accounts, income and expenditure flow, and summary, and the summary figures should be generated by formulas. The table below provides suggestions for common situations.

your situation	Recommended practices	reason
I just want to remember my daily income and expenses	Select the simple template of the three tables "Account + Income and Expenditure Details + Monthly Summary"	Less structure, faster to get started, and fewer mistakes
Need to control expenses	Choose a template with "Budget" and "Actual" comparison columns	Can directly see which category is overspending
Many people in the family remember together	Put it in a shared location, specify only one person to enter, and others can only read it.	Avoid data confusion caused by multiple people making changes at the same time
The form involves bank card numbers or identity information	Only used on local computer, not uploaded to online websites	Reduce the risk of sensitive data leakage
Unknown download source, prompt to enable macros	Test with an empty table first and confirm that there are no abnormalities before considering using it.	Macros may bring security risks. Do not enable unfamiliar macros casually.

Template structure overview

A complete personal accounting template usually contains the following parts. You can refer to this table before downloading to see if the target template is complete.

part	what to write	Space suggestion
Instructions for use	The purpose, filling order, and classification meaning of each table	1–2 paragraphs
Account table	Cash, bank cards, e-wallets and other accounts and opening balances	3–5 items
Income and expenditure details	The date, amount, account, category, and remarks of each transaction	Rolling records by month or year
Classification table	Classified list of income and expenses for drop-down selection	8–15 items
monthly summary	Statistics of monthly income, expenses and balances by category	One row or area per month

How to write part by part

Instructions for use

The function of the explanation page is to allow you to still understand the table six months later. Write three things clearly: what each table does, which table to fill in first, and what the boundaries of the classification are.

Example: "The income and expenditure details only record the actual transactions; transfers are not included in income or expenses, and are classified separately as 'transfer'."

Account table

The statement of accounts records the opening balance of each account. The current balance can be calculated using the formula at the end of the month, which is"beginning balance + income − expenses".

Example: [Account name], opening balance [amount], note the account opening time or purpose. The number of accounts should not exceed actual needs. Too many accounts will increase the burden of entry.

Income and expenditure details

This is an important table, with one row for each transaction. Recommended fixed columns: date, amount, income or expense, account, category, remarks. The amount should be uniformly positive, and the "Income or Expenditure" column should be used to distinguish the direction, so that subsequent statistics will be simpler.

Example: date [date], amount [amount], type "expenditure", account "[bank card name]", category "catering", remark "[supermarket shopping]".

Classification table

The classification chart determines whether you can see clearly where the money is spent later. It is more appropriate to control the number of categories to 8-15, and the boundaries of each category should be clearly written. The category column of the income and expenditure details uses a drop-down list to reference the category table, which can avoid the same type of expenditure being written with different names.

Example: Expenditure categories include "catering", "transportation", "residence", "communication", "shopping", "medical care" and "others", where "other" is only used when it really cannot be classified.

Monthly summary

The summary table should be calculated from the income and expense details using formulas rather than manually entered. Use the summation formula to calculate the monthly amounts by category, and then calculate the total income, total expenses and balance. If the formula is already written in the template, just check whether the reference range covers the number of rows you want to use.

Example: This month’s income [number], this month’s expenses [number], balance = income − expenses. Check your bank statement at the end of the month and find out the reason if the difference exceeds a few yuan.

Layout in the editor

The "layout" of the accounting form mainly refers to the convenience of entry and reading, and does not require fancy layout. The following steps apply to most spreadsheet software.

After opening the template, save a copy first, with the file name indicating the purpose and year, such as "Family Accounting 2025", and the original template will remain unchanged.
Set the first row as the header and freeze the first row so that you can still see the column names when you scroll down.
Set a unified format for the date column, set a drop-down list for the category column, and reference the range of the category table.
Add filter buttons to the income and expenditure details to facilitate searching for a certain record by month or category.
When you need to print, first set the print area to the summary and details of a certain month, and check whether paging cuts a record in half.
If you only have the PDF version of the accounting form at hand, you can use the PDF export to Excel function of the OmniDoc desktop client to convert it. The download address is download.omnidoc.top. After conversion, you need to check the numbers and dates line by line, and the conversion results may not be completely accurate.

Common writing problems

The classification is too detailed. Dozens of categories make people too lazy to enter them. In the end, a large number of them are recorded as "others".
The amount is mixed with words or units, such as "[number] yuan", causing the summation formula to treat it as zero.
The date format is not uniform. Some are text and some are dates. There will be errors in filtering and monthly statistics.
Manually fill in the monthly total, but the summary does not change after the details are changed.
Transfers are treated as income and expenses, and balances appear to be less or more than they actually are.
Only save one file, no backup, all data in the past few months will be lost after accidental deletion or damage.

❓ Frequently Asked Questions (FAQ)

1. The downloaded accounting template prompts you to enable macros. Do you want to enable it?

Don't turn it on directly yet. If the source of the template is reliable, the purpose of the macro is clearly stated in the description, and you can understand what the macro does, then consider enabling it. Macros from unknown sources may pose security risks. You can test with an empty table first, or choose a version that does not contain macros.

2. The formula in the template seems to be calculated incorrectly. How can I check it?

Click on the summary cell and see if the range referenced by the formula contains all the required rows. A common problem is that the reference only goes to a certain row, and subsequent newly added records are not counted. In addition, the results can be verified with test data of several known amounts.

3. There are many online conversion websites on the Internet. Can I upload the accounting form for conversion?

It is recommended that forms involving bank card numbers, identity information or complete transaction records be processed locally only and not uploaded to unfamiliar online websites. When choosing a tool, you can pay attention to file size limits, whether there is a watermark, whether there are instructions on how to process the data, and whether typesetting and numbers are preserved after conversion.

4. What is the difference between free templates and paid templates?

The difference between the two mainly lies in whether the functions are complete, whether the formulas have been checked, and whether the files are secure, not just in price. No matter what kind of template it is, you should first look at the structure and formula before deciding whether to use it for a long time.

5. How long does it take to make a new version of the accounting table?

If there are significant changes in income sources, accounts, or classifications, adjustments to the classification schedule and account schedule will be required. You can review it once a year, write down the problems found during use, and generate a new version. The old version is retained as a historical record.

📝 Summary

The value of a free Excel accounting template lies in its clear structure and automatic summary, rather than its many features. Check the formulas and macros before downloading, and insist on "recording the flow in detail and using formulas for summary" when filling in, so that most of the rework can be saved.

Open OmniDoc and work with your spreadsheet file