Sales Target Tracking Format in Excel (Free Template + When to Stop Using It)

Almost every sales team in India starts target tracking the same way: one Excel sheet, one tab per month, and a manager who rebuilds it every quarter because last quarter's version got mangled.

That is not a bad starting point. A spreadsheet is free, everyone can read it, and it forces you to decide what you actually measure. This post gives you a target tracking format you can copy today, the formulas that make it useful rather than decorative, and an honest account of where the format breaks.

The core sales target tracking format

Keep one row per rep per month. Resist the urge to add a row per deal — that is a pipeline tracker, and mixing the two is the single most common reason these sheets collapse.

Column	Example	Why it earns its place
Rep	Anil K.	One row per rep per month
Month	Aug 2026	Never rely on tab names for this
Target	8,00,000	Set once, at the start of the month
Committed	5,20,000	Deals the rep says will close this month
Closed	3,10,000	Actually won, not "verbally confirmed"
Collected	2,10,000	Money in the bank
Achievement %	39%	=Closed/Target
Gap	4,90,000	=Target-Closed
Days left	9	=EOMONTH(TODAY(),0)-TODAY()
Run rate needed	54,444	=Gap/Days left

Two columns do most of the work here.

Collected, separate from Closed. A target hit on paper but not collected is not revenue, it is a receivable. Teams that track only Closed discover this in month four, usually during a cash crunch.

Run rate needed. Achievement percentage tells a rep where they have been. Run rate needed tells them what today has to look like. It is the only number in the sheet that changes behaviour on a Tuesday afternoon.

Formulas worth adding

Achievement %      =IF(C2=0,"",E2/C2)
Gap                =MAX(0,C2-E2)
Days left          =MAX(0,EOMONTH(TODAY(),0)-TODAY())
Run rate needed    =IF(I2=0,"",H2/I2)
Pace status        =IF(E2>=C2*(DAY(TODAY())/DAY(EOMONTH(TODAY(),0))),"On track","Behind")

That last one is the one managers actually use. It compares achievement against how much of the month has elapsed, so a rep at 50% on the 15th reads "On track" instead of triggering a pointless call.

Add conditional formatting on Pace status — green for on track, amber for behind — and you have a sheet a manager can read in eight seconds.

Setting the targets themselves

A tracking format is only as good as the numbers you put in it. Three rules that hold up across most SMB sales teams:

Work backwards from capacity, not ambition. If a rep closes 4 deals a month at an average value of 1,80,000, their realistic target is around 7,20,000. Setting 15,00,000 because the company needs it does not create pipeline, it creates sandbagging and quiet attrition.

Set targets per rep, not just per team. Team-level targets let weak performance hide behind strong performance for two or three months. By the time it surfaces, the quarter is gone.

Freeze targets at the start of the month. Revising a target mid-month because things are going badly destroys the only baseline you have.

The three signs your Excel sheet has stopped working

Spreadsheets fail slowly, which is what makes them dangerous. Watch for these.

1. The numbers only update when someone asks. If the sheet is accurate on the 1st and on review day but nowhere in between, you do not have a tracking system. You have a reporting ritual. Reps update it because a manager chased them, which means the sheet reflects what reps want the manager to see.

2. Nobody can answer "why is Anil behind?" from the sheet. The format above tells you that someone is behind. It cannot tell you whether it is a lead-volume problem, a conversion problem, or a follow-up problem, because the sheet has no connection to the actual leads and activity underneath. Every diagnosis requires a separate conversation.

3. There are now four versions of it. One on the manager's laptop, one shared on WhatsApp, one on Drive from March, one a rep maintains privately because they do not trust the shared one. This is the terminal stage.

What replaces it

The honest answer is: not a better spreadsheet. The problems above are structural. They come from targets living in a different place than the leads, follow-ups, and payments that produce them.

When target tracking sits on the same records as the pipeline, three things change. Achievement updates itself as deals move, because closing a deal is the update. "Why is Anil behind?" becomes a click into his actual pipeline rather than a meeting. And collected-versus-closed stops being a manual reconciliation, because payments are tracked against the same deal.

TasqMan does this — targets per rep and per team, tracked against real closed business and real collections, on web and mobile so field reps update from the phone. But the broader point stands regardless of tool: if your targets and your pipeline live in different files, someone is doing reconciliation by hand every week, and that someone is usually your best manager.

Start with the spreadsheet. Just know what its expiry date looks like.

FAQ

What is the best format for tracking sales targets in Excel?

One row per rep per month, with separate columns for Target, Committed, Closed, and Collected, plus calculated columns for Achievement %, Gap, Days left, and Run rate needed. Keep deal-level detail in a separate pipeline sheet — mixing target tracking and pipeline tracking in one tab is the most common reason these sheets become unusable.

How do I calculate sales target achievement percentage?

Divide closed business by the target for the period: =Closed/Target. To judge whether a rep is genuinely behind, compare that against how much of the month has elapsed rather than against 100%, since a rep at 50% achievement on the 15th of the month is on pace, not behind.

Should sales targets be set per rep or per team?

Both, but per-rep targets are essential. Team-only targets let underperformance hide behind strong performers for two or three months, by which point the quarter cannot be recovered. Set individual targets that roll up into the team number.

When should a sales team move from Excel to a CRM for target tracking?

When the sheet only updates because someone chased for it, when you cannot diagnose why a rep is behind without a separate conversation, or when multiple conflicting versions of the file exist. These are structural failures of the spreadsheet model, not formatting problems, and a better template will not fix them.

What is the difference between closed and collected in sales target tracking?

Closed is business won on paper. Collected is money actually received. Tracking only closed business overstates performance and hides receivable problems, which typically surface as a cash crunch several months later. Track both columns separately from day one.