Microsoft Excel offers lots of tools and functions to its users. Accounting and marketing professionals can extract a number of benefits from Excel, as it enables to make calculations, create graphs and make links with various worksheets effortlessly. In accordance with requirement a lot of things can be done in excel conveniently, such as, using functions, making calculations and sorting etc. As regards accounting ledger or statement of account, the rows and columns of excel are well suited to create ledger or statement of accounts. As an accounting professional, you need to prepare a ledger or statement of accountant asand when needed. Let’s see how excel is useful in performing that particular task.
If you want to prepare a leger, after you have provided the heading, company name and ledger month, you need to create 8 columns, naming them as, 1-Document date, 2-document number, 3-Reference, 4-Division or Department, 5-Narration, 6-debits, 7-credits and 8-balance.
Assuming that your ledger or statement of accountant starts from A10 and ends at H10 horizontally, you need to create tables for the required number of rows. Put the opening balance in either columns, F11 or G11. Give this formula in H11, [=F11-G11]. Now that you have provided the opening balance, you would require to enter the values, with corresponding details, either in debit side or credit side starting from A12 row. Before you do so, you need to give the formula which is very much important to provide you the closing balance proceeding from theopening balance and including all the transactions.
Assign this formula in H12, [=H11+F12-G12] - Note that you need to put the formulas in the specific columns without brackets. After you have entered this formula, copy it and paste it to the required number of rows. You will notice that the balance figure changes at each row on entering either debit value or credit value in the corresponding columns. This is how you will have prepared a ledger or statement of account. You can also make tables for the required number of rows and columns. For your reference, you can type this in the last row and Column E, “closing balance as of particular month”. If you find any difficulty in preparing the ledger as mentioned above, kindly drop a comment. I would feel happy to help you.
K.A.Fareed (Fareed Siddiqui)
Writer, Trainer, Author, Vba/Vb Developer
BBA; MBA-Finance; M.Phil-FinancialManagement; (PhD-Management)
Individual Member of Institute of Management Consultants of India
You may also like these