Basic Excel CPM Formula
When people search for how to calculate CPM in Excel, they usually need one clean cell formula that turns spend and impressions into a CPM rate. The math matches the standard definition on our CPM formula page:
In Excel, multiply by 1,000 with *1000 — not with a separate column unless you intentionally structure it that way.
Assume Row 1 is headers and Row 2 is your first campaign:
- B2 = Total Cost (currency)
- C2 = Impressions (whole number)
- D2 = CPM (currency per thousand impressions)
This is the core CPM calculator Excel pattern: divide cost by impressions, then scale to per-thousand pricing.
Quick check: if B2 is $1,500 and C2 is 600,000, D2 returns 2.5 — format D2 as currency to read $2.50 CPM. You can verify the same numbers with our online CPM calculator.
Reverse Formulas for Cost and Impressions
A good calculate CPM in Excel workbook solves all three variables — not only CPM. Use the same layout (B2 = cost, C2 = impressions, D2 = CPM) and pick the formula for the value you are missing.
Total cost from CPM and impressions
Equivalent to: Total Cost = (CPM × Impressions) ÷ 1,000. For a dedicated walkthrough, see calculate cost from CPM.
Impressions from budget and CPM
Equivalent to: Impressions = (Total Cost ÷ CPM) × 1,000. More context and examples: calculate impressions from CPM.
Tip: Do not average CPMs row-by-row to get a portfolio CPM unless every row has equal weight. For blended CPM across campaigns, use total cost and total impressions: =(SUM(B2:B100)/SUM(C2:C100))*1000.
Example Spreadsheet Setup
Below is a minimal CPM calculator Excel template you can copy. Keep one consistent row per campaign; extend formulas down from row 2.
| A | B | C | D | |
|---|---|---|---|---|
| Row 1 | Campaign | Total Cost ($) | Impressions | CPM ($) |
| Row 2 | Spring Display | 1500 | 600000 | =(B2/C2)*1000 |
| Row 3 | Remarketing | 800 | 200000 | =(B3/C3)*1000 |
Format B and D as Currency, C as Number with zero decimals and thousands separators. Name the sheet CPM_Model or similar so exports stay readable for stakeholders.
IFERROR and Formatting Tips
Production sheets should fail gracefully when impressions are missing or zero — otherwise how to calculate CPM in Excel becomes a wall of #DIV/0! errors.
IFERROR wrapper
Returns blank instead of an error when C2 is 0 or empty. Use a visible placeholder like "—" if you prefer.
ROUND for reporting
For client-facing tables, round to two decimals: =IFERROR(ROUND((B2/C2)*1000, 2), ""). Keep unrounded values in a hidden column if you need audit trails.
Formatting checklist
- Currency for cost and CPM columns
- Number with comma separators for impressions
- Freeze header row (View → Freeze Panes) for long campaign lists
- Data validation on impressions (e.g., whole numbers ≥ 0)
Google Sheets Version
Calculate CPM in Excel and calculate CPM in Google Sheets use the same formulas: =(B2/C2)*1000, =(D2*C2)/1000, and =(B2/D2)*1000. Named ranges, IFERROR, and ROUND behave the same way for typical media plans.
Sheets-specific notes:
- Sharing: set view or comment access for stakeholders; protect formula columns if needed.
- Locale: if your sheet uses comma decimal separators, adjust formula syntax (semicolons) per your locale — the underlying CPM math is unchanged.
- Collaboration: version history helps when someone overwrites CPM cells with hard-coded values — restore formulas from history if that happens.
Common Spreadsheet Mistakes
Even strong analysts slip on these when building a CPM calculator Excel file:
1. Forgetting ×1,000
Dividing only B2/C2 yields cost per impression. Your CPM will look hundreds or thousands of times too small. Always multiply by 1,000 for standard CPM.
2. Mixed units (thousands of impressions)
If a column stores impressions in thousands (e.g., 600 meaning 600,000), you must either convert back to raw impressions or change the formula. Mixing units across rows destroys blended CPM.
3. Averaging CPMs instead of weighting
=AVERAGE(D2:D20) is wrong when row spend differs. Weight by cost and impressions with =(SUM(B2:B20)/SUM(C2:C20))*1000.
4. Hard-coding CPM after one export
Pasting values from a platform report is fine for snapshots — but label them as static. Live models should reference cost and impressions so calculate CPM in Excel stays reproducible.
5. Using reach instead of impressions
Reach counts unique users; impressions count all ad views. CPM formulas require impressions, as explained on our CPM formula page.
Related Tools
Use these when you want browser-side checks or deeper reading alongside your spreadsheet:
Instantly calculate CPM, total cost, or impressions — cross-check your Excel model in seconds.
Full explanation of all three CPM formula variations with step-by-step examples.
Focus on reverse math: impressions from budget and CPM.
Estimate total campaign cost from CPM and impression goals.
Publisher-side effective CPM from earnings and impressions.
Compare your spreadsheet outputs to typical platform ranges.
FAQ
Use CPM = (Total Cost ÷ Impressions) × 1,000. With cost in B2 and impressions in C2, enter =(B2/C2)*1000. That is the standard answer for how to calculate CPM in Excel and matches the definition on our CPM formula page.
Create columns for campaign name, total cost, impressions, and CPM. Put =(B2/C2)*1000 in the CPM column and fill down. Add IFERROR for blank or zero impressions, format currency and number columns, and use SUM for totals plus a blended CPM row with =(SUM(B:B)/SUM(C:C))*1000 (adjust ranges).
Yes for standard arithmetic. The same CPM calculator Excel formulas work in Google Sheets: =(B2/C2)*1000 for CPM, =(D2*C2)/1000 for cost, and =(B2/D2)*1000 for impressions. Only locale-specific separators may differ.
Use our focused guides: calculate impressions from CPM and calculate cost from CPM. Both pair well with a spreadsheet model and with the CPM calculator for quick checks.
You likely omitted the *1000 multiplier or divided the wrong fields. Cost per single impression is much smaller than CPM. Re-read the CPM formula section and confirm impressions are full counts, not thousands.