A trial balance in Excel for review engagements is one tab listing every account with its prior-year balance, current unadjusted balance, adjustments and adjusted balance, plus dollar and percent change columns. Each account carries a lead sheet code, and separate lead sheet tabs pull their figures from that tab with formulas, so nothing is typed twice.
Laying out trial balance columns for a review engagement
A review trial balance tab needs one row per account and a fixed set of columns: account number, name, lead sheet code, prior-year adjusted balance, current unadjusted balance, adjustments, current adjusted balance, and the variance columns. Store every balance as one signed number, debits positive and credits negative, so each column totals zero.
With signed balances, every formula needs one reference, and a column that does not total zero has an error in it. Converting a client's debit and credit columns into one signed column is covered under trial balance import sign conventions.
The column-by-column layout
The layout below is for a fictional client, Example Supply Co., reviewed for the year ended December 31, 2026. Data starts in row 5; cells M1 and M2 hold the variance thresholds the flag column uses.
| Column | Heading | Contents or formula (row 5) |
|---|---|---|
| A | Account no. | The client's account number, stored as text |
| B | Account name | As on the client's trial balance |
| C | Lead | Lead sheet code, such as A for cash or B for receivables |
| D | PY adjusted | Last year's adjusted balance, pasted as values |
| E | CY unadjusted | This year's balance from the client's trial balance export |
| F | Adjustments | =SUMIFS(AJE!$E:$E,AJE!$C:$C,A5)-SUMIFS(AJE!$F:$F,AJE!$C:$C,A5), where the AJE tab's column C holds account numbers, column E debits and column F credits |
| G | CY adjusted | =E5+F5 |
| H | $ change | =G5-D5 |
| I | % change | =IF(D5=0,"n/a",H5/ABS(D5)) |
| J | Flag | =IF(AND(ABS(H5)>=$M$1,ABS(H5)>=$M$2*ABS(D5)),"Inquire","") |
| K | Ref | Workpaper reference and tickmarks |
The SUMIFS function reference from Microsoft gives the syntax used in column F. The dollar signs in $M$1 and $M$2 make them absolute references, which Microsoft's article on relative, absolute and mixed references explains do not change when a formula is copied down; F4 toggles between the reference types.
Below the last account, put a total under D, E, F and G. Each should be zero. Put a second check next to it: the count of accounts in column A, compared with the count on the client's report. A missing row still leaves a zero total if it had a zero balance, but it will not leave the count unchanged.
The worked trial balance
Here is the Example Supply Co. tab with the thresholds set to $5,000 and 10%. All figures are fictional. Two adjusting entries have been posted from the log: $9,000 of depreciation and a $4,000 expense accrual.
| A | B | C | D, PY adj. | E, CY unadj. | F, Adj. | G, CY adj. | H, $ chg | I, % chg | J |
|---|---|---|---|---|---|---|---|---|---|
| 1000 | Checking XXXX-4821 | A | 48,000 | 52,000 | 52,000 | 4,000 | 8.3% | ||
| 1010 | Savings XXXX-7310 | A | 20,000 | 20,500 | 20,500 | 500 | 2.5% | ||
| 1200 | Accounts receivable | B | 35,000 | 61,000 | 61,000 | 26,000 | 74.3% | Inquire | |
| 1210 | Allowance for doubtful accounts | B | -2,000 | -2,000 | -2,000 | 0 | 0.0% | ||
| 1500 | Equipment | C | 90,000 | 90,000 | 90,000 | 0 | 0.0% | ||
| 1510 | Accumulated depreciation | C | -30,000 | -30,000 | -9,000 | -39,000 | -9,000 | -30.0% | Inquire |
| 2000 | Accounts payable | AA | -22,000 | -24,000 | -24,000 | -2,000 | -9.1% | ||
| 2100 | Accrued liabilities | BB | -5,000 | 0 | -4,000 | -4,000 | 1,000 | 20.0% | |
| 3000 | Retained earnings | CC | -101,000 | -134,000 | -134,000 | -33,000 | -32.7% | Inquire | |
| 4000 | Sales | R | -350,000 | -400,000 | -400,000 | -50,000 | -14.3% | Inquire | |
| 5000 | Cost of sales | R | 210,000 | 240,000 | 240,000 | 30,000 | 14.3% | Inquire | |
| 6000 | Operating expenses | R | 98,000 | 126,500 | 4,000 | 130,500 | 32,500 | 33.2% | Inquire |
| 6500 | Depreciation expense | R | 9,000 | 0 | 9,000 | 9,000 | 0 | 0.0% | |
| Total | 0 | 0 | 0 | 0 |
Several flags have plain answers. The retained earnings change of $33,000 equals last year's net income (sales of $350,000 less $317,000 of costs), and accumulated depreciation moved by exactly the year's charge. Others, such as receivables up 74.3%, need a question to the client. How the adjusting entry log behind column F is kept is covered in the section on keeping an adjusting journal entry log in Excel.
How do lead sheets link to the review trial balance?
A lead sheet is a tab that summarizes one area of the financial statements, such as cash or receivables, and lists the accounts that make it up. Each lead sheet pulls its figures from the trial balance tab by account number or lead code, so a change to the trial balance updates every lead sheet without retyping.
A cash lead sheet for Example Supply Co., on a tab named A Cash:
| Row | A, Account no. | B, Name | C, PY | D, CY adjusted | E, Ref |
|---|---|---|---|---|---|
| 6 | 1000 | Checking XXXX-4821 | =XLOOKUP(A6,TB!$A:$A,TB!$D:$D,0) | =XLOOKUP(A6,TB!$A:$A,TB!$G:$G,0) | A-1 |
| 7 | 1010 | Savings XXXX-7310 | =XLOOKUP(A7,TB!$A:$A,TB!$D:$D,0) | =XLOOKUP(A7,TB!$A:$A,TB!$G:$G,0) | A-2 |
| 8 | Total cash | =SUM(C6:C7) | =SUM(D6:D7) | ||
| 9 | Check to TB | =C8-SUMIFS(TB!$D:$D,TB!$C:$C,"A") | =D8-SUMIFS(TB!$G:$G,TB!$C:$C,"A") |
With the example figures, row 8 shows $68,000 prior year and $72,500 current year, and row 9 shows zero in both columns.
Row 9 is the important one. If someone adds a new bank account to the trial balance with lead code A but forgets to add it to the lead sheet, the lead sheet total stays the same while the TB total for code A changes, and row 9 stops being zero.
Microsoft's XLOOKUP function reference gives the syntax, including the if_not_found argument used here to return 0. The same page notes XLOOKUP is not available in Excel 2016 or Excel 2019. In those versions, =SUMIF(TB!$A:$A,A6,TB!$G:$G) returns the same balance; the SUMIF function reference covers it.
Each balance sheet area gets its own tab: A cash, B receivables, C fixed assets, AA payables, BB accrued liabilities, CC equity, and one for revenue and expenses. The trial balance tab's lead codes are the map. Once every account has a code and every lead sheet's check row is zero, the workbook is complete in the sense that every dollar on the trial balance appears on exactly one lead sheet. The next step, building the balance sheet itself from the trial balance tab, is covered in the post on building a balance sheet from a trial balance in Excel.
Prior-year variance columns on a review trial balance
Prior-year variance columns compare each account's current adjusted balance with last year's, in dollars and as a percent. A flag column marks accounts that cross both a dollar threshold and a percent threshold. Those accounts get a question to the client and a written explanation. The thresholds are the firm's own policy for each engagement, not fixed numbers.
The AICPA's standard for reviews is AR-C section 90, Review of Financial Statements. The AICPA page for SSARS No. 25, which amends AR-C section 90, offers the amendment's text with a free account. This post does not restate what the standard requires; variance columns are a working tool, and the firm decides how they fit its procedures.
The example uses $5,000 and 10%, stored in cells M1 and M2. Those are illustrative figures for a fictional client with $400,000 of sales. A firm sets its own thresholds per engagement, often from its materiality judgment for that client, and records the basis on the workbook.
Requiring both thresholds is deliberate. A dollar threshold alone flags every large account that grew with inflation. A percent threshold alone flags a $300 office supplies account that doubled. Both together point to changes that are large and unusual at once.
Write the explanation next to the flag, in a column after K. Good explanations are specific and checkable: "AR up $26,000: December invoice to Example Retail Group of $24,000, collected 1/18/2027 per client; see B-2." Weak ones restate the number: "AR increased due to higher receivables."
Some changes need a question even without a flag. An account that did not move at all when it should have, such as a payroll tax liability with the same balance two years running, is as worth asking about as one that doubled. Read the variance column for zeros too.
Tickmarks and workpaper references in an Excel trial balance
Tickmarks are short symbols beside a number that say what was done to it: agreed to a statement, recalculated, inquired about, or traced to last year. A workpaper reference is the code of the tab or document that supports the number. Together they let a reviewer see what work stands behind each balance without asking.
In a spreadsheet, tickmarks are a convention the firm sets. Two common approaches:
- A legend of letters or symbols. Put a legend at the top of each tab, such as: T = traced to prior-year statements, A = agreed to bank statement, R = recalculated, I = inquired of client, see note. Type the letter in the Ref column beside the number.
- A Wingdings or symbol font. Some firms keep a small palette of check marks and circled letters in a formatted cell and copy it as needed. It looks more like paper tickmarks but is harder to search or filter.
Letters in a column are easier to work with than symbols. A filter on the Ref column shows every balance with no tickmark, which is the list of work not yet done.
Workpaper references link the trial balance to support. In the example, the checking balance on the cash lead sheet carries A-1, which is the tab holding the bank reconciliation and the December statement. For the procedures that tie client-prepared statements back to the ledger, see the section on tying client-prepared financial statements to the ledger.
Support documents arrive from clients through the request list. The post on a PBC request list for audits covers how to build that list; a review engagement uses a shorter version of the same idea. Tickmark A can only be written once the statement it refers to is in the file.
What breaks an Excel review trial balance?
An Excel review trial balance usually breaks in one of five ways: a number typed over a formula, a partial sort that scrambles rows, rows inserted outside a total's range, account numbers stored as text in one tab and numbers in another, or links to another workbook that moved. Each one can leave totals that look right.
Hard-coded numbers. Someone types 61,000 over the formula in column G to "fix" a balance. The cell now ignores the adjusting entry log. Before sign-off, scan each formula column for cells that hold a typed value instead of a formula.
Partial sorts. Sorting columns A to C without D to K reassigns balances to the wrong accounts. The totals still sum to zero, which is why this error is dangerous. Keep the data in one block and select all of it before sorting.
Excel tables and filtered totals. Microsoft's overview of Excel tables notes that a table's total row uses SUBTOTAL, which by default ignores rows hidden by a filter. A filtered total that looks like zero may not be the total of all accounts. Put the proof totals outside the table with plain SUM.
Rows outside the range. A new account added below the total row is left out of the total. Whole-column references in SUMIFS and XLOOKUP avoid this for the lead sheets, but a SUM over D5:D17 does not grow on its own.
Text versus numbers. Account number 1000 stored as text on one tab and as a number on another will not match in XLOOKUP, which returns the not-found value instead of the balance. Store account numbers as text everywhere.
External links. A trial balance that pulls from last year's file by link breaks when that file is renamed or moved. Paste prior-year balances as values instead.
Rolling the review trial balance forward to next year
Rolling the review trial balance forward means turning this year's workbook into next year's starting file. This year's adjusted balances become next year's prior-year column as values, the current-year and adjustment columns are cleared, and the tickmarks, explanations and engagement dates are reset. The formulas and lead sheets stay as they are.
Steps for Example Supply Co., moving from 2026 to 2027:
- Save the finished 2026 workbook as a final copy, and save a new file for 2027.
- Copy column G, CY adjusted, and paste it as values into column D, PY adjusted.
- Clear column E, CY unadjusted, and the adjusting entry log on the AJE tab.
- Clear the Ref column, the explanation column and any review notes.
- Update the period end dates in headers and the report title rows.
- Agree column D to the issued 2026 financial statements before any 2027 work. Every lead sheet's prior-year total should match the comparative column of the statements.
- Keep last year's thresholds visible but re-decide them for the new year.
Step 2 brings both balance sheet and income statement accounts across, which is what the variance columns need: last year's full-year sales compared with this year's full-year sales. The retained earnings row is the one that looks odd, because its prior-year figure is the opening balance and its current figure includes last year's profit. The worked example above shows that pattern.
Step 3 is where the new year's work starts: the client's 2027 trial balance export goes into column E. When that export or the client's ledger arrives as a PDF among the year's other documents, Schema CPA classifies it with the rest of the client's file and exports the figures to Excel or CSV. The workflow section on the homepage shows that sequence.