Journal Entries in Excel (Create and Use)

In this Excel tutorial, you will learn everything you need to know about journal entries in Excel. We will show you how to make journal entries and create a Forex Trading Journal using Excel. Also, we will explain how to post transactions and journal entries in Excel.

We have used Microsoft 365 when preparing this article, but the operations are applicable to all versions of Excel.

Each business is built on some kind of investment and strategy. We would like to have tracked every investment in the business in order to track its growth. It must be updated on a regular basis in order to track the financial growth of the company. To accomplish this, we must keep an entry of all financial transactions. It also allows for faster evaluations and record transfers later in the accounting process. We hope this article will help you learn everything you need to know about journal entries in Excel.

Download Practice Workbook

What Is Journal?

A journal is a thorough account that documents all of a company’s financial activities. It is used for future account reconciliation and the transfer of data to other official accounting records, including the general ledger. A journal is the first step in the accounting cycle, in which all transactions are recorded before being posted to the general ledger.

How Many Types Are There of Journal Entries?

There are several types of journals used to record specific types of transactions. It may vary depending on the organization’s size, industry, and accounting system. In general, we can list 6 types of journal entries:

• Opening entries: Opening entries, also known as opening journal entries or opening balances, are the initial journal entries made at the beginning of a new accounting period or when a new accounting system is implemented.
• Transfer entries: Transfer journal entries record the movement of funds between accounts or entities within an organization which reflects internal transactions.
• Closing entries: In closing journal entries, temporary account balances (such as revenue, expenses, and dividends) are transferred to retained earnings.
• Adjusting entries: Under the accrual method, adjusting entries record changes to accounts that are not otherwise accounted for in the journal.
• Compound entries: Compound journal entries involve more than two accounts and multiple debits or credits.
• Reversing entries: Reversing entries are made at the beginning of an accounting period to reverse the effects of certain adjusting entries made in the previous period.

How to Make Journal Entries in Excel?

We are going to discuss the whole procedure of making journal entries in 3 steps. Here are they:

Step-01: Create Formation of an Initial Balance Sheet

• You must take into account every expense at the start of the business and organize them.
• We have organized the data in Particulars, Credit/Debit, Debit and Credit columns.

• Now insert the following formula to sum the debit and drag the Fill Handle to the right to get the credit total.

• Press Enter.

Step-02: Generate Financial Statements for a Certain Period

• Enlist the financial activities for a certain period. Here, we have enlisted the financial activities of a company in the month of July 2022.
• Now, rearrange the financial activities into a balance sheet mentioning debit and credit.

Step-03: Finalize Journal Entries in Excel

• Now, we will make entries of the financial data in the journal.
• Consider every financial statement into account considering both the initial balance sheet and the newly created balance sheet.
• Calculate every financial statement with these two sheets.
• Input the following formula in Cell D5 and press Enter.

• Here, Dataset and Making Journal Entries are two different sheets that we used.

• Now, make entries of every financial statement in the journal following the above procedure.
• Input the following formula to sum the debit and AutoFill the right cell to get the total credit.

• Thus we can create balanced journal entries since both the debit total and credit total are equal.

How to Create a Forex Trading Journal Using a Simple Excel Sheet?

A forex trading journal is used by forex traders to track and analyze their trading activities. Forex Trading (also known as Foreign Exchange Trading) is the marketplace where national currencies of different countries are exchanged. You can have your own Forex Trading Journal by following some steps using Microsoft Excel.

• First, make a spreadsheet like the following image.
• Insert the Initial and Highest balance.

• Now, we will create some Data Validation.
• Select cell C5 and then choose Data >> Data Tools >> Data Validation.

• Select List under the Allow section and type the currency pairs in the Source text box from the Data Validation window.
• Click OK.

• Now, drag the Fill icon downwards to AutoFill the lower cells with this Data Validation.
• Click on the drop-down icon shown in the following image to see the currency pairs.

• Similarly, create another Data Validation list for the Long and Short positions of the traders.

• Now, insert the following formula in cell I5 to calculate the Risk/Reward ratio.

• Now, insert the data according to the market infrastructure and complete the datasheet.

• Thus, we can easily create a Forex Trading Journal in Excel.

How to Post Transactions Using Journal Entries in Excel?

From the beginning of this article, we’ve learned that journaling is a must if you want to keep accurate records. It also makes it easy to check records and move them around later in the financial process.

Here is a sample journal entry that includes the date, the journal entry number, account number, dollar amounts, and a description of each entry. Journal entries have debits and credits that determine where a certain dollar amount is posted in the entry.

Now, let’s just remind you what debit and credit are. A debit adds to the expense and asset accounts and subtracts from the liability, revenue, and equity accounts, while a credit subtracts from the expense and asset accounts and adds to the liability, revenue, and equity accounts.

How to Post Journal Entries Using Excel?

You can follow the below steps to create accounting templates for your journals each month like this.

• Make a copy of your chart of accounts and paste it into the spreadsheet.
• Create column headings for dates, account numbers, account titles, debit, credit, etc.
• For each entry, add rows to the document.
• From the chart of accounts, copy and paste the account numbers and titles.
• Calculate total debits and credits using formulas. The total formula tracks the total dollar amount of all debits and credits.

Now, here is a sample journal entry that we have created to make you understand better.

Frequently Asked Questions

1. Can I import journal entries from other sources into Excel?

Answer: Yes, you can import journal entries from other sources into Excel. You can use the Text to Columns feature or the Get External Data option for that purpose.

2. Does Excel have a journal entry template?

Answer: Yes, Excel does have journal entry templates. You can also follow this article to learn more about how you can use Excel to create your own journal entry.

3. What are the formulas of journal entries?

Answer: Here is the formula of journal entries that connects the balance sheet components. It adds liabilities to equity to determine your assets.

Key Takeaways from the Article

• First, we have discussed what a Journal is.
• Explained 6 different types of journal entries.
• Showed a step-by-step procedure to create journal entries in Excel.
• Also showed how to create a Forex Trading Journal using a simple Excel sheet.
• Discussed how to post transactions as well as journal entries in Excel.
• Provide solutions to frequently asked questions by readers.

Conclusion

This article covers everything you need to know about journal entries in Excel. We have explained what a journal is and the types of journal entries. We also showed how to create journal entries in Excel step-by-step. Hope this article was informative and useful for you. Feel free to leave any questions, comments, or recommendations in the comments section.

Journal Entries in Excel: Knowledge Hub

• Make Journal Entries
• Create a Forex Trading Journal
• Create a Bullet Journal
• Make a Trading Journal

<< Go Back to Excel for Accounting | Learn Excel