Build real Excel workbook with self-auditing formulas and error flags
Build Self-Auditing Formulas in Sarah's Ledger
Now you'll add SUMIF formulas, balance checks, and red flags to your Lesson 04 workbook. Follow the build sequence and produce a self-auditing ledger that catches errors automatically.
Build Sequence (by Sarah's Audit Trail)
1.1 Insert new sheet: Right-click sheet tab → Insert → Blank sheet. Name it "Account Summary".
1.2 Set up structure:
- Column A: Account Name (list: Cash, Accounts Receivable, Service Revenue, Supplies Expense, etc.)
- Column B: Total Debits
- Column C: Total Credits
- Column D: Balance (Debit - Credit)
1.3 Apply professional formatting: Bold headers, currency on B:C, borders around summary.
Verification Checkpoint 1
Before continuing: Confirm Account Summary sheet exists with 4 columns, all account names listed, and currency formatting applied to totals columns.
2.1 Total Debits formula (Column B):
=SUMIF(LedgerTable[Account], A2, LedgerTable[Debit])
Change A2 to match your first account cell reference.
2.2 Total Credits formula (Column C):
=SUMIF(LedgerTable[Account], A2, LedgerTable[Credit])
2.3 Fill formulas down: Select both formulas and drag down to all accounts.
2.4 Balance formula (Column D): =B2-C2
Verification Checkpoint 2
Before continuing: Confirm each account shows correct totals, formulas reference LedgerTable columns (not fixed ranges), and all accounts are calculated.
3.1 Add trial balance section (below accounts):
- Row below last account: Label "Total Debits"
- Row below that: Label "Total Credits"
- Row below that: Label "Trial Balance Status"
3.2 Total Debits formula: =SUM(B2:B[last_account_row])
3.3 Total Credits formula: =SUM(C2:C[last_account_row])
3.4 Trial Balance Status formula:
=IF(total_debits_cell = total_credits_cell, "Balanced", "Out of Balance")
Replace cell references with your actual formula cell locations.
Verification Checkpoint 3
Before continuing: Confirm total debits and credits are calculated correctly, and Trial Balance Status shows "Balanced" (since your data should balance).
4.1 Flag trial balance errors:
- Select Trial Balance Status cell
- Home → Conditional Formatting → New Rule
- Rule type: "Use a formula to determine which cells to format"
- Formula:
=status_cell="Out of Balance" - Format: Red background, bold white text
4.2 Flag negative balances (optional but recommended):
- Select Balance column (Column D)
- Conditional Formatting → Highlight Cells Rules → Less Than
- Value: 0
- Format: Red text
4.3 Add documentation notes:
- Add a comment to Trial Balance cell: "Checks if ledger is balanced"
- Add a note to Account Summary sheet explaining purpose
Verification Checkpoint 4
Before finishing: Test conditional formatting by temporarily changing a value in LedgerTable. Confirm red flags appear when trial balance is off.
| Account Name | Total Debits | Total Credits | Balance |
|---|---|---|---|
| Cash | =SUMIF(...) | =SUMIF(...) | =B2-C2 |
| Accounts Receivable | =SUMIF(...) | =SUMIF(...) | =B3-C3 |
| Service Revenue | =SUMIF(...) | =SUMIF(...) | =B4-C4 |
| Supplies Expense | =SUMIF(...) | =SUMIF(...) | =B5-C5 |
Note: Your Account Summary should include all accounts from Sarah's ledger. Formulas use structured references (LedgerTable[Account]) to automatically update when new transactions are added.
Technical Requirements
- □Account Summary sheet exists with 4 columns
- □SUMIF formulas use LedgerTable structured references
- □All accounts show correct debit/credit totals
- □Trial balance check formulas in place
- □Conditional formatting highlights "Out of Balance"
- □Documentation notes explain each audit control
Verification Tests
- □Trial balance shows "Balanced" for current data
- □Test: Change a value → status turns "Out of Balance"
- □Test: Add new row to LedgerTable → formulas update
- □Workbook saved with clear filename
What You'll Produce
By end of Phase 4, you'll have a self-auditing ledger that automatically totals debits/credits by account, verifies trial balance, and highlights errors with red flags. Sarah can now prove her books are accurate to any investor.
Problem: SUMIF returns #VALUE!
Cause: Mixing text and numbers in range
Fix: Check for blank cells or text in Debit/Credit columns
Problem: Formulas don't update for new rows
Cause: Used fixed range like A2:A100
Fix: Use LedgerTable[Column] structured references
Problem: Conditional formatting doesn't trigger
Cause: Formula references wrong cell
Fix: Check cell reference in conditional formatting rule
Problem: Trial balance always shows "Out of Balance"
Cause: Typos in account names or missing data
Fix: Check Account column for consistent spelling
Ready for Phase 5!
Once your workbook meets all Definition of Done criteria, you're ready for the assessment phase. In Phase 5, you'll complete a short technical check and explain which audit control matters most for Sarah's investor presentation.