Marketers · problem and fix
How to make a PivotTable from messy data in Excel on Mac, step by step
To make a PivotTable from messy data in Excel on Mac, clean the export first, turn it into one table, and then go to Insert > PivotTable. Most failed pivots break at the cleaning step, not the pivot step. This guide covers cleaning, building, fixing wrong totals, and refreshing the pivot each week.
The short answer
- Delete title rows, total rows, and blank rows so one header row sits at the top.
- Press ⌘T to make the cleaned range an Excel table, then go to Insert > PivotTable.
- Set Values to Sum, and check one total against the ad platform before you share it.
- Refresh the pivot after each new export, and keep the raw file untouched.
What building pivottables from exported campaign data costs you
Hours each week
Treasure Data's 2022 survey puts senior marketers at 14.5 hours a week managing customer data collection. Eighteen percent spend more than 20 hours. That covers customer data broadly, not only campaign pivots. Use it as a rough signal, then count your own hours for a week.
Wrong numbers in a shared deck
Panko's audit summary reports important errors in 91% of 55 spreadsheets across five core field audits. Those audits covered operational models, and the samples are small. The lesson still holds. A pivot inherits every mistake in the table under it.
Rebuilding from scratch each week
When the export layout shifts, a pivot built on a fixed range can quietly miss rows. The old total still looks plausible, so nobody checks. You find out only when someone asks why spend moved in a week that looked flat.
Row and cell limits
An Excel worksheet holds 1,048,576 rows. Google Ads upload spreadsheets cap at 1 million rows and 50MB. Google Sheets files cap at 20 million cells or 100MB. A large export can hit a limit before the pivot is done, so you split the data and merge results by hand.
Software needed for Power Query
Power Query repeats your cleaning steps on every refresh, which saves real time. On Excel for Mac it needs Microsoft 365 for Mac. Microsoft lists Microsoft 365 Personal at $9.99 a month or $99.99 a year, and that plan includes Excel. Check Microsoft's plan page for current prices.
Why it happens
Exports are built for reading
Ad platforms format exports for people, not for analysis tools. You get title rows, summary rows at the bottom, currency symbols, and dates in a format that varies by report. The file looks tidy on screen. A pivot reads each row literally, so every one of those details counts.
Labels drift across exports
The same campaign shows up as Spring_Sale, spring sale, and Spring Sale. The pivot treats them as three rows. Naming changes in the ad account, a new tracking template, or a copy and paste from a brief can all cause it. Each drift splits a campaign's totals in two.
Numbers stored as text
Exports often store numbers as text, especially when they carry a currency symbol or a thousands separator. Sum ignores text values. The pivot may then show a count of rows where you expected spend. The fix is in the source column, not in the pivot.
Manual joins across files
Many marketers combine two exports by pasting one under the other. Header rows repeat in the middle. Dates land in two formats. Blank rows separate the blocks. Microsoft's data rules exist because each of these breaks a pivot in a different way.
Human error in spreadsheets
Panko's development experiments report a cell error rate of 3.9% across 697 spreadsheets. When individuals worked alone, the share of spreadsheets with errors ranged from 18% to 86% across studies. These are lab tasks, so treat them as a warning, not a forecast for your report.
A pivot built on a fixed range
A pivot points at the cells you selected when you built it. Add next week's rows below the old ones, and the pivot stays blind to them until you change its source or use a table. Many stale totals come from this one habit, not from bad data.
How to fix it
These work with or without any app.
Delete everything outside the data
Keep one header row at the top. Delete report titles, date ranges, and filter notes above it. Delete total and summary rows at the bottom, and remove blank rows inside the block. Google Ads says you need not delete totals for an upload file. That guidance covers uploads, not pivots. In a pivot, a total row is just another row, so remove it.
Give each column one plain header
Use one short header per column, with no line breaks and no merged cells. Drop units from the header when every value uses the same currency. Two columns with the same header, such as two Date columns, confuse the field list. Rename one of them before you build anything.
Turn text numbers into real numbers
Select the column. If a green triangle appears in a cell corner, click it and choose Convert to Number. For a large column, strip currency symbols and thousands separators with Find and Replace first, then convert. Compare one total with the platform before you move on.
Map messy labels to clean ones
Put the messy names in one column. Put the clean name next to each one on a second tab. Look up the clean name for every row, then pivot on that column. The setup takes time once. Each later export takes minutes instead of an afternoon.
Make the data an Excel table with ⌘T
Click one cell inside the cleaned data and press ⌘T. Check that My table has headers is ticked. The table gets a name, and new rows join it when you refresh. Microsoft calls tables ideal sources for this reason.
Build the pivot on a new sheet
Select a cell in the table, choose Insert > PivotTable, and pick New Worksheet in the Create PivotTable dialog. A separate sheet means the pivot cannot overwrite the export, and you can delete and rebuild it without losing your data.
Set Values to Sum
Right-click a value in the pivot, choose Field Settings, then set Summarize by to Sum. Do this for spend and conversions separately, because each value field has its own setting. Then check one campaign against the platform's own report for the same dates.
Refresh after each export
Paste the new export into the same table, then right-click inside the pivot and choose Refresh. If the pivot shows last week's total, check the table range before anything else. Compare totals with last week and note any new label that did not match.
Use Numbers or Google Sheets without Excel
In Numbers, use Organize > Create Pivot Table with a source that has no merged cells across adjacent rows. In Google Sheets, select the clean data and use Insert > Pivot table. Both need the same clean source you would build in Excel, so cleaning comes first in every tool.
Most pivot problems start before you click Insert. The export looks tidy in the ad platform, then it lands in Excel with title rows, total rows, numbers stored as text, and labels that do not match. A PivotTable reads every row as data. So a messy source gives you a neat table with wrong totals. That is the worst kind of error, because nothing looks broken.
91%
Share of spreadsheets with important errors in Panko's five core field audits (55 sheets)
Panko: Audits of Operational Spreadsheets3.9%
Cell error rate across 697 spreadsheets in Panko's development experiments
Panko: Spreadsheet Development Error Experiments14.5 hours
Average weekly time senior marketers spent managing customer data collection, 2022 vendor survey
Treasure Data press release: data efficiency survey1,048,576
Rows on one Excel worksheet, per Microsoft's specifications and limits
Microsoft Support: Excel specifications and limitsWhy campaign exports break a PivotTable
Ad platforms build exports for people to read, not for a pivot to parse. A report often opens with a title and a date range. Totals sit at the bottom. Money columns may carry a currency symbol. None of this looks wrong on screen. All of it matters to a PivotTable.
- A title or date range sits above the header row.
- A total or average row sits at the bottom of the data.
- Numbers align to the left, which often means they are stored as text.
- One campaign appears under two or three spellings.
- Two exports sit in one sheet, with a second header row in the middle.
Clean the export before you build the pivot
Cleaning is the slow part, and most of the accuracy depends on it. Work on a copy. Keep the raw export in its own file or tab, so you can check your work against it later. Microsoft's data rules are simple. Use one header row, no blank rows or columns, no merged cells, and one data type per column.
- Delete report titles, date ranges, and filter notes above the header row.
- Delete total and average rows at the bottom of the data.
- Remove blank rows and blank columns inside the block.
- Unmerge cells, then give each column one short, unique header.
- Convert text numbers into real numbers, then check one total against the platform.
- Replace spelling variants of each campaign name with one clean label.
- Turn every date into a real date in one format.
Build the PivotTable on Mac, step by step
Start by making the range an Excel table. Click one cell inside the cleaned data and press ⌘T. Check the range in the dialog, and confirm that My table has headers is ticked. Next, click one cell in the table and choose Insert > PivotTable. The Create PivotTable dialog confirms the range and where the pivot goes. Pick New Worksheet, then click OK. Keep the pivot off the data sheet, so a stray paste cannot overwrite the export.
| Field | Drop it into | What you get |
|---|---|---|
| Campaign | Rows | One line per campaign |
| Date or week | Columns | One column per period, if you need a trend |
| Spend | Values | Total spend for each campaign |
| Conversions | Values | Total conversions for each campaign |
| Channel | Filters | Limit the view to one channel at a time |
Numbers and Google Sheets on Mac
If Excel is not your tool, Numbers and Google Sheets build the same kind of pivot. In Numbers, select the table, then choose Organize > Create Pivot Table. You can also click the Pivot Table toolbar button. Pick a new or current sheet, then add fields. A new pivot stays blank until you add fields. Numbers needs no cells merged across adjacent rows, and you must refresh the pivot after you change the source. In Google Sheets, select the data and go to Insert > Pivot table.
Fix numbers that look wrong in the pivot
Most wrong totals come from three causes. The pivot counts instead of summing. A text number got skipped. Or a total row got added into the sum. Check the value field first. Right-click the value in the pivot, choose Field Settings, then set Summarize by to Sum. On Mac the menu says Field Settings, not Value Field Settings.
Excel defaults a field to Count when the column holds text. That is why a spend column can show a row count. Fix the source column first, then change the summary. Changing the summary alone will not fix text numbers.
Mistakes people make with campaign pivots
- Building the pivot on a range that stops one row short of the newest data.
- Leaving a total row in, then wondering why spend looks doubled.
- Sorting one column alone, which splits campaign names from their numbers.
- Mixing real dates and text dates in one column, so grouping by date fails.
- Trusting a pivot that nobody refreshed after the last export.
Keep the pivot current when the export changes
A pivot is only as current as its source. Paste the new export into the same table, then right-click inside the pivot and choose Refresh. Microsoft calls Excel tables ideal PivotTable sources, because new rows and columns are picked up on refresh. If the total does not move, check the table range first. A range typed by hand is a common reason a pivot goes stale.
Limits and versions that change the plan
Size matters first. An Excel worksheet allows 1,048,576 rows and 16,384 columns. A Google Ads upload spreadsheet must have 1 million rows or fewer and be 50MB or less. Uploads time out after 2 hours of processing. A Google Sheets file caps at 20 million cells or 100MB. If your export sits near a limit, filter it or split it by month, then combine the results by hand.
Version matters second. Power Query, which replays your cleaning steps each time you refresh, works on Excel for Mac only with Microsoft 365 for Mac. Microsoft says there is no Power Query support in Excel 2016 and Excel 2019 for Mac. Recommended PivotTables also needs a Microsoft 365 subscription. The manual steps in this guide work in older versions too, though you repeat the cleaning by hand.
What this costs you in time and accuracy
A 2022 survey from Treasure Data, a data vendor, found senior marketers spend 14.5 hours a week on average managing customer data collection. Eighteen percent spend over 20 hours. The survey covers customer data more broadly than campaign reports. Treat it as a rough signal, not a measure of your own week.
Accuracy is the bigger cost. Raymond Panko runs a spreadsheet risk research site. His summary of field audits reports important errors in 91% of the 55 spreadsheets in five core studies. His development experiments found a cell error rate of 3.9% across 697 spreadsheets. Those studies looked at operational models and lab tasks, not campaign pivots. Use them as a warning, not a forecast.
A short check before you share the pivot
- Totals match the ad platform for the same date range.
- Row count in the table matches the export, minus any totals you removed.
- No campaign appears twice under two labels.
- The date range sits in the file name or on the first tab.
- A colleague can find the source tab in under a minute.
Where Lazur helps, and where it does not
Lazur is a voice assistant for your Mac. Hold ⌥ ⌘ to ask about anything on your screen, and Lazur answers out loud. Ask where a command is, and Lazur draws a circle and a label around it. Ask how to do something, and Lazur guides you one click at a time, waiting for each click. It does not click buttons or edit files for you. The cleaning and the building stay with you.
The useful jobs here are finding commands, explaining a number you do not trust, and walking through a dialog you have not used before. Hold fn to dictate clean text at the cursor, for example a short note for your manager. Lazur runs on macOS only, and Windows is planned, not available.
Next steps for the same campaign data
Once the pivot is right, share it where your team reads it. For the sheet side of this work, see Google Sheets for marketers and how to count unique values in Google Sheets. Lazur plans and pricing are on the pricing page. Start with the Day 1 checklist, and build the pivot only after the totals match.
Faster with Lazur
Where Lazur helps with building pivottables from exported campaign data
Hold ⌥ ⌘ and say what you want. Lazur sees your Google Sheets window. Lazur draws a circle and a label around the button or menu you need.
- 1
Hold ⌥⌘ and talk
- 2
Lazur draws a circle and a label around the button or menu you need.
- 3
You stay in Google Sheets. Nothing to copy into a chat window.
More things to say in Google Sheets
- “Where is PivotTable in Excel?”
- “Walk me through the Create PivotTable dialog, one click at a time.”
- “Why does this column show Count instead of Sum?”
- “Dictate a short summary of this pivot for my manager.”
- “Draft a label mapping list from the names on my screen.”
- “Remind me Monday morning to refresh the campaign pivot.”
Free for 3 days, then $18/month. Download for Mac
You say“Where is PivotTable in Excel on my Mac?”
Lazur · points at itLazur circles the command and labels it on screen. You still click it, and Excel does the rest.
What marketers say to Lazur for this
Each one is a single hold of ⌥ ⌘.
“Where is PivotTable in Excel?”
Lazur draws a circle and a label around the command on your screen, so you find Insert > PivotTable without guessing.
Points at it
“Walk me through the Create PivotTable dialog, one click at a time.”
Lazur guides you one click at a time and waits for each click. That helps the first time you see the dialog.
Walks you through it
“Why does this column show Count instead of Sum?”
Hold ⌥ ⌘ and ask about the screen. Lazur answers out loud, for example why a spend column shows a row count.
Explains what's on screen
“Dictate a short summary of this pivot for my manager.”
Hold fn to dictate clean text at the cursor in any app. Useful for a short note after you refresh.
Types what you say
“Draft a label mapping list from the names on my screen.”
Lazur drafts and types text, such as a mapping list or a summary email. Check every number against the source yourself.
Drafts it
“Remind me Monday morning to refresh the campaign pivot.”
Lazur sets an Apple Reminders reminder or a timer for the weekly refresh, so the routine does not depend on memory.
Handles the small stuff
“Look up each ad platform's export format rules and summarize them.”
Research runs in Lazur's own Chrome tabs and comes back with a summary and a report. Useful when export rules change.
Researches in the background
A plan you can start this week
- Day 1
Pull and label the export
Download the report as CSV or XLSX from the ad platform. Save the raw file, then write one line of notes with the date range and filters. Check that the first and last dates match the range you asked for.
- Day 2
Clean a working copy
Copy the raw data into a working tab. Delete title rows, totals, and blanks, fix the headers, and turn text numbers into numbers. Standardize campaign names with a mapping tab, then check one total against the platform.
- Day 3
Build the pivot
Make the range an Excel table with ⌘T. Insert the PivotTable on a new sheet, add campaign to Rows, and set spend and conversions to Sum. Compare one campaign with the platform before you share anything.
- Every week
Refresh and compare
Paste the new export into the table, right-click the pivot, and choose Refresh. Compare totals with last week. Add any new label that did not match your mapping list.
- Monthly
Review the setup
Remove dead campaign names from the mapping list. Check your row count against the limits in this guide. Save a finished copy of the workbook with the date in the file name.
Questions people ask
Can I make a PivotTable from a CSV export in Excel on Mac?
Yes. Open the CSV in Excel for Mac, check that dates and numbers came through as the right type, then clean the range as described above. CSV files do not keep formatting, so check every numeric column.
Why does my PivotTable miss this week's rows?
The pivot points at the range you selected when you built it. Convert the cleaned data to an Excel table, paste new rows into it, then refresh the pivot. Microsoft says tables pick up new rows on refresh.
Why does a spend column show a count?
Excel counts text values instead of summing them. Fix the column so every value is a number, then right-click the value, choose Field Settings, and set Summarize by to Sum.
Is Recommended PivotTables available on Mac?
It is limited to Microsoft 365 subscribers. It builds several layouts on a new sheet, which is useful for a quick look. It does not replace the cleaning step, so check the source before you trust a layout.
Who does the cleaning and the building when Lazur is open?
You do. Lazur does not click buttons or edit files. It can circle a command, guide you one click at a time, and explain a number out loud. The cleaning and the building stay with you.
Is my campaign data sent to an AI provider?
Lazur sends the audio and the screenshot to AI providers to answer, and they are not stored. Content is never used to train AI models. If the export holds client or customer data, follow your employer's rules before you use any tool.
Does this guide work for Excel on Windows?
The menu paths here are for Excel on Mac. Windows labels differ, and Microsoft's page notes that Mac calls the setting Field Settings. Lazur runs on macOS only, and Windows is planned, not available.
Where do the row limits come from?
Microsoft's Excel specifications list 1,048,576 rows per worksheet. Google Ads and Google Drive publish their own limits, and those pages are listed in the sources for this guide.
Sources
- Create a PivotTable to analyze worksheet data · Microsoft Support · checked 2026-11-08
- Excel specifications and limits · Microsoft Support · checked 2026-11-08
- About Power Query in Excel · Microsoft Support · checked 2026-11-08
- Create a pivot table in Numbers on Mac · Apple Support · checked 2026-11-08
- Format spreadsheets - Google Ads Help · Google Ads Help · checked 2026-11-08
- File size and limits for Google Sheets · Google Drive Help · checked 2026-11-08
- Spreadsheet Development Error Experiments · Raymond Panko · checked 2026-11-08
- Audits of Operational Spreadsheets · Raymond Panko · checked 2026-11-08
- Treasure Data press release: data efficiency survey · Treasure Data · checked 2026-11-08
- Microsoft 365: Compare all products · Microsoft · checked 2026-11-08
Fix building pivottables from exported campaign data with Lazur, free for 3 days
Hold a key and ask. Lazur sees your screen, points at the button and talks you through it, in any Mac app. No credit card.