Build a CRM in Google Sheets for Deal Tracking

Start by creating a sheet named Deals. Enter these headers in A10:H10:

Next, enter the following illustrative records in rows 11 through 18. Leave column G for the formulas added later.

The copied template displays a dash for D-105. To reproduce the missing-amount control, select D15 and press Delete so the cell is genuinely blank.

Apply these formats:

• D11:D and G11:G as USD
• E11:E as percentages
• F11:F as dates

If Google Sheets interprets the example dates as text, set the spreadsheet locale under File > Settings > Locale, or enter them with formulas such as =DATE(2026,10,20).

To keep stages and owners consistent, create dropdown lists:

1. Select C11:C.
2. Choose Data > Data validation > Add rule.
3. Select Dropdown.
4. Add Discovery, Qualified, Proposal, and Negotiation.
5. Set invalid data to Reject input.
6. Repeat for H11:H with Ari, Bo, and Chen.

For D11:D, add a custom-formula validation rule:

=AND(ISNUMBER(D11),D11>=0)

For E11:E, use:

=AND(ISNUMBER(E11),E11>=0,E11<=1)

For F11:F, use the is valid date criterion. Google explains these controls in its data-validation guidance.

Together, these rules allow nonnegative numeric amounts and probabilities from 0% through 100%. They make manual entry more consistent, but you’ll still need to review pasted or imported values.

In G11, enter:

=IFERROR(IF(AND(ISNUMBER(D11),D11>=0,ISNUMBER(E11),E11>=0,E11<=1),D11*E11,"Check amount/probability"),"Check amount/probability")

Fill it through G18. The expected results are:

D-105 produces a warning because its amount is missing. D-106 stays in the table but contributes $0 to weighted pipeline because its probability is 0%.

Blank, text, whitespace-only, negative, or error-containing amounts produce the warning. So do blank, nonnumeric, or error-containing probabilities and percentages outside the 0%-100% range.

Some spreadsheet locales require semicolons instead of commas in formulas.

Enter Pipeline amount in A5. Then enter this formula in B5:

=IFERROR(SUM(FILTER(IFERROR(D11:D,0),ISNUMBER(IFERROR(D11:D,"")),IFERROR(D11:D>=0,FALSE))),0)

Enter Weighted pipeline in A6, followed by this formula in B6:

=IFERROR(SUM(FILTER(IFERROR(G11:G,0),ISNUMBER(IFERROR(G11:G,"")))),0)

The expected results are:

• B5: $164,000
• B6: $80,000

The underlying arithmetic is:

Pipeline:
18,000 + 32,000 + 25,000 + 12,000 + 21,000 + 40,000 + 16,000
= 164,000

Weighted pipeline:
3,600 + 17,600 + 20,000 + 3,600 + 0 + 28,000 + 7,200
= 80,000

The row-level IFERROR checks prevent an error in one source cell from causing the entire summary to return $0. Invalid amounts are excluded, while the affected rows remain visible for correction.

Add the monthly close schedule

Set up the schedule with these labels:

• D5: Expected close month
• E5: Scheduled deal amount
• F5: Forecast label

In D6, enter:

=DATE(2026,10,1)

In D7, enter the following formula and fill it through D9:

=EDATE(D6,1)

Format D6:D9 as MMM-YY.

Next, enter this formula in E6 and fill it through E9:

=IFERROR(SUM(FILTER(IFERROR($D$11:$D,0),ISNUMBER(IFERROR($D$11:$D,"")),IFERROR($D$11:$D>=0,FALSE),ISNUMBER(IFERROR($F$11:$F,"")),IFERROR($F$11:$F>=EOMONTH(D6,-1)+1,FALSE),IFERROR($F$11:$F<EOMONTH(D6,0)+1,FALSE))),0)

Enter Unweighted forecast in F6:F9.

This formula includes dates from the first through the last day of each month and excludes the first day of the following month. It also excludes individual rows with blank, nonnumeric, or error-containing amount or date cells.

The scheduled amounts are unweighted deal forecasts, while B6 is the probability-weighted pipeline. Neither measure represents invoiced revenue, collected cash, or guaranteed bookings.

Select A10:H, then choose Data > Create a filter. The filter controls let you inspect deals by:

• Stage
• Owner
• Expected close period
• Upcoming close date

Google’s sorting and filtering documentation also explains filter views. With filter views, different users can save their own views without changing the underlying table.

Now add conditional-formatting rules through Format > Conditional formatting. Apply each rule only to its relevant field so that overlapping row conditions don’t compete for the same fill color.

Use red for invalid inputs, amber for overdue dates, and a separate neutral style for valid zero probabilities. Conditions that apply to the same cell are mutually exclusive. See Google’s conditional-formatting instructions for the setup process.

An overdue close date means the row needs review. It doesn’t prove that the opportunity has stalled. Measuring inactivity would require a separate last-activity field, which this CRM doesn’t include.

Run these checks in a copy. Reset the stated baseline before each independent test:

• With D11 = 18000 and E11 = 20%, G11 should equal $3,600.
• Change E11 to 0%. A recorded Google Sheets execution produced G11 = $0 and B6 = $76,400 without deleting or moving D-101. Restoring E11 to 20% returned B6 to $80,000.
• Clear D15 while keeping E15 = 60%. G15 should display Check amount/probability.
• In an unvalidated test copy, try -10%, 110%, text, a blank, whitespace, and an error value in E11. Reset E11 to 20% between cases. Each invalid value should produce the warning.
• Restore E11 to 20%, then try text, a negative value, and an error value in D11. Each should produce the warning and should not count as valid pipeline.
• Restore D11 = 18000. Set F11 to =DATE(2026,10,31). E6 should remain $50,000. Change F11 to =DATE(2026,11,1); E6 should become $32,000 and E7 should become $55,000. Restore F11 to =DATE(2026,10,20); E6 should return to $50,000 and E7 to $37,000.
• Replace D9 temporarily with =DATE(2027,2,1). With no February deals, E9 should return $0. Restore D9 to =EDATE(D8,1) to return to January.

After restoring the fixture, confirm:

• B5 = $164,000
• B6 = $80,000
• E6:E9 = $50,000, $37,000, $21,000, $56,000

Except for the recorded zero-probability control, these are expected results derived from the stated formulas and synthetic inputs.

In Google Sheets, ModelMonkey can receive the active spreadsheet, sheet, and range context. It can help you read and organize deal data, work with formulas and formatting, analyze the pipeline, and create or adjust charts.

Select the populated range, open the ModelMonkey sidebar, and enter a bounded request such as:

Inspect Deals!A10:H18. Check Amount, Probability, Expected close, and
Weighted amount for missing or invalid inputs. Summarize issues by deal ID
without describing pipeline forecasts as collected cash.

You can also ask for help creating a chart of weighted amount by owner or stage. Before using the result, review the source rows, formulas, and proposed changes. If the workflow presents a preview, inspect it before proceeding. Spreadsheet assistance doesn’t guarantee that every formula or analysis is correct.

For each new deal:

1. Enter its fields in the next unused row.
2. Copy the weighted-amount formula down column G.
3. Confirm that validation and conditional formatting cover the new row.
4. Check that total pipeline, weighted pipeline, and the applicable monthly amount update as expected.

Review open deals regularly and update each deal’s stage, amount, probability, expected close date, and owner. Check warnings and overdue dates. Then reconcile the summary with the source rows before using it for an internal decision.

If inconsistent values accumulate, follow a structured Google Sheets data-cleaning process.