Customer Relationship Management (CRM) systems and CRM software can help you increase sales by streamlining communications with your potential and current customers. Online CRM software can help you integrate revenue tracking, sales, and email into an all-in-one system. If you don't need that type of integration and are just looking for a simple (and free) way to keep track of leads, then you can try using an Excel template. I designed the CRM Template below based on the request from a few users. It may not be everything you want it to be, but it might give you a head start on customizing your own solution or help you decide whether a spreadsheet-based solution is right for you.

Advertisement
Watermark - CRM
categories: Business

CRM Template

for Excel
CRM Template
Screenshots: 1
Download
Version: Excel 2003 or later

Template Details

License: Private Use
View Agreement >
(not for distribution or resale)
Support
Visit Support Page >

"No installation, no macros - just a simple spreadsheet" - by


Advertisement

Description

A critical feature of any CRM system is to track communications with customers, plan follow-up contacts, update the lead status, and after gaining a customer, schedule future communication that will help you keep them as a customer. Tracking sales is also an important part of CRM.

The CRM template provides a starting point for people looking for an Excel-based CRM solution. It is designed to be easy to customize (limited only by your knowledge of Excel).

In it's current form, this template is just a table formatted to allow sorting, filtering, and display of important customer information - a fancier version of the Contact List Template.

The most complex part of the spreadsheet has to do with the conditional formatting used to highlight the dates in the Last Contact and Next Contact columns. I'm a fan of conditional formatting, but if you don't like it or want to change the colors, see Excel's help system (F1) for how to clear or change conditional formats. For more information on using these columns, see below.

I personally do not use CRM software, so future updates and versions of this template will be based on the feedback and requests that I get from users.

How to Use the CRM Template in Excel

Sorting and Filtering: This template is designed around Excel's built-in Sorting and Filtering features. With a couple clicks on the arrow in the column header, you can do things such as:

  • Order the table based on the Estimated Sale value
  • Filter out customers from the list where the Lead Status is "loss" or "cold"
  • Sort the table by Next Contact date so you can make the most urgent contacts first

Demographics - For any contact, you'll want to record their name, company, job title or position, email, phone #, etc. Information that may be nice to know what not necessary to refer to on a day-to-day basis could be saved in the optional ContactDetails worksheet (which is just the contact list added to this workbook).

Estimated Sale - For active customers, this might be the average monthly or annual sales figure - something to help you easily see the value of keeping that customer. For potential customers, this could be your estimate of the customer's potential monthly or annual sales.

Last Contact - When you make a contact, you record the date in the Last Contact field. The highlighting will make the cell green if the contact was recent, pink if it has been a long time since that last contact, or yellow for somewhere in between. Use the Settings worksheet to customize the number of days that are used for the basis for when the cell should be green/yellow/pink.

Next Contact - Planning follow-up communication is important, so use this column to record the date for the next contact and use the Next Action column to enter a code that describes what your follow-up action will be.

Notes - There are many ways that you can record notes about communications with your leads. You could add cell comments as shown in the screenshot image above. You can use a Notes column with Wrap Text turned on (though for long notes, that will make the table messy). You can also use the optional ContactLog worksheet, to record notes about each individual communication.

Sales Log - If you want to create monthly or quarterly or annual sales reports, you'll need a way to record individual sales. That could be done using the optional SalesLog worksheet. You could then add a Pivot Table to summarize the data, or create your own Excel dashboard to display important summary data.

CRM References

  • [1] Customer Relationship Management - wikipedia.org - This wikipedia page, although lacking substantive information, opened my eyes to the wide variety of ways that businesses use CRM systems - and thus made me realize I only wanted to create the most basic of templates.
  • [2] Create a CRM System Using Google Docs - blog.softwareadvice.com - Most of the initial features I added to the CRM template came from the ideas presented in this blog post.
  • [3] Spreadsheet CRM - frontofficebox.com - The blog post helped confirm to me that creating a CRM template for Excel would be useful, at least for some people.

Like This Template?

Become a Fan

Vertex42's Facebook Page Vertex42's Google+ Page Vertex42's Pinterest Page
Master Excel - Spreadsheet Tips Workbook