Create The Journal and Journal Entry with Excel (Part 1)

A journal includes all accounting transactions and is considered the historical record for a business entity. All transactions must be recorded through a journal entry that provides specific instructions in a line-by-line sequence. In this post, I will show you How to create the Journal and journal entry with Excel.


1. Make a list of your accounts in excel on sheet1, rename this sheet with the name "Accounts". You can read my article about How to Label Your Account in Excel.

2. Open Sheet2 on Excel and rename it as "Journal". Make the format of your journal, a common format usually includes date, P/R: The posting reference identifying the number of the account posted, Account Titles, Debit, and Credit. You can alse add Journal Code Number if you need it.


3. Insert the current date in a "Date Column" or transaction date. to insert the current date, just press "Ctrl ;". You can type the transaction date by retyping the current date.


4. Click the first row on "R/F" column, at a ribbon menu (for Ms Excel 2007 or more), select Data - Data Validation. You will see a new dialog box. At a setting tab, select List and type "=Account_Num" at source dialog. Make sure that Ignore Blank and In-cell dropwdown are active.




Note  that "Account_Num" is the name you have been prepared at the Account sheet for Account Number.

5. If you are correct in step 4, then the selected cell will appear the dropdown button and when clicked, it will show the entire list of account numbers that you have prepared earlier. Copy this cell down for the same rule.

Continue reading part 2.
Create The Journal and Journal Entry with Excel (Part 2)