Skip to content

How to Create a CRM in Google Sheets (Step-by-step)

How to Create a CRM in Google Sheets

Do you just keep your customers’ notes in a notepad on your desk, or as a saved document on your hard drive?  This can cause you some difficulties, such as:

  • Making them hard to find.
  • Or send them to your partners or coworkers—who will also need to find them.

In addition, notes that are hidden become especially urgent when your client is already on the phone. But it doesn’t have to be this complex. 

If you ever get stuck trying to find the best CRM, or you can’t afford to get the one you prefer, why not build one that is functional in Google Sheets? Here’s how to do it.


Add Titles and Freeze the Top Row of Your CRM in Google Sheets

Begin by inputting the names of the fields you want to include at the top of your spreadsheet. 

Some basic columns you may want to add include name, email, phone number, status, date contacted, and notes.

You should also consider some columns specifically for your sales or business cycle. What details will be useful when it’s time to pick up the conversation again?

This may be anything such as your potential client’s budget, their desired product or service, who they talked to, their place of work, and lots more. If you have a current system, you can look for a reference here.

After you’ve chosen your titles, and you’ve inputted them, you should freeze the top row of the Google Sheet. Here’s how:

  • Highlight the entire row.
  • Go to View in the top-left corner.
  • Select Freeze.
  • Choose 1 row.

Now while scrolling the titles will stay at the top.

Add Drop Down Menus and Conditioner Colour Coding to Your CRM in Google Sheets

As for recurring answers, you can include a dropdown list of options below any heading, it will be useful. Some examples of this are product names, services, or a list of your salespeople.

Creating a dropdown is also great for adding a working status column to your Google Sheet. Statuses will give you a quick overview of where a lead is in the sales cycle. To do this:

  • Click into the cell below your heading.
  • Go to Data in the top-left menu.
  • Select Data validation.
  • From the options beside Criteria, choose List from a range.
  • In the field beside that, enter the words you’d like to include, separated by a comma.
  • Hit Save once your list is ready.

Now your options should appear in the cell, and you can add conditional color coding to help you navigate your CRM.

Conditional formatting can be found under the Format menu at the top. When you click it, a sidebar will open, and you can customize a range of cells. 

Click the cell that has your dropdown menu and include your colors by following these steps.

  • Below Format rules, click the fly menu under Format cells if… and select Text contains.
  • A field will appear for you to enter the word you’d like to color code.
  • Under Formatting style, change the background color to the one you want— lighter pastel colors work better with black text.
  • When you’re ready, hit Done.
  • Repeat these steps with the remaining options.

Whenever you click your options in your menu, the cell will change color. To add this to the entire column, copy the cell and select the column by clicking its letter at the top. Before pasting, deselect the title cell by holding CMD or CTRL and clicking it.

Another useful formatting to build your CRM in Google Sheets

To make sure your entries look the same every time, you can include additional formatting to the columns on your sheet. 

If your CRM has a date column, you can keep this data consistent by adding a format. Here’s how to do it:

Click the letter at the top of the date column to select the whole thing.

Go to Format in the top menu.

Select Number.

Choose Date.

Now each time you enter a date, your sheet will format it the same every time.

You can also do this to keep any currency in your CRM consistent. Follow the same steps, but choose from the variety of options available or go to Custom currency for more options, including different types.

Another advantage to keeping your data consistent is that you can use the sort function in Google Sheets to quickly find information.

Adding Notes to Your CRM in Google Sheets

If you’d prefer to keep all your notes in one place, you can add them right in your Google Sheet. 

Although it may seem a little clumsy at first, a little bit of formatting will make my things look clean and organized.

Check out these tips to help you take notes in your CRM:

  • Double-click the cell to edit it.
  • You can add a line break by pressing Option + Enter on Mac or Ctrl + Enter on PC.
  • You can add a bullet point by pressing Option + 8 on Mac or Alt + 0149 on PC.
  • To avoid using the bullet point shortcut each time, copy and paste them.
  • If you prefer, you can use dashes as points to make things easier.
  • Set your Text wrapping to Clip to keep text neatly tucked inside instead of sprawling out.
  • The clip will also prevent cells from getting overly tall.
  • Once you double-click it again, you can view its contents in full.
  • Alternatively, if you want to create a separate document for each potential client, you can use this column for links to Google Docs.


Keep Your Customers’ Relationship Notes Together in Google Sheets

Finding the perfect CRM for you and your team is a project on its own—especially if your team is small or just starting. 

Maybe you don’t need all the features that come with some CRM subscriptions, or you can’t afford to invest in one for now.

In the meantime, you can track all your sales notes in Sheets. The first thing to do is decide what type of information you need to collect to help your sales process go smoothly. 

From there, just follow the outlined steps in this guide to create exactly what you need. With this, you’re less likely to miss bits of helpful information on scrap paper or notepads.

Leave a Reply

Your email address will not be published.