How to make internal accounts in Excel?

When we use excel, we usually use tables, and many accountants also use excel to keep accounts. So what should I do when I make an account? Today, we will tell you how to do accounts with excel.

Turn on step reading mode

operational approach

0 1

1, set up an account

On-the-job description uses Excel to create a workbook and multiple worksheets, which are used to store accounts and their opening balances, accounting vouchers, and general ledger and subsidiary ledger automatically generated according to accounting vouchers.

Preparatory knowledge:

Workbooks and worksheets and their relationships

Operational skills:

Create new workbooks and worksheets, and rename worksheets.

02

2. Specific operation:

Step 1: Open Exce | software and create a new workbook.

Step 2: Name Sheet 1 as "Account Setting Table"

Step 3: Name Sheet2 as "Opening Balance Table"

Step 4: Name Sheet3 as Voucher Template.

Step 5: Add worksheets Sheet4, Sheet5 and Sheet6, and name them voucher input table, general ledger, trial balance and subsidiary ledger respectively.

Step 6: Save the workbook and name it "Accounting Processing"

03

3. Job description:

Create an account setting table, and the account settings are stored in the account setting table-allow users to set multiple subordinate accounts under the general ledger account as required.

manipulative skill

Format and border cells, add rows and columns, delete rows and columns, and freeze panes.

04

4. Job description:

Establish the opening balance table, and enter the opening data in this table to realize the trial balance.

Preparatory knowledge:

General ledger account balance = sum of subordinate account balances, sum of debit general ledger account balances = sum of credit general ledger account balances.

Sum function sum (vertical and horizontal)

Operational skills:

Format and border cells, merge and center cells, add and delete rows and columns, freeze panes, sum functions, and F9 recalculate worksheets.

05

5. Enter the task description of the accounting voucher:

Create a voucher entry table and enter all business vouchers into the table. The header should contain all the information of accounting vouchers, and certain data verification functions should be set during the input process.

Preparatory knowledge:

The debit and credit bookkeeping rules, bookkeeping voucher elements, and the input account code system automatically give the account name.

manipulative skill

Freeze panes, data validity, define names, fill formulas, and use LEFT and VLOOKUP functions.

06

6. Specific operations:

Set the data validity of columns such as date, accounting period, account number, attachment, voucher type, voucher number, abstract and account number. And it should be noted that when inputting serial source, different options should be separated by commas in English input state. If Chinese input is used,

07

When using VLOOKUP function in general ledger account, there will be an error, because the left function is nested in VLOOKUP function, and the data returned by the left function exists in text format, which cannot be recognized by ⅵ search function, so the error message "#NA" will appear.

Correction method:

Add two short lines "-"before the left function. This symbol means to convert the data returned by the LEFT function from text format to numerical format.

08

8. Generate general ledger on-the-job description:

Establish a general ledger table, summarize all voucher data in the table, and automatically generate a general ledger according to bookkeeping vouchers.

Preparatory knowledge:

The concepts of general ledger and trial balance; Calculation formula of asset account balance.

Operational skills:

Use inter-row calculation, inter-column calculation, filling formula, ABS, SUM, SUMF and IF functions.