Explore Courses

How to Track Billable Hours, Cases, and Client Trust Accounts Without Expensive Software

 

Excel for Law Firms: How to Track Billable Hours, Cases, and Client Trust Accounts Without Expensive Software

Most law firms are bleeding money and they don’t even know it.

According to the American Bar Association’s 2025 Legal Technology Survey, solo and small firms lose an average of 12% of billable hours due to poor time tracking. That’s $15,000 to $40,000 per attorney per year left on the table because time was tracked on sticky notes, remembered at the end of the day, or entered incorrectly.



Clio, MyCase, and PracticePanther are great. But they cost $79 to $129 per user per month. If you’re a solo attorney, a small firm, or a law student learning practice management, you don’t need to pay that yet.

You need a single, reliable Excel system that tracks three things that run a law firm: Cases, Time, and Money.

This is the exact system we teach at LaxiLab Academy for professional services firms. No hype, just a system that works, is audit-ready, and can be automated later.

Why Law Firms Still Use Excel (Even in 2026)

Legal practice management software is powerful, but Excel remains the backbone for 74% of small law firms for three reasons:

  1. Flexibility: You can build a tracker for personal injury settlement values that no software offers out-of-the-box.
  2. Client Trust Accounting Compliance: Many state bars require you to maintain a separate ledger for IOLTA/trust accounts. Excel gives you full control and a clear paper trail for audits.
  3. Offline & Private: Client data never has to leave your computer if you don’t want it to.

The problem is not Excel. The problem is using Excel like a notepad instead of a database.

We are going to build it like a database.

The 3-Sheet System Every Law Firm Needs

Forget one giant sheet. You need three connected tables, just like a real database.

Sheet 1: Clients & Matters Database (Your Source of Truth)
Sheet 2: Billable Hours Log (Where Money is Made)
Sheet 3: Trust & Retainer Ledger (Where Compliance Lives)

Let’s build them.

Sheet 1: The Clients & Matters Database



This is the most important sheet. If this is messy, everything else breaks. This is your master list.

Create a new workbook named Law_Firm_Tracker_2026.xlsx. Rename Sheet1 to CLIENTS_MATTERS.

Create these columns in Row 1:

A: Matter ID (e.g., SMITH-001) - This is your primary key. Never duplicate it.
B: Client Name
C: Matter Description (e.g., Smith vs. Jones - Breach of Contract)
D: Practice Area (Personal Injury, Family Law, Corporate, Criminal Defense)
E: Attorney Responsible
F: Date Opened
G: Status (Active, Pending, Closed, On Hold)
H: Statute of Limitations / Critical Deadline
I: Hourly Rate

Pro Tip for Law Students: Use Data Validation for Status and Practice Area. Select column D, go to Data > Data Validation > List, and enter: Personal Injury,Family Law,Criminal Defense,Corporate,Real Estate,Other. This prevents typos that break your dashboard later. For Matter ID, use a simple formula: =LEFT(B2,5)&"-"&TEXT(ROW()-1,"000")

This sheet is now your single source of truth. Every other sheet will pull from here.

Sheet 2: The Billable Hours Log (Stop Losing 12%)



This is where attorneys lose the most money: reconstructing time at the end of the day from memory.

We will build a log that takes 15 seconds to enter and is impossible to forget.

Create a new sheet named TIME_LOG.

Columns:

A: Entry ID (Auto)
B: Date
C: Matter ID (Dropdown from Sheet1!)
D: Client Name (Auto-filled)
E: Timekeeper (Attorney/Paralegal Name)
F: Task Code (Use ABA codes: L100 Research, L200 Drafting, L300 Court Appearance)
G: Description (Be specific for billing compliance - “Drafted Motion to Dismiss, 2.5 hrs” not “Worked on case”)
H: Hours (Use decimal: 0.1 = 6 minutes)
I: Rate (Auto-filled)
J: Billable Amount (Formula)
K: Billed? (Yes/No)
L: Invoice #

The Automation That Saves You:

In column C, set Data Validation > List > Source = =CLIENTS_MATTERS!$A$2:$A$1000. Now you can only select valid Matter IDs.

In column D (Client Name), enter this formula in D2 and drag down:
=XLOOKUP(C2,CLIENTS_MATTERS!$A$2:$A$1000,CLIENTS_MATTERS!$B$2:$B$1000,"Client Not Found")

In column I (Rate), same logic:
=XLOOKUP(C2,CLIENTS_MATTERS!$A$2:$A$1000,CLIENTS_MATTERS!$I$2:$I$1000,0)

In column J (Billable Amount):
=H2*I2

Now you have a time entry system that auto-fills client and rate just by selecting a Matter ID. This eliminates 80% of entry errors that cause billing disputes.

The Golden Rule for Billable Hours: Enter time contemporaneously. Don’t wait. Keep this sheet open all day. The ABA Model Rule 1.5 requires billing entries to be reasonable and documented. “Drafting” is not reasonable. “Drafted 3-page client letter re: settlement offer per client request” is.

Sheet 3: The Client Trust & Retainer Ledger (IOLTA Compliance)



This is where most solo attorneys get in trouble with the State Bar. Commingling client funds with operating funds can lead to disbarment.

You need a separate ledger per client matter.

Create a new sheet named TRUST_LEDGER.

Columns:

A: Date
B: Matter ID
C: Client Name (XLOOKUP again)
D: Transaction Type (Retainer Deposit, Trust Deposit, Expense Paid from Trust, Fee Transfer to Operating)
E: Description
F: Deposit (Money INTO trust)
G: Withdrawal (Money OUT of trust)
H: Trust Balance (Running Balance)

Critical Formula for Running Balance in H2:
=F2-G2
In H3:
=H2+F3-G3
Drag down. This gives you a per-row running balance, which is required for trust account audits.

Compliance Rule: Your Trust Balance in this sheet must exactly match your bank’s IOLTA account balance for that client. Reconcile it weekly. Never let a client’s trust balance go negative. In Excel, use Conditional Formatting: Select H2:H1000 > Conditional Formatting > Highlight Cells Rules > Less than > 0 > Red Fill. If any cell turns red, you have a compliance violation.

This single sheet has saved law firms during random bar audits.

Step 4: Build Your Law Firm Dashboard (The Attorney View)



Attorneys don’t want to scroll through 3 sheets. They want one dashboard that answers: How much money did we make this month? What cases are at risk? Which clients owe us?

Create a new sheet named DASHBOARD.

At the top, create 4 KPI boxes using simple formulas:

1. Total Unbilled Hours (Money left on table):
=SUMIFS(TIME_LOG!J:J,TIME_LOG!K:K,"No")

2. Total Billable Amount This Month:
=SUMIFS(TIME_LOG!J:J,TIME_LOG!B:B,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1))

3. Active Matters Count:
=COUNTIF(CLIENTS_MATTERS!G:G,"Active")

4. Matters Approaching Statute of Limitations (Next 30 Days):
=COUNTIFS(CLIENTS_MATTERS!H:H,"<="&TODAY()+30, CLIENTS_MATTERS!H:H,">="&TODAY(), CLIENTS_MATTERS!G:G,"Active")

Then, create two tables:

Table 1: Revenue by Attorney/Paralegal:
Use a PivotTable from TIME_LOG with Rows = Timekeeper, Values = Sum of Billable Amount. This instantly shows who is most profitable.

Table 2: Cases at Risk:
=FILTER(CLIENTS_MATTERS!A:H, (CLIENTS_MATTERS!H:H <= TODAY()+30)*(CLIENTS_MATTERS!G:G="Active"), "No critical deadlines")

This one formula shows every active case where the statute or critical deadline is within 30 days. This is a malpractice prevention tool.

Step 5: From Spreadsheet to System (The Automation Part)



A great spreadsheet is good. An automated system is better.

Once your 3 sheets are working, you can automate it in 20 minutes using the LaxiLab AI Automation Stack:

  1. Voice-to-Time Entry: Use an AI automation in Make.com. You say “Hey Siri, log 0.5 hours for Matter Smith-001 drafting motion” -> It parses it with ChatGPT and adds a new row to TIME_LOG automatically. No more end-of-day reconstruction.
  2. Automatic Deadline Alerts: Connect your CLIENTS_MATTERS sheet to Gmail via Zapier. Rule: IF Deadline is within 14 days AND Status = Active, THEN send email to Attorney Responsible with subject “URGENT: SOL Approaching for [Matter ID]”.
  3. Client Invoice Generation: Use a Google Docs template + Document Studio. It pulls all unbilled entries for a specific Matter ID from TIME_LOG and generates a professional invoice PDF ready to send.

This is how you go from a $100/hr attorney who tracks time poorly to a $250/hr firm that runs like a business.

Final Compliance Checklist for US Attorneys

Before you use this, verify with your state bar:

  1. Never use one sheet for operating and trust accounting. Keep them separate, even if in the same workbook.
  2. Keep 7 years of records. Save this file monthly as Law_Firm_Tracker_2026_08.xlsx and back it up.
  3. No formula should ever overwrite a hard dollar amount entered. Deposits and withdrawals must be hard-entered, not calculated.

Excel is not a replacement for a CPA for your year-end, but it is the best daily operating system for a lean law firm.

#LegalTech #ExcelForLawyers #LawFirmManagement #BillableHours #AttorneyLife #LawPractice #LaxiLab