The Solo Lawyer & Attorney Client Intake CRM Spreadsheet Guide

For solo attorneys, independent legal practitioners, and boutique law firms, managing client relationships requires balancing intake inquiries with strict court deadlines and legal retainer accounting. Whether practicing family law, estate planning, criminal defense, real estate law, or corporate litigation, your firm’s profitability depends on converting consultation leads, managing active case matters, tracking trust retainer draw-downs, and respecting statutes of limitations.

According to a benchmark report by Clio Trends, solo attorneys spend an average of only 2.5 hours per day on billable legal work, with over 45% of their workday consumed by administrative client management, phone consultations, conflict checks, and manual invoice tracking. More critically, up to 20% of billable legal hours are never invoiced due to poor matter logging.

While specialized legal practice management platforms like Clio, PracticePanther, MyCase, or Smokeball exist, their subscription fees ($1,000 to $2,500 per attorney per year) represent an unnecessary recurring overhead expense for solo lawyers and small firms.

In this comprehensive guide, we will walk you step-by-step through building a Lawyer Client Intake & Matter CRM Spreadsheet in Google Sheets or Microsoft Excel. You will learn how to structure client intake pipelines, track legal retainers, monitor court filing dates, log billable hours, and maintain complete administrative organization without monthly software fees.

Why Solo Lawyers & Small Law Firms Need a Spreadsheet CRM

Legal practices require strict organization, conflict checks, and retainer accounting. Operating out of scattered email folders and paper legal pads creates liability risks.

Here is why independent attorneys choose Google Sheets or Excel over heavy legal software suites:

Zero Monthly Overhead: Eliminating $150/month SaaS subscriptions keeps your law firm’s fixed overhead low, protecting net fee income.
Instant Mobile Matter Access: Storing client contact details, court case numbers, judge assignments, and opposing counsel details in a cloud Google Sheet allows you to check case notes on your smartphone while standing in court or attending depositions.
Custom Retainer Draw-Down Accounting: Legal trust accounting requires tracking client deposit retainers and calculating remaining unearned funds accurately. Spreadsheets allow custom formula accounting for every matter agreement.
Data Ownership & Attorney-Client Privilege: Keeping client records in your private Google Workspace account ensures complete data ownership and security without uploading sensitive case files to third-party SaaS databases.

Architecture of a Legal CRM Spreadsheet

To keep your legal practice organized, compliant, and billable, structure your spreadsheet into six core tabs:

[1. Client Intake Pipeline] ➔ [2. Active Matter Register] ➔ [3. Court & Statute Radar] ➔ [4. Retainer Ledger] ➔ [5. Billable Time Log] ➔ [6. Firm Analytics Dashboard]

Tab 1: `Client Intake Pipeline`

Tracks incoming prospective clients from initial contact form to consultation date, conflict check verification, fee agreement sent, and retainer signed.

Tab 2: `Active Matter Register`

Indexes every open legal case with Client Name, Matter Title, Case Number, Court Jurisdiction, Assigned Judge, Opposing Counsel, and Matter Status.

Tab 3: `Court & Statute Radar`

An automated schedule tracking court hearing dates, motion filing deadlines, discovery cutoff dates, and statutory limitation expiration warnings.

Tab 4: `Retainer Ledger`

Logs initial client retainer deposits, unearned trust balances, earned fee draw-downs, and low-retainer replenishment warnings.

Tab 5: `Billable Time Log`

Tracks daily legal tasks, billable hours (in 1/10th hour increments: 0.1, 0.2, 0.5), hourly rates, and invoice status.

Tab 6: `Firm Analytics Dashboard`

Displays executive practice metrics: total active billable hours, collected legal fee income YTD, low-retainer client alerts, and case acquisition rates by referral source.

Step-by-Step: Building Your Legal CRM Spreadsheet

Let’s build the Client Intake Pipeline and Retainer Ledger tabs in Google Sheets or Excel.

1. Structure the Client Intake Pipeline Tab

Create a tab named Client Intake and set up the following headers in Row 1:

Column	Header Name	Data Type	Description / Formula
A	Intake ID	Formula	`=IF(ISBLANK(B2), "", "LEGAL-" & TEXT(ROW()-1, "000"))`
B	Prospective Client	Text	Client full name or business entity
C	Practice Area	Dropdown	`Estate Planning`, `Family Law`, `Real Estate`, `Criminal Defense`, `Corporate`
D	Phone Number	Text	Primary contact phone
E	Email Address	Email	Primary contact email
F	Conflict Check Status	Dropdown	`Pending Check`, `Cleared`, `Conflict Found (Declined)`
G	Consultation Date	Date	Scheduled initial consultation date
H	Intake Stage	Dropdown	`1. Inquiry`, `2. Consult Scheduled`, `3. Fee Agreement Sent`, `4. Retainer Signed`, `5. Closed/Declined`
I	Quoted Retainer ($)	Currency	Required initial legal retainer amount
J	Follow-Up Status	Formula	Visual alert checking intake staleness

2. Automating Legal Retainer Draw-Downs & Low-Balance Alerts

Tracking legal retainer balances prevents performing legal work without secured funds. Set up dynamic retainer formulas on your Retainer Ledger tab:

Client / Matter	Initial Retainer ($)	Total Billed Fees ($)	Remaining Retainer Balance ($)	Retainer Status
Smith vs. Smith	$5,000.00	$3,850.00	`=B2 - C2` ($1,150.00)	`⚠️ Replenishment Due`
Apex Contracts	$3,500.00	$1,200.00	`$2,300.00`	`✅ Secure`

Remaining Balance Formula (Column D):

=IF(ISBLANK(B2), 0, B2 - C2)

Low Retainer Replenishment Alert Formula (Column E):

In cell E2, check if the remaining retainer balance has dropped below your firm’s minimum threshold (e.g. $1,500):

=IF(ISBLANK(B2), "No Deposit", IF(D2 <= 0, "🚨 EXHAUSTED (Stop Work)", IF(D2 <= 1500, "⚠️ Replenishment Due", "✅ Secure")))

Explanation:

If remaining retainer is $0 or negative, flags as 🚨 EXHAUSTED (Stop Work) (Work paused until deposit cleared).
If remaining balance is under $1,500, flags as ⚠️ Replenishment Due (Send retainer replenishment invoice).
Otherwise, displays ✅ Secure.

3. Setting Up the Court & Statute Deadline Radar

Missing a court motion deadline or statute of limitations filing date can lead to case dismissal or malpractice claims.

Create a tab named Court Radar and set up automated date tracking:

=IF(ISBLANK(Filing_Deadline), "", Filing_Deadline - TODAY())

Apply Conditional Formatting to highlight filing deadlines that are within 7 days in bright red (#FEE2E2).

5 Practice Management Hacks for Solo Attorneys

Log Time in 6-Minute Increments (0.1 Hours): Use standard legal billing increments in Column D of your Time Log tab (0.1 = 6 mins, 0.2 = 12 mins, 0.5 = 30 mins). Multiply hours by your hourly rate (=Hours * 350) for instant billable item calculations.
Perform Conflict Checks Systematically: Use the keyboard shortcut Ctrl + F (or Cmd + F) to search your entire spreadsheet database for opposing party names before scheduling consultation calls to avoid ethics conflicts.
Categorize Matter Case Types for Revenue Analytics: Tag cases by area of law to determine which practice area yields the highest hourly billing realization rate.
Attach Secured Case File Links: Store direct Google Drive folder links for each case matter in Column K for instant one-click access to court pleadings, contracts, and evidence scans.
Log Client Communication Timestamps: Keep brief notes of every phone call, email, and court appearance in your Activity Log to protect against client fee disputes.

Frequently Asked Questions (FAQ)

Can I track both hourly billing cases and flat-fee legal matters in the same CRM?

Yes! In your Active Matter Register tab, include a Fee Structure dropdown (Hourly, Flat Fee, Contingency). The spreadsheet applies custom billing calculations depending on the selected fee arrangement.

How do I manage client trust account rules (IOLTA) in a spreadsheet?

Keep unearned retainer funds logged in your Retainer Ledger tab. As billable legal hours are completed each week, transfer earned amounts from the trust balance to earned legal fee income.

Is Google Sheets compliant for storing attorney-client privileged information?

Google Sheets utilizes enterprise Google Cloud encryption. Ensure your Google account uses Two-Factor Authentication (2FA), restrict spreadsheet permissions to authorized law firm personnel, and avoid sharing sheets publicly on the web.

Upgrade to the ViaSheet Lawyer CRM Spreadsheet

Building a custom legal database with client intake pipelines, low-retainer warnings, court filing radars, and billable time ledgers requires hours of formula design.

If you want a pre-built, battle-tested spreadsheet engineered specifically for solo attorneys and small law firms, explore our Lawyer CRM Spreadsheet.

The ViaSheet Lawyer CRM features:

Firm Analytics Dashboard: Real-time stats on active legal matters, collected fee revenue YTD, billable time totals, and low-retainer client alerts.
Client Intake Pipeline: Track prospective clients from initial inquiry to conflict check, consultation, and retainer signed.
Active Matter Register: Index cases by matter number, court jurisdiction, judge, opposing counsel, and case status.
Retainer Ledger: Track initial deposits, earned fee draw-downs, remaining trust balances, and low-retainer replenishment warnings.
Court & Statute Deadline Radar: Color-coded alerts for hearing dates, motion filing deadlines, and statutory limitations.
Billable Time Tracker: Log legal tasks in 0.1-hour increments with automated invoice calculation.
One-Time Purchase: Pay just €39 once for lifetime access in Google Sheets and Microsoft Excel—no monthly fees or subscriptions ever.

Organize your legal practice, eliminate administrative stress, and protect your billable hours. Download the ViaSheet Lawyer CRM today!