Build the real Excel workbook with clean table structure and professional formatting
Build Sarah's Professional Ledger Table
Now you'll build Sarah's real Excel ledger table. Download the starter workbook, follow the build sequence, and produce a professional table structure ready for automated formulas in the next lesson.
Workbook Contents
- • Sheet 1: Raw transaction data (Sarah's TechStart transactions)
- • Data includes: Date, Description, Account, Type, Debit, Credit columns
- • Pre-loaded: 12 sample transactions from Sarah's business
- • Your job: Convert to professional Excel Table with proper formatting
Important: Save the downloaded file to your computer with a clear name like "Sarah_Ledger_Table_YourName.xlsx" before you begin.
Step 2: Build Sequence (Follow in Order)
1.1 Check Data Quality
- Review the 12 pre-loaded transactions
- Confirm all cells have values (no blanks)
- Verify Debit/Credit columns have numbers (not text)
- Check that Account column has proper account names
1.2 Apply Number Formatting
- Select columns E and F (Debit and Credit)
- Apply Currency format: Ctrl+Shift+$ (Windows)
- Verify: 2 decimal places, comma separators, $ signs
- Format negatives as red text: $(500.00)
Verification Checkpoint 1
Before continuing: Confirm that all monetary values show "$" and 2 decimal places. No plain numbers should remain in Debit/Credit columns.
2.1 Select Data Range
- Click any cell in the transaction data (A1:F13)
- Excel will automatically detect your data range
- Confirm the selection includes all 6 columns
2.2 Create Table
- Press Ctrl+T (Windows) or Cmd+T (Mac)
- CRITICAL: Check "My table has headers"
- Click OK to create the table
- Observe: Table gets blue banding and filter arrows
Verification Checkpoint 2
Before continuing: Confirm that you see blue/white row banding, filter arrows in all headers, and the Table Design tab appears in the ribbon.
3.1 Rename Table
- Go to Table Design tab in the ribbon
- Find "Table Name" box (default: Table1)
- Type:
LedgerTable - Press Enter to apply the name
3.2 Test Table Features
- Click filter arrow on Account column
- Select "Cash" to see only cash transactions
- Clear filter to show all transactions
- Click in last row, add a test transaction
Verification Checkpoint 3
Before continuing: Confirm table name is "LedgerTable" in Table Design, filter buttons work on all columns, and new rows automatically format with currency when you add them.
4.1 Apply Professional Styling
- In Table Design, choose a professional table style
- Adjust font size to 11 or 12 for readability
- Ensure column headers are bold and clear
- Check that banding colors are professional (not garish)
4.2 Save Final Workbook
- Save your workbook (Ctrl+S or Cmd+S)
- Name it clearly: "TechStart_LedgerTable_YourName.xlsx"
- Keep this file—you'll use it in Lesson 05
- Take a screenshot for your records
Verification Checkpoint 4
Before finishing: Save your file, reopen it to confirm table persists, and verify that all 12 original transactions plus any new ones are properly formatted.
| Date | Description | Account | Type | Debit | Credit |
|---|---|---|---|---|---|
| 2/2/2025 | Bakery website billed | Accounts Receivable | Asset | $2,200.00 | $0.00 |
| 2/2/2025 | Bakery website billed | Service Revenue | Revenue | $0.00 | $2,200.00 |
| 2/4/2025 | Supplies purchased | Supplies Expense | Expense | $150.00 | $0.00 |
Note: This is just the first 3 transactions. Your table should include all 12 transactions with consistent formatting, blue/white banding, and filter arrows.
Technical Requirements
- â–¡Excel Table created from data range (Ctrl+T applied)
- â–¡Table named "LedgerTable" (or similar clear name)
- â–¡6 columns: Date, Description, Account, Type, Debit, Credit
- â–¡Currency formatting on Debit and Credit columns
- â–¡Filter arrows visible on all column headers
- â–¡Professional table styling applied
Verification Tests
- â–¡Filter by Account works (shows subset of transactions)
- â–¡New row automatically applies currency formatting
- â–¡Workbook saved with clear filename
- â–¡Table persists when file is reopened
- â–¡All 12 transactions present and formatted correctly
What You'll Produce
By the end of Phase 4, you'll have a professional Excel Table containing Sarah's TechStart transaction data, properly formatted and structured for automated formulas in Lesson 05. This table is the foundation of Sarah's investor-ready smart ledger system.
Problem: Can't create table
Cause: Data not in contiguous range
Fix: Delete blank rows/columns between data
Problem: First row becomes header
Cause: Forgot "My table has headers" checkbox
Fix: Delete table, start over, check box
Problem: Formatting lost on new rows
Cause: Applied format before creating table
Fix: Select table column, re-apply format
Problem: Filter buttons missing
Cause: Filter options turned off
Fix: Table Design → Filter Button → On
Ready for Phase 5!
Once your Excel Table meets all Definition of Done criteria, you're ready for the assessment phase. In Phase 5, you'll demonstrate your understanding of table structure and complete a brief artifact task.
Next Phase Preview:
Phase 5 includes a short technical check on Excel Tables and a brief artifact task where you'll explain why your table structure decisions matter for Sarah's investor presentation.