Build a Simple Customer Tracker (CRM) in Excel

A simple CRM in Excel is a customer tracker: one row per customer, with their stage, deal value, owner and the date of the next follow-up. CRM stands for customer relationship management, and for a freelancer or a small team, a well-built sheet covers the basics. This guide builds one with an Excel table, a Stage drop-down, filters and COUNTIF summaries, so you always know who to call next.

What columns a simple CRM needs#

Start small. Each column should be something you will filter, count or act on. Here is the core layout:

A few rules keep it working as it grows:

• One row per customer. Don't add a second row for the same person; update the existing one.
• One fact per column. Put the company and the name in separate columns, so you can sort and filter by either.
• No blank rows or merged cells. They break filters, sorting and formulas.
• Real dates. Type dates so Excel recognises them, and they'll sort and compare correctly.

You might also add Email, Phone, Source (where the lead came from) and Last contact. Keep notes short; long histories are hard to read in a cell.

Step 1: Turn the list into an Excel table#

An Excel table makes the CRM easier to use and to extend. Click any cell in the list and press Ctrl+T (Mac: ⌘T), check that My table has headers is ticked, and click OK.

You get three things at once:

• Filter buttons on every header.
• Automatic growth. Type in the row below the table and it becomes part of the table, with the same formatting and formulas.
• Banded rows, which make long lists easier to read across.

Step 2: Add a drop-down list for the stage#

A drop-down list makes everyone type the stage the same way. "proposal", "Proposal sent" and "Prop" would otherwise count as three different stages.

1. Type your stages in a spare area, such as J2:J6: Lead, Contacted, Proposal, Won, Lost.
2. Select the Stage column cells, C2:C8.
3. Choose Data > Data Validation, set Allow to List, and set Source to =$J$2:$J$6.
4. Click OK.

Each Stage cell now has an arrow with the five options. Do the same for Owner if several people share the CRM.

Step 3: Filter to find who to call#

Filters show only the rows you need right now, without deleting anything. Click the arrow on a header and pick values:

• Stage = Proposal shows the deals closest to closing.
• Owner = Ana shows one person's customers.
• Next follow-up > Date Filters > Today (or Before a date) shows calls that are due.

Filters on two columns combine: Owner = Sam and Stage = Proposal shows just Maya and Grace. To clear every filter at once, press Ctrl+Shift+L (Mac: ⌘⇧F) twice: once to turn filters off and once to turn them back on. You can practise filtering a table in a live sheet.

Step 4: Count customers by stage with COUNTIF#

A small summary shows your pipeline at a glance. A pipeline is the set of deals at each stage. List the stages in J2:J6 (you already have them from Step 2) and in K2 type:

=COUNTIF($C$2:$C$8,J2)

Fill it down. The results are Lead 1, Contacted 1, Proposal 3, Won 1 and Lost 1. The $ signs lock the Stage column, and J2 changes to J3, J4 and so on as you fill.

To add up the value at each stage, use SUMIF in L2:

=SUMIF($C$2:$C$8,J2,$D$2:$D$8)

Proposal is worth 12,300 (4,800 + 2,300 + 5,200). A simple win rate is won deals divided by closed deals: =COUNTIF(C2:C8,"Won")/(COUNTIF(C2:C8,"Won")+COUNTIF(C2:C8,"Lost")), which is 50% here. Practise COUNTIF to get quick at these summaries.

Step 5: Flag overdue follow-ups#

Conditional formatting colors a row when its follow-up date has arrived. That way, nothing slips through.

1. Select the data rows, A2:G8.
2. Choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
3. Enter =AND($F2<>"",$F2<=TODAY()) and pick a light red fill.

The rule colors any row whose follow-up date is today or earlier. The $F2<>"" part skips Won and Lost customers with no date. TODAY returns the current date, so the colors update every day you open the file. To count overdue calls, use =COUNTIFS(F2:F8,"<="&TODAY()).

Step 6: Keep the CRM tidy#

A CRM is only useful while it's up to date. A few habits help:

• Update after every call. Change the stage, write a short note and set the next follow-up date before you hang up.
• Never leave an open deal without a date. A Lead, Contacted or Proposal row with no follow-up date is easy to forget.
• Archive old rows. Move customers who were Lost a long time ago to an Archive sheet, so the main list stays short.
• Back up the file. Save it somewhere with version history, such as OneDrive.

Excel works well up to a few hundred customers and a small team. If you need email tracking or many people editing at once, that's the time to look at dedicated software.

Related trackers#

The same pattern (one row per item, a status drop-down, COUNTIF and dates) works for many trackers. Build a job application tracker for your own search, or an order tracker that looks up orders by ID. If you work with customers every day, the article on Excel skills for customer service jobs shows which of these skills employers test.

Practise it#

Try the exercise on this page: count the customers at the Proposal stage with COUNTIF. Then practise filtering a table in a live spreadsheet to find the right customers fast, and practise COUNTIF to turn your list into a pipeline summary.