How to Create a Journal Using Microsoft Excel

A journal is a spreadsheet that tracks transactions in chronological order, as they occur. The journal includes the following information: date, account and the amount of the debit or credit to that account. Optional journal information includes reference numbers (invoice or check numbers, for example) and memos to describe the transaction. A general journal would be used for all transactions. Special journals for specific types of transactions may include an accounts payable journal, accounts receivable journal and/or a cash journal. The information in these special journals would also be entered into the general journal.

Open a new Excel file. From the menu, select “File.” From the drop-down list, select “Save As.” In the pop-up window, type in the file name and click “Save.” Here, the file was named “Accounts Payable Spreadsheet.”

Change the worksheet layout by selecting “File” from the menu. From the drop-down list, select “Page Setup.” In the pop-up window, on the “Page” tab, “Orientation” section, select the “Landscape” radio button. Select “OK.”

Add gridlines to the worksheet by selecting “File” from the menu. From the drop-down list, select “Page Setup.” In the pop-up window, on the “Sheet” tab, “Print” section, select the “Gridlines” checkbox. Select “OK.”

To print the column headings on all pages of the printed journal, select “File” from the menu. From the drop-down list, select “Page Setup.” In the pop-up window, on the “Sheet” tab, “Print Titles” section, enter “$1:$1” in the “Rows to repeat at top” field. Select “OK.”

Format column width by left clicking on the column header and then selecting “Format” from the menu. From the drop-down list, select “Column.” From the drop-down list, select “Width.” For column A, enter “10” for the width. For columns B through D, enter “20” for the width. For column F, enter “30” for the width. For column G, enter “15” for the width.

In the first row, label the columns as follows: A-Date, B-Account, C-Debit, D-Credit, E-Memo, F-Reference. Select the first row by left clicking on the row leader (1). Bold the column labels by holding down the “Ctrl” and “B” keys at the same time.

Format the “Date” column by right clicking on the column header (A). In the drop-down list, select “Format Cells.” In the pop-up window, select “Date” from the “Category:” listing. In the “Type” listing, select the desired date format. Here, the type “*3/14/2001” was chosen. Select “OK.”

Left align the “Account” column by left clicking the column header (C). Select “Format” from the menu. In the drop-down list, select “Cells.” In the pop-up window, on the “Alignment” tab, “Text Alignment” section, select “Left (Indent)” in the “Horizontal:” field. Select “OK.”

Format the “Debit” and “Credit” columns by right clicking on the column headers (D and E). In the drop-down list, select “Format Cells.” In the pop-up window, select “Accounting” from the “Category:” listing. Select “OK.”

In cell B2, enter the following formula: "==IF(C2>0,G2,REPT(" ",5)&G2)." Copy this formula down through the worksheet.

At the bottom of the last sheet of the journal, enter the following information: In column A, enter the label “Subtotals”; in column D, enter the formula "=SUM(D2:D36)," where D36 is the last entry in the “Debit” column; in column E, enter the formula "=SUM(E2:E36)," where E36 is the last entry in the “Credit” column; in column F, enter the label “Total”; in column G, enter the formula "=D37-E37," where D37 is the final cell in the “Debit” column and E37 is the final cell in the “Credit” column.

To set the print area, select “File” from the menu. From the drop-down list, select “Page Setup.” In the pop-up window, on the “Sheet” tab, in the “Print Area” section, type “A:F.” Select “OK.”

As credit and debit transactions occur, enter data in the appropriate columns on the next available row. Do not enter data into column B; copy down the formula from the cell above. This properly aligns account information, indenting credits and leaving debits left aligned.