Last updated: 5 November 2026 · By Leads Nimble Team (a product of Luminous Labs, Dhaka)
A free CRM template in Google Sheets is a shared spreadsheet with fixed columns, dropdowns and formulas that track every Facebook page customer from first message to delivery. Below is the exact layout we recommend for Bangladeshi page sellers: 21 columns, a Settings tab, a Dashboard with COUNTIF formulas, and follow-up alerts. It takes about 20 minutes to build.
Key takeaways
- Use three tabs: Leads, Settings and Dashboard.
- Dropdowns for Channel, Status, Payment, Area and Courier keep data clean and countable.
- A “Follow-up due” formula plus red highlighting tells you who to message today.
- COUNTIF and SUMIFS turn the sheet into a simple sales report: orders, COD pending, leads by channel.
- Move to a real CRM when chats come from several channels, or more than two people edit the sheet daily.
What is a Google Sheets CRM template?
A Google Sheets CRM is a spreadsheet used as a customer relationship management system. Each row is one lead or order, each column is one fact about it, and dropdowns control the values.
For a small Facebook page, this is often the best first step. It is free, works on phone and laptop, and the whole team can edit it at once.
It is not automatic, though. Someone still has to copy each customer from Messenger or WhatsApp into the sheet. Keep that in mind; we will come back to it.
Free CRM template layout: the exact columns
Create a new Google Sheet and rename the first tab to Leads. Type these headers in row 1, from column A to U.
| Column | Header | Type / rule | Example |
|---|---|---|---|
| A | Lead ID | Formula (auto) | LN-0042 |
| B | Date | Date of first message | 05/11/2026 |
| C | Customer name | Text | Nusrat Jahan |
| D | Phone | Plain text, 11 digits | 01712xxxxxx |
| E | Channel | Dropdown | Messenger |
| F | Source post / ad | Text | Eid kurti boost |
| G | Product / code | Text | KT-118 |
| H | Size / variant | Text | M, Maroon |
| I | Qty | Number | 2 |
| J | Unit price (BDT) | Number | 1,250 |
| K | Delivery area | Dropdown | Outside Dhaka |
| L | Delivery charge | Formula (auto) | From Settings tab |
| M | Total (BDT) | Formula (auto) | Qty × price + delivery |
| N | Payment | Dropdown | COD |
| O | Status | Dropdown | Order confirmed |
| P | Courier | Dropdown | Steadfast |
| Q | Consignment ID | Text | From courier panel |
| R | Next follow-up | Date | 07/11/2026 |
| S | Owner | Dropdown (team names) | Rafi |
| T | Notes | Text | Wants gift wrap |
| U | Follow-up due? | Formula (auto) | DUE |
Select row 1 and choose View → Freeze → 1 row so headers stay visible while you scroll.
Why format the Phone column as plain text?
Bangladeshi mobile numbers start with 0, such as 017, 018 or 019. Sheets drops the leading zero if the column is a number.
Select column D, then Format → Number → Plain text. Now 01712345678 stays exactly as typed.
How to add dropdowns to your CRM template
Dropdowns make every status countable. “Confirmed”, “confirm” and “CONFIRMED” become one value.
First, add a second tab called Settings. Put each list in its own column:
| Settings column | List name | Values (edit to fit your business) |
|---|---|---|
| A | Channel | Messenger, WhatsApp, Instagram, Page comment, Phone call, Website |
| B | Status | New, Price asked, Interested, Order confirmed, Shipped, Delivered, Returned, Lost |
| C | Payment | COD, bKash, Nagad, Bank, Card |
| D | Delivery area | Inside Dhaka, Dhaka suburbs, Outside Dhaka |
| E | Delivery charge | Your charge for each area, in the same order as column D |
| F | Courier | Pathao, Steadfast, RedX, Own rider, Other |
| G | Owner | Names of the people who reply to customers |
Put the header in row 1 and values from row 2 down. Then follow these steps for each dropdown column on the Leads tab:
- Select the column range, for example E2:E1000 for Channel.
- Click Data → Data validation → Add rule.
- Under Criteria, choose Dropdown (from a range).
- Enter the range, for example Settings!A2:A20.
- Under Advanced options, choose Reject the input so nobody types a free-text status.
- Click Done. Repeat for K (Settings!D2:D20), N (C2:C20), O (B2:B20), P (F2:F20) and S (G2:G20).
Google explains each option in its help article on creating an in-cell dropdown list.
Formulas for the CRM template (copy-paste)
Enter each formula in row 2, then drag it down to row 1000. Blank-row checks keep empty rows clean.
Auto Lead ID (column A)
=IF(C2="","","LN-"&TEXT(ROW()-1,"0000"))
This creates LN-0001, LN-0002 and so on as soon as you type a name. Do not sort rows if you need IDs to stay fixed; use filters instead.
Delivery charge from area (column L)
=IF(K2="","",IFERROR(INDEX(Settings!$E$2:$E$20,MATCH(K2,Settings!$D$2:$D$20,0)),""))
Change a charge once in Settings, and every new row uses the new amount.
Order total (column M)
=IF(OR(I2="",J2=""),"",I2*J2+N(L2))
Follow-up due flag (column U)
=IF(AND(R2<>"",R2<=TODAY(),O2<>"Delivered",O2<>"Lost",O2<>"Returned"),"DUE","")
Any open lead whose follow-up date is today or earlier shows “DUE”.
Highlight due follow-ups and duplicate phones
Select A2:U1000 and open Format → Conditional formatting. Choose Custom formula is and enter:
=$U2="DUE"with a light red fill, for overdue follow-ups.=AND($D2<>"",COUNTIF($D$2:$D,$D2)>1)with a yellow fill, for repeat customers or double entries.
Google’s guide to conditional formatting rules shows where each setting sits.
Phone number check
Add a data validation rule on D2:D1000 with Custom formula is:
=AND(LEN(D2)=11,LEFT(D2,2)="01")
This catches numbers with a missing digit before the courier calls a wrong number.
How to build the Dashboard tab with COUNTIF
Add a third tab called Dashboard. Type the labels in column A and the formulas in column B.
| Metric | Formula |
|---|---|
| Total leads | =COUNTA(Leads!C2:C) |
| New leads today | =COUNTIF(Leads!B2:B,TODAY()) |
| Orders confirmed | =COUNTIF(Leads!O2:O,"Order confirmed") |
| Delivered | =COUNTIF(Leads!O2:O,"Delivered") |
| Lead-to-delivery rate | =IFERROR(B5/B2,0) (format as %) |
| Follow-ups due now | =COUNTIF(Leads!U2:U,"DUE") |
| Delivered revenue (BDT) | =SUMIF(Leads!O2:O,"Delivered",Leads!M2:M) |
| COD still with courier (BDT) | =SUMIFS(Leads!M2:M,Leads!O2:O,"Shipped",Leads!N2:N,"COD") |
| Leads from Messenger | =COUNTIF(Leads!E2:E,"Messenger") |
| Leads from WhatsApp | =COUNTIF(Leads!E2:E,"WhatsApp") |
| Returns | =COUNTIF(Leads!O2:O,"Returned") |
The formulas assume the labels start in A2 and follow the table order, so “Total leads” is B2 and “Delivered” is B5. Adjust references if you change the order. The COUNTIF help page explains the syntax.
How to copy and share the template with your team
- Build it once using the steps above, then add two or three test rows to check the formulas.
- Delete the test rows but keep the formulas in columns A, L, M and U.
- Make a master copy. Use File → Make a copy and name it “CRM Template – Master”. Never type real leads into the master.
- Share the working copy with your team using Share → Editor. Give view-only access to anyone who just needs reports.
- Protect formula columns. Right-click column A → View more column actions → Protect range, so nobody overwrites a formula by mistake.
- Start a fresh copy for each new page or each year, so the sheet stays fast.
Daily routine: how to use the sheet
- Morning: filter column U for “DUE” and message those customers first.
- Every new chat: add a row with name, phone, channel and product. Set status to “New” or “Price asked”.
- After confirming: change status, add courier and consignment ID.
- Evening: check the Dashboard for COD still with the courier and returns.
Google Sheets CRM vs a real CRM: when do you outgrow it?
Sheets is a great start. But the copy-paste step does not scale.
| Task | Google Sheets template | Chat-based CRM (e.g. Leads Nimble) |
|---|---|---|
| Capture new Messenger lead | Manual copy | Automatic from the inbox |
| Reply to the customer | Switch to Messenger app | Reply from the same screen; AI can answer 24/7 |
| Know which ad brought the lead | Typed by hand, often skipped | Campaign/ad attribution |
| Follow-up reminder | Formula + someone checking | Reminders and tasks |
| Several channels | One “Channel” dropdown | Messenger, WhatsApp, Instagram, web chat, Telegram in one inbox |
| Team roles | Everyone edits everything | Roles and permissions |
| Cost | Free | Free plan; paid plans in Taka |
Signs you have outgrown the sheet:
- You spend more than an hour a day copying chats into rows.
- Two people answer the same customer, or nobody does.
- Leads from WhatsApp never reach the sheet.
- The sheet takes seconds to load on a phone.
Read the full checklist in free vs paid CRM: 7 signs you’ve outgrown Excel, or compare options in our guide to the best free CRM software in Bangladesh.
Next step: automate the copy-paste
Leads Nimble turns every Messenger chat into a lead in a visual pipeline, so the sheet fills itself. Its AI assistant replies in Bangla and English, and it can still sync with Google Sheets if your team likes the spreadsheet view.
The Free plan includes 1 user, one Messenger page and 500 AI credits a month, with no credit card. See all features or book a demo.
Frequently asked questions
Is there a free CRM template for Google Sheets?
Yes. You can build one in about 20 minutes with the layout in this guide: a Leads tab with 21 columns, a Settings tab for dropdown lists and a Dashboard tab with COUNTIF formulas. It costs nothing beyond a Google account. Make a master copy first so you can reuse the clean template for other pages.
Which columns should a CRM sheet have for a Facebook page?
At minimum: date, customer name, phone, channel, product, price, delivery area, delivery charge, total, payment method, status, courier, consignment ID, next follow-up date and owner. Add the source post or ad if you run boosts, so you can see which posts actually bring orders.
How do I stop Google Sheets removing the 0 from phone numbers?
Select the phone column and choose Format → Number → Plain text before typing. Bangladeshi numbers then keep their leading 0. You can add a data validation rule with the formula =AND(LEN(D2)=11,LEFT(D2,2)=”01″) to catch numbers with missing digits.
How do I count orders by status in Google Sheets?
Use COUNTIF on the status column. For example, =COUNTIF(Leads!O2:O,”Order confirmed”) counts confirmed orders. For two conditions, such as shipped COD orders, use SUMIFS or COUNTIFS. Dropdowns keep spelling identical, which is what makes these formulas accurate.
Can several people use the same Google Sheets CRM?
Yes. Share the sheet with Editor access and everyone sees changes live. Protect formula columns so nobody deletes them, and add an Owner column so each lead has one person responsible. With more than two or three active editors, conflicts and double replies become common.
When should I move from a spreadsheet to a CRM?
Move when copying chats takes too much time, when leads come from several channels, or when follow-ups are missed. A chat-based CRM such as Leads Nimble captures Messenger leads automatically, reminds you to follow up and shows which ad brought each customer. Its Free plan is a low-risk way to test.
