Finding them again. Sending them to your coworkers—who will also need to find them again.
To add to this, 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 you’re 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.
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 reference.
Once you’ve decided on your titles, and you’ve typed them in, you can freeze the top row of the Google Sheet. To do so:
Highlight the entire row. Go to View in the top-left corner. Select Freeze. Choose 1 row.
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 list of options under any heading, it will be useful. A few examples of this are product names, services, or a list of your salespeople.
Creating a dropdown is also an excellent way to add 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 of items. 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 list of options will appear in the cell, and you can add conditional color coding to help you navigate your CRM.
You’ll find Conditional formatting under the Format menu at the top. Once you click it, a sidebar opens where you can customize a range of cells. Click the cell that contains your dropdown menu and add your colors by following these steps.
Under 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’d like—the lighter pastel colors work better with black text. Once you’re ready, hit Done. Repeat with the remaining options.
As you click your options in your menu, the cell will now change color. To add this to the entire column, copy the cell and select the column by clicking its letter at the top. Before you paste, deselect the title cell by holding CMD or CTRL and clicking it.
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:
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 benefit to keeping your data consistent is that you can use the sort function in Google Sheets to quickly find information.
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 Sheet. 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. 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. 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. It may be that you don’t 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 type of information you need to collect to help the sales process go 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 bits of helpful information on notepads or on scrap paper.
