Yes—a sales tracking spreadsheet template can be free: create a workbook in Excel and use the column headings and formulas below as a ready-to-build starter. It tracks leads, deal stages, values, owners, follow-up dates, and closed sales without requiring a paid template or a CRM subscription.
What a sales tracking spreadsheet does
A sales tracker is a structured record of potential and completed sales. Each row represents one opportunity, such as a prospective customer, quote, subscription, or order. Columns hold the details needed to understand its progress and decide what to do next. Unlike a simple sales total, a useful tracker can show both what has closed and what may close later.
Small businesses, independent salespeople, and teams with straightforward processes often start with a spreadsheet because it is familiar and easy to adapt. It can answer practical questions: Which deals are still open? Who owns each opportunity? What follow-up is due? How much revenue did the team close this month? A spreadsheet does not automatically make those records accurate, however; people still need to enter updates consistently.
Free starter template: columns to include
In a blank Excel worksheet, paste these headings into row 1, one heading per column. Keep one opportunity per row and avoid merged cells, blank separator rows, or multiple deals in a single row.
- Opportunity ID: A unique reference, such as O-1001, useful when names repeat.
- Customer: The company or person associated with the potential sale.
- Owner: The salesperson responsible for the next step.
- Product or service: What the customer may buy.
- Stage: A consistent status such as Lead, Qualified, Proposal, Negotiation, Won, or Lost.
- Amount: The estimated or final value, using one consistent currency and value convention.
- Probability: An estimated chance of closing, entered as a percentage for open opportunities.
- Expected close date: The current estimate for when an open deal may close.
- Closed date: The actual date for a Won or Lost opportunity.
- Next action: A concrete task, such as “send revised quote.”
- Next action date: When that task should happen.
For example, one row might record O-1001, Northstar Bakery, Riley, Catering, Proposal, 12000, 50%, an expected close date, a blank closed date, “confirm guest count,” and a follow-up date. The example amount and probability are illustrative, not a forecast guarantee. If you do not need forecasting, omit Probability; if you sell simple one-time orders, consider recording completed orders rather than maintaining a pipeline.
Set up the workbook in Excel
In Excel for Microsoft 365 on Windows, enter the headings, select a cell in the data range, and choose Insert > Table. Confirm that the table has headers. A table adds filter buttons and generally extends formatting and formulas when you add rows. Give it a clear name from Table Design > Table Name, such as Sales, if you plan to use structured-reference formulas. Alternatively, keep the formulas below in a separate summary area and adjust their ranges as your list grows.
Use consistent data types: actual Excel dates in date columns, numbers in Amount, and percentage values in Probability. Format the relevant columns with Home > Number formats, choosing Date, Currency, or Percentage as appropriate. Do not type currency symbols into every amount as text; numeric values are easier to total. If using Excel for the web, the core table, formatting, and formula approach is similar, though available commands and their placement can differ slightly. On Mac, Excel has comparable features, but ribbon labels or locations can vary by release.
To keep headings visible while scrolling, choose View > Freeze Panes > Freeze Top Row in Windows. On Mac, use the View tab’s Freeze Panes controls. For a controlled stage list, select the Stage cells and choose Data > Data Validation, then set the allowed values as a list. The exact dialog can vary by Excel version. A shared vocabulary prevents one person entering “Proposal” and another using “Quote sent” for the same stage.
Useful formulas for a basic sales dashboard
The following examples assume headings in row 1 and opportunities in rows 2 through 1000, with Stage in column E, Amount in column F, Probability in column G, and Closed date in column I. Put summary formulas outside those columns, such as on a Dashboard sheet. These are ordinary Excel formulas and use commas between arguments; regional settings may require different separators.
With Stage in E2:E1000 and Amount in F2:F1000, this formula totals deals marked Won. If the Won rows contain 12,000 and 6,500, it returns 18,500.
=SUMIFS(F2:F1000,E2:E1000,"Won")
With Stage in E2:E1000, this counts opportunities marked Lost. If 7 rows have that exact stage, the result is 7.
=COUNTIF(E2:E1000,"Lost")
With Stage in E2:E1000, Amount in F2:F1000, and Probability in G2:G1000, this estimates the weighted value of open pipeline by excluding Won and Lost rows. If three open deals are 12,000 at 50%, 8,000 at 25%, and 4,000 at 75%, the result is 11,000: 6,000 + 2,000 + 3,000. Probability should be entered as percentages such as 50%, not the whole number 50.
=SUMPRODUCT((E2:E1000<>"Won")*(E2:E1000<>"Lost")*F2:F1000*G2:G1000)
This simple forecast assumes the probabilities are meaningful and the amounts are comparable. It does not predict the future with certainty. For more dependable monthly reporting, define whether Amount means total contract value, first-year value, or expected invoice value, and keep that definition consistent. A fixed range ending at row 1000 is easy to understand, but extend it if the list grows beyond that point. A formatted Excel table can also make a growing dataset easier to manage.
How to keep the tracker reliable
Agree on definitions before several people start entering records. For instance, decide what makes a lead “Qualified,” whether a deal is recorded when a quote is sent or only when a prospect confirms interest, and how to handle renewals. Record a Won opportunity once, with its actual closed date; do not leave it as open simply because someone still needs to complete administrative work.
A practical weekly routine is more valuable than adding elaborate formulas:
- Review open opportunities and confirm the owner, stage, amount, and expected close date.
- Give every active opportunity a specific next action and a date.
- Move completed deals to Won or Lost and enter the actual closed date.
- Check for missing values, duplicate records, and follow-up dates that have passed.
- Compare the dashboard totals with the source of truth for completed orders or invoices when accuracy matters.
Filters help people focus on a salesperson, stage, or date range without deleting records. Excel tables include filter controls in their header row. Conditional formatting can draw attention to important values; for example, Home > Conditional Formatting > Data Bars can visually compare amounts. Formatting is a prompt, not a substitute for reviewing the underlying data.
Common mistakes and limitations
The most common reporting problem is treating a pipeline estimate as realized revenue. Open opportunities may fall through, slip into a later period, or change in value. Keep closed sales separate from weighted pipeline and label dashboard figures clearly. Another frequent mistake is mixing currencies, tax-inclusive and tax-exclusive values, or recurring and one-time revenue in a single Amount column. If those distinctions matter, add separate fields or maintain separate reports.
Spreadsheets also rely on manual discipline. They do not inherently alert an owner to a missed follow-up, preserve a full activity history, or prevent two users from editing conflicting copies. Shared workbooks can reduce version confusion, but access permissions, accidental changes, and stale downloads still need attention. Use OneDrive or another approved shared storage location if collaboration is needed, and decide who may edit the file.
Be careful with customer information. Store only the details needed for sales work, restrict access to people who need it, and follow your organization’s privacy and retention policies. A basic tracker should not become an unsecured store for sensitive payment details or unnecessary personal information. Back up important workbooks and check that formulas still cover new rows after structural changes.
Spreadsheet or CRM: which is the better fit?
A spreadsheet is a reasonable starting point when one person or a small group tracks a manageable number of opportunities and the process is simple. It is flexible, inexpensive to create, and easy to tailor. It is also useful for a short-term campaign or a one-off analysis where installing a separate system would add needless overhead.
A customer relationship management (CRM) system is designed for ongoing customer records and sales workflows. Depending on the product and plan, it may provide reminders, interaction histories, permission controls, shared records, automation, and reporting. Those features can reduce manual work as a team grows, but software plans, setup, and training can involve costs. Compare the actual features and terms of a specific product rather than assuming every CRM includes the same tools.
There is no universal deal-count threshold at which a spreadsheet stops working. Consider switching when updates are often missed, several people need a reliable shared activity history, reporting requires repeated manual cleanup, or the team needs automated assignment and reminders. A CRM does not fix unclear stage definitions or poor data entry, so settle those basics whichever tool you choose.
Is a free template really free?
Building the layout above does not require buying a template; the headings and formulas can be entered in a blank workbook. Excel itself may be available through different arrangements, and the features and licensing terms depend on whether you use Microsoft 365, a standalone Office license, or Excel for the web. Check Microsoft’s current product information for your account and region rather than relying on an old price or feature list.
“Free template” can also mean a downloadable file offered by a third party. Read its instructions, inspect formulas, and remove sample customer data before using it. Avoid enabling macros in a workbook unless you trust its source and understand why they are needed. A sales tracker with the basic fields above does not require a macro.
Frequently Asked Questions
Where can I get a free sales tracking spreadsheet template?
Use the column list and formulas on this page to build one in a blank Excel workbook. You can also search Microsoft’s template gallery or other reputable providers, but check the workbook’s formulas, features, and license before relying on a downloaded file.
Can I use this sales tracker in Google Sheets?
Yes. The headings and basic SUMIFS, COUNTIF, and SUMPRODUCT formulas shown here also exist in Google Sheets. Some Excel-specific table behavior and menu paths differ, so check the sheet after importing it and use Google Sheets’ own data validation and filter controls.
What is the difference between sales revenue and pipeline value?
Revenue in a closed-sales report refers to deals recorded as Won under your chosen accounting or reporting definition. Pipeline value represents potential sales that are still open. A weighted pipeline applies estimated probabilities to open deal amounts, but it is still an estimate rather than booked revenue.
How often should I update a sales spreadsheet?
Update it whenever a meaningful event changes the opportunity—such as a new quote, a changed close date, a Won or Lost decision, or a completed follow-up. Many small teams also schedule a weekly review so that open records do not quietly become outdated.
When should I move from Excel to a CRM?
Consider a CRM when manual reminders, shared activity history, permissions, or reporting become difficult to manage in the workbook. The right time depends on how your team works, not a fixed number of sales records. Compare costs and required features before migrating.
Related
- How to Open a TSV File in Excel
- How to Open a TXT File in Excel Without Losing Data
- How to Open a Query in Excel and Edit It in Power Query
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Shortcut to Repeat the Last Command or ActionIn Excel for Microsoft 365 on Windows, press F4 or Ctrl+Y to repeat the last action when that…
- Excel Data Validation List: Build a Dynamic SourceTo make an Excel data validation list expand automatically, base it on an Excel Table, a named range,…
- Create Rules in Outlook on the Web: A Step-by-Step GuideTo create a rule in Outlook on the web, open Settings, go to Mail > Rules, choose Add…
- Family Feud PowerPoint Template: Build Your Own GameYes. You can create a Family Feud-style game template in Microsoft PowerPoint by making reusable question, answer-board, and…
- How to Delete a Blank Extra Page in WordTo delete a blank extra page in Word, show formatting marks, find the paragraph mark, page break, or…
- Google Sheets Array Formulas: How They Work and When to UseIn Google Sheets, an array formula processes a range of cells and can return multiple results from one…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.