Build a CRM Sales Pipeline in Google Sheets (2026)

This article covers each piece, including where the formulas break at scale and what to swap in when they do.

The CRM Sales Pipeline Data Structure That Doesn't Break

The biggest mistake is building formulas directly on top of your CRM export. HubSpot, Salesforce, and most CRMs export a CSV that looks clean until you open it: close dates in 3 different formats, stage names with trailing spaces, deal values stored as "$12,000.00" text strings, and rep names that don't match your internal list because someone changed their display name in the CRM last week.

Your raw data sheet should have these columns:

At 1,500-4,000 deal rows per year (typical for a 10-20 rep team), this sheet stays fast. Above 8,000 rows - common if you're importing multi-year history or merging CRM instances after an acquisition - filter down before running any cross-sheet lookups.

Parse close dates defensively. If your export has "2024-01-15", "1/15/24", and "15 Jan 2024" in the same column (it will), wrap any date logic with IFERROR:

=IFERROR(DATEVALUE(TEXT(A2,"YYYY-MM-DD")), IFERROR(DATEVALUE(A2), ""))

This doesn't fix everything, but it stops one malformed row from killing a formula running across 3,000 rows.

Strip currency formatting from the Amount column with a helper column ("Clean Amount"):

=IFERROR(VALUE(REGEXREPLACE(B2,"[$,]","")),0)

Do this in a helper column rather than overwriting the raw data. When the CRM re-exports next week with a slightly different format, you change one formula, not 40.

Stage-Weighted CRM Pipeline Value

Stage weighting is where most pipeline trackers fall apart. The common mistake: hardcoding probabilities directly in the SUMIF formula. When sales ops decides "Proposal" should move from 40% to 50%, you're hunting through 8 formulas across 3 sheets.

Instead, maintain a separate stage-weight table on a Config sheet:

Then calculate weighted pipeline value with SUMPRODUCT:

=SUMPRODUCT(
  (Data!D2:D5000<>"Closed Won")*(Data!D2:D5000<>"Closed Lost"),
  IFERROR(VLOOKUP(Data!D2:D5000, Config!$A$2:$B$9, 2, FALSE), 0),
  IFERROR(Data!E2:E5000*1, 0)
)

This runs 3-5x faster than nested SUMIF chains at the same row count, and degrades gracefully. If a stage name doesn't match the config table, IFERROR substitutes 0 instead of throwing a #N/A that poisons the whole sum.

For per-rep weighted pipeline, QUERY handles 8,000+ rows cleanly where ARRAYFORMULA-based SUMIF alternatives start choking above 50,000 cells touched:

=QUERY(Data!A:F,
  "SELECT C, SUM(E) WHERE D<>'Closed Lost' GROUP BY C ORDER BY SUM(E) DESC",
  1)

Stale Deal Detection

A pipeline that includes deals nobody has touched in 45 days is a fantasy pipeline. About 40% of pipeline inaccuracy at most ops teams comes from deals that should have been marked lost weeks ago but weren't because nobody had a flag in front of them.

Flag stale deals with a helper column on your data sheet:

=IF(
  AND(
    D2<>"Closed Won",
    D2<>"Closed Lost",
    IFERROR(TODAY()-DATEVALUE(G2), 999)>45
  ),
  "STALE",
  ""
)

Column G is Last Activity Date. IFERROR substitutes 999 days for blank activity dates, so deals with no logged activity always flag as stale. That's intentional - a deal with no activity date is the same problem as a deal with a 200-day-old activity date.

Conditional formatting on this column (red fill for "STALE") turns the data sheet into a working review tool rather than a passive record.

Build a CRM Sales Pipeline Dashboard Tab

The dashboard tab is what your sales director actually looks at. It should answer 3 questions without any drilling: what's the total weighted pipeline this quarter, how is it distributed by stage, and which rep owns the most stale deals.

Weighted pipeline by stage:

=QUERY(Data!A:H,
  "SELECT D, SUM(E), COUNT(A)
   WHERE D<>'Closed Won' AND D<>'Closed Lost'
   GROUP BY D
   ORDER BY SUM(E) DESC",
  1)

Label the output columns Stage, Total Amount, Deal Count. At 5,000 rows this runs in under 2 seconds. Above 12,000 rows, pre-filter the source data by fiscal year before the QUERY runs.

Weekly pipeline trend: Pull close dates into ISO weeks using =ISOWEEKNUM(close_date) and group by week in a second QUERY. If close dates are messy (they will be), pre-clean them in a helper column using the DATEVALUE wrapper from above before you reference them here.

Top 5 reps by weighted pipeline:

=QUERY(Data!A:H,
  "SELECT C, SUM(E*F) WHERE D<>'Closed Lost' GROUP BY C ORDER BY SUM(E*F) DESC LIMIT 5",
  0)

Column F is your probability column. If your CRM doesn't export probability, do a VLOOKUP to your stage-weight config table in a helper column first, then reference that column in the QUERY.

Stale deal count by rep:

=COUNTIFS(Data!C:C, B2, Data!H:H, "STALE")

Where H is your stale flag column. Run this down your rep list in column B. At 5,000 rows it's instant.

The dashboard itself should fit 1 screen with no scrolling. Four summary numbers at the top (total weighted pipeline, open deal count, deals closing this month, stale count), then 2 tables below. If your director is scrolling, you've got too many metrics.

Where ModelMonkey Fits

The piece that breaks most often isn't the formula - it's the schema. Your CRM adds a new stage, renames a field, or starts exporting probability as a decimal instead of a percentage, and suddenly the QUERY on your dashboard returns 0 rows or a blank table.

ModelMonkey can rewrite those QUERY formulas in plain English when the schema shifts. Describe the new column name or the stage rename, and it adjusts the formula in place rather than you hunting through 6 sheets to find every reference. When your export quietly swaps "Proposal Sent" for "Proposal" and your weighted pipeline drops to $0 at 8am on a Monday, that's the difference between a 10-minute fix and a 2-hour archaeology session. Try it free for 14 days.