Excel PivotTables for WordPress Lead Data

PivotTables turn a raw list of form submissions into answers without a single formula. With 400M+ paid Microsoft 365 commercial seats in play (Microsoft FY2024 (2024)), most teams already own Excel, so the analysis layer costs nothing extra.

A PivotTable groups, counts, and summarizes thousands of rows in seconds. Ask "how many leads came from each campaign last week?" and drag two fields. No SUMIFS, no manual filtering, no copy-paste.

The catch is data flow. PivotTables are only as fresh as the table beneath them. If you export a CSV every Monday, your pivots are stale by Tuesday. That's the problem real-time delivery solves, and it's why the structure of your source table matters more than the pivot itself.

Structure decides whether pivots work at all. PivotTables require a clean, flat table: one header row, one record per row, no blank columns, and consistent data types. Spreadsheet research found up to ~88% of audited sheets contained errors (Panko (2016)), and most trace back to messy structure.

When SheetLink delivers to an Excel Online table, it writes into a real Table object (Insert > Table), not loose cells. That single choice fixes most pivot headaches because the table auto-expands as rows arrive.

Keep one field per column. Map your form fields to columns like submitted_at, source, campaign, form_name, and email. Avoid combining "campaign + medium" into one cell. A pivot can always concatenate later, but it can never reliably split a merged value back apart.

Building the first pivot takes under a minute on a well-structured table. Click any cell in the table, choose Insert > PivotTable, and Excel detects the full table range automatically because SheetLink wrote into a named Table object that grows with each row.

For leads by source: drag utm_source to Rows and drag email (or any always-present field) to Values, set to Count. You instantly see how many leads each channel produced.

To break it down by campaign within each source, drag utm_campaign beneath utm_source in Rows. Add submitted_at to Columns and right-click to Group by Weeks for a leads-by-week-by-source matrix. The same three-field pattern answers most marketing questions. Our dashboards guide shows the equivalent build in Sheets.

PivotTables cache their data, so new rows do not appear until you refresh. This is the single most misunderstood behavior. Even though SheetLink delivers each lead in real time, the pivot still shows yesterday's numbers until it re-reads the table.

Refreshing is one click: right-click the PivotTable and choose Refresh, or press Alt+F5. To refresh every pivot in the workbook, use Data > Refresh All.

For a hands-off setup, open PivotTable Options and tick "Refresh data when opening the file." Now the pivot updates automatically each time someone opens the workbook. Because the underlying table already holds every submission the instant it arrives, that one refresh reflects truly current data, not an export from last week. Real-time delivery plus auto-refresh is what keeps the dashboard honest.

Most pivot failures come from data, not the pivot. Spreadsheet studies put the average cell error rate near 3.9% (Panko (2008)), and dirty source data corrupts every summary built on top of it.

Three problems cause the bulk of trouble:

Text dates. If submitted_at arrives as text, Excel cannot Group by Week or Month. Format the column as Date once and the grouping menu unlocks.

Inconsistent labels. "Facebook", "facebook", and "FB" pivot as three separate sources. Normalize UTM values at the form level so the channel groups cleanly.

Blank rows or merged cells. These break the table range and confuse the pivot. Because SheetLink writes into a structured Excel Table, the range stays intact automatically, which removes the most common cause on its own.