The majority small and medium enterprises (SME) all tend optimal cost management, commissioning, particularly in the array of accounting. Therefore, Excel – familiar tools, flexible – often selected as the platform to build the system of bookkeeping original. With the ability to handle spreadsheets powerful, easy to customize, no cost copyright, Excel become “temporary solution” to help businesses set up accounting process, from the number of 0.
However, to how to do bookkeeping on Excel effective, businesses need to understand the principles of accounting, system design, data items, especially knowing how to apply the Excel tool to the accounting profession. In this article, AccNet will help you to understand deeply about how guide to bookkeeping on Excelfrom the concept condition to the implementation steps in detail. The end of the article, you will also be hints when should switch to accounting software professional for system optimization financial accounting for business.
1. Clarify the concept of accounting on Excel from the perspective practice
Accounting what is accounting system business?
Ledger is where record the entire economic and arise are related to assets, capital, revenue, cost... of the business. The recording right, full book accounting is grounds for financial reporting, tax settlement. Under the provisions of circular no. 200/2014/TT-BTC and circular 133/2016/TT-BTC, the system accounting include:
- General.
- Ledger accounts.
- Window detail according to each subject accounting.
- Balance sheet arises, the synthesis report.
Guide to bookkeeping on Excel is the:
- Design templates for accounting using Excel spreadsheet.
- Self-built system, recipe data link between the sheet to simulate the activity record window, synthesis, for lighting, such as in accounting software professional.
Any business should apply accounting on Excel?
This method is particularly suitable for:
- Doanh nghiệp vừa thành lập hoặc quy mô nhỏ (ít nghiệp vụ, <300 chứng từ/tháng).
- The business transformation business model.
- The business no investment budget software or are in the testing phase process, internal accounting.
Important note: The instructions do bookkeeping on Excel should only be the beginning, help businesses become familiar with accounting principles, before turning to specialized software – where provides full-featured books, reports, connected with electronic invoicing, banking, tax...
Why business should be interested in the guide to bookkeeping on Excel?
Here is the solution to save cost, easy to deploy for small business. According to a survey by Clutch (2024), there are up to 38% of small businesses still use Excel for bookkeeping in the initial startup phase. This reflects the trend to the fact that Excel is still the popular choice for businesses that are not eligible investment accounting software for professional use.
A number of benefits when applied guide to do bookkeeping on Excel:
- No cost software copyright.
- Easily customized to model the operation of the business.
- Can take advantage of internal resources (personnel know how to use Excel) without the need to hire accounting outside.
However, Excel is not the solution long-term. Excel is very powerful, but does not have the ability to control internal to develop input errors, difficult to expand when the data increases. So, to know how to do bookkeeping on Excel in a professional manner will help your business:
- Increase the accuracy and transparency of data.
- Create a platform to easily switch up accounting software to business development.
Read more:
2. Guide to bookkeeping on Excel detailed step by step
This is the core part of the article – help business can start implementing right without being in-depth accounting.
Step 1: design of the original item
The categories accounting is the foundation for the entire data operation. You should build the table:
- The category accounting accounts: Code – Name – Properties Owe/Have.
- The items customers / vendors: Code – Name – MST – Address.
- List of goods: item Code – Name – unit – Group – row- Price.
Note: the encryption to be logical, systematic, to avoid duplication – for example KH001 for customers, NCC001 for suppliers.
Step 2: Input the certificate from arising daily
Create the table to enter data in a form familiar:
- Receipts, voucher.
- Purchase invoices and sales orders.
- Votes import – export warehouse.
You can use:
- Dropdown list automatically from the list to reduce the error type.
- Recipe hints accounting (apply with repeat transactions).
- Automatically numbered voucher using the formula ="PT"&TEXT(ROW(A2)-1,"000").
Step 3: Establishment general
Windows common log is where sum up the whole of the service entered.
Structure: On – voucher – interpretation – TK Debt – TK Are – the amount Of money.
You can use the function:
- FILTER() or QUERY() (on Google Sheets) to auto filter from sheet stock from.
- Calculate the total according to each type of business for comparison.
Step 4: Set up ledger account
- Each account will have a panel ledger separately, including: Day – description – amount-Debt – Has – balance end of the period.
- Dùng SUMIFS() để tổng hợp số liệu theo từng tài khoản từ Nhật ký chung: =SUMIFS('NKC'!F:F, 'NKC'!D:D, "111", 'NKC'!A:A, ">=01/01/2024", 'NKC'!A:A, "<=31/01/2024")
- Should put the main account (111, 112, 131, 331, 511, 621, 632...) on the sheet individually to easy-to-follow.
Step 5: Set up the balance Sheet arising, financial reporting
Aggregate balance beginning of the period, arisen, ending balance of each account. Formula used:
- SUMIFS() to arise.
- IFERROR() to handle the line is empty or error.
- Conditions filter by month/quarter/year.
Create table:
- The balance sheet arising.
- Report business results.
- Balance sheet accounting.
3. Conditions to do bookkeeping on Excel for business
To the instructions to do bookkeeping on Excel no tangle, wrong data or wrong rules, businesses need to ensure a number of conditions following platforms:
Understand & apply the right accounting system according to circular 200 or circular 133
Here are two modes of accounting most popular in Vietnam today:
- Circular 133/2016/TT-BTC: for small and medium enterprises.
- Circular no. 200/2014/TT-BTC: for business, medium-sized and above.
Businesses need to clearly define ourselves are applying mode how to design, window fitting, avoid wrong account system, group, service, structure books. You can download free templates standard of both circular from the website of the Ministry of Finance to use as a background document.
The structure data Excel accounting must be designed science
A file window, good accounting is not simply the sheet discrete, which must be a strong link between the tables to cater for:
- Data entry professional.
- Synthesis report.
- Data access according account, object, month, quarter, year.
Optimal structure include:
- Sheet category: account, accounting, customers, suppliers, shipping code, voucher code.
- Sheet stock from: receipts, voucher, enter the warehouse, stock, bills of sale.
- Sheet book: general journal, ledger, ledger details.
- Sheet statements: the balance sheet arising financial statements.
Skills using Excel's accountant must meet certain qualifications
To build window accounting standard accountant need to know:
- Function Excel: IF, VLOOKUP/XLOOKUP, SUMIFS, INDEX/MATCH, FILTER, TEXT, ROUND, ISERROR, etc.
- Advanced technical: name the test data (Data Validation), conditional formatting (Conditional Formatting), create dynamic table (Pivot Table).
- Link to the sheet using the formula instead of copy-paste manually.
Lack of this skill easily lead to false data when the number of vouchers grew.
Register for a free software consultation
ERP on a web-based platform, consolidating multi-branch data.
Hệ thống chỉnh sửa theo đặc thù của doanh nghiệp.
Integrated with complete enterprise management tools.
Xử lý dữ liệu lớn với hàng ngàn giao dịch/ngày.
Accumulated data for over 10 years.
4. Template file ledger on Excel – Download free & structure analysis
Download template bookkeeping Excel free
In order to save time designing from scratch, you can download the template ledger Excel is standardized according to circular 133 or 200. These generally include:
- The category accounts, customers, goods.
- General journal, ledger, ledger details.
- Financial statements basic.
You can download the template bookkeeping Excel for small business here:
Analyze the template structure to apply instructions bookkeeping on Excel efficiency
A sample file good need to ensure:
- The structure closely linked the middle of the sheet.
- Restrict data entry craft many times, increased automation using the formula.
- Easy to customize by type of business: commercial, service, production...
Hint: Businesses can rely on this form to:
- Start implementing accounting internal.
- Training accounting personnel practice on sample data.
- Do platform that converts data into accounting software later.
5. The common fault when making guide bookkeeping on Excel
Use Excel to do accountants make mistakes. Below are the common errors:
Error recipe – link wrong calculations, wrong
For example: link data from the general journal to the ledger is lack of conditions that day, or account. How to fix:
- Always use the SUMIFS instead of SUMIF.
- Check the reference skills, put the name of the region to be easy to manage.
- Create table test to collate results.
Coincident vouchers or lack of coding reasonable
Type the voucher code, easy to duplicate, lack of control. How to fix:
- The self-code auto like: ="PT"&TEXT(ROW(A2)-1,"000")
- Use Data Validation in combination dropdown list to limit the import error.
Do not lock the car data, anyone can edit
Data changed to cause deviation window. How to fix:
- Protect sheet function “Protect Sheet” in Excel.
- Just to empty the input box and materials required, the rest should lock the whole.
Read more:
6. When should I transfer from Excel to accounting software for professional use?
- When the number of vouchers exceeds 500/month, Excel becomes slow, error, formula, difficult to control, cash flow, debts and inventory. This is when businesses need to turn to specialized software.
- When many people do accounting or need internal control, then cyoke guide to bookkeeping on Excel doesn't have functions authorized users. Anyone can edit the original data, causing the risk of losing control or errors to report.
- When businesses need extended features (property, payroll, inventory, budget), but Excel can't manage fixed assets, depreciation, cost allocation, payroll, fixed level of production, profitability analysis according to each order.
- When businesses need to synchronize data between electronic invoices, banking and tax system, something that Excel can't do. Then accounting software such as AccNet ERP can fully meet the efficiency with the following features:
- Automatic accounting from the electronic invoice.
- Integrated digital bank (MB Bank, VIETCOMBANK, BIDV,...).
- Connect API with the tax system to report quickly.
PHẦN MỀM KẾ TOÁN ACCNET ERP TÍCH HỢP “TRỢ LÝ TÀI CHÍNH AI” With 7 phân hệ lõi tích hợp từ kế toán, bán hàng, mua hàng, sản xuất, kho vận, nhân sự đến phân phối, phần mềm quản lý doanh nghiệp AccNet ERP tạo nên một hệ sinh thái quản trị tài chính – điều hành khép kín, đồng bộ thông tin xuyên suốt: TÍCH HỢP TRỢ LÝ TÀI CHÍNH KẾ TOÁN AI - RA MẮT 2025 AccNet ERP là một nền tảng công nghệ mở, tích hợp các giải pháp tiên tiến như: DOANH NGHIỆP ĐƯỢC GÌ KHI TRIỂN KHAI ACCNET ERP? ✅ Quản lý tài chính chủ động – Không còn “bơi trong số liệu rời rạc” ✅ Hiệu quả rõ rệt khi ứng dụng trợ lý tài chính AI ĐĂNG KÝ NHẬN DEMO NGAY Vui lòng điền các thông tin vào form chúng tôi sẽ liên hệ lại với bạn trong 24h làm việc. KHÁCH HÀNG TIÊU BIỂU ĐÃ VÀ ĐANG TRIỂN KHAI ACCNET ERP
✅ Demo miễn phí full tính năng ✅ Báo giá cá nhân hóa theo quy mô doanh nghiệp ✅ Tư vấn 1:1 cùng chuyên gia có nhiều kinh nghiệm
Way guide to bookkeeping on Excel is a savings option, logical, with the new business established. However, when business development, accounting data increasingly complex, the amount of stock from rising demand financial control become stricter, Excel will gradually reveal the limitations in terms of accuracy, security, interoperability. Continued how to make crafts can lead to risks skewed data, loss costs, even influence the process of business decisions.
That is the reason why many smart business was soon switch to the accounting software as professional as AccNet ERP – solution modern accounting is designed specifically for the business environment in Vietnam. With the ability to automate the process of accounting, deviation alerts, access reports, instant, flexible management all types of businesses
Hãy trải nghiệm bản demo miễn phí AccNet Cloud ngay hôm nay, để thấy rằng một phần mềm kế toán mạnh mẽ không còn là đặc quyền của những doanh nghiệp lớn, mà là công cụ thiết yếu cho bất kỳ ai muốn quản trị hiệu quả ngay từ ngày đầu tiên!
CONTACT INFORMATION:
- ACCOUNTING SOLUTIONS COMPREHENSIVE ACCNET
- 🏢 Head office: 23 Nguyen Thi huynh, Ward 8, Phu Nhuan District, ho chi minh CITY.CITY
- ☎️ Hotline: 0901 555 063
- 📧 Email: accnet@lacviet.com.vn
- 🌐 Website: https://accnet.vn/
Theme: