Managing customer relationships is crucial for any business, but dedicated CRM software can be expensive. What if you could build a powerful, customizable CRM using a tool you likely already have access to? This guide will walk you through creating a simple, yet effective, CRM in Google Sheets, just like demonstrated in the “Simple CRM in Google Sheets: FREE & Easy Customer Tracking (No Code)” video by Simply Productive Tips.

What You’ll Need

To follow along, all you need is a Google account. If you don’t have one, you can easily create one for free by visiting accounts.google.com.

Let’s Get Started!

Step 1: Create Your Google Sheet

  1. First, navigate to Google Drive at drive.google.com
  2. From Google Drive, create a new Google Sheet
  3. Rename your new spreadsheet to something like “Customer Relationship Manager”

Step 2: Set Up Your CRM Headers

These headers will form the backbone of your customer tracking system.

  1. In the first row, add the following headers:
    • First Name
    • Last Name
    • Email
    • Phone
    • Company
    • Last Contacted
    • Notes
    • Status
  2. Bold the first row to clearly identify your headers
  3. Freeze the first row (Go to View > Freeze > 1 row). This allows you to scroll down the spreadsheet while keeping the headers visible at all times
  4. Add filters to the first row (Select the first row, then go to Data > Create a filter). This will enable you to easily filter your data later

Step 3: Add Sample Data

To see how your CRM will function, it’s helpful to add some example customer data. You can start with a sample customer like “Joe Smith,” with details such as “555-123-4567,” “ABC Corp,” “July 25th, 2024” for last contacted, and “Lead” for status. The video adds more sample data for demonstration purposes.

Step 4: Implement Data Validation for ‘Status’

To standardize your customer statuses and make data entry easier, use data validation with dropdown menus.

  1. Click on the column letter for ‘Status’ (e.g., column H)
  2. Go to Data > Data Validation
  3. Add a rule. The system will automatically suggest unique values already in your column
  4. Define your desired status values. The video suggests:
    • Active (Green)
    • Inactive (Red)
    • Lead (Blue)
    • Referral (Purple)
  5. You can assign colors to each status to make them visually distinct at a glance
  6. Ensure that users can only select values from your defined list. Click Done

Step 5: Calculate Days Since Last Contact

This column will automatically show you how many days have passed since you last contacted a customer, helping you prioritize follow-ups.

  1. Insert a new column to the right of your ‘Last Contacted’ column. Name it “Days Since Last Contact”
  2. In the first cell of this new column (e.g., G2), enter the formula: =TODAY()-F2 (assuming ‘Last Contacted’ is in column F). This formula subtracts the ‘Last Contacted’ date from the current date
  3. If the result shows a date, format the column as a number (Select the column, then Format > Number > Number or Automatic)
  4. Drag the small blue dot in the bottom-right corner of the cell down to apply the formula to all your existing customer rows

Step 6: Apply Conditional Formatting for Follow-ups

Make overdue contacts stand out by using conditional formatting.

  1. Highlight the ‘Days Since Last Contact’ column (e.g., column G)
  2. Go to Format > Conditional formatting
  3. Set a rule: If the value is greater than 30 (or your desired number of days), highlight the cell as red
  4. Click Done. Now, any customer you haven’t contacted in over 30 days will immediately grab your attention

Step 7: Optimize Your Notes Field

The ‘Notes’ field can become hard to read if text overflows. Use word wrap to fix this.

  1. Highlight the ‘Notes’ column
  2. Go to Format > Wrapping > Wrap. This will make sure all your notes are visible within the cell by adjusting the row height
  3. Also, ensure your ‘Last Contacted’ column is formatted as a date (Format > Number > Date) for consistency

Step 8: Filter Your Data

Using the filters you set up in Step 2, you can easily view specific segments of your customer base.

  1. Click on the filter icon in the header row of the column you wish to filter (e.g., ‘Status’)
  2. Select the values you want to display (e.g., “Lead”) and click OK
  3. Now you’ll only see the customers matching your filter criteria, making it easy to focus on specific tasks, like following up with leads

Conclusion

You now have a fully functional, no-cost CRM system built right in Google Sheets! This flexible system allows you to:

  • Track customer interactions with detailed contact information
  • Manage customer statuses with dropdown menus and color coding
  • Automatically calculate follow-up priorities with date tracking
  • Filter and organize your customer data for focused work
  • Customize everything to fit your unique business needs

All without needing to purchase expensive software or learn complex code. You can customize it further to fit your unique business needs.

Want to see this in action? Check out this example CRM in Google Sheets — feel free to make a copy and play around with it!

What other productivity tools would you like to learn how to build? We’d love to hear from you in the comments or reach out directly.