How to Build a CRM in Excel (Step by Step, With Formulas)

TL;DR: You can run a CRM in Excel with three tables: Contacts, Deals and Activities, linked by an ID column, with drop-down lists for stages and a formula that highlights overdue follow-ups. It works well for one to three people and a few hundred active deals. Move to CRM software when several people edit at once, or when follow-ups start slipping because Excel cannot remind anyone.

A CRM in Excel is a workbook that stores your customers, your open deals and your next actions in structured tables, so you can filter, total and follow up without paid software. The setup below takes about an hour. It uses only built-in features: Excel tables (Ctrl+T), data validation drop-downs, conditional formatting and COUNTIFS/SUMIFS. Every formula is written out so you can paste it.

Is an Excel CRM right for you?

An Excel CRM is right for you if you have a small team, a simple sales process and fewer than a few hundred live deals. It is the wrong choice if many people need to update records at the same time or if you rely on automatic reminders.

Excel is not limited by size: a worksheet holds 1,048,576 rows by 16,384 columns (Microsoft: Excel specifications and limits). It is limited by people. Nothing in Excel tells you a follow-up is due unless you open the file.

How do you create a CRM in Excel?

Create three sheets named Contacts, Deals and Activities, turn each into an Excel table, and link them with an ID. Then add drop-downs, an overdue highlight and a summary sheet.

Step 1: Build the contact manager

On the Contacts sheet, type these headers in row 1, select them plus one empty row, and press Ctrl+T (tick "My table has headers"). On the Table Design tab, set the table name to Contacts.

Format Phone as Text before typing so Excel does not drop the leading + or turn long numbers into 9.82E+11.

Step 2: Build the deal pipeline

On the Deals sheet, create a table named Deals with: DealID, ContactID, Deal name, Stage, Value, Currency, Probability, Expected close, Next step, Next step date, Owner, Lost reason.

Pull the company name in automatically so you never retype it. In a Company column, use:

`` =XLOOKUP([@ContactID], Contacts[ContactID], Contacts[Company], "not found") ``

XLOOKUP works in Excel for Microsoft 365, Excel 2024 and Excel 2021, but not in Excel 2016 or 2019 (Microsoft: XLOOKUP). On older versions use:

`` =IFERROR(INDEX(Contacts[Company], MATCH([@ContactID], Contacts[ContactID], 0)), "not found") ``

Fill Probability from the stage so forecasts stay consistent. Add a small table named Stages on a Lists sheet with Stage and Prob columns (Qualified 10%, Discovery 25%, Quote sent 50%, Negotiation 75%, Won 100%, Lost 0%), then:

`` =XLOOKUP([@Stage], Stages[Stage], Stages[Prob], 0) ``

Step 3: Add drop-down lists for stage, source and owner

Drop-downs stop "Quote Sent", "quote sent" and "Qt sent" from becoming three stages. Select the Stage column in Deals, go to Data > Data Validation, on the Settings tab choose Allow: List, and in Source enter =INDIRECT("Stages[Stage]") (Microsoft: data validation). For short fixed lists you can type values separated by commas, for example Website form,Referral,WhatsApp,Trade fair,Cold outreach.

Repeat for Source, Owner, Status and Currency.

Step 4: Highlight overdue follow-ups

This is the closest Excel gets to a reminder. Select the data rows of the Deals table (say A2:M500), go to Home > Conditional Formatting > New Rule > Use a formula, and enter:

`` =AND($J2<>"", $J2<TODAY(), $D2<>"Won", $D2<>"Lost") ``

Here J is Next step date and D is Stage; adjust the letters to your layout. Choose a red fill. Add a second rule with =$J2=TODAY() and an amber fill for today's follow-ups. Sort by Next step date each morning and work top to bottom.

Step 5: Log activities

On Activities, create a table with Date, DealID, Type (Call, Email, Meeting, WhatsApp), Summary and Next step date. One row per touch keeps the deal row clean. To see the last contact date on each deal, add this column in Deals:

`` =MAXIFS(Activities[Date], Activities[DealID], [@DealID]) ``

Format it as a date; a result of 0 means no activity has been logged.

Step 6: Build the pipeline summary

On a Summary sheet, list your stages in A2:A7 and use:

If you sell in more than one currency, never add rupees and dollars in one total. Add Deals[Currency], "INR" as an extra condition to each SUMIFS and keep one row per currency. For a visual pipeline, insert a PivotTable from Deals with Stage in Rows and Value in Values, then a funnel or bar chart on top of it.

Step 7: Import and clean your existing contacts

Most people start with contacts scattered across phone exports, email and old invoices. Paste them into a scratch sheet first, never straight into the table. Then:

1. Split combined fields with Data > Text to Columns, for example "Priya Shah, Shah Interiors" on the comma.
2. Remove stray spaces with =TRIM(A2) and fix capitalisation of names with =PROPER(A2).
3. Lower-case emails with =LOWER(TRIM(C2)) so duplicates match.
4. Run Data > Remove Duplicates on the Email column, then on Phone.
5. Give each row a ContactID: ="C-"&TEXT(ROW()-1,"0000"), then Copy > Paste Special > Values so the IDs never change when rows move.
6. Paste the cleaned rows as values below the Contacts table; it expands automatically.

Phone exports from Google Contacts or an iPhone usually arrive as CSV with one column per label (Mobile, Work, Home). Keep one primary number in Phone and put others in Notes rather than adding columns you will not maintain.

How should you share an Excel CRM with a team?

Share it by saving the workbook to OneDrive or SharePoint and using co-authoring in Microsoft 365, so several people can edit the same file. Do not use the old Shared Workbook feature: it cannot be turned on in any workbook that contains Excel tables, which this design relies on (Microsoft: Excel specifications and limits). Google Sheets is an alternative; it supports XLOOKUP, data validation and conditional formatting, though the table syntax differs.

Rules that keep a shared sheet usable:

1. One owner of structure. One person adds columns, stages and lists. Everyone else only adds rows.
2. Never sort a single column. Sort from the table header so rows stay intact.
3. Protect the formula columns with Review > Protect Sheet, leaving data columns unlocked.
4. Keep dated backups. Save a copy weekly, for example CRM_2026-10-24.xlsx, or rely on OneDrive version history.

What tips keep an Excel CRM accurate?

Review the data on a fixed schedule; an Excel CRM fails slowly as rows go stale.

• Weekly: clear every red row: log the follow-up or move the deal to Lost with a reason.
• Monthly: remove duplicates with Data > Remove Duplicates on Email, check the win rate, and ask which Source produced the won deals.
• Quarterly: archive Lost deals older than 90 days to an Archive sheet so filters stay fast.
• Ask the right questions: which stage has the most deals sitting longest, and which owner has the most overdue rows. The summary sheet should answer both without filtering.

When is it time to switch to dedicated software?

Move when Excel's missing parts start costing sales. Concrete signs:

• Follow-ups are missed because nobody opened the file, even with red highlighting.
• Two people overwrite each other's edits more than once a month.
• You retype the same client details into quotes and invoices.
• You need email, calls or form submissions logged without copy and paste.
• Your summary needs per-rep forecasts or stage-to-stage conversion over time.

When you move, the three-table design pays off: most CRMs import Contacts and Deals from CSV, mapping ContactID, Stage and Value to their fields. Keep your stage names and exit rules the same so the history still means something. For the concepts behind the columns, see lead vs prospect and what a sales funnel is.

Where does LuckPanda fit?

LuckPanda keeps client records that feed quotes and invoices directly, so a won deal does not mean retyping details, and its leads and pipeline are being built. Until then, pair your Excel CRM with the free quote generator for priced quotations in any currency.