Accounting Ledgers in Excel
Accounting ledgers provide details about the transaction in that particular accounting head. Ledgers are prepared from Journal entries and each entry is passed to ledgers. Here, we will provide the procedure of how you can prepare accounting ledger and excel with the help of pivot table features. Once set-up, you will only enter the data in the JOURNAL and ledgers will update automatically at the click of a button. Before you go on reading the entire article, download the excel file from here. The file has ledgers, Trial Balance and vouchers details all updated from the same Journal entry sheet.
Step by Step Procedure
- First set-up your Journal entry sheet in the format provided below. Account Level 1, 2 and 3 are the level of groups. For example Account level 3 is a sub group of account level 2 and account level 2 is sub-group of account level 1.
- Format the Journal entry as TABLE by selecting the table and then enter CTRL+T and Hit ENTER to format it as TABLE.
- Once you entered enough data to prepare a report, Select the whole table and go to insert Journal entry Table to enter the Pivot Table and Click OK.
- Now drag the Account Level 3 to Filter Option, Debit and Credit to Values Option and Date to Rows Option (Days and not months).
- Now select the “Bank” from the filter list. You Report will look like the one below.
- We are one step away from the preparing the ledger. Let’s enter the closing Balance Column to the side of Sum of Credit. Click on the “Sum of Credit” and go to Analyze tab and click on “Fields, Items, and Sets” and then Select “Calculated Field”.
- In the Calculated Field, Enter the Name as Closing Balances and in Formula Enter =Debit-Credit.
- In the Closing Balances Column, Right Click on it and go to “Show Values as” and the select “Running Total in—.
- Keep in mind that Accounts ledgers with credit Balances will show as negative since our formula was Debit-Credit.
- Able to prepare accounting ledgers in excel through pivot table will make your time more effective and efficient.
- By doing so the errors will be reduced since reports will be updated with the click of a button.
In case you prefer videos over articles, Please watch the below video.
Click here to Download the file. You might be asked to complete a small offer before you download the file.