Sync an Excel Model With Deal Data Safely

Start with the method that matches your reporting frequency and source-system requirements.

Microsoft documents imports from tables, workbooks, CSV files, and other supported sources through Power Query.

For a recurring CSV process, export the complete current deal list to a controlled location. Then, in Excel, select Data → Get Data → From File → From Text/CSV, review the preview, and load the data into a separate staging table.

Using a complete replacement extract makes missing deal IDs visible. By contrast, appending extracts without deletion logic can leave obsolete or duplicate deals in the model. For more information about workbook connections, see Excel IMPORTRANGE equivalents.

At minimum, include these fields:

• Deal ID: stable, unique text identifier
• Client: customer or prospect
• Stage: controlled pipeline status
• Amount: numeric deal value in a defined currency
• Probability: decimal from 0 to 1
• Expected close: valid date
• Owner: responsible person or team

Map each field by header name, not column position. Imported records should also remain separate from model calculations so a refresh doesn’t overwrite formulas.

Before accepting an update, confirm that IDs are present and unique. Amounts should be numeric and nonnegative, probabilities should range from 0 to 1, and expected-close values should be valid dates.

The Deals sheet uses A11:H18 for synthetic deal records. Columns D, E, and F hold deal values, probabilities, and expected close dates. Column G is reserved for calculated weighted amounts, while column H contains the owner.

Enter these headers in A10:H10:

Enter these illustrative records in A11:H18. Leave the Weighted amount cells in column G blank for now; place each owner in column H.

Actual Google Sheets screenshot of the restored synthetic example.

In G11, enter the weighted-amount formula and fill it through G18:

=D11*E11

For D-101, the calculation is $18,000 × 20% = $3,600.

Next, calculate total unweighted pipeline in B5:

=SUM(D11:D18)

Expected result: $164,000.

Calculate weighted pipeline in B6:

=SUM(G11:G18)

Expected result: $80,000.

A zero amount or zero probability produces a zero weighted amount. However, blank, nonnumeric, or out-of-range inputs require review and shouldn’t be treated as valid zeros.

Enter the month-start dates in D6:D9:

D6: =DATE(2026,10,1)
D7: =DATE(2026,11,1)
D8: =DATE(2026,12,1)
D9: =DATE(2027,1,1)

In E6, enter this formula and fill it through E9:

=SUMIFS($D$11:$D$18,$F$11:$F$18,">="&D6,$F$11:$F$18,"<"&DATE(YEAR(D6),MONTH(D6)+1,1))

The expected scheduled amounts are:

Together, E6:E9 total $164,000, which reconciles to B5 because every deal falls within those months. The total doesn’t equal the $80,000 in B6: the monthly schedule uses full deal amounts, while B6 applies probabilities.

The formula includes the first day of each month and excludes the first day of the next month. A month without qualifying dates returns zero. Locales configured for semicolons may require semicolon argument separators.

Change E11 from 0.2 to 0.

The recorded Google Sheets execution produced:

• G11: $0
• B6: $76,400

D-101 remains in row 11. Its $18,000 value stays in B5, as well as October’s scheduled amount in E6. Only its weighted contribution changes.

Restore E11 to 0.2. The weighted pipeline in B6 returns to $80,000.

This execution evidence covers only the synthetic Google Sheets calculation. It doesn’t verify Excel execution, Power Query refreshes, or a vendor integration.

Before reporting refreshed results, confirm:

• The refresh completed without credential, path, missing-column, or type errors.
• Source and staging row counts agree.
• Deal IDs are populated and unique.
• New and missing IDs have explanations.
• Changes to stages, amounts, probabilities, dates, and owners are expected.
• B5 ties to the imported amount column.
• B6 equals the sum of G11:G18.
• Monthly amounts reconcile to the applicable deal rows.

Remember that the formulas count every row, including duplicate IDs. They also don’t detect malformed values automatically.

ModelMonkey can inspect workbook context in Excel or Google Sheets and help analyze formulas, find data issues, organize ranges, or propose spreadsheet changes. For this workbook, a relevant request is:

Check Deals!A11:H18 for blank or duplicate deal IDs, probabilities outside 0-1, invalid dates, and formula errors. Then reconcile B5 and B6.

Review the identified issues, proposed changes, and control totals before using the results. To reproduce the checks, copy the Google Sheets template or download the Excel workbook.