You have 50 customers, 20 active leads and three salespeople who all keep their contacts in their own systems – or worse, in their heads. When someone leaves, the knowledge walks out of the door with them. A simple CRM in Excel solves this in a couple of hours.

After 15 years of consulting work I have seen hundreds of companies begin their CRM journey. Most start wrong – they buy advanced systems for SEK 100,000 that are then never used. They should have started simply, with Excel.

An Excel-based CRM is a perfect fit for companies with 10-200 customers, 2-10 salespeople and a relatively simple sales process. This guide shows exactly how to build it.

When an Excel CRM works excellently

Excel is not the right CRM for everyone. But for many small businesses it is perfect.

The right size and complexity

An Excel CRM works when you have: – 10-200 customers/leads to track – 1-10 people who need access – A simple sales process (2-5 steps) – Limited integration requirements – A small budget (under SEK 20,000 annually for CRM)

A real example: A consulting firm with 80 corporate customers and three salespeople has used an Excel CRM for five years. They need to track contact details, last contact, upcoming meetings and deal status. Excel gives them all of this without monthly costs.

When Excel is NOT enough

Upgrade to a dedicated CRM if you have: – 500+ customers with complex history – 20+ simultaneous users – An advanced sales process (10+ steps) – A need for automatic e-mail integration – Integration with e-commerce, invoicing, support

For these situations we recommend HubSpot, Pipedrive or similar.

Read more: Excel vs dedicated tools: when is a template enough?

What an Excel CRM should contain

A functional CRM does not need to be complex. Here are the basic components.

Customer register (main sheet)

This is the core. A table with:

Basic info: – Customer name (company) – Contact person – E-mail – Phone – Address – Website

Categorisation: – Industry – Customer type (prospect, active, former) – Size (number of employees or turnover) – Priority (A/B/C customer)

Business info: – Sales owner – Status in the sales process – Deal value (potential or actual) – Last contact date – Next activity – Comments/notes

Interaction log

A separate sheet where you log all customer contact:

  • Date
  • Customer name
  • Type of contact (meeting, e-mail, phone)
  • Who from your side
  • Summary
  • Follow-up activity

This gives you a history and ensures nothing is forgotten.

Sales pipeline

Visualisation of where customers are in the sales process:

Example steps: 1. Lead (initial contact) 2. Qualified (confirmed interest and budget) 3. Quote sent 4. Negotiation 5. Closed (won or lost)

A pivot table automatically summarises the number of customers per step and the total deal value.

Dashboard

A summary sheet with key figures: – Total number of customers – Number of active leads – Pipeline value (sum of potential deals) – Average deal size – Conversion rate (how many leads become customers) – Activity in the last month

Use charts for a visual overview.

Build your Excel CRM – step by step

Now we will build a functional CRM from scratch.

Step 1: Create the main structure

Open a new Excel document. Create the following sheets:

  1. Customers (main register)
  2. Interactions (history)
  3. Pipeline (sales overview)
  4. Dashboard (summary)

Step 2: Build the customer register

In the “Customers” sheet, create the columns:

A B C D E F G H I J
Customer ID Company Contact person E-mail Phone Industry Status Value Owner Last contact

Tip for Customer ID: Use automatic numbering. In cell A2, type 1. In A3, type the formula:

=A2+1

Copy it down. Now every new customer automatically gets a unique ID.

Status dropdown: Create a dropdown for consistent data entry.

  1. Select column G (Status)
  2. Go to Data > Data Validation
  3. Choose “List”
  4. Source: Lead,Qualified,Quote,Negotiation,Customer,Former customer

Repeat for other categories (Industry, Owner, etc).

Step 3: Format it professionally

Header row: – Bold – Background colour (light blue) – Centred text – Freeze the top row (View > Freeze Panes > Freeze Top Row)

Cell formats: – E-mail: Hyperlink format (Ctrl+K to create a clickable link) – Phone: Text format (otherwise it is interpreted as a number) – Value: Currency format (SEK 1,234) – Date: Short date format (2026-01-30)

Conditional formatting for status: Select the status column: 1. Home > Conditional Formatting > New Rule 2. “Format only cells that contain” 3. Cell Value = “Customer” → Green background 4. Cell Value = “Lead” → Yellow background 5. Cell Value = “Former customer” → Grey background

Step 4: Build the interaction log

In the “Interactions” sheet, create the columns:

A B C D E F
Date Customer ID Company Contact type Owner Summary

Company name automatically: In column C, use VLOOKUP to fetch the company name based on Customer ID:

=LETARAD(B2,Kunder!A:A,Kunder!B:B,0)

This fetches the company name from the Customers sheet based on Customer ID. Now you only need to enter the ID, and the name is filled in automatically.

Step 5: Create the pipeline overview

In the “Pipeline” sheet, build a pivot table:

  1. Go to the “Customers” sheet
  2. Insert > PivotTable
  3. Place it in a new sheet “Pipeline”

Pivot table settings: – Rows: Status – Values: Count of Customer ID (number of customers per step) – Values: Sum of Value (total deal value per step)

Add a bar chart based on the pivot table for a visual overview.

Step 6: Build the dashboard

In the “Dashboard” sheet, use formulas to calculate key figures:

Total number of customers:

=ANTAL.OM(Kunder!G:G,"Kund")

Number of active leads:

=ANTAL.OM(Kunder!G:G,"Lead")+ANTAL.OM(Kunder!G:G,"Kvalificerad")

Total pipeline value:

=SUMMA.OM(Kunder!G:G,"<>Kund",Kunder!H:H)

(Sums the value for everyone who is not yet a customer)

Conversion rate:

=(Antal kunder) / (Antal kunder + Antal förlorade) * 100

Format the dashboard with large numbers, colour codes and pie charts.

Advanced features for your Excel CRM

Once the basics are in place you can add more functionality.

Automatic reminders

Highlight customers who need follow-up:

In the column “Next contact”, enter a date. Create a column “Reminder status” with the formula:

=OM(J2<IDAG(),"FÖRSENAD",OM(J2<=IDAG()+7,"DENNA VECKA",""))

Conditional formatting colour-codes red for overdue, yellow for this week.

Sending e-mails from Excel

With VBA you can send e-mails directly from the customer register:

Sub SkickaMail()
    Dim OutlookApp As Object
    Dim Mail As Object

    Set OutlookApp = CreateObject("Outlook.Application")
    Set Mail = OutlookApp.CreateItem(0)

    With Mail
        .To = Range("D2").Value  'E-post från kund
        .Subject = "Uppföljning"
        .Body = "Hej " & Range("C2").Value & "," & vbCrLf & _
                "Vi följer upp..."
        .Display  'Eller .Send för att skicka direkt
    End With
End Sub

Read more about automation: Automate Excel with VBA

Reports and analysis

Build monthly reports automatically:

  • Number of new customers per month
  • Sales development
  • Conversion rate over time
  • Performance per salesperson

Use pivot tables and timeline filters for dynamic analysis.

Sharing the Excel CRM with the team

Several people often need to work in the CRM. Here is how to solve that in Excel.

Option 1: SharePoint/OneDrive

Save the file on SharePoint or OneDrive. Several people can open it at once (with some limitations).

Advantages: – Automatic syncing – Version history – Basic collaboration

Disadvantages: – Can become locked if several people edit at the same time – Some delay in syncing

Option 2: Dedicated sheets per person

Each salesperson has their own sheet for their customers. A main sheet consolidates automatically.

Advantages: – No locking conflicts – Personal view

Disadvantages: – More complex to maintain – Risk of duplication

Option 3: Access control

Use Excel protection to lock certain parts:

  • The main structure locked
  • Only specific cells open for editing
  • Some sheets visible only to certain users

Review > Protect Sheet, choose what may be edited.

Common mistakes with an Excel CRM

I have seen these mistakes repeated.

Mistake 1: Too complex from the start

Companies want a perfect system from day one. 50 columns, complicated formulas, dashboards with 20 charts.

The result? Nobody uses it.

Solution: Start simple. Name, contact, status. Add features when you actually need them.

Mistake 2: No data quality control

Free-text fields quickly become messy: – “Consultant”, “consultant”, “Consulting services”, “consulting firm”

Dropdowns ensure consistent entry.

Mistake 3: Forgetting backups

Someone deletes a row by mistake. Two weeks of work gone.

Solution: Automatic backup via OneDrive, or manually copy it every week.

Mistake 4: Sharing the master instead of a copy

If everyone works directly in the main file, someone can wreck the structure.

Solution: Use a template approach – protected master, working copies for daily use.

Mistake 5: Not updating regularly

A CRM that is not updated becomes unusable. Old phone numbers, wrong status, forgotten follow-ups.

Solution: Schedule a weekly update. 15 minutes on Friday afternoon to update all contacts.

When it is time to upgrade from Excel

An Excel CRM works excellently – until it no longer does. Here are the signs.

Too many customers

When you have 500+ customers Excel becomes slow and unwieldy. Filtering takes time, reports take minutes instead of seconds.

Complex integration requirements

If your CRM needs to talk to: – E-mail marketing (Mailchimp) – Invoicing systems – Website forms – Phone systems

Then Excel integration becomes complex and fragile.

Real-time collaboration required

If 15 salespeople need to update at the same time, Excel cannot handle the load.

Automation reaches its limits

Dedicated CRMs can automatically: – Send follow-up e-mails – Assign leads based on rules – Calculate lead scoring – Sync calendar and meetings

In Excel this requires advanced VBA and is still limited.

Upgrade paths

When Excel is not enough, consider:

For small businesses (10-100 customers, budget <SEK 5,000/month): – HubSpot CRM (free basic version) – Pipedrive – Insightly

For mid-sized companies: – Salesforce – Microsoft Dynamics – Zoho CRM

The upside? An Excel-based CRM teaches you exactly what you need. When you upgrade, you know which features are actually used.

Frequently asked questions about CRM in Excel

Can I use Google Sheets instead of Excel? Yes, the same principles apply. Google Sheets has better real-time collaboration but weaker formula tools.

How often should the CRM be updated? Weekly at a minimum. Daily after customer contact is recommended. Schedule 10 minutes every morning.

Can I import data from my old CRM? Yes, most CRMs can export to CSV. Import into Excel and map the columns.

Do I need VBA to build an Excel CRM? No, the basic functionality is handled with formulas and pivot tables. VBA can add automation but is not necessary.

How do I share the CRM with a sales team? SharePoint/OneDrive for small teams (<5). Dedicated sheets per person for larger teams. A dedicated CRM for 10+ users.

Is an Excel CRM GDPR-safe? Excel itself is a tool. GDPR compliance is about how you use it: encryption, access control, data minimisation, a documented process. Read more at the Swedish Authority for Privacy Protection (IMY).

Next step: Build your Excel CRM today

An Excel CRM takes 2-3 hours to set up the first time but gives immediate benefit.

Start here:

  1. Open Excel and create the sheets according to the guide
  2. Fill in your 10 most important customers
  3. Log the latest interaction with each one
  4. Set the next follow-up date
  5. Share with the team

Need help?

If you want a ready-made template or professional build, we are happy to help:

  • Tailor-made CRM templates for your industry
  • Integration with other Excel tools
  • Training for sales teams
  • Migration from old systems

Book a free consultation

Further reading: – Excel templates for business: complete guide – Excel vs dedicated tools: when is a template enough? – Automate Excel with VBA

A structured customer register is the first step towards professional sales. Start today.

Written by Lukas Lilja, Excel Department. We have helped hundreds of small businesses build effective CRM processes, from Excel to advanced systems.

Need help with this? Our Excel templates can help you – the first consultation is free of charge.

Book a free call or phone 010-264 20 20.