Laptop with spreadsheet software on display

How to Create a Simple CRM in Google Sheets

Posted on

Where do you keep your customer notes? Do they sit on your desk in a notepad or a document on your hard drive? Doing this can create a couple of challenges:

  1. Finding them again.
  2. Sending them to your coworkers—who will also need to find them again.

Also, hidden notes become especially urgent when the client is already on the phone. But it doesn’t need to be this way. If you’re stuck trying to find the perfect CRM or are not quite ready to invest in the one you want, why not build a functional one in Google Sheets? Here’s how.

1. Adding Titles and Freezing the Top Row of Your CRM in Google Sheets

Start by adding the names of the fields you’d like to include at the top of your spreadsheet. Name, phone number, email, date contacted, status, and notes are some basic columns you may want to use.

Spreadsheet with titles along the top

You’ll also want to consider some columns unique to your business or sales cycle. What information will be helpful when it’s time to pick up the conversation again?

This could be anything—your potential client’s budget, their desired product or service, who they spoke with previously, where they work, etc. If you have a current system, this is another place to look for references.

Once you’ve decided on your titles and typed them in, you can freeze the top row of the Google Sheet. To do so:

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

Spreadsheet software with freeze row options open

Now, as you scroll, the titles will stay at the top.

2. Adding Drop Down Menus and Conditional Color Coding to Your CRM in Google Sheets

For recurring answers, you can add a dropdown under any heading. This helps keep the data and information you enter consistent, especially when working with others in the same spreadsheet. That way, you can select from a list of pre-determined options. A few examples of where dropdown menus can come in handy are product names, services, or a list of your salespeople.

CRM build in spreadsheet software

Creating a dropdown is also an excellent way to add a working status column to your spreadsheet. Statuses will give you a quick overview of where a lead is in the sales cycle. To do this:

  1. Highlight the Status column.
  2. Go to Insert in the top menu or right-click on your mouse or trackpad.
  3. Click Dropdown, and a sidebar will open.
  4. Type the status options you’d like to include into the text fields, clicking Add another item button to create additional spots.
  5. If you want, you can also assign each option a color using the dropdown to the left of the text field.
  6. Hit Done.

Now your list of options will appear in the cell, with color coding to help you swiftly navigate your CRM.

Dropdown menu options in spreadsheet software

Any time you want to change your dropdown, go to Data in the top menu and click Data validation. This will bring up your sheet’s Data validation rules in a sidebar. In the list, look for the range of cells that match the dropdowns you want to edit and select it. Now, you can make your edits.

Data validation is what Google Sheets does in the background to create dropdowns. It’s kind of like the backend code to the front end of a website.

3. Other Helpful Formatting to Build Your CRM in Google Sheets

To ensure your entries look the same each time, you can add 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. To do this:

  1. Click the letter at the top of the date column to select the whole thing.
  2. Go to Format in the top menu.
  3. Select Number.
  4. Choose Date.

Now, when 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 benefit to keeping your data consistent is that you can use the sort function in Google Sheets to find information quickly.

4. Adding Notes to Your CRM in Google Sheets

If you’d like to keep everything in one place, you can add notes right in your Google Sheets. While it may seem a little clumsy at first, a little bit of formatting can help you keep things looking clean and organized.


Here are some tips to help you take notes in your CRM:

  • Double-click the cell to edit it.
  • 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.
  • 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 All Your Customer Relationship Notes Together in Google Sheets

Finding the right CRM for you and your team is a project in itself—especially if you’re a smaller team or just starting out. You may not need all the features that come with some CRM subscriptions, or you’re not ready to invest in one just yet.

In the meantime, you can keep track of all your sales notes in Sheets. The first step is deciding what information to collect to help the sales process smoothly. From there, you can follow the steps in this article to make exactly what you need. That way, you’re less likely to lose helpful information on notepads or scrap paper.