How to Use CPM Formula in Google Sheets and Excel Guide

1. Manual CPM calculation in Excel and Google Sheets

Before you automate, it helps to master the basics. CPM (cost per mille) is:

CPM = (Ad Spend / Impressions) * 1000

‍

A. Manual CPM in Excel

1. Open Excel and create a table with headers in row 1:
   • A1: Campaign
   • B1: Channel
   • C1: Ad Spend
   • D1: Impressions
   • E1: CPM
2. Enter example data from your ad platforms in rows 2–n.
3. In E2, enter the CPM formula:
   • =(C2/D2)*1000
4. Use the fill handle to drag the formula down column E for all rows.
5. Format the CPM column:
   • Select E2:E100 (or your range).
   • Right‑click → Format Cells → Number → set decimals to 2.
6. Add a basic summary:
   • Below the table, use =AVERAGE(E2:E100) for average CPM.

Official Excel formula help: https://support.microsoft.com/en-us/office/overview-of-formulas-in-excel-ecfdc708-9162-49e8-b993-c311f47ca173

‍

B. Manual CPM in Google Sheets

1. In Google Sheets, set up similar headers in row 1:
   • A1: Campaign
   • B1: Platform
   • C1: Ad Spend
   • D1: Impressions
   • E1: CPM
2. Paste in performance data from Meta Ads, Google Ads, LinkedIn, etc.
3. In E2, enter:
   • =(C2/D2)*1000
4. Press Enter, then drag the fill handle down to copy the formula.
5. Format CPM nicely:
   • Select E2:E.
   • Format → Number → Custom number format → "$"0.00 if you want dollars.
6. Add quick insights:
   • Use =AVERAGEIF(B2:B, "Facebook", E2:E) to see Facebook CPM.

Formula reference for Sheets: https://support.google.com/docs/answer/3093480

‍

C. Manual sanity checks

• If impressions are zero, CPM will error or be enormous. Wrap your formula:
  • Excel or Sheets: =IF(D2=0, "N/A", (C2/D2)*1000)
• Spot outliers with conditional formatting (very high CPM in red) so your human eye can quickly see bad buys.

Manual methods are perfect for small accounts, but they quickly break once you’re exporting from five ad platforms every week.

‍

2. No-code automation with Sheets and Excel

When copy‑paste starts eating your mornings, use built‑in automation and no‑code tools to keep CPM metrics fresh without an analyst glued to CSVs.

‍

A. Use Google Sheets with connected data

1. If you export to Google Drive (for example, from Looker Studio or a data connector), keep a raw data tab like RAW_META.
2. In a CPM_CALC tab, reference those raw cells:
   • Spend: =RAW_META!C2
   • Impressions: =RAW_META!D2
   • CPM: =IF(D2=0, "N/A", (C2/D2)*1000)
3. Turn the raw sheet into a dynamic range using FILTER or QUERY:
   • Example: =ARRAYFORMULA(IF(RAW_META!D2:D=0, "N/A", (RAW_META!C2:C/RAW_META!D2:D)*1000))
4. Schedule refreshes:
   • If you use a connector (like Google Analytics or BigQuery), set scheduled refresh, then CPM updates automatically.

Docs on ARRAYFORMULA and related functions: https://support.google.com/docs/answer/3093275

‍

B. Excel with Power Query

1. Save your ad platform exports (CSV) to a folder such as /Ad_Exports/.
2. In Excel, go to Data → Get Data → From File → From Folder.
3. Point to your export folder and click Combine & Load.
4. In Power Query, ensure columns include Spend and Impressions; rename if needed.
5. Add a CPM column inside Power Query:
   • Add Column → Custom Column → = [Spend] / [Impressions] * 1000.
6. Close & Load to bring the combined table back to Excel.
7. Click Data → Refresh All whenever you drop new CSVs in the folder; CPM values recompute automatically.

Power Query overview: https://support.microsoft.com/en-us/office/get-started-with-power-query-7104fbee-9e62-4cb9-a02e-5bfb1a6c536a

‍

C. Automate imports with no-code tools

Use tools like Zapier or Make to push ad metrics directly into Google Sheets or Excel Online:

1. Trigger: New row in your ad platform report or daily schedule.
2. Action: Append a row in the Sheet or Excel table with date, campaign, spend, and impressions.
3. Your existing CPM formula column calculates automatically for each new row.

‍

Pros of no-code methods:

• No engineer needed, quick to set up.
• Great for mid‑volume accounts.

‍

Cons:

• Can get messy with many campaigns and files.
• Still fragile if column names change in exports.

‍

3. Scaling CPM with AI agents (Simular)

At some point, even no‑code breaks: you add more platforms, custom reports, currencies, and weekly pivots. This is where an AI computer agent like Sai becomes your operations teammate.

‍

A. Let an AI agent operate Excel and Google Sheets for you

Simular is a computer-use agent that behaves like a power user on your desktop and browser. For CPM reporting, you can:

1. Define the workflow once:
   • Open Meta Ads, Google Ads, LinkedIn, TikTok.
   • Export latest campaign performance.
   • Clean column names and currencies.
   • Open your master Google Sheet or Excel file.
   • Paste or import data into the correct raw tabs.
   • Ensure the CPM formula columns are filled: =(Spend/Impressions)*1000.
   • Refresh pivot tables and charts.
2. Sai then replays this workflow reliably, step by step, every day or week.

Because Simular agents can work across desktop, browser, and cloud tools, they do not depend on brittle APIs. They literally click, type, and drag like your analyst—but at machine speed.

‍

B. Pros and cons of AI-agent automation

‍

Pros:

• Handles thousands to millions of steps without getting tired.
• Works across multiple ad accounts, logins, and 2FA flows.
• Transparent execution: every action is recorded and auditable, so you can see exactly how CPM numbers were produced.
• Easy to integrate into your existing pipelines via webhooks.

‍

Cons:

• Requires a short initial setup to demonstrate the ideal workflow.
• Best suited when you have recurring, stable reporting needs (weekly, monthly, by client).

‍

C. Example: Agency-wide CPM reporting at scale

Imagine a performance agency with 40 clients. Instead of each account manager spending Monday morning exporting reports, you:

1. Create a standard Excel or Google Sheets template with CPM, CPA, and ROAS.
2. Use Sai to:
   • Log into each client’s ad platforms.
   • Download the last 7 or 30 days of data.
   • Normalize it into your template.
   • Verify all CPM columns are populated correctly, flagging any rows where impressions are zero.
3. Have the agent send you a Slack or email summary with the top 10 highest and lowest CPM campaigns.

Result: your team focuses on strategy (fix bad CPMs, test new creatives) while the AI computer agent runs the tedious data choreography across Google Sheets and Excel in the background.