How to make a simple CRM in Google Sheets or Excel (free template)

Short answer: Download the free CRM template (.xlsx). It opens in Excel and imports into Google Sheets. To build your own: one row per client, dropdowns for stage and owner, a next follow-up date on every open deal, a formula that flags overdue follow-ups, and a summary sheet that counts deals and value per stage.

Disclosure: I co-founded Teamo.

What's in the free CRM template?

The template is one Excel file with four sheets. It uses only what both Excel and Google Sheets support: dropdowns, conditional formatting, formulas such as COUNTIFS, SUMIFS and REPT, a frozen header row and a filter. No macros, add-ons or links to other files.

The Clients sheet has 13 columns:

The columns that keep deals moving are Next follow-up and Last contact. The template flags an active deal in red when its follow-up date has passed, in amber when it's due in the next 7 days, and otherwise in purple when nobody has been in touch for more than 30 days (Gone quiet). An active deal with no follow-up date at all is marked No date, unless its Last contact is old or missing, in which case it shows Gone quiet first. Won and Lost rows are never flagged.

How do you make a CRM in Google Sheets?

To use the template in Google Sheets, open a blank spreadsheet, choose File > Import > Upload, pick the file and choose Create new spreadsheet (Google's import guide). If your Google Sheets or Excel uses semicolons between arguments, as is common in Europe, swap each comma in the formulas below for a semicolon. To build the same thing yourself:

1. Make three tabs: Clients, Pipeline and Settings. On Clients, type the column names from the table above in row 1.

2. Freeze the header row. Select row 1, then View > Freeze > 1 row (guide).

3. Fill in Settings. Put three small tables side by side, with headers in row 6 and values from row 7: Stage, Still active? (Yes or No) and Chance of winning in columns A to C, owners in column E, lead sources in column G. Mark Won and Lost as not active, with Won at 100% and Lost at 0%. In A24 type "Gone quiet after (days)" and in B24 the number, such as 30.

4. Add the dropdowns. Select E2:E500 (the Stage column below its header), then Insert > Dropdown (or Data > Data validation > Add rule), choose Dropdown from a range and enter Settings!A7:A20 (guide). Repeat for Owner (cells F2:F500, Settings!E7:E20) and Source (cells H2:H500, Settings!G7:G20).

5. Add the follow-up formula. In L2, paste this and copy it down the column:

   =IF(A2="","",IF(IFERROR(VLOOKUP(E2,Settings!$A$7:$B$20,2,FALSE),"Yes")="No","",IF(AND(K2<>"",K2<TODAY()),"Overdue",IF(AND(K2<>"",K2<=TODAY()+7),"Due soon",IF(OR(I2="",I2<TODAY()-Settings!$B$24),"Gone quiet",IF(K2="","No date",""))))))
   

   It stays empty for finished stages. Otherwise it checks, in order: is the follow-up date past (Overdue) or within 7 days (Due soon), is the last contact older than the days in Settings or missing (Gone quiet), and is there no follow-up date at all (No date).

6. Color the rows. Select A2:M500, then Format > Conditional formatting, choose Custom formula is and enter =$L2="Overdue" with a light red fill (guide). Add two more rules: =$L2="Due soon" with light amber and =$L2="Gone quiet" with light purple.

7. Add up the pipeline. On the Pipeline tab, list the stages from A5 down, and fill in each row:

   • Deals: =COUNTIFS(Clients!$E$2:$E$500,A5)
   • Value (column C): =SUMIFS(Clients!$G$2:$G$500,Clients!$E$2:$E$500,A5)
   • Active? and Chance (columns D and E): the stage's Yes or No and percentage from Settings
   • Expected: =IF(D5="No","",C5*E5), the value weighted by the chance of winning
   • A bar for the value: =IF(MAX($C$5:$C$12)=0,"",REPT("█",ROUND(C5/MAX($C$5:$C$12)*20,0))) draws up to 20 blocks, scaled to the biggest stage, and stays empty while there are no deals

   In B14, below the stage rows, the win rate is won deals divided by all finished deals: =IFERROR(SUMIFS(B5:B12,D5:D12,"No",E5:E12,1)/SUMIFS(B5:B12,D5:D12,"No"),""). Count each flag with =COUNTIF(Clients!$L$2:$L$500,"Overdue"), and so on.

8. Add a filter. Select all the data, including the header row, then Data > Create a filter (guide).

How do you make a CRM in Excel?

To use the template, open the downloaded file in Excel. To build it yourself, the columns, Settings tables and formulas are the same as in Google Sheets, REPT included (Excel's REPT, Sheets' REPT); only the menus differ:

1. Make the Clients, Pipeline and Settings sheets, type the column names in row 1 of Clients, and fill in Settings as described above.
2. Freeze the header row: View > Freeze Panes > Freeze Top Row (guide).
3. Add the dropdowns. Select E2:E500 (the Stage column below its header), then Data > Data Validation. Set Allow to List and enter =Settings!$A$7:$A$20 as the Source (guide). Repeat for Owner (cells F2:F500, =Settings!$E$7:$E$20) and Source (cells H2:H500, =Settings!$G$7:$G$20).
4. Add the follow-up formula in L2 (the same formula as above) and fill it down.
5. Color the rows. Select A2:M500, then Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and enter =$L2="Overdue" with a red fill (guide). Add =$L2="Due soon" with amber and =$L2="Gone quiet" with purple.
6. Add up the pipeline with the same COUNTIFS, SUMIFS, expected value, win rate and REPT formulas.
7. Add a filter: click a cell in the header row, then Data > Filter (guide).

How do you use a spreadsheet CRM day to day?

A spreadsheet CRM works as long as it's updated at the moment something happens. The habits that keep it useful:

• Give every active deal a next follow-up date. A deal without one is a deal nobody is chasing. The template marks it "No date".
• Update three cells after every call or email: Last contact, Next step and Next follow-up. A deal with a follow-up date weeks away can still go cold; when Last contact is more than 30 days ago, the row turns purple (Gone quiet).
• Start the day with a filter. Filter the Follow-up column on Overdue, or the Owner column on your name.
• Look at the Pipeline sheet once a week: what's stuck in Proposal sent, who has the most flagged follow-ups, the expected value of active deals, and the win rate. Adjust each stage's chance of winning on Settings once you know your real numbers.
• Write stages the same way every time. The dropdowns enforce this; typing "Closed won", or a stray space, breaks the counts.

What goes wrong when several people edit a spreadsheet CRM?

Google Sheets and Excel for the web both let several people edit at once, so overwriting is rarer than it used to be. What still goes wrong:

• One person's filter or sort changes everyone's view. In Google Sheets, use Data > Create filter view instead: it filters only your screen (guide). Excel has View > Sheet View > New for the same job, in files stored on OneDrive or SharePoint (guide).
• Someone types over a formula. The Follow-up column then stops updating on that row. Settings needs the same care: a stage typed twice turns red there, but renaming a stage leaves the old name on every Clients row that used it, and those rows drop out of the Pipeline counts. In Google Sheets, Data > Protect sheets and ranges can show a warning before anyone edits it (guide).
• Copies multiply. Someone downloads the file or emails it as an attachment, and now two versions get updated. Share one link and keep one file.
• Dropdowns get pasted over. Pasting a cell from elsewhere can bring in a value that isn't in the list. Filter the Stage column now and then to spot strays.

What can't a spreadsheet CRM do?

A spreadsheet does more than it gets credit for. Google Sheets keeps a per-cell edit history (right-click a cell, Show edit history), though it misses added or deleted rows and format changes (Google's guide). Charts and formulas update as soon as someone edits a row, and several people can work in the file at once.

What it can't do without scripts or add-ons:

• Remind anyone on a date. The red row only helps if someone opens the file. Google Sheets can email you when the file changes, not when a follow-up date arrives.
• Hold a client's history. Emails and messages stay in each person's inbox, not on the row.
• Handle one client with several deals or projects without repeating the client on several rows or adding "Deal 2" columns.
• Hide some columns from some people. Everyone with access to the file sees every deal value.

Signs that these gaps have started to cost you deals are in Signs you've outgrown your spreadsheet CRM. When you're ready to switch, How to move from a spreadsheet to a CRM covers the cleanup and the import step by step.

What if you outgrow the template?

The template's columns map straight onto a CRM. In Teamo, you attach the file in a chat with Amo, the assistant, and ask it to import the Clients sheet into your Clients module. Amo reads the first sheet unless you name another, shows what it's about to create and waits for your approval. It imports up to 2,000 rows per file, matches the Owner column to your team members by name or email, and adds any stage it doesn't recognize as a new option, so merge variants such as "Closed won" and "Won" first. Rows whose owner doesn't match a team member are skipped, so invite your team first and use the same names, or import Owner as a text column. Save a copy, delete its Follow-up column (column L) and attach that copy: the formula runs down to row 500, so Amo would count those empty rows too. Teamo doesn't need the column.

Each gap from the spreadsheet then has a feature that covers it:

Teamo starts at €10 / $12 a seat a month on yearly billing. 10-day trial on Max, no card.

FAQ

Is there a free CRM template for Google Sheets? Yes. Download this one and import it with File > Import > Upload in Google Sheets. It has stage and owner dropdowns, overdue and gone-quiet deals flagged, and a pipeline summary with expected value and win rate, with no sign-up.

Can you create a CRM in Excel? Yes. One sheet of clients with a column per detail, dropdowns from Data > Data Validation, a follow-up formula, conditional formatting for overdue rows and a summary sheet with COUNTIFS and SUMIFS. The template does all of this and opens in Excel directly.

Is Google Sheets or Excel better for a CRM? Google Sheets, if more than one person updates it: everyone edits the same file in the browser, and filter views stop one person's filter from changing everyone else's. Excel works just as well for one person, or for a team that keeps the file on OneDrive or SharePoint.

How do I track follow-ups in a spreadsheet? Give every active deal a Next follow-up date, then add a formula that compares it and the Last contact date with TODAY(), and conditional formatting that colors overdue and gone-quiet rows. The spreadsheet can't remind you on the date, so open it and filter on Overdue every morning.

Should a CRM spreadsheet have one row per client or one per deal? One row per client works while each client has one deal at a time. When clients start buying twice, add a second row with the same company name, and expect the client's history to spread across rows. That's usually the point to move to a CRM, where deals sit under the client.