How to Build a CRM in Excel

Almost every business advice website lauds CRM as a must-have tool to help manage customer relationships and improve sales numbers. While those results sound great, the reality is that CRM software can be pricey, especially for newly-founded businesses just making their first few dozen sales.

But even if you don’t have the finances to invest in a fully-fledged CRM yet, you can still tap into the principles governing CRM to build a customer and sales management system that will help your business operations run more smoothly. To that end, we’ve created this handy guide on how to build a CRM in Excel so that you can improve your fledgling business.

What is CRM?

Rather than the methodology, the term CRM nowadays usually refers to CRM software systems. 

CRM systems are software tools that record and store all of a client’s personal and sales-related data in one easily-accessible location. The goal is to help businesses keep track of each customer and where they are in their sales journey to make more sales in a timely manner.

As a general statement, you should consider utilizing a CRM system once your business operations grow unwieldy and you have too many customers to track with pencil-and-paper management. A CRM helps you consolidate all of your customers’ information into one location so you can personalize interactions with each customer, quickly see how far along they are in the sales process, and analyze trends in your sales data.

When Should I Use Microsoft Excel to Build a CRM?

We’ll be completely honest with you: some software vendors, such as HubSpot CRM and Zoho CRM, provide entry-level CRM software for free. Plus, if you’ve already paid for task management platforms such as Notion or Asana, those can also be used as lightweight CRMs with a bit of modification. So really, there’s no need to go through all this trouble to make your own CRM from scratch, even if you don’t have the budget for advanced features. 

With these other more ready-to-use CRM alternatives in mind, the only reasons we’d recommend choosing Excel as a CRM over these other options would be if you:

1. Deal with customer data that’s a bit more confidential and don’t trust third parties with the information (but be aware that Excel isn’t very safe either)
2. Don’t have a stable internet connection that allows you to consistently communicate with these application providers
3. Only use one device for your sales management
4. Only have a few leads or customers in your sales funnel at any given time
5. Have a very simple sales process

If you check most or all of these boxes, read on to begin building your Excel CRM system.

How to Build a CRM in Excel

From our definition of CRM and the standard tools most CRM platforms provide, there are a few key elements that your Excel CRM should be able to tell you from a glance, such as:

• The customer’s information
• Where they are in your sales pipeline
• What tasks need to be completed to serve them

In other words, your custom CRM Excel sheet should contain cells that cover this information. Let’s go through these elements one by one in a bit more detail.

1. Customer Information

First up is the client’s information: both the company, your primary contact person, and details about the deal itself.

Some data you’ll want to record about the client and contact are:

• The client business’s name
• Their website
• The date you first interacted with them
• The name of the primary contact for this company
• The phone number and email address you contact them through
• Their address (street number and street name, city, state, and zip code)

You’ll also want to include some basic details about the opportunity itself, such as:

• Opportunity status (whether the deal is open or closed)
• Opportunity value (amount of money you expect to receive)

Start off your CRM spreadsheet by setting each of these items as a column header. Put the most important items closer to the right so they’ll be visible without scrolling sideways  – for example, I’ve put the opportunity status and value right next to the company name because I need to know this from a quick glance.

You’ll notice I left some blank columns next to Opportunity Value. That’s not a mistake – the next few items are quite important, so I’ve put them close to the client’s name.

2. Your Sales Pipeline

Next is to define your sales pipeline, which is the steps your company takes to guide leads through their customer journey and make a purchase.

For our example, let’s go with the most basic stages of the sales pipeline: 

• Prospecting
• Lead qualification
• Meeting scheduling
• Proposal
• Negotiation
• Closing
• Follow-up

Every business model and sales process is different, so feel free to customize these steps to fit your own operations.

Remember those blank columns from earlier? Let’s set one column as Sales Stage, and use the Data Validation tool on the whole column to create a drop-down list with each sales stage as an option.

3. Tasks Needed to Serve Them

Finally, in the last two empty columns, you should input:

• Next Task (what steps your business needs to take to nurture the prospect further)
• Account Manager

In the Next Task column, use Data Validation again to create another drop-down menu, listing the next step your sales rep will take to guide the prospective client through each corresponding stage of the sales pipeline to closing a sale.

For example, our tasks could be:

• Prospecting → Research on LinkedIn
• Lead qualification → Email client
• Meeting scheduling → Call client
• Proposal → Draft contract
• Negotiation → Sign contract
• Closing → Fulfill order
• Follow-up → Email client

Depending on your own procedures, some tasks might look the same for different stages in the sales process, such as how we have “email client” listed twice above.

Next, the account manager (below) is the employee or sales rep you designate to handle interactions with this particular client based on your company’s lead distribution system. Create another drop-down menu for your sales reps.

If you’re not sure how to assign reps to leads, check out our handy guide on What is Lead Distribution. Alternatively, if you’re a single-person business and manage all of your clients yourself, you can delete this column entirely.

Our final Excel CRM spreadsheet looks like this:

Tips for Building a CRM in Microsoft Excel

Now that you’ve got your bare-bones CRM set up, it’s time to optimize it and make it look amazing. To that end, here are four tips to make the best out of your Excel spreadsheet.

1. Only record the information you need

This one’s pretty obvious: why record data you’re not using? As we mentioned earlier with the Account Manager cell, if you find that you don’t need to specify a specific item for each customer, you’re free to delete the column and save yourself time. 

Some other examples of cases where you could delete an item are:

• You only sell one product at a set price (delete Opportunity Size)
• You only sell to clients in one state (delete State)
• You only contact clients via phone (delete Email Address)

Alternatively, you might find yourself in need of more columns to record elements that are important to you. Some examples of columns you could add in are:

• Lead Source (how you discovered the lead)
• B2B or B2C (if you sell both wholesale and retail)
• Company Size (if you only sell to companies of a certain size)

2. Carefully consider when to put data in separate cells

When building Excel spreadsheets, people logically make separate columns only when they anticipate sorting those elements separately: for example, with our spreadsheet, you could easily sort clients by, say, the size of their opportunity or which state they’re in.

Conversely, you should merge elements that you don’t anticipate ever having to sort or manipulate. For example, we listed our client’s Primary Contact by their full name (“Bob Bobson”) rather than splitting it into first and last names, and we kept the client’s street number and street name together. It’s likely that we’re never going to sort our spreadsheet to filter out all clients named “Bob,” or arrange clients by business address number, so it’s safe to merge this information with other cells. 

Of course, this changes depending on your own business model and needs – for example, if you ran an extremely local business going door-to-door, you’d pay closer attention to each street, and would want to separate house number and street name while deleting the column for state and zip code.

3. Make a new entry per transaction

If you get repeat customers, you might be tempted to simply add a new row beneath their first entry and fill in only the details that have changed, or manipulate their old transaction data to reflect their current stage in the sales process.

Not only will this prevent you from sorting and manipulating the data properly, manipulating historical account information will permanently destroy those records in your spreadsheet, preventing you from getting a full understanding of your client’s history.

Instead, take care to fill out a whole new row for each separate transaction to get a more complete and useful record of your transaction history.

4. Use conditional formatting

If you’re a veteran Excel user, you can use conditional formatting to color-code your spreadsheet.

Some cells you could put conditional formatting on are:

• Opportunity Status
• Opportunity Value
• Sales Stage

Putting these cells in varying shades helps you see from a glance how your opportunity is going from a glance and will make your CRM spreadsheet much nicer to look at. Here’s our example CRM spreadsheet from earlier, now with more examples, cell formatting, and color coding.

Conclusion

A small business owner might not have the money or time to manage a full-fledged CRM solution. Luckily, you can follow these simple steps to make your own CRM using Excel and save yourself the hassle of third-party software while getting a holistic view of your sales opportunities.
Better yet: if you don’t want to make your own spreadsheet from scratch, you can make a copy of our Excel CRM template for free!