CRM Excel Template and Client Spreadsheet

Set Up Your Client and Loan Registers

Keep one row per person in Clients and one row per application in Loan files. Give each person a unique Client ID, even when two people share a name. Give each application its own Loan ID and use the existing Client ID when someone comes back for a second loan.

In the fictional example, Avery Reed at C-101 has two files, L-201 for a purchase and L-202 for a refinance. The other Avery Reed is C-102. Each loan needs a stage and an assigned staff member. Use the stage names shown in the dashboard, plus Settled and Withdrawn for closed files.

Use Follow-ups for dated actions and contact history. Each row needs a Task ID, the Client ID, a type, an assignee, a status and the next action. Add the Loan ID for an application task. A client review can also link to a loan if it concerns one. Leave dates empty until you know them.

Type or paste each register in the column order shown above, as rows copied from a spreadsheet or as comma-separated text. Use YYYY-MM-DD for dates and give IDs at least one letter. Each register holds up to 500 rows. To start with an empty workbook, clear the fictional registers and download the workbook.

Use the Dashboard and Follow-Ups

The dashboard counts Enquiry, Preparing, Lodged and Approved as open loan files. In the example, L-201 starts at Enquiry. When the application goes in, change its stage to Lodged. The Enquiry count drops from one to zero and Lodged rises from one to two. Its existing deposit task and first-call note stay with L-201.

An open application task is overdue when its due date is before today and the loan is still open. A task due today stays out of that count. In the workbook, filter the Follow-ups Queue column to Overdue application. Mark a completed task Done and add a fresh row for the next action so the earlier note stays in the history.

Client reviews have their own count. On Follow-ups, Review due marks open client review tasks due within 30 days or already past due. Find settled loans due for a review in the Loan files Review queue. The workbook uses Next review date when you assign one. Otherwise it uses the next settlement anniversary, with 28 February for a leap-day settlement in a non-leap year.

Each qualifying loan or follow-up counts as one review entry. Use one reminder per planned conversation if you want the count to match the number of calls.

Open the Template in Excel or Google Sheets

Download the editable XLSX workbook and open it directly in Excel. For Google Sheets, upload the XLSX to Drive, open it in Sheets and choose File > Save as Google Sheets. The workbook uses formulas supported in both editors and includes drop-down validation for loan stage, follow-up type and status.

After conversion, change a fictional loan stage and check that the two stage counts update. Open the Stage drop-down on that row before adding real records. If the drop-down is missing, recreate it using Google Sheets data validation. Keep the calculation columns and fill the prepared rows first.

For Airtable, download the CSV bundle and unzip it. Start with the Clients CSV and make Client ID the primary field. In the Loan files table, set Loan ID as the primary field and link Client ID to Clients. Add the Follow-ups table last and identify each record by Task ID. Link Client ID and any Loan ID to the matching tables.

Keep IDs and phone numbers as text. Use date fields for settlement, review and due dates. Set Stage, Type and Status to single select. Build the pipeline and due-date views in Airtable and set the base’s access permissions there. The CSV bundle carries records and column names.

Try the two fictional loans against C-101 before importing real files. Check that both link to the same client and the second Avery Reed remains separate. An unmatched ID can create a new record during Airtable’s linked-record import. Correct an unmatched ID in the CSV before retrying.

For a native workspace with its own database relationships, use the Notion CRM guide for mortgage brokers.

Keep the Client Register Controlled

Give named staff the access they need and revoke it when they leave. Keep identity documents in your brokerage’s approved document system. If you add a link to a loan record, restrict the linked folder to authorised staff and check that the link uses those permissions.

Keep a restricted backup copy in a separate approved location and check that you can restore it. Worksheet protection helps prevent accidental edits to formulas. Control who can open the workbook through your storage permissions. The OAIC’s guide to securing personal information explains access controls and backups.

If the same client has two IDs, confirm that both records belong to that person. Move the affected loan and follow-up links to the ID you are keeping, then clear the duplicate’s input cells. For a mistaken stage, correct the Stage cell on the original loan row. Check Rows to check on the dashboard and compare the affected totals. Keep the calculated cells intact.