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
Entry Date in A1.
Account Name in B1.
Debit/Credit in C1.
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:
your General Journal Worksheet active, go to Data | Pivot Table & Pivot
Chart Report. Click Next.
B1: D50. Click Next.
New Worksheet and then click Finish.
and drop the Name Of Account field to the Row fields
and drop the Debit/Credit field to the Column fields
and drop the Amount field to the Data field area.
the Pivot Table button in the Pivot Table toolbar and select Table Options.
the check mark from the Grand totals for rows check box.
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.