What columns does a Google Sheets sales pipeline tracker need?

At minimum: Deal ID (for deduplication), Deal Name, Owner, ARR or deal value, Stage, Expected Close Date, and Last Activity Date. Most real pipeline dashboards also need Created Date (to calculate velocity — how many days deals spend at each stage) and a Weighted ARR column (ARR × stage probability). Keep these in a clean processing tab separate from the raw CRM export. CRM exports change schema without warning, and you need the original columns intact when that happens.

How do I calculate weighted pipeline in Google Sheets?

Multiply each deal's ARR by a stage probability (Discovery = 10%, Proposal = 30%, Negotiation = 60% are typical starting points) and sum the results. For under 5,000 rows, use `=SUMPRODUCT(probability_range * arr_range)` with IFERROR wrappers on both ranges. Above 5,000 rows, SUMPRODUCT recalculates noticeably slowly — switch to a QUERY that groups ARR by stage, then multiply by probability weights in a helper column. Store weights in a separate lookup tab so you can update them without rewriting formulas when your sales ops lead adjusts the stage model.

How do I handle mixed date formats in a CRM export?

Wrap every date reference in `IFERROR(DATEVALUE(A2), IFERROR(VALUE(A2), ""))` to attempt multiple parse methods before giving up. For a more reliable fix, add a `Close_Date_Clean` helper column during import that normalizes all dates to a consistent format, then reference only that column in downstream formulas. A fast diagnostic: real dates align right in Sheets, text-formatted dates align left. If your Close Date column shows left-aligned values, they failed to parse and will return errors in any date arithmetic. At 30,000 rows, even a 5% parse failure rate means 1,500 deals silently excluded from your pipeline totals.

How many rows can a Google Sheets pipeline tracker handle before performance degrades?

Google's documentation states Sheets supports up to 10 million cells, but formula performance degrades well before that. A pipeline sheet with 30,000 rows and complex ARRAYFORMULA logic typically sees recalculation times of 10-20 seconds. Above 50,000 rows, split the architecture: raw data in one spreadsheet, QUERY-based aggregations in a second sheet, summary dashboard as a third. Switching from ARRAYFORMULA to QUERY for group-by aggregations is the single biggest performance win — QUERY pushes computation server-side, while ARRAYFORMULA runs in the browser tab and blocks interaction while it recalculates.

How do I flag stale pipeline deals automatically in Google Sheets?

Add a helper column with: `=IF(AND(Stage<>"Closed Won", Stage<>"Closed Lost", IFERROR(TODAY()-VALUE(LastActivity), 999)>30), "STALE", "")`. The `IFERROR(..., 999)` handles blank or unparseable activity dates by treating them as maximally stale — which is intentional, because a deal with no recorded activity is a problem regardless of close date. Apply conditional formatting to highlight STALE rows in red, then surface the count in your summary dashboard with `=COUNTIF(flag_column, "STALE")`. Most sales ops teams set the threshold at 21-30 days depending on their average sales cycle length; adjust the `>30` accordingly.

Sales Pipeline Tracker in Google Sheets (2026)

The core structure is a raw data tab fed by CRM export (or IMPORTRANGE from a connected sheet), a calculation layer that handles dirty data, and a summary view that answers the 3 questions your director asks every Monday: what's the total weighted pipeline, which reps have stale deals, and where are opportunities dropping out of the funnel.

What the CRM Export Actually Looks Like

Salesforce and HubSpot exports land in Sheets looking roughly civilized — until you check the Close Date column. A real 30,000-row Salesforce export will contain "2026-05-01", "5/1/2026", "May 1, 2026", and a handful of blanks all in the same column, because 3 different data entry workflows touched those records over 18 months. The Stage field has "Proposal", "proposal", "PROPOSAL - REVISED", and whatever the new SDR typed before anyone corrected them.

This isn't edge-case data. This is the export. Your tracker has to handle it before it hits any formula.

The Schema That Holds Up

Keep the raw export tab untouched — never transform in place. Build a Pipeline_Clean tab that standardizes as it reads. Seven columns covers 90% of pipeline reporting needs:

Column	Header	Notes
A	Deal ID	Unique key for deduplication
B	Deal Name	Text, no transforms needed
C	Owner	TRIM + UPPER to normalize casing
D	ARR	VALUE() wrapper required
E	Stage	Normalized via lookup table
F	Close Date	Parsed with DATEVALUE()
G	Last Activity	For stale-deal flagging

The Stage column breaks most trackers. Don't try to clean it in the raw export — maintain a small Stage_Map lookup tab that maps every variant ("proposal", "Proposal - Revised", "PROP") to a canonical value. Then pull it into Pipeline_Clean:

=IFERROR(VLOOKUP(TRIM(LOWER(Raw!E2)), Stage_Map!$A:$B, 2, 0), "UNKNOWN")

The IFERROR is mandatory. When a new stage name appears (and it will, usually mid-quarter), the formula returns "UNKNOWN" instead of breaking every downstream SUMIF. An "UNKNOWN" that shows up in your funnel table is visible. A broken SUMIF that returns zero is not.

Weighted Pipeline: The Number Your Director Actually Wants

Weighted pipeline multiplies each deal's ARR by its probability of closing at the current stage. Most sales orgs assign rough percentages: Discovery at 10%, Proposal at 30%, Negotiation at 60%, Verbal Commit at 85%. Put those weights in a named Weights tab, not hardcoded into the formula — stages get renamed and re-weighted every few quarters and you don't want to hunt through 12 SUMPRODUCT calls to update them.

For under 5,000 rows, SUMPRODUCT works well:

=SUMPRODUCT(
  (Pipeline_Clean!E2:E5001<>"Closed Lost") *
  (Pipeline_Clean!E2:E5001<>"Closed Won") *
  IFERROR(VLOOKUP(Pipeline_Clean!E2:E5001, Weights!$A:$B, 2, 0), 0) *
  IFERROR(VALUE(Pipeline_Clean!D2:D5001), 0)
)

Above 5,000 rows, SUMPRODUCT starts lagging. Switch to QUERY for the aggregation:

SELECT E, SUM(D)
WHERE E <> 'Closed Lost' AND E <> 'Closed Won' AND D IS NOT NULL
GROUP BY E
LABEL SUM(D) 'Total ARR'

Then multiply against the Weights tab in a helper column. Less elegant, but it recalculates in under 2 seconds on a 30,000-row sheet. The equivalent nested SUMPRODUCT takes 18+ seconds on the same data.

Stage Conversion: Where the Funnel Actually Leaks

The weighted total tells you how big the pipeline is. Stage conversion tells you where deals go to die. For a director standup, that's often the more important number.

Build a COUNTIF-based funnel table where each row is a canonical stage, with columns for deal count, total ARR, and conversion rate to the next stage:

=COUNTIF(Pipeline_Clean!$E:$E, A2)

Conversion rate between adjacent stages is:

=IFERROR(B3/B2, 0)

Where B2 is the count at stage N and B3 is the count at stage N+1. Wrap it in IFERROR because Discovery will sometimes be zero on the first Monday of a new quarter and a #DIV/0! on a director dashboard is a bad way to start a meeting.

Industry benchmarks for B2B SaaS suggest roughly 20-25% of qualified pipeline converts to closed-won, but your own historical data from the same export is more useful than any benchmark. The funnel table is how you build that history.

Flagging Stale Deals Before the Monday Call

A deal that hasn't had activity in 30 days and hasn't closed is either dead or needs a push — and in either case the ops team needs to know before the meeting, not during it. Add a Stale_Flag column to Pipeline_Clean:

=IF(
  AND(
    E2<>"Closed Won",
    E2<>"Closed Lost",
    IFERROR(TODAY()-VALUE(G2), 999)>30
  ),
  "STALE",
  ""
)

The IFERROR(TODAY()-VALUE(G2), 999) is doing real work. If Last Activity is blank or arrived as a text string, VALUE() fails, IFERROR returns 999, and the deal gets flagged as stale — which is the right call. A deal with no recorded activity date is effectively stale. Pull the stale count into your summary dashboard with =COUNTIF(Pipeline_Clean!H:H, "STALE") and pair it with a breakdown by owner.

Where Sheets Starts to Buckle

Row Count	Recommended Approach
Under 5,000	ARRAYFORMULA + SUMPRODUCT freely
5,000–20,000	QUERY for aggregations, ARRAYFORMULA for row-level transforms
20,000–50,000	QUERY only for aggregations; minimize volatile functions (TODAY, NOW)
50,000+	Split architecture — Sheets as display layer only

According to Google's Sheets documentation, the hard limit is 10 million cells per spreadsheet. But real-world formula performance degrades well before that. A pipeline sheet with 50,000 rows and 15 formula columns hits recalculation times of 30+ seconds, which breaks the "live dashboard" use case entirely.

The architectural fix: keep the raw export in one spreadsheet, run cleaning and aggregation in a second sheet with IMPORTRANGE pulling only processed summary data, and point the director's dashboard at the summary sheet. Three tabs, one source of truth, load times under 3 seconds.

The Column That Breaks Everything (An Honest Warning)

Here's the failure mode nobody warns about: the CRM admin adds a column to the Salesforce export between Monday and Tuesday. Your IMPORTRANGE range is locked to Raw!A:M. The new column pushes ARR from column D to column E. Every formula referencing column D by position now pulls the wrong field — silently, with no error, just wrong numbers.

The fix is defensive referencing: use MATCH to find the column header position, then INDEX to pull by name rather than by column letter.

=INDEX(Raw!$A:$Z, ROW(), MATCH("ARR", Raw!$1:$1, 0))

This adds formula overhead but survives schema changes, which in a live CRM integration happen more often than anyone admits. As of May 2026, Google Sheets doesn't have native named-column referencing outside of structured table features, so MATCH/INDEX remains the standard approach for schema-resilient trackers.

The Part Nobody Fixes: The Manual Export Step

The biggest operational drag on any pipeline tracker isn't the formulas — it's the Monday morning ritual of downloading a CSV from Salesforce, uploading to Drive, re-pointing the IMPORTRANGE, and hoping nothing shifted. That's 20 minutes every week, and it breaks if anyone is out sick.

ModelMonkey connects directly to Salesforce (read-only) and HubSpot, pulls the export into your Sheets structure on a schedule, and runs the cleaning logic on import. Its SQL engine — which runs queries like SELECT Stage, SUM(ARR) FROM pipeline GROUP BY Stage directly against your sheet data — handles the aggregations that would otherwise require nested QUERY formulas. For sales ops teams running weekly pipeline reviews, that's the one manual step that can actually disappear.

Try ModelMonkey free for 14 days — it works in both Google Sheets and Excel.