How to Analyze a CRM Export in Excel

Your CRM's built-in reports answer the common questions but not the specific one you have right now. The usual move is to export the deals to Excel and pivot them yourself. Here is how to do that cleanly — and the dirty-data trap that quietly gives you the wrong number.

The quick version: export the deals view to CSV, press Ctrl+T to make it a Table, and pivot it. The three questions a CRM dashboard usually fumbles are win rate by source, pipeline by stage this month, and deals that have gone quiet. Here's each — and the dirty-data trap that gives you a clean, confident, wrong number.

Get the deals out

In HubSpot, Pipedrive, or Salesforce, open the deals (or opportunities) list and export it to CSV. Make sure the columns include amount, stage, source, owner, create date, and close date — those are what the interesting questions hang on. Open the file in Excel, click inside the data, and press Ctrl+T so it's a real Table. Pivots and formulas built on a Table pick up new rows on their own next time you export.

Win rate by source

Add a helper column Won that returns 1 for won deals and 0 otherwise:

=IF([@Stage]="Closed Won", 1, 0)

Insert a PivotTable with Source in Rows, and two values: a Count of deals and a Sum of Won. Win rate per source is Won ÷ total. Decide deliberately whether still-open deals belong in the denominator — including them understates your rate, excluding them overstates it.

Pipeline by stage, this month

PivotTable with Stage in Rows and Sum of Amount in Values gives you open pipeline by stage. To scope it to deals created this month, drag Create Date to Filters (or group it by month), so you're not mixing this quarter's pipeline with last year's.

Deals that have gone quiet

Add a column for days since the deal last moved:

=TODAY()-[@[Last Activity Date]]

If you don't have a last-activity field, use create date as a rougher proxy. Sort descending, or filter for anything over your threshold (say 30 days), to surface the deals quietly dying in the pipeline.

The trap: dirty categories

This is the one that bites. CRM exports are full of near-duplicate category values — Inbound and inbound  with a trailing space, Referral and Referrals, an owner entered two different ways. A PivotTable treats each as its own group, so your "by source" breakdown quietly splits one real source across two rows and every total looks plausible but is wrong.

Before trusting any pivot, put each category column through a quick check — a PivotTable of just that column, or UNIQUE, to list the distinct values. Fix the near-duplicates with Find & Replace or TRIM first. A tidy-looking total from dirty categories is more dangerous than an obvious error, because nobody double-checks it.

Or ask the export directly

Pivots are the right tool when you'll ask the same question every week. For the one-off question, the Quiriz Excel add-in lets you type it in a cell — =QUIRIZ.ASK("win rate by source for deals created this month", "table") — and get the table back without building anything. The part worth knowing: when you ask about a value that isn't in the export — a source you never track, an owner who's spelled differently — it tells you the value isn't in your data instead of returning a confident zero. On dirty CRM exports, that refusal is the difference between a wrong number and a caught one.

Frequently asked questions