building a sales tracker in Google Sheets: complete tutorial
most solopreneurs run sales out of Notion, a CRM trial that expired, or worse, mental notes. they remember three deals, forget two, and never see a real conversion rate because they never had a single source of truth. by the time they buy HubSpot or Pipedrive, they have already missed two months of pipeline data that would have shaped pricing, positioning, and which lead sources actually paid.
the gap is not budget. you do not need a $90-per-month CRM to run a sales tracker. you need one Google Sheets tab with eight columns, three formulas, and two charts. that is enough to track every conversation, see your real win rate, and forecast monthly revenue inside 1% of actual.
this tutorial walks you through the entire build using a realistic 18-row sample dataset. by the end, you will have a working tracker, a live dashboard, and a system that takes 90 seconds to update per deal. no integrations, no Zaps, just Google Sheets.
the sample dataset
below is the dataset we will use throughout this tutorial. paste it into Google Sheets, save as sales-tracker-2026, and follow along.
| deal_id | created_date | company | source | stage | deal_value | close_date | owner_notes |
|---|---|---|---|---|---|---|---|
| D001 | 2026-04-01 | Brightline Co | won | 4800 | 2026-04-12 | annual prepay | |
| D002 | 2026-04-02 | Mango Studio | referral | won | 2400 | 2026-04-10 | quick close |
| D003 | 2026-04-03 | Ferrous Labs | cold_email | lost | 6000 | 2026-04-08 | budget freeze |
| D004 | 2026-04-04 | Pageturner | won | 3600 | 2026-04-15 | upsell candidate | |
| D005 | 2026-04-05 | Steele Group | referral | qualified | 8400 | demo scheduled | |
| D006 | 2026-04-06 | Quill House | cold_email | lost | 1800 | 2026-04-11 | wrong fit |
| D007 | 2026-04-08 | Northwind | proposal | 5400 | waiting on legal | ||
| D008 | 2026-04-09 | Kimchi Co | referral | won | 2200 | 2026-04-14 | three-month deal |
| D009 | 2026-04-10 | Pinewood | cold_email | qualified | 3000 | second call booked | |
| D010 | 2026-04-11 | Skyhigh Inc | lost | 4200 | 2026-04-19 | chose competitor | |
| D011 | 2026-04-12 | Rivermark | referral | won | 7200 | 2026-04-22 | annual contract |
| D012 | 2026-04-13 | Gilded Box | cold_email | proposal | 2800 | pending review | |
| D013 | 2026-04-14 | Brightline Co | upsell | won | 1800 | 2026-04-20 | add-on module |
| D014 | 2026-04-16 | Neon Forge | qualified | 4400 | discovery done | ||
| D015 | 2026-04-17 | Coastline | referral | won | 5600 | 2026-04-25 | repeat client |
| D016 | 2026-04-18 | Mossbank | cold_email | lost | 2200 | 2026-04-22 | no decision maker |
| D017 | 2026-04-20 | Tilework | proposal | 6800 | contract sent | ||
| D018 | 2026-04-21 | Hopfield | referral | won | 3400 | 2026-04-28 | small project |
a sales tracker in Google Sheets is the simplest CRM a solopreneur can run: one row per deal with stage, source, and value, plus pivots and conversion formulas to surface win rate by source, sales cycle in days, and monthly closed revenue. the standard build takes 30 minutes and replaces the first $90/month tier of HubSpot, Pipedrive, or Close. the tracker becomes a forecast input the moment you have 30 closed deals.
step 1: structure the master sheet
open Google Sheets. name the first tab deals. paste the dataset above into A1:H19.
lock data validation on stage
select column E (stage) → Data → Data validation → Dropdown from a list: qualified, proposal, won, lost. add a fifth value nurturing if you talk to leads who are not actively buying.
without this, future rows will end up as “Won”, “WON”, “won.” and your win rate calculation will silently break.
lock data validation on source
select column D (source) → same dropdown: linkedin, referral, cold_email, upsell, content, paid, other.
freeze headers
View → Freeze → 1 row. small thing, big quality of life difference once you have 200 deals.
common mistake: most solopreneurs skip the dropdowns and end up with five spellings of
step 2: derive sales cycle and month-of-close
add three helper columns starting at column I.
column I: cycle_days
formula in I2:
=IF(G2="", "", G2-B2)
expected output for D001: 11. for D005 (no close date): blank.
column J: close_month
formula in J2:
=IF(G2="", "", TEXT(G2,"yyyy-mm"))
expected output for D001: 2026-04. this lets you pivot by month later.
column K: is_won
formula in K2:
=IF(E2="won", 1, 0)
expected output: 1 for won, 0 otherwise. this gives you a simple sum-divisor for win rate calculations.
drag all three formulas down to row 19.
step 3: build the win rate by source pivot
select A1:K19 → Insert → Pivot table → New sheet.
configure the pivot
- Rows:
source - Values:
is_won(SUM) - Values:
deal_id(COUNTA)
then add a calculated field. Pivot editor → Values → Add → Calculated field → name win_rate → formula is_won / deal_id. format as percent.
expected output:
| source | won | total | win_rate |
|---|---|---|---|
| cold_email | 0 | 5 | 0.0% |
| 2 | 6 | 33.3% | |
| referral | 5 | 6 | 83.3% |
| upsell | 1 | 1 | 100.0% |
referrals close at 83%. cold email closes at 0%. that is the headline finding before you do anything else, and it should change how you spend your next 10 hours.
step 4: build the average deal size by source pivot
second pivot, same data range.
- Rows:
source - Filter:
stageiswon - Values:
deal_value(AVERAGE)
expected output:
| source | avg deal_value (won only) |
|---|---|
| 4200 | |
| referral | 4267 |
| upsell | 1800 |
linkedin and referral close at similar deal sizes. but the win rate gap means referral revenue per opportunity is far higher. expected revenue per deal opportunity is win_rate × avg_deal_value:
| source | expected_revenue_per_opp |
|---|---|
| cold_email | $0 |
| $1,400 | |
| referral | $3,557 |
each referral conversation is worth $3,557 in expected revenue. each cold email conversation is worth $0. you should be reading this and asking what your time looks like in those terms.
step 5: monthly closed revenue dashboard
third pivot.
- Rows:
close_month - Filter:
stageiswon - Values:
deal_value(SUM),deal_id(COUNTA)
expected output:
| close_month | revenue | deals |
|---|---|---|
| 2026-04 | 31000 | 8 |
with one month of data this is a single row. once you have 6+ months, this is the chart that goes in your monthly review. add a 3-month trailing average column with =AVERAGE(B-2:B). solopreneur revenue noise is high; the trailing average shows the trend.
step 6: pipeline value by stage
fourth pivot.
- Rows:
stage - Filter: exclude
wonandlost - Values:
deal_value(SUM)
expected output:
| stage | open_pipeline |
|---|---|
| proposal | 15000 |
| qualified | 15800 |
this is your forecast input. apply stage probabilities (proposal: 50%, qualified: 25%, nurturing: 10%) and you get a weighted forecast:
weighted = 15000 × 0.5 + 15800 × 0.25 = $11,450 expected to close in next 30 days.
with one month of historical win-rate data you can refine those stage probabilities. this is the simplest forecast model that beats gut feel.
step 7: chart the dashboard
create a new tab called dashboard. insert four charts:
- column chart:
monthly closed revenue - bar chart:
win rate by source - column chart:
average deal size by source - stacked bar:
pipeline value by stage
select each pivot → Insert → Chart → choose the type. drag all four charts onto the dashboard tab. give each a clear title.
side-by-side, the four charts answer the four questions every solopreneur should ask monthly: am I growing, where do I close, how big are my deals, what is in flight.
comparing google sheets sales tracker against paid CRMs
| capability | google sheets tracker | hubspot starter | pipedrive essential |
|---|---|---|---|
| price | free | $20/mo | $14/mo |
| custom fields | unlimited | 10 | 30 |
| pipeline stages | unlimited | unlimited | unlimited |
| email tracking | manual | built-in | built-in |
| automation | apps script | basic | basic |
| reports | pivots + charts | dashboards | dashboards |
| mobile | yes | yes | yes |
| time to setup | 30 minutes | 2 hours | 90 minutes |
below 30 deals/month and one user, the sheet wins on speed and flexibility. above 30 deals/month or two users, the paid CRM wins on email logging and shared visibility. there is no shame in upgrading when you outgrow it. start with the sheet.
our google sheets pivot table tutorial covers the broader pivot pattern, and our companion analyzing customer support tickets in Excel tutorial applies the same workflow to support data.
advanced: source attribution with multi-touch
if you want to know which content piece generated which deal, add two more columns: first_touch_source and last_touch_source. the first is whatever brought them in, the last is what closed them. these often differ, and the gap tells you whether your content is generating awareness or actual conversions.
run the same pivot on first_touch_source and you may discover linkedin generates the first touch but referrals close. that means linkedin is doing its job and your closing problem is somewhere else.
frequently asked questions
how many deals do I need before win rate is meaningful?
at least 20 closed deals (won + lost) per source before you trust the number. with our 18-row sample, cold email shows 0% win rate from 5 attempts, which is directional. for a real decision you would want 20+ deals per source.
should I track lost deals?
absolutely. lost deals are how you learn. track lost_reason as a free-text field, then categorize quarterly. patterns like “wrong fit” tell you to fix targeting. patterns like “price” tell you the offer needs work.
how do I import deals from past months?
dump your email, Slack, or memory into a list. minimum viable import: one row per deal with company name, source, value, and outcome. exact dates can be approximate within ±7 days. do not let perfect-data stop you from starting.
when should I move to a real CRM?
three signals: you missed a follow-up because the sheet did not nudge you, you have two team members trying to share the same tracker, or you want email auto-logging. before that, the sheet wins.
can I forecast revenue from this sheet?
yes. multiply weighted pipeline (stage × probability) plus prior-month closed revenue × growth rate. simple version: next_month_forecast = (open_pipeline × avg_win_rate) + (prior_month × 1.0).
conclusion: ship version one this afternoon
the gap between “I should track sales” and “I track sales” is 30 minutes. the sample dataset above gives you the schema. the four pivots give you the dashboard. the dropdowns keep your data clean enough to trust six months from now.
ship version one today. dump every deal in the last 90 days, even if half is from memory. you will instantly see your real win rate by source and probably realize one channel is carrying the rest. that single insight is worth the 30 minutes.
if you want to go further, our revenue forecasting in Excel and Sheets guide turns this tracker into a 6-month forecast, and our marketing funnel analysis tutorial connects the top of funnel to closed deals. start the sheet, then layer.