Episode: 504 |
Will Bachman:
Using a Spreadsheet as a CRM System:


Will Bachman

Using a Spreadsheet as a CRM System

Show Notes

In this episode of Unleashed, Will Bachman explains how independent professionals can use a spreadsheet as their CRM system. A spreadsheet can be an effective first step before investing in a paid CRM system, and for independent professionals, the most fundamental use of a CRM spreadsheet is to keep track of the people, organizations, and project opportunities, in addition to confirming new projects, but  the first step in setting up a CRM system should be to create a spreadsheet of all your contacts, and another tab with all your past projects. 

Ultimately, using a spreadsheet as a CRM system can be a great way to get started and gain familiarity with the features of a paid CRM system before investing, but when you do, you’ll be able to upload your spreadsheet into the CRM.

Will walks you  through setting up a CRM system. The initial tabs discussed are 1.contacts, 2.opportunities, and 3.companies.

Using a Spreadsheet for Contacts

When setting up the contacts tab, a few fields are recommended, including:

  • info about the person, like employer and title, name, pronouns, ID number
  • URLs/links/social media links
  • Physical address

It’s also recommended to have separate columns for first name, middle name, last name, maiden name, and nickname. It is also recommended to  include the option to track if the contact is a connection on LinkedIn or not.

When it comes to tracking contacts, emails are essential, and often, the preferred method of communication. It may also be helpful to track physical address, how the contact was originally met, the strength of the relationship, the level of decision making power, whether they are a client or potential partner, the industry they are in, the date of the last contact, the time interval between points of contact, if they are subscribed to your newsletter, if they are on your holiday card list, what gifts have been sent, topics of interest, the name of their executive assistant and their contact info, and the next action to be taken with the contact. Additionally, it is helpful to also track notes from interactions with the contact. Will suggests creating a separate tab for notes on each conversation, and to filter it for any given individual. However, the drawback of using a spreadsheet is that it is not as great as a relational database. 

Using a Spreadsheet for Opportunities 

For opportunities, the fields suggested to track are:

  • Current pipeline
  • Project/opportunity ID, title, associated company/organization
  • Client executive name, billing contact, and any other names related to the opportunity
  • A description and stage of the opportunity
  • Source of opportunity

Will learned this system from David A. Fields and he talked about it  in episode 172  of Unleashed. David’s three stages are: identified opportunity, but not yet talked to the client; completed context discussion (which entails live interaction with client), and finally,  submitted proposal. If you have several projects in the pipeline, you could also add several stages such as: ‘in contract phase’. Will  suggests adding two stages for a contract:  ‘1 active’ – for current projects, and ‘1 complete’, for the final stage. It may also be useful to add a stage for ‘lost’ and/or ‘on hold’ and ‘possible opportunity’ for potential opportunities on the horizon that you may want to follow up on in a few months. You could also track the dollar size of the opportunity and likelihood of closing. By tracking ‘likelihood to close’ you’ll gain a better understanding of your ability to your accuracy in estimating potential opportunities, and dates of each stage. And most importantly, columns to include are your next action(s) and action due date(s).

Using a Spreadsheet to Keep Track of Organizations

  • Company ID, website, LinkedIn, industry
  • All the projects you have done for the same company
  • Who else works at the same company
  • Be able to see all the projects you have done for the same company
  • Who else you might speak with at the company

Will also suggests adding if you have a service agreement with the company, termination dates, payment terms, and any other key contractual terms, such as a non-compete agreement, and invoicing instructions. While the former company details may not be necessary if you have the details of the company contact  in place, the latter is particularly useful if it’s been a few years since you worked on a project for the company.

A link to a downloadable spreadsheet template has been added in links to to help you customize your own spreadsheet.



  • 00:01 Using a spreadsheet as a CRM system for independent professionals 
  • 01:49 Tracking contact Information for professional relationships 
  • 09:27 Tracking conversations and opportunities 
  • 16:21: Tracking contact and contract information on organizations 


CRM Software Guide

One weekly email with bonus materials and summaries of each new episode:


Will Bachman


Will Bachman  00:01

Hello, and welcome to Unleashed. I’m your host will Bachman. Today I’m going to talk about how as an independent professional, you can use a spreadsheet as your CRM system. Now in the next episode of Unleashed, I’m going to talk through how to use a CRM system as your CRM system. But a lot of independent consultants start with a spreadsheet, and that’s fine. In fact, it makes a ton of sense because it’s a good first step before you use a regular paid CRM system to use a spreadsheet. Number one, using a spreadsheet for a while will help you get some familiarity with it gets you a sense of what sorts of features you would actually want to pay for and would be helpful. And number two is, when you actually are going to set up a paid CRM, the very first step is to get tables together of information you want to upload. So you want to have a table of your contacts, a table of past projects, you’ve done a table of companies that you have relationships with, so you’re gonna have to do that anyways. And a lot of people decide to use a spreadsheet as their CRM system for some period of time. In some cases, it’s good enough and serves them on an ongoing basis, I’m going to talk through in this episode, three different tabs that you probably want to have in your spreadsheet. And we have a spreadsheet you can download. With a template, you can certainly create your own, but we’re gonna, we have a spreadsheet, you can download it visit umbrex.com/resources. On that, you can find a resource on how to set up your CRM system. And there you can find the download. Okay, so let me talk through the three tabs, the three tabs are contacts, opportunities, and companies. Okay, so let’s go through each one. Let’s first let’s talk about contacts. Now, I’m gonna go through each of the fields that you might theoretically include. But not all of these are by any means required, this is a bit of an exhaustive list. The in some cases, if you have a very short number, a short list of contacts, you might just have their names as a memory device, that would probably be the minimum to include just the names. And if it’s 20, people, you may be able to remember enough about them and you have their contact info somewhere else, that that’s all you need. So this is the exhaustive list, pick and choose some subset. Okay, let’s go ID number for that contact. So you might have two people named Jane Smith, and you want to be able to differentiate them. So contact ID number. And then let’s go through name. So it’s probably have a separate column for first name, middle name, last name, maiden name, and nickname. So if they go by a different name than their first name, you might include pronouns, let’s go through URLs or links. So their LinkedIn URL, that’s a definite you want include in there to easily access that. If you you might just track if you’re a connection with the person on LinkedIn, yes or no. The website URL of if they have a website, and I would probably not do this myself, but maybe you want to track their Facebook, or some other social media URL, or if they’re on Twitter, and you’re on Twitter a lot, check their Twitter, Link, okay. Emails, of course, you want to have their email, email, work, email, personal email, other you might have several columns for email addresses, several columns for phone numbers, so mobile, number one, mobile number two work, etc. The preferred method of communication, so maybe, you know, some people prefer email, some people respond better on LinkedIn message, some people respond better on phone, et cetera. Physical Address, you may or may not track that, you know, if you actually mail out physical stuff, from time to time, perhaps you have a different address book for tracking that could be helpful to have at least their metro area and their country and their continent. And here if you work across geographies, categories, okay, so you can probably have several different categories. This will be personal individual to each person’s practice. You might have one, which is where did you get to know the person from college grad school, some employer or client just as a reminder of how you first connected, you might have a category of who introduced you to that person. So if you know the name of the person introduce you to remember and refer to that strength of relationship. So I’m taking this from David A fields from his book, The irresistible consultant guide to winning clients, you might track the strength of relationship on a score of one, two or three, where one is a strong tie. Close friend. Two is a weak tie someone who knows you who would recognize you if you call them up, but you haven’t been in touch with recently not a close friend, but someone who would recognize you and three, someone who you’ve met once they’re barely a contact The second next one would be a category of level of decision making power. So is this an A, a decision maker A be an influencer, someone who could influence a decision maker or see everyone else? Another category type might be the is this a client or potential client? Or is this more of a subcontractor or a potential partner? Or is this an attorney or a friend that’s unrelated to business. So what’s the category type? For a lot of independent consultants, they might have one set that’s clients and one set of potential subcontractors or partners that they deal with other consultants. So that might be the basic division. Another category might be industry. So you might have a drop down for that, if you serve across industries keeping track of that current title at their current job, and their current employer. And then you might next have some dates. So you might have the date of the last contact with that person. And then you might have a column that doesn’t change, which is what’s the time interval between points of contact that you want to maintain. So some people might be, you want to touch them every three months, some people every 12 months, some people every six months. So you could then the third column, there would be the date of your next contact, ideally, and you would just say, okay, the date of the last contact plus the duration equals the date of the next contact to keep keep that going. You might have some other things like maybe you have keeping track of things, there might be several columns around this. So is this person subscribed to your newsletter? Yes or no? Yes or no? Are they on your holiday card list? What gifts have you sent this person, if any, you might keep track of any particular topics of interest this person has. If the person has an executive assistant, you might have a separate column with that person’s name. So if the person has an executive assistant, you might keep track of that person’s name. And then you could have a second separate row for the executive assistants contact info. And you might have a column for the next action with this person. So that could be written out free tax that could be a drop down selected of are you going to send the person a phone call or an outbound email of some kind, and your next action date? That might be duplicative? You’ve already we already had talked about the date of your next contact, but somehow tracking what is your next interaction with that person? What’s the task? Okay, so that’s context. Ideally, you would also like to track your notes from your interactions with this contact. That is something that is a little trickier in a spreadsheet, because if you just had one conversation, you could put the notes right there, if you had two conversations, you could maybe add more text to that one cell. But it gets a little tricky if you’re having multiple interactions over time. So one solution could be to create a separate tab where you just have notes of every single discussion, and then put the person’s name and the date of that discussion and so forth. And then you would go and you could filter on that tab for all the discussions with a given individual. But that starts getting a little bit more unwieldy. And one is one of the one of the drawbacks of using a spreadsheet is it’s not as great as a multi as a relational database. And a nice thing about using an actual CRM system, but having some way to track what your notes are of your conversations with that person. Okay, so next after contacts is you want to have a tab for opportunities. All right, and on the opportunities, what are the fields you want to track? So project ID number or opportunity ID, a title of the opportunity, short and sweet. The company or organization that is associated with that opportunity, and then the client executive name, so what’s your key point of contact by name for that opportunity? You could also have a son other column for, you know, names of any other clients that are somehow related to that, maybe you’d have like your billing contact for that, or an additional, you know, additional key point of contact. Usually, one column would probably be enough, and then a description of the opportunity. And then what stage the opportunity is in. So this is a system that I have learned from David de fields, we talked about this back in episode 172 of this show, and I continue to use it with some slight modifications. So the basic idea from David is to have he has three stages, I’ve added a few. So his three stages are number one identified opportunity. So someone an opportunity has come into you probably by email, or text, or you got a phone, you got a call or something, or you’ve seen it online, perhaps. But there’s been an opportunity that has been identified, you have not yet spoken with a client about it. The second stage would be completed context discussion, or completed a discovery call. I like David’s term of completed context discussions. So this is where you’ve had a chance to actually interact live with the client about that opportunity. Normally, I would not like to submit a proposal until I’ve at least had one live interaction. So completed context, discussion, and number three submitted proposal. So you’ve had your discussion, you’ve submitted your proposal, and now you’re waiting. Now, depending on how many projects you have going on, at any one time, that number of submitted proposal might be small number to track that it’s easy enough to keep in your head, the status of each different one, you know, some of them, you’ve submitted the proposal, and you’re going back and forth on contracting, and so forth. So that’s for most people, that’s probably a big enough, you know, term to, you won’t have too many projects in that. But if you needed to, you could think about having additional stages, like, you know, in contract phase, something like that. After I like to have two stages for one, so one’s status, one stage called one active for active projects that are currently going on. And then once they get completed a stage called one complete, and that would be final stage, the other final stage would be lost. So if you lose a project, having it be lost, it’s also helpful to have these are ones that I’ve added from David’s model. So having a stage called on hold, where you perhaps completed a context discussion, you may be submitted a proposal, maybe not. But then the client said, Hey, this one is on hold, so you haven’t lost it. It says, Well, we’re getting some, you know, something else came up, we’re probably going to do this now in the third quarter. So you don’t want to forget about it. But you’d also don’t want to complete it, keep it in your completed context discussion, clogging up the other stuff. So I like to move it off to the side it’s on hold. Similar similar to that was, and you could combine it the same one is a stage called possible future opportunity. So these are ones that are similar to on hold, but the on hold ones are ones where you’ve had context discussion, you’ve submitted a proposal perhaps and you were close to in discussions to get it confirmed. And then it got put on hold possible future opportunities, a client says, Hey, thanks for good speaking with you, by the way, sometime later this year, we might need some help on this. So you haven’t really even gotten into the details of it, it’s just something on the horizon. So you want to keep track of those. So you can follow up in few months, but you don’t want them clogging up your main three, you know, the main three stages. So stage of opportunity, that will be a drop down. So you use the same, you know, same categories each time. Some people like to track the size of the opportunity in dollars. So you could put that in there. Some people, a lot of people probably don’t bother, because you have a rough sense of it. And you might not even know exactly what the size is going to be. But you could track that likelihood of closing. That’s it almost all CRM systems, frankly, I don’t use it myself. I mean, if the likelihood is super low, then don’t bother. And, you know, it’s hard to judge the likelihood anyway, sometimes we may have a rough sense, but it’s not going to change your actual action on it. The only thing I’d say with that is it would be interesting to put your initial estimate of likelihood and don’t change it. And then over time, you could get a sense of how accurate are you you should close, you know, 10% of those that you judged as 10% likely and 90% of those that were 90% likely. So you could kind of over time after you know doing many, many of these get a sense of how accurate you are. Let’s see, the next one would be the source of the project. So who referred it to you? How did you how did it come in next This one, what was the reason for winning it? Or what was the last reason? Why did you lose it? You could have some dates in there. So the date, the way you identified the opportunity date of the context, discussion date, you submitted the proposal date that you won or lost it. Now, you could track these in a spreadsheet. But again, with a CRM system, these would be tracked automatically when you move them from one state to another. If the project was one, you might track if that client is willing to serve as a reference, or even if they’ve written up a reference, yes or no? Have you written a case study on that? Do you have sanitized work product available from that project? Have you added that project to your project list? And then finally, the most important part of all this is having two columns for what is your next action on that project? Opportunity? And what is your next action due date? Now, this is not you know, once it’s a live project, try and track that. But in terms of going to the flow of identified to context discussion to submit a proposal, what’s the next thing that has to happen? So if you’re waiting to schedule something, or if you have to submit a proposal, or if you want to follow up on what’s the status proposal, being able to scan down and saying, Okay, what, what action do I have to do next? So those are opportunities. Finally, you want to have a tab on company or organization. And this would be similar set up to the other ones. Frankly, I don’t use this tab very often. I’ve been using a CRM system pretty actively now for, I don’t know, five years. I don’t use this tab almost ever. But all systems have it and you probably want to have it. It would just list. company ID, name of company, website of company, LinkedIn of company, the industry of the company. And then this stuff is helpful. Do you have a service agreement in place with the company yes or no? So if it’s a company you’ve served before, do you have a master service agreement in place? Does that have a termination date? You could track the termination date of it? What’s your payment terms with this with this company? Is it net 15, net, 30, net 45, Cetera, any other key contractual terms? So is there a non compete you have in place or something else that you want to keep track of and invoicing instructions. So those those the second part is pretty helpful. You could probably get by without a company list, frankly, and just have your contacts in your opportunities because you know what company they work for. But it is helpful to keep track of some of those invoicing instructions and status of your agreements with each company case you want to get them renewed, or if you’re invoicing two or three companies and it’s been a while several years perhaps since you invoice them before. Some companies want you to email an invoice some want you to upload it to some particular system or you need to get a Pio first etc. So keeping track of all that. So, again, you can download the the template that we created. And you know on the umbrex.com/resources you can find the serum resource, and I hope this is helpful and listen to the next episode we’re going to talk about some of the advantage of moving from a spreadsheet to an actual paid CRM system. Thanks for listening

Related Episodes


Author of For Profit: A History of Corporations

William Magnuson


Commercial Leadership Roles in Professional Services Firms

Scott Ratliff


How Expert Networks Can Add Value to Primary Research

Ammad Ahmad


Founder of MacKay CEO Forums

Nancy MacKay