A CRM system is an excellent tool for businesses to manage their customers. But when you’re just starting out, jumping straight into an online CRM like Zoho or Salesforce can be scary, especially if you don’t have time to learn how it works. They can be complex tools and be challenging to understand for someone who has never used a system like that before. 

Instead, you can start by using a spreadsheet as a CRM. I’ll use Google Sheets to build a CRM, but you can use Microsoft Excel if you’re more comfortable with that. I’ll show you how to do it, and you can even download my basic example spreadsheet CRM.

When should you use a Spreadsheet as a CRM:

• You have very few sales or customers.
• The business is small enough that you don’t have to worry about admin and data entry.
• You have a very small team or run it on your own.
• Your sales process is straightforward.
• Your data reporting needs are simple.

Some benefits of using a spreadsheet for a CRM:

• They’re easy to use. Most people understand how a spreadsheet works.
• Finding information in a spreadsheet is easy because you can filter it and use the find function (Ctrl+F or ⌘+F).
• It’s easy to modify a spreadsheet. It can easily be changed to grow with you. However, this can also be a problem, which I’ll explain a little later.

Limitations of a CRM spreadsheet 

• Because a spreadsheet is easy to edit, it’s also easy to break. However, you can lock cells to protect it.
• The data is spread out in different parts of the spreadsheet. Combining that data with helpful information about your business and customers can be challenging.
• The structure of a spreadsheet means that if you put too much information into one page or tab, it will become too big to manage. For example, the data structure will be hard to use if you try to put your customer’s transactions in the same place as their contact details. 
• It doesn’t have a strict data structure. You can add lots of errors to your spreadsheet very quickly. I’ll show you a good spreadsheet structure later in this article.
• Because your CRM is in a spreadsheet, you must manually update it and keep adding information rather than have it updated automatically.
• Maintaining control over good practices will be difficult if multiple people update your spreadsheet. The data quality may suffer, leading to poor customer service.
• Connecting to other tools like your website or e-commerce sites isn’t as easy. Sure, there are ways for an online spreadsheet to get populated by external data sources such as your website, but if you get to that point, upgrade to a proper CRM like Zoho CRM.
• A spreadsheet CRM is unsuitable for complex or itemised sales for a customer. You may have to do that in a separate spreadsheet and add the total to your CRM spreadsheet.

Essential rules about your spreadsheet CRM

You need to ensure your spreadsheet is set up well so that it will not cause you problems as you grow. Getting this structure right is essential because it’ll allow you to grow and eventually migrate to proper CRM later on.

Here are a few rules to structure your CRM:

• Ensure your spreadsheet is divided by the type of information about the customer. Each type of information about a customer is called an “Entity”. An entity in the CRM could be a business, a person, a product or another detail about the customer. All these entities will be separate but related.
• Each type of entity will need a tab in the spreadsheet. So you’ll have a tab for a company and one for a person in that company.
• Each record will be a new row in that tab so they can be managed and organised.
• Every row in a tab will need an incremental number. This number is called a unique identifier, or “key” in database terms.
• Picklists (also known as dropdowns) help you and your users enter data, so add a few to your spreadsheet. If this doesn’t make sense, look at my example spreadsheet.
• Use formulas for any specific calculations, such as recording sales.

How to use a spreadsheet CRM 

Finding and updating information

As it’s a spreadsheet, you can use the find function to look for information across your whole system. Use Ctrl+F or ⌘+F. You’ll need to be exact with your search.

Alternatively, use the filter at the top of each column to find information.

Once you’ve found your record, overwrite the information, or if you double-click a cell first, you can update it rather than replace the values.

If you get it wrong, you can correct your mistake by pressing undo or Ctrl+Z or ⌘+Z on your keyboard.

Adding Accounts and Organisations

Your spreadsheet may not need an organisation tab, especially if you only sell to consumers.

Go to the Organisations / Account Tab in your spreadsheet. Add a new row and ensure it has a unique identifier (first column). Complete the details across the row with what you need.

Adding Contacts

This is the same as the Accounts tab. However, if you have Accounts and Contacts, you must pick the Account from a dropdown list when adding a new row.

It’s essential to capture their contact details and any other information you may need about them.

Adding Sales and simple transactions

The Sales tab can be tricky as everyone sells different things. However, you must understand that you’ll use the picklists feature to pick the Account and Contact when adding a row. You’ll be adding the product to the record from a list.

Because it’s a spreadsheet, you’ll need to record each row of the spreadsheet for a different product or service per customer., i.e. you can’t have multiple products on the same row. Otherwise, it’ll be hard to manage.

Make sure you add the price and amounts. If you have the pricing on the products tab, you can use vLookup to get the list price automatically for you. The downside is that changing your pricing will affect all your historical data, so you’ll need to create a new product per price.

Adding Issues

An issue is essential to track in your CRM. This relates to the customer service part of your business. Create a new row per issue, and use the lists feature to help you avoid manually typing in data.

The issue’s status is important, so keep an eye on this and keep it updated.

If you want to improve your customer service support, try Zoho Desk. It’s a complete customer help desk system.

Adding and managing Products

To record sales in your CRM, you must register your products and services.

Make sure you add a new row for each product and mark an old product as inactive if it’s no longer to be sold. If you’re changing the price of a product, make sure to create it as a new row, i.e. a new product.

Add the list price for the product and all the details you need as columns. You can record a supplier if you have to purchase it yourself. If you need to manage stock, add current stock as a column or upgrade to a CRM like Zoho CRM or an inventory management tool like Zoho Inventory.

CRM spreadsheet template

I’ve created a CRM spreadsheet template, which you can make a copy of for your use.

Once you’ve made a copy, you can modify it however you choose.

Ignore the “N/A” errors. They’ll appear until you add some data to the spreadsheet.

View the spreadsheet CRM here:

There are two ways to use this:

• Online – Sign in with a Google account, select File> Make A Copy. Then save it wherever you want and edit your version of it.
• Offline – Select File > Download. Pick your download format preference. Use it on your local computer. Note: you can use offline mode with the online version too. But if you do not have or want a Google account, pick this option.

How to move from a spreadsheet to a CRM

If you’ve grown too big or complex for a spreadsheet-based CRM, you’re ready to move to something like Zoho CRM. You’ll need to ensure the data is in a structure that can be migrated. The structure of the spreadsheet CRM is similar to Zoho CRM, except for a few differences.

• The Sales tab will need to be split into two tabs. One is called the Deal, and the other is called Sales Orders. The easiest option is to do the following:
  1. Duplicate the data of the sales tab into another tab. 
  2. Ensure the duplicated data in the second tab is called Sales Orders. 
  3. This new tab needs a new column for another set of IDs. Make sure the original column for IDs is also kept. 
  4. This second set can also be the same set of numbers, but you should prefix it when you migrate them.
  5. One of the ID columns will be mapped to the Deals ID when you migrate the Sales Order module.
  6. The other will be mapped to the Sales Order ID. This may sound unclear, but when you try it, it’ll make sense.
• Make sure you use vLookups to get the IDs to each record from each tab. This is for the mapping. You may need to swap the position of the name and key columns in your spreadsheet. Make sure it doesn’t break your list value columns.
• Check that the phone number columns or any column with a leading 0 is set to text in your spreadsheet. Otherwise, the spreadsheet will ignore the 0 part of the number.
• Break each tab out as separate spreadsheets once all the data is organised in a single spreadsheet.

If any of this is confusing, check out my article on migrating and importing data into Zoho CRM. https://thecrmtrainer.com/zoho-crm-data/

What kind of CRM do you need?

There are a few different types of CRM systems out there. Here are some options to help you pick the right one for your business.

• Contact Managers – These allow you to manage lists of organisations and their contacts. A simple solution like Zoho Contact Manager fills this need if that’s all you require.
• Simple CRMs – If all you need from your CRM is to handle basic sales information for each customer, then use something simple like Zoho Bigin.
• Flexible CRMs – Zoho CRM is the best choice if you want a CRM system that allows you to handle a more complex sales pipeline or manage many different things relating to your customers. You can achieve a lot of automation with tools like Zoho CRM, such as sending emails to customers based on specific actions.

How to use Zoho CRM

I won’t go into detail about how to use a Zoho CRM system here. I have almost 20 hours of instructional how-to video courses on my website that teach you how to use Zoho CRM.

Check out the “Manage Your CRM” course, which will explain how to configure Zoho CRM to your needs.

Important Points

A CRM you use is better than a fancy one you pay for but don’t use. 

Keep your costs low when starting a new business and using a spreadsheet. If the admin is getting too much and your needs are developing, get a proper CRM system that’ll grow with your business.

After you’ve read and watched all the articles and videos on my website on how to use Zoho CRM and you still need assistance, contact me and let me know your issue. I’ll let you know if this can be solved easily and, if so, how.

The example CRM Spreadsheet is just for guidance. Make sure you take a copy and customise it for your own needs.