Weighted total (SUMPRODUCT)
=SUMPRODUCT(scores_range, weights_range)
Calculates the weighted score by multiplying each per-criterion score by the corresponding weight and summing. The most useful single formula in an RFP scoring workbook.
Format: Microsoft ExcelFormat Deep Dive
Format Guide / Excel
The RFP Excel Workbook That Scores, Calibrates, and Compares
Excel is the right tool for the evaluation half of an RFP. Multi vendor scoring, weighted rubrics, calibration across evaluators, side by side comparison, and a one page summary are all natural Excel work. This is the tab structure, the formula patterns, and the calibration logic that produces a defensible evaluation.
The Workbook Tab Structure
Seven tabs in a typical RFP scoring workbook. Each tab has a single purpose. Some tabs are shared with vendors (Requirements response template); most are internal to the buyer's evaluation team.
| Tab | Purpose | Shared with |
|---|---|---|
| 1. Cover + Setup | RFP reference, criteria list, criteria weights (sum to 100%), scoring scale (1 to 5) | Internal |
| 2. Requirements | Numbered REQ-IDs, MoSCoW priority, description, response columns (Fully / Partially / Won't / Custom) | Vendor fills response columns |
| 3. Evaluator A | Per-criterion scoring by evaluator A; comments | Internal (evaluator A only) |
| 4. Evaluator B | Per-criterion scoring by evaluator B; comments | Internal (evaluator B only) |
| 5. Evaluator C | Per-criterion scoring by evaluator C; comments | Internal (evaluator C only) |
| 6. Calibration | Average across evaluators; standard deviation flag; calibration notes after discussion | Internal |
| 7. Summary | Final weighted scores per vendor; ranking; cost normalised; recommendation | Internal |
The Formula Patterns That Do the Work
Multi-evaluator average
=AVERAGE(EvalA!B3, EvalB!B3, EvalC!B3)
Average across evaluator tabs for the same cell reference. Replicated down the criteria column on the Calibration tab.
Evaluator disagreement flag
=IF(STDEV.S(EvalA!B3, EvalB!B3, EvalC!B3) > 1.5, "REVIEW", "")
Flags criteria where evaluator scores diverge by more than 1.5 (on a 1 to 5 scale) so the team can discuss and calibrate. Threshold can be tuned to your evaluation tolerance.
Cost normalisation
=MIN(prices_range) / vendor_price
Converts cost into a 0-to-1 score where the lowest-cost vendor gets 1.0 and others get a proportional fraction. Multiplies by the cost-criterion weight in the weighted total.
Conditional formatting (winner highlight)
Top-rank value gets green background; ties get amber; bottom-rank gets light red
Visual aid on the Summary tab so reviewers can spot ranking at a glance. Manage in the Conditional Formatting dialog, not via formulas in cells.
VLOOKUP / XLOOKUP across tabs
=XLOOKUP(REQ-FNC-014, requirements_index, response_column)
Pulls vendor response for a specific requirement from the requirements tab. Useful in the Summary tab for surfacing critical-requirement responses without copy-paste.
Microsoft's Excel function reference is at support.microsoft.com Excel functions. For a more advanced cost-estimation framework with Excel patterns, see the GAO Cost Estimating and Assessment Guide which has worked examples in its appendices.
The Calibration Step
The calibration step is where the evaluation team meets to reconcile divergent scores. Without calibration, the average score can hide a real disagreement (e.g., two evaluators scored 5 and one scored 1, producing an average of 3.7 that no one believes). The calibration workflow:
- Independent scoring first. Each evaluator scores in their own tab without seeing others' scores. The independence prevents anchoring on the first opinion shared.
- Calibration meeting. Team meets with the Calibration tab open. The standard deviation flag highlights criteria where evaluators diverged by more than the threshold. Discuss the divergent items; understand why scores diverged.
- Update scores or notes. Evaluators may update their score after hearing the discussion (because they missed something in the proposal, or because their initial interpretation was off). The update is logged in the Calibration tab notes column so the audit trail captures the reasoning.
- Recompute summary. The Summary tab recomputes automatically once individual evaluator scores update. The final weighted score reflects the calibrated team view, not the raw arithmetic average.
For the upstream evaluation framework see the RFP evaluation criteria page and the broader RFP authoring guide.
Frequently Asked Questions
Q.When does Excel work better than Word for an RFP?+
A.Excel beats Word for the evaluation-scoring half of the RFP: weighted rubric, multi-evaluator scoring, calibration, vendor comparison. Word still works better for the narrative parts (project overview, scope, terms). The hybrid pattern is Word for the published RFP, Excel for the evaluation workbook used internally to score and compare proposals.
Q.Should I share the Excel scoring workbook with vendors?+
A.Share the requirements matrix tab so vendors can fill in their response, fully meets / partially meets / will not meet / custom. Do not share the scoring tabs that contain weights, evaluator scoring rules, and calibration logic. Vendors can see what they are being evaluated on (the criteria in the RFP) but not how the buyer's evaluation team is doing the scoring.
Q.What formulas should the scoring workbook use?+
A.SUMPRODUCT is the workhorse for weighted scoring: SUMPRODUCT(scores_range, weights_range) produces the weighted total in one cell. AVERAGE for multi-evaluator scores. STDEV.S to flag evaluator-disagreement outliers above a threshold (typically 1.5 standard deviations). VLOOKUP / XLOOKUP to pull vendor data across tabs. Conditional formatting on the summary tab to colour-code the winner / runner-up / disqualified.
Q.How should multi-evaluator scoring work in Excel?+
A.Separate tab per evaluator, each protected so evaluators only edit their own. Summary tab averages across evaluators with the calibration step. Conditional formatting flags any criterion where evaluator scores diverge by more than 1.5 points (on a 1-5 scale) so the evaluation team can discuss and calibrate. Some procurement teams use a master Excel file with one tab per evaluator; others use individual files merged after scoring.
Q.Can I do RFP scoring in Google Sheets instead?+
A.Yes, with the same tab structure. Google Sheets has stronger real-time collaboration (multiple evaluators in the same file simultaneously); Excel has stronger advanced statistical functions and PivotTables. For most RFP scoring, either tool works well. Google Sheets is the better default if your team already operates in Google Workspace; Excel is better if your team needs the advanced analytics or works in a Microsoft-only environment.
Q.Is there a published RFP Excel scoring template?+
A.Many procurement bodies publish examples. The GAO Cost Estimating and Assessment Guide includes Excel examples of cost-estimation models. NIGP and ISM publish member-only templates. State procurement offices (e.g., California DGS, Texas DIR) often publish public-domain Excel evaluation templates. Build your own from the tab structure described in this page; published templates are often calibrated to a specific procurement type that may not match yours.