How to Make a Spreadsheet CRM: The Basics

Ah, weary web traveller. You’ve googled and googled until your fingers bleed. All you want to know is how to build a CRM in spreadsheets, but every single resource you click on says ‘derp, just subscribe to my CRM instead’. Come here, give us a cuddle. We’ll wrap a blanket around you and stick the kettle on. Kick back; relax. Want a biscuit? Look at that, we’ve got your favourite kind. 😘

We, the nice guys at NetHunt, have put together a complete guide for exactly what you need. It gives you a no-strings-attached lowdown on building your own CRM system on a spreadsheet.

Oh and hell yeah, you best believe that there are templates.

What’s the point in building a Spreadsheet CRM?

Look, we’re also a CRM system ourselves. But we understand that you might not exactly be in a position to commit to a subscription fee as you look up from the bottom of the business mountain. We get that as a small business, maybe even a one-person business, you need to start somewhere. A Spreadsheet CRM is the perfect place to get your blossoming small business organised. Besides, you can always import all that data into a ready-made CRM when things start to take off.

Here are the key advantages for small business owners to build a Spreadsheet CRM.

• Easy.
  Most of us already know how to use spreadsheets; a lot of us learnt it in schools. There’s no need to be a coding whizz or to reinvent the wheel. A Spreadsheet CRM can be built from the ground up alongside a business, growing as the business does. All it requires is a little bit of manual data entry.
  
• Diverse.
  You can have different pages of the same spreadsheet to maintain different data. That means you can have some advanced CRM features, like multiple pipelines and integrations with different functions like Mail Merge for Gmail. Hit Ctrl-f to search for absolutely any data you need, and you’ve got context on request.
  
• Free.
  CRM Spreadsheets can be shared with any amount of members of a team at no extra cost; no need to worry about upgrading subscription plans or adding users. Unless you’re using Microsoft Excel, a Spreadsheet CRM is always free. That’s good news to anybody.

Tickled your CRM pickle, aye? Logically, your next question would be...

Where can I build a Spreadsheet CRM?

First thing you need to do is choose the platform that’ll host your Spreadsheet CRM system. All in all, you’re probably going to choose from three options which represent three tech juggernauts: Microsoft Excel, Google Sheets, and Apple Numbers. They’re all pretty similar in their features and functionality, but they each have their pros and cons.

• Microsoft Excel is the Grandfather, who has been around the block.

It’s a system that most of us know, recognise, and one that most of us learnt how to do Spreadsheets on when they became a thing. As a result, its features have been nailed down to a tee and it has everything you need to build a homemade CRM system. It works across any platform, with iOS both available… but it comes at a price. The Microsoft Office Suite, featuring Word, Powerpoint, and Outlook as well, costs $150 for a one-time purchase, but there is also a subscription plan available.

Our verdict. A classic, but why pay when you don’t really need to?

• Google Sheets is the dependable, older sister; everybody’s favourite.

You know Google by now, it does everything. Built from the ground up as a simple search engine idea to index the whole internet, to an internationally free productivity tool from where you can run a whole business. Google Sheets is a drop in the Google ocean, but the fact that it is free and accessible across any device, makes it perfect for a simple, shared CRM amongst a team. It has all the features you need to build a solid CRM system… apart from automatic charting. Finally, it seamlessly integrates with the other Google productivity tools.

Our verdict. A more modern classic, accessible to anybody. The system I’ll be using for this demo.

• Apple Numbers is the youngest brother; aloof and locked in his bedroom.

Apple Numbers is only available to the iOS users amongst us. It’s jam packed with features and in typical Apple fashion it looks great. It can be collaborated on at the same time by different iOS users, but it’ll need to be exported to be shared with other operating systems, rendering it useless as a CRM-building technology. With no audit trail feature to tell you who did what, it’s a big thumbs down from NetHunt for CRM for Apple Numbers.

Our verdict. Leave your bedroom Apple, there’s a whole world out here.

How to build a contact spreadsheet

Boil any CRM system down to its very bare basics, and you’ll end up with the same thing: an address book. On top of the squillions of other things almost every CRM system can do these days, contact management is where it started. Your contact list is your business, it's what makes you money. Without it you’ve got nothing.

Things to remember when building a contact spreadsheet

The first thing they teach you in marketing and sales outreach school is that personalisation is absolutely essential. As scary as it is sometimes, data drives business. The following list are the essentials of building a contact spreadsheet. Without doing them, you won’t get the most out of your data and you might as well maintain your contacts on sticky notes.

• It’s essential that you maintain one data point to each cell.

Keeping one data point per each individual cell maintains absolute clarity for anybody else that is reading your spreadsheet. More importantly, it maximises your ability to filter and sort your contacts based on different variables and information points. Eventually, you’re going to start segmenting your growing audience and using mail merge to personalise your outreach. Done correctly, each horizontal row should be a full contact record.

• The more data, the better.

But even more importantly, make sure you’ve got the absolute basics. In my opinion, the basic data points for any contact list in first name, last name, their relationship with your company and some kinds of contact information- be it a phone number, email address… even a fax number. After that, the data points are up to you. Still we can offer a few hints as to what they might look like, just to make sure you haven’t forgotten any.

• Make your contact list export-friendly.

No matter how beautiful it is; how much blood, sweat, and tears go into your Spreadsheet CRM, eventually you’re going to move onto bigger and better things. A lot of CRM providers can automatically match your pre-set headers with their own records. Otherwise, you might need to create a copy of your contact spreadsheet with export-friendly titles such as first-name.

• Make sure to maintain uniform formatting across the board.

Make sure currency is formatted as currency where it needs to be, and not as a data. Make sure phone numbers include their country code, especially if they’re not native to where your business is operating out of. Make sure there are no discrepancies, such as the USA being called US in some records and USA in others. The magic Format tap on the top menu has everything you need to keep it smooth.

🔥 NetHunt’s Hot Tip 🔥

Use Google Contacts to create your contact database. If you’re spreadsheet-savvy, it’s a much more intuitive way of gathering data than typing cell-by-cell of data. There are dozens of different fields that you can fill out multiple times, and there are even custom fields for extra industry-specific information. If you do need your contact list in spreadsheet form, it’s easy to export!

Check out our article about organising contacts in Google Contacts!

How to build a pipeline spreadsheet

A sales pipeline is a visual snapshot made up of the different deal stages a lead goes through before they become a buyer. It’s a simple tool to help businesses get organised, prevent any leads from falling through gaps, and maximise profits. It’s essential for salespeople as they juggle multiple deals at one time; it gives them an overview of all their leads, which concrete actions need to be completed, and how valuable that lead is for their business.

If you don’t know, get to know. A sales pipeline is an essential part of any business, providing a heap of different benefits; opening a bunch of new doors.

• Improve your sales process.
• Provide accurate business forecasts.
• Provide accurate, automatic sales reporting.
• Offer insights into the success of different sales strategies.
• Allow you to allocate resources where they need allocating.
• Help you provide a more accurate, personalised outreach.
• Help you get on top of your deals and data.
• Align messaging and outreach between different teams.
• Prevent leads from falling off course.

Things to remember when building a spreadsheet pipeline

Every basic pipeline is built on the same foundation. That is, a deal name, a contact for that deal, the stage at which they are in your sales process, and how much that deal is worth. In the absence of a fully automated, fully-fledged CRM system, you might also want to include a forecast section which

• Research is an absolute requirement.

Not all sales pipelines are the same. In fact, they can differ massively from business to business. When you build your sales pipeline, you need to seriously consider your sales process. This is what your pipeline follows, and if you have a successful sales process, then your reps will be able to follow it from interest to close by heart.

• Keep your sales cycle short.

It might be tempting to hit as many touch points as you can with a lead to butter them up and get them moving down the pipeline. Don’t. The longer your sales process is, the more chance your lead has of falling out. Shortening your sales cycle doesn’t mean bombarding your leads with follow-ups to try and annoy them down the pipeline. It means shortening the time in between follow-ups, experimenting with different stages, and tweaking until you hit that sweet spot.

• Have your Contacts sheet and Pipeline sheet interact with each other.

By keeping the two documents together in the same file but on different pages, you’ll be able to have them interact with each other. This cuts down manual data entry on your part, nobody wants that. Data Verification cells mean you can choose from a real time list of data options plucked from the other sheets. It’s kind of hard to explain, and probably better to show you what I mean.

☝️ This is my Contacts Sheet. Yeah, it’s pretty nifty.

☝️ This is my Pipeline Sheet. I can select any contact’s first name from my contacts sheet using an in-cell dropdown menu.

🔥 It’s Template Time 🔥

As if that big, beefy article wasn’t enough for you, we’ve done everything we’ve just said. We said there would be templates, and alas there is. Use it as your own inspiration, or make a copy use it as your own CRM… we don’t care. We said there’d be no string attached, but maybe there’s one little dangling thread... that is to remember it was made with love by The NetHunt Team x

Download your copy of The NetHunt Spreadsheet CRM

Do you like what you see? That was just made by one of our copywriters. Imagine what our development team of dedicated CRM coders can do over five years. If you’re ready to make the leap to a fully-fledged Gmail CRM, there’s a full trial waiting for you over at nethunt.com. The Customer Success Team is ready to show you how NetHunt bends to fit your small business, so you don’t have to bend your small business to fit NetHunt.

Table of Contents

Originally published December 23, 2020, updated February 18, 2022