Summarize a general ledger with a PivotTable

A general ledger answers questions one line at a time. Managers ask questions about totals: how much went on advertising each month, how many invoices the sales team booked, what one department spent this quarter. A PivotTable turns the first into the second in a few choices, without a single formula. This page explains those choices, works through an example by hand and covers the mistakes that make a PivotTable show the wrong summary.

What a PivotTable does

A PivotTable groups the rows of a list and summarizes each group. You tell it four things:

• Rows: the field whose values run down the left side, one row per value. In a ledger summary this is usually the account.
• Columns: an optional second field whose values run across the top. Months are the classic choice, so you can read each account's spending period by period.
• Values: the number to put in each cell and how to summarize it. Sum of Amount adds the dollar amounts; Count of Amount counts the transactions.
• Filter: an optional field that limits which rows are included at all, such as one department or one account.

Each cell of the result answers one small question: "what is the total for this account in this month, counting only the rows the filter lets through?" Grand totals at the end of each row and the bottom of each column add those answers up again.

Nothing in the ledger changes. The PivotTable is a view of the data, so you can rearrange it as often as you like. That is also why it is a good habit to check one or two cells by hand: a PivotTable will display whatever summary you ask for, including the wrong one.

The MO-220 objective this covers

These exercises are MO-220 practice for "Prepare and analyze financial statements", under presenting financial data, which lists PivotTables alongside charts and dashboards. MO-220 task instructions describe the purpose rather than the steps. A task will read like "summarize expenses by account for each month", and you decide which field goes where and how the values are summarized. The exercises here are worded the same way. Accounting Exercises is not affiliated with Microsoft or Certiport.

In the practice exercises you build the summary in a guided PivotTable builder: a filter, rows, columns and values, with the resulting table shown as you choose. Your work is checked on the values the table shows, compared with a summary calculated directly from the ledger. Two layouts that give the same table are both right. The same thinking applies in Microsoft® Excel® workbooks, where you drag fields into the same four areas.

Before you build: is the ledger ready?

A PivotTable is only as good as the list under it. Before you start, check that:

• Every column has a heading. Headings become the field names you choose from.
• There are no subtotal or blank rows inside the data. A subtotal line would be counted as one more transaction and its amount added twice.
• Each fact sits in its own column. If you want to summarize by month, you need a month you can group on. Excel can group dates by month for you; a ledger may also carry a Month column, built with a date formula as on the convert dates to months page.
• Labels are consistent. "Rent Expense" and "Rent expense " with a trailing space become two separate rows. The clean text data page covers fixing that.

The practice ledgers have a Date, a Month, an Account, a Department and an Amount for each expense transaction, already clean and in date order.

Worked example: expenses by account and month

Northside Print Shop has twelve expense transactions for January to March 2026. The owner wants to see how much was spent on each account, with the months side by side.

Read the request and turn each phrase into a choice:

1. "for each account": Account goes in Rows.
2. "months side by side": Month goes in Columns.
3. "how much was spent": Values is Sum of Amount.
4. Nothing limits the transactions, so there is no filter.

The PivotTable then shows this (amounts calculated from the ledger above [My calculation]):

Check one cell by hand. Advertising in January is two lines, 746.27 and 896.71, which add to 1,642.98. Utilities in March is four lines: 625.62 + 321.56 + 283.39 + 542.66 = 1,773.23. Then check the corner: the grand total, 9,023.14, should equal the sum of the Amount column in the ledger. It does, so no transaction has been dropped or counted twice.

Notice the blank cells. There was no insurance in February and no utilities bill in January, so those cells are empty rather than zero. A PivotTable only shows combinations that exist in the data.

Counting instead of adding

Now suppose the owner asks a different question: how many expense transactions did each department record? The rows become Department and the values become Count of Amount. Admin has 4 transactions, Operations 2 and Sales 6, for a grand total of 12, which is exactly the number of lines in the ledger. When you count, the grand total is your check: it must equal the number of rows that the filter lets through.

Adding a filter

Finally, the Sales manager wants the account-by-month table for Sales only. Keep the layout from the worked example and add Department as the filter, showing only Sales. Advertising becomes 896.71 in January, 1,619.15 in February and 1,604.15 in March, a total of 4,120.01. Utilities becomes 396.24 in February and 826.05 in March (283.39 + 542.66), a total of 1,222.29. Insurance disappears, because Sales recorded none. The grand total is 5,342.30 [My calculation], smaller than the full ledger total, as it should be.

The filter does not change the shape of the table. It decides which ledger lines feed it. That is why a filter is the right tool when a request says "only" or names one department: the rows and columns stay what the reader expects.

Choosing the layout from the request

Most PivotTable mistakes come from reading the request too quickly. A few rules of thumb help:

• "For each …" or "by …" names the field for the rows. If there are two, the one described as "side by side", "across" or "per month" usually goes in the columns.
• "How much", "total", "spent" means Sum. "How many", "number of" means Count.
• "Only", "just", or a named department or account means a filter.
• Put the field with more values in the rows. A table with five accounts down the side and three months across is easier to read than the reverse, and it matches how statements and schedules are laid out.

Common mistakes

• Count instead of Sum. When you add a text field to Values, Excel counts it; when a numeric column contains blanks, it may also default to Count. A cell that shows 3 where you expected 1,642.98 is the giveaway. If the question is about dollars, the values must be a sum.
• Sum instead of Count. The reverse: a "how many" question answered with dollar totals. Check that the grand total equals the number of transactions, not the ledger total.
• Rows and columns swapped. All the numbers are right but the table is on its side, with months down the left and accounts across the top. It answers the question, but not in the layout requested, and it is harder to compare with a statement or budget that lists accounts down the side.
• The wrong field in the rows. Summarizing by department when the question asks about accounts gives correct totals for the wrong question.
• Forgetting the filter. Without it the table adds up every department. The layout looks right, which is what makes this mistake easy to miss; the grand total being equal to the whole ledger is the clue.
• Not refreshing. In Excel, a PivotTable does not update itself when the ledger changes. After adding or correcting transactions, refresh it before you trust the numbers.

The exercises recognize these mistakes and tell you which one you made.

PivotTables or formulas?

You can produce the same account-by-month table with conditional sum formulas, as on the totals by account with SUMIFS page. Formulas update the moment the ledger changes and can sit inside a formatted report. A PivotTable is quicker to build, quicker to rearrange and handles new accounts on its own, which makes it the better tool for exploring and for one-off questions. Many accountants use both: a PivotTable to find the answer, and formulas for the report that has to be produced every month in the same layout.

Either way, the summary should tie back to the books. The grand total of an expense summary should agree with the total of the expense accounts on the trial balance for the same period.

How the practice works

1. Read the request above the task. It names the company, what the owner wants to see and, sometimes, the one department that matters.
2. Scroll the ledger to get a feel for the accounts, months and departments.
3. Choose the filter, rows, columns and values. The PivotTable updates as you go.
4. Check a cell or the grand total by hand, then check your answer. Cells that match the expected summary turn green; the rest turn red.

"New numbers" gives you a fresh ledger for the same task, so you can practice until the choices are automatic.