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

  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
  5. Drag
    and drop the Debit/Credit field to the Column fields
  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

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.