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