How to Create and Use Journal Entries in Excel

Step 1 – Create Formation of an Initial Balance Sheet

• Take into account every expense of the business and organize them.
• We have organized the data in Particulars, Credit/Debit, Debit and Credit columns.

• Enter the following formula to sum the debit.
• Drag the Fill Handle to the right to get the credit total.

• Press Enter.

Step 2 – 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.
• Rearrange the financial activities into a balance sheet mentioning debit and credit.

Step 3 – Finalize Journal Entries in Excel

We will create entries of the financial data in the journal.

• Consider every financial statement, including the initial balance sheet and the newly created balance sheet.
• Calculate every financial statement with these two sheets.
• Enter the following formula in Cell D5:

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

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

We have created 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

Steps:

• Create a spreadsheet like the following image.
• Enter the initial and highest balance.

• Select cell C5 and 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.

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

• Create another Data Validation list for the Long and Short positions of the traders.

• Enter the following formula in cell I5 to calculate the Risk/Reward ratio:

• Enter the data according to the market infrastructure and complete the datasheet.

We have created a Forex Trading Journal in Excel.

How to Post Transactions Using Journal Entries in Excel

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.

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

Steps:

• 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.
• Copy and paste the account numbers and titles from the chart of accounts.
• Calculate total debits and credits using formulas. The total formula tracks the total dollar amount of all debits and credits.

Here is a sample journal entry that we have created.

Download the Practice Workbook

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