How to Create Double Entry Bookkeeping in Excel

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.

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.

• Now, we will insert the following formula in the Debit column of the Inventory item:

• After inserting, press Enter.

• 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:

Final Output

• Finally, we have successfully created Financial Statement using double-entry bookkeeping in Excel.

Download Practice Workbook

To practice by yourself, download the following workbook.

Conclusion

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. Last but not least, please use the comment section below to post any questions or make any suggestions you might have.

Related Articles

• Bookkeeping for Truck Drivers in Excel

<< Go Back to Excel Bookkeeping Templates | Accounting Templates | Excel Templates