How to build an Excel CRM (with template)
Topics
Your sales tactics are beginning to pay off, but you’ve suddenly got more inbound leads than you know what to do with. It’s time to upgrade the way you manage customer data.
Customer Relationship Management (CRM) software is the best way to manage your contacts and sales pipeline. The good news is, it’s easy to set up an Excel-based CRM that fits your needs.
In this article, we’ll help you decide whether an Excel CRM is right for you. Using our customizable CRM template, we’ll then explain how you can create your own Excel CRM. Finally, we’ll provide some high-level tips you can use to get the most out of your CRM system.
Download the CRM Excel template
The first step is to download our free template. We’ve prepared an Excel CRM – which can be uploaded as a Google Sheets CRM template – that you can customize to fit your needs. Once you’ve downloaded the template, it’s time to set it up to fit your personal CRM use case.
Get your free CRM Excel template
Is an Excel CRM right for you?
When most people hear “CRM”, they think of the platforms people use to manage their customer data. However, customer relationship management is actually the process that salespeople use to handle their interactions with customers.
Before dedicated CRM software was around, companies would use written notes or simple spreadsheets to track their customer interactions – and many still do.
A CRM platform helps you manage communication and nurture relationships by centralizing large amounts of customer data. CRMs can help segment your audience, automate key tasks and forecast sales in the future (among other things).
While spreadsheets can’t match the full functionality of a dedicated CRM, they are still useful in many cases.
Before you get started with an Excel CRM, let’s run through the pros and cons to see whether it’s a good fit for your company. The advantages of building a CRM in Microsoft Excel are:
It’s affordable: Pricing is one of the major factors when deciding on any software solution, and free CRM software can help get you to the point where you can afford a better solution. While the Microsoft Excel desktop app requires an Office subscription, the Excel web app can be accessed for free online. Google Sheets is free for a single user (and Google Workspace for business starts from $6 per user per month).
It’s easy to use at a basic level: Most people have basic knowledge of how spreadsheet software works, so it’s simple to get an Excel CRM up and started. Excel is relatively easy to learn with thousands of helpful tutorials available online.
It’s customizable: Excel is renowned for the depth of its functions. With enough time and effort, it’s possible to set up a powerful custom CRM. Better yet, there are plenty of templates out there to get you started (like ours above).
It works well for small-scale companies: For small businesses with simple processes and a low number of contacts, an Excel spreadsheet can work perfectly. However, after you reach 50 to 100 contacts, it starts getting complicated, and it’s easy to let things fall through the cracks without the reminders and automation you get in the software.
However, an Excel CRM isn’t right for everyone. There are a few limitations that can hold you back, especially as you try to scale your business. For example:
It’s fully manual: Data entry is time-consuming. One reason companies often switch to dedicated CRMs is that they provide workflow automation and key tools to help you minimize tedious work.
It’s hard to scale: An Excel-based CRM is challenging to share with a team. Keeping track of users and the changes they make is next to impossible. Plus, the more contacts you get, the harder it is to manually track them.
It isn’t secure: Excel can crash, your hard drive can become corrupted and sales data can easily be lost. A cloud-based CRM tool stores your data securely, ensuring you don’t fall victim to data loss.
There’s no integration with sales tools: Excel doesn’t directly integrate with any sales tools, unlike most dedicated CRMs. This means you’ll have to constantly import and export data, switching to other tools to complete crucial sales tasks.
There’s no place to store important documents: In an Excel CRM, it can be hard to store or link to important documents like contracts, proposals, email threads and other communications. This means potentially losing documents or wasting time trying to find vital information in time-sensitive situations.
If Excel is the right choice for you at this time, then great – let’s dive in and see how to set one up.
How to build a CRM in Excel
In the CRM template, you’ll see the following four sheets:
Contacts: The Contacts sheet is where you can store customer data in an easy-to-view way. The intuitive sheet lets you input contact information and use drop-down boxes to categorize them as potential customers, current customers or others.
Companies: The Companies sheet is the same as your Contacts page but for storing your B2B sales leads and clients.
Pipeline: The Pipeline sheet is the foundation of your Excel CRM. This is where you’ll track communications with customers, plan follow-ups and update the lead status to track sales.
Settings: This is where you can input custom fields for your CRM. Add contact types, pipeline stages, deal status, lead source and loss reasons to add new fields to the drop-down menus on the other sheets.
Below, we’ll explain how to customize these sheets to create your very own, fully functional CRM.
Step 1: Create your contact manager
Keeping your contact details in one place saves time when your team needs to reach out to your potential customers. By doing so, you also avoid accidentally misplacing data and wasting time trying to recover it.
Navigate to the Contacts sheet in the CRM template.
For each customer, we’ve included several categories for you to fill in with your customer information (be sure to remove the example information first). Most of these are demographics like name, phone number and LinkedIn address. You’ll want to add as much data as you have for each contact to streamline your communications.
For the “Tag” field, we recommend adding a keyword that describes the type of contact or deal stage that the customer is at. For example, you could use “Hot” to show they’re a hot lead and need to be contacted soon. With these tags, you’ll be able to easily scan your contact list for key leads.
In the “Description” field, add as many notes as possible that describe your previous interactions and other key information that isn’t covered elsewhere. This will help you track your customers and their preferences.
Next, navigate to the Companies sheet in the CRM template and repeat the steps above for the companies that you interact with.
Step 2: Fill your sales pipeline
Now that your contacts have been imported, it’s time to add your deal opportunities to our premade sales pipeline so that it links up with your sales processes.
We’ve included the following fields to get you started:
Tag: Use an optional keyword in this field to categorize and keep track of your opportunities
Name: Here, you would add what type of sale or service the potential customer has requested. If you’re a web designer, you might include “Create landing page” and “Design interactive blog”.
Company: The customer you’re interacting with for this sale opportunity
Stage: These are the deal stages that you can update as your prospect moves through the sales pipeline. Stages include Appointment, Proposal, Negotiation, Pilot and Agreement. You can customize these according to your sales plan (we’ll show you how in Step 3).
Value: The predicted monetary value of the opportunity
Close Date: When you expect to finalize the deal
Status: Whether that opportunity is open, won or lost
Loss Reason: The reason why an opportunity was lost. Filling in this field gives you insight into improvements you can make to land more deals.
Priority: Whether that opportunity is a high, medium or low priority. This lets you see which opportunities are hottest and if any can be put on the back burner for a little while.
Source: This is where you gained the opportunity from. For example, the lead may have come via advertising, your website or from a cold call.
If you haven’t defined your sales process yet, load your lead data into this sheet and see if it makes sense to you. As you make progress in your deals, update the sheet. You’ll notice that your Dashboard (the first sheet in our template) automatically updates with the information you load into your Pipeline.
If you already have a defined sales process, or the sales pipeline template doesn’t quite fit your needs, you can easily customize it. Follow the next step to see how.
Step 3: Customize your sales pipeline
Having a customizable sales funnel is vital as it allows you to evolve your processes as you expand.
To customize your sales pipeline in the Excel CRM, open the Settings sheet. Here, you’ll see the premade contact types, pipeline stages, statues, lead sources and deal lost reasons. Under each of these are the categories that appear when you select the drop-down menus on the other sheets.
To add custom fields to any of these premade categories, add a new cell (or replace an existing cell) with the word you’d like to appear.
However, that’s not all you can do. Our template uses pivot tables so you can easily sort and filter your pipeline to manage your data. For example, you can:
Sort the sales pipeline table based on the estimated value of an opportunity. To do so, select the drop-down menu beside “Value” and choose “A to Z”. The table will now format your deals from lowest value to highest.
Filter our lost or closed deals to clean up your pipeline. Select the drop-down menu next to “Status” and untick “Closed” or “Lost” to hide them from view.
5 tips to get the most out of your Excel CRM
Manage your Excel CRM effectively to keep your sales pipelines flowing.
Here are five tips and tricks to improve productivity and ensure you don’t lose track of any leads.
1. Review your data periodically
It’s easy for an Excel database to become cluttered with old data. Clutter makes it hard for your team to effectively use the data. It causes roadblocks as they waste valuable time sifting through for important information.
That’s why it’s vital to clean up your CRM data regularly. The tidier your spreadsheet, the easier it is to keep track of customer and prospect data.
Set aside time every month to go through your data and fix anything that’s out of date. Scan through the opportunities in your sales pipeline. Check whether the information is accurate and if any deals have progressed or closed that haven’t yet been updated.
Take out your lost deals so they don’t clutter your spreadsheet. Hide these from view by using the filter option described above.
2. Ask the right questions
When setting up or reviewing your CRM, ask yourself key questions about your sales and marketing processes.
What information do you need to track? Make sure this is presented in a way that’s easy to understand.
Are you covering all of the important data? Check your CRM for any key information that’s missing.
Do your lead sources and pipeline stages provide actionable insights? If not, add detailed notes that can help you uncover more leads and speed up the sales cycle.
Is it easy to track where your customers are in the pipeline? If not, consider removing or adding stages to clear things up. You could also color-code or sort your leads by stage.
Are leads falling through the cracks? Consider new ways to add reminders and key information.
An effective CRM solution is more than just a contact list. It should help you understand and improve your sales processes, move toward your business goals and improve relationships with your customers.
3. Organize your data efficiently
It’s more efficient to separate large volumes of information into different sheets or workbooks when working with Excel or Google Sheets. This is why our template has a separate sheet for Contacts and for the sales Pipeline, keeping your data organized.
Well-organized data helps you:
Keep accurate records
Get info at a glance, especially when you’re busy
Export the data easily at a later point
Extract key insights and develop visual aids to display your data
Automatically populate linked sheets to save time
This is why it’s vital to maintain one data point in each cell. It enables you to sort and filter your tables as required. If you fill each cell with different data sources (for example, you include the contact’s name and number in a single cell), you can’t automatically populate your other sheets with that information.
4. Delegate control of the spreadsheet
With multiple people using the same CRM spreadsheet, things can get messy, quickly. Even though you can create shared workbooks or use cloud-based Excel to allow many users at once, these don’t support key features like charts, sorting and data validation.
Introduce guidelines so that each team member is using the spreadsheet correctly, and put someone in charge of monitoring the data. For instance, make sure that people only update their own data and you won’t lose data when conflicting changes are made in a shared file.
The person in charge of the data (whether it’s you or another salesperson) can make sure that information is being regularly updated and reported in the correct way.
Delegating control is important for a second reason: data security. One of the downfalls of an Excel CRM is that it’s vastly more insecure than a dedicated CRM system. To make it as secure as possible, be sure to set user permissions that only allow user access to key individuals.
Using these preferences, you can ensure that only some people have editing capabilities, while others are only able to view the spreadsheet.
5. Know when to upgrade
An Excel CRM can be a powerful tool for small-scale businesses and startups. The problem is, it’s difficult if not impossible to scale a spreadsheet as your business grows.
Our research found that companies that use CRM technology are 15% more likely to have grown their revenue in comparison with those that don’t use it.
If you experience any of the problems below, it’s a sign that you’re in need of an upgrade:
Customer information is being lost or misreported regularly
Your sales reps are finding it hard to collaborate. You may find that it’s challenging to maintain the CRM, or that sharing information with your sales team is difficult and leads to delays
The manual data entry is taking up too much time, reducing your team’s productivity
Choosing a cost-effective, scalable sales CRM like Pipedrive can help you avoid these issues altogether. Pipedrive’s visual sales pipeline makes customer relationship management simple, helping you track leads, find insights and focus on sales.
Final thoughts
With the right CRM, you can boost your productivity and vastly improve your sales outcomes. An Excel CRM is a great choice if you’re just starting out or you deal with a small pool of customers.
When you’re ready to take your CRM to the next level, take a look at Pipedrive. With a single source of truth, custom data fields, workflow automations and hundreds of third-party integrations, you can easily build the perfect CRM system that scales with you.
