Create a General Ledger in Excel from General Journal data

by Mary Ann Richardson | Mar 13, 2007 7:00:00 AM

Tags: Microsoft Office, Mary Ann Richardson, General Journal, General Ledger, Microsoft Excel, Microsoft Office Suite Excel Tips Newsletter

Takeaway: Convert your daily General Journal data into a more extensive, yet focused, General Ledger using Excel in this tip from Mary Ann Richardson.

Many businesses keep a journal of their daily transactions, called the General Journal. The General Journal entries, which can be entered directly into an Excel worksheet or imported from another application into Excel, record individual transactions in chronological order as debits and credits. These entries from their specific accounts form the General Ledger, from which users can derive other reports, such as the income statement.

To use Excel to create a General Ledger from General Journal data, you can set up a worksheet, which includes a number of array formulas that sum up the transaction into the proper accounts. Or, you can have Excel's Pivot Table feature summarize the data from the General Journal and build the General Ledger for you.

Before you can use Pivot Tables to build the General Ledger, you need to arrange the General Journal transaction data into four columns as follows:

1. Enter Entry Date in A1.
2. Enter Account Name in B1.
3. Enter Debit/Credit in C1.
4. Enter Amount in D1.

Say for the month of January you entered 50 transactions in the General Journal in rows 2 through 51. To create the General Ledger for this list of transactions, follow these steps:

1. With your General Journal Worksheet active, go to Data | Pivot Table & Pivot Chart Report. Click Next.
2. Select B1: D50. Click Next.
3. Click New Worksheet and then click Finish.
4. Drag and drop the Name Of Account field to the Row fields area.
5. Drag and drop the Debit/Credit field to the Column fields area.
6. Drag and drop the Amount field to the Data field area.
7. Click the Pivot Table button in the Pivot Table toolbar and select Table Options.
8. Clear the check mark from the Grand totals for rows check box.
9. Click OK.

Miss a tip?

Check out the Microsoft Excel archive, and catch up on our most recent Excel tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Print/View all Posts Comments on this article

HIBI General Ledger mpratt.architects@... | 06/04/07

RE: Create a General Ledger in Excel from General Journal data rajesh_nagumalla@... | 01/27/09