In this article, we will learn how to create double-entry bookkeeping in Excel. Bookkeeping is the process of recording financial transactions. In double-entry bookkeeping, we need to insert two-sided accounting entries of the financial transaction. So, without delay, let’s explain the steps of creating double-entry bookkeeping in Excel.
Download Practice Workbook
To practice by yourself, download the following workbook.
What Is Double Entry Bookkeeping in Excel?
Bookkeeping is the process of recording financial transactions. There are 2 types of methods available for bookkeeping. One is single–entry bookkeeping and other is the double–entry bookkeeping. In double-entry bookkeeping, we need to insert two-sided accounting entries of the financial transaction. Therefore, we must include all relevant details on the origin and destination of the money.
Step-by-Step Procedures to Create Double Entry Bookkeeping in Excel
To explain the methods, we will use a dataset of the initial balance sheet of a company. It contains a dataset related to the starting condition of the production business. We have organized the data in Particulars, Credit/Debit, Debit, and Credit columns. To learn the procedure, let’s go through the steps.
STEP 1: Enter Documents for Double Entry Bookkeeping
- Firstly, we have selected the following dataset.
- You can see the Particulars, Credit/Debit, Debit and Credit Amounts inside the dataset.
- Now, at present, we will calculate the total amount of Credit and Debit in this dataset.
- In the next step, we will calculate the total Debit amount.
- So, write down the following formula:
- Now, press Enter to have the debit summation.
- Similarly, we will find out the summation of the Credit amount.
- Therefore, write down the following formula:
- Press Enter to calculate the Credit summation.
STEP 2: Make Journal Entries in Excel
- In the following step, we need to enlist the financial activities for a certain period.
- In our dataset, we have enlisted the financial activities of a company in the month of July 2022.
- Now, we need to rearrange the financial activities into a balance sheet.
- We have reorganized this information into a similar balance sheet as the previous dataset.
- In this step, we will make entries to create Journal.
- You need to consider every financial statement into account considering both the initial balance sheet and the newly created balance sheet.
- For this reason, we will calculate every financial statement with these two sheets.
- So, insert the following formula to find the Debit of the Machine.
- After inserting the formula, you must have to press Enter.
Here, Dataset is our first initial balance sheet name and Entries is our newly formed balance sheet. Machine entry is available in the Debit column of both sheets. In the Dataset balance sheet, it is presented in the D5 cell and in the Entries balance sheet, it is presented in the E15 cell. So, we have added the Debit value and found out our new balance.
- Now, we will insert the following formula in the Debit column of the Inventory item:
- After inserting, press Enter.
Here, in our newly formed Entries named dataset, Inventory is not presented, we have taken the entry of the Dataset sheet. It is presented in the D6 cell.
- Similarly, we have made the entries of each financial statement.
- Finally, we can see the Journal of the financial statement and can analyze the condition of the company.
- Now, we will check the correctness of our calculations.
- For checking, we will calculate the summation of Debit and Credit.
- Now, write the following formula in the Debit column to calculate the summation:
- After writing the formula, hit Enter.
- Now, write the following formula in the Debit column to calculate the summation:
- Hit Enter.
- Finally, we can tell that we have done our work accurately as Debit equals Credit.
STEP 3: Create Ledger and Trial Balance from Journal Entries
- Now, in this step, we will create Ledger and Trial Balance from our previous Journal Entries.
- Firstly, you have to create a layout for the ledger with the necessary cell allocation for items.
- We need to insert some important items like date, activity description, client, account type, debit, and credit amount.
- After that, you also have to assign two cells to insert the start and end dates of the period.
- In the next step, you will create a new worksheet to create trial balance data linked with the ledger balance sheet.
- Here, we created 3 columns for Accounts, Debit, and Credit.
- Now, after creating the layout, we will insert the possible account names in the Accounts column of Trial Balance.
- We will use these account names in the ledger sheet.
- Now, insert the end day and start day of the Ledger Balance Sheet.
- Here, we are doing our task for the month of July 2022.
- That’s why we will insert 1st July and 31st July as our start and end day.
- After that, we will create a drop-down menu for the cells of the column Account Type.
- For creating the drop-down menu, select all the cells of the Account Type column.
- Then, go to the Data tab in the top ribbon.
- After that, click on the drop-down menu of Data Validation from the Data Tools group.
- Now, a wizard will come out and select Data Validation from there.
- In the Data Validation window, select List in the Allow criteria option.
- After that, select cells of range B5:B16 in the “Trial Balance” worksheet in the Source box.
- Before doing this step, we need to rename the sheet of Ledger Balance Sheet as Trial Balance.
- Finally, press the OK button.
- As a result, we can now select any of the accounts from the list of available accounts by clicking on the drop–down in the Account Type column.
- In the following step, move to the Trial Balance worksheet and insert the following formula in cell C5 to calculate the total debit amount for each account.
- Then, insert the following formula in cell D5 to calculate the total Credit amount for each account.
- Now, drag the Fill Handle icon to paste the used formula respectively to the other cells of the column.
- So, we have successfully inserted the formula in the Trial Balance sheet to calculate Trial Balance from the Ledger.
- In the following step, return to the Ledger Balance Sheet and make Ledger from the Journal.
- We have kept input from the initial dataset of the Journal against the date of 1st July 2022.
- After that, we have made the Ledger information from the newly inserted Journal entries.
- Now, insert the following formula in the F5 column to sum the Debit total:
- After inserting the formula, press Enter.
- After that, insert the following formula in the G5 column, to sum up the Debit total.
- After inserting the data in Ledger, automatically update the value in Trial Balance.
- Finally, we have successfully formed Trial Balance from Ledger and also Ledger from Journal.
STEP 4: Prepare Financial Statements from Trial Balance
- In the following step, we will prepare Financial Statement from the Trial Balance.
- Firstly, we will insert Asset items and their values.
- Assets are the main resources owned by the company.
- Accordingly, we will insert the items from the Ledger.
- After that, we will summarize the Assets.
- In C15 cell, write the following formula to calculate Total Assets:
- In the following step, we will insert Liabilities and Owner’s Equity.
- Liabilities are things that the company owes to a person or another company, like cash, loans, etc. and we will insert accordingly.
- Owner’s Equity represents the value for a company’s shareholders after all the company’s assets have been sold off and all company liabilities have been paid off.
- Here, in the following figure, we have inserted Liabilities and Owner’s Equity from the Ledger.
- Now, we will sum up the Liabilities by using the following formula in the C26 cell:
- Hit Enter to observe the sum.
- In the next step, we will add Liabilities and Owner’s Equity.
- So, we need to add Total Liabilities and Owner’s Equity data.
- So, type the following formula in the C29 cell:
- Finally, we have successfully created Financial Statement using double-entry bookkeeping in Excel.
In this article, we have demonstrated step-by-step procedures to create double-entry bookkeeping in Excel. There is a practice workbook at the beginning of the article. Go ahead and give it a try. To read similar articles, check out the ExcelDemy website. Last but not least, please use the comment section below to post any questions or make any suggestions you might have.