# Excel for data analysts on Mac: cleaning, pivots, and Power Query in Microsoft 365

> Excel for data analysts on Mac: the steps for cleaning data, PivotTables, and Power Query, plus how Lazur helps you find buttons in Excel.

URL: https://www.lazur.app/for/data-analysts/excel
Updated: 2026-11-06

Excel for data analysts on Mac comes down to three jobs: cleaning raw data, building PivotTables, and loading tables with Power Query. This page covers each one in Microsoft 365 for Mac, with the Mac shortcuts, the limits, and a crash report you should check before you start. Lazur can point, explain, and guide you through each step, but you do the clicking.

## Quick answer

- Most analyst time goes to cleaning data. A 2016 survey of about 80 data scientists put it at 60%.
- Two Mac shortcuts to learn first: ⌘⇧R opens the Sort dialog, and ⌥F12 opens the Power Query Editor.
- Check your build under Excel > About Excel. A November 2026 thread reports crashes on some 16.114.x builds during PivotTable changes.
- Lazur on a Mac: hold ⌥⌘ and ask where a button is. It circles the button and explains it. You click it yourself.

## What data analysts do in Excel on Mac all week

Most analyst work in Excel is not fancy. You pull a file, fix it, count things, then send a number that someone trusts. A 2016 survey of about 80 data scientists found that cleaning and organizing data took 60% of their time. The same survey found 76% called data preparation the least enjoyable part of the job. Your week probably looks similar.

On a Mac, that work runs through Microsoft 365 for Mac. The newest build in Microsoft release notes is 16.114.3, released October 27, 2026. Microsoft lists only quality and performance improvements for it. The release notes list no renamed features or menu moves, so the menus you know should still be where you left them.

- **60%** of data scientist time goes to cleaning and organizing data, in a 2016 survey of about 80 data scientists ([source](https://www.forbes.com/sites/gilpress/2016/03/23/data-preparation-most-time-consuming-least-enjoyable-data-science-task-survey-says/))
- **76%** said data preparation was the least enjoyable part of their work, in the same survey ([source](https://www.forbes.com/sites/gilpress/2016/03/23/data-preparation-most-time-consuming-least-enjoyable-data-science-task-survey-says/))
- **1,048,576** rows per worksheet in Excel, with 16,384 columns ([source](https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3))
- **16.114.3** latest Excel for Mac build in Microsoft release notes, released October 27, 2026 ([source](https://learn.microsoft.com/en-us/officeupdates/release-notes-office-for-mac))

## Clean the data before you build anything

Clean first, or every pivot repeats the mistake. The usual problems are numbers stored as text, dates in two formats, stray spaces, and duplicate rows from a bad export. Each one breaks sums and counts without a visible error. A total can look right and still be wrong.

1. Check the header row. Every column needs one clear name, and no merged cells should sit inside the data.
2. Select a numeric column and read the status bar. If Sum is missing, the numbers are probably stored as text.
3. Split mixed dates with Data > Text to Columns. Choose Delimited, then set the date order that matches your source.
4. Trim stray spaces with =TRIM() in a helper column, then paste the results back as values.
5. Remove duplicates with Data > Data Tools > Remove Duplicates, after you confirm which columns define a unique row.
6. Turn the range into a table with ⌘T, so new rows join your formulas and pivots.

Here is a small example. A customer export has some dates written day first and others written year first, in the same column. Excel treats the two groups as text, so a monthly total splits into two groups. Convert both formats to real dates, then group by month. The totals line up again, and the pivot stops lying to you.

![Data analyst reviewing an Excel spreadsheet on a Mac laptop at a desk](https://lfobmih1tpj3sgpo.public.blob.vercel-storage.com/content-images/role-app/data-analysts-excel/excel-for-data-analysts-on-mac-1-d79fcb.jpg)

## Build a PivotTable on Mac step by step

Start with a clean range. Click any cell inside the data, then choose Insert > PivotTable. Excel reads the range for you. Put the PivotTable on a new sheet so your raw data stays untouched. Drag fields into Rows, Columns, Values, and Filters. A text field in Values gives you a count, not a sum, so check the field setting first.

The full walkthrough with screenshots is in [How to make a pivot table in Excel on Mac](https://www.lazur.app/how-to/make-a-pivot-table-in-excel). If you prefer to be walked through it in real time, [Lazur's Excel page](https://www.lazur.app/apps/excel) explains how the guidance works.

A PivotTable can hold up to 256 value fields and 256 report filters. Report filters can run out of memory before they hit that count. If a pivot feels slow, cut the number of filters first and test again.

| Task | Mac shortcut | Note |
| --- | --- | --- |
| Open the Sort dialog | ⌘⇧R | Works for PivotTable sorting |
| Add or remove a filter | ⌘⇧F or ⌃⇧L | Select a cell in the range first |
| Show the filter list for a cell | ⌥↓ | Select the cell, then press the key |
| Open Power Query Editor | ⌥F12 | Use it for loading and cleaning |
| Page Down on a MacBook | fn+↓ | Some keys need fn on the laptop keyboard |

_Shortcuts from Microsoft's Mac keyboard page. If one fails, check Apple's keyboard settings._

## Power Query on Mac: what to expect when loading data

Power Query pulls tables in from files and folders, then records every step. Use it when the same cleaning happens every week. Open the editor with ⌥F12, or go to Data > Get Data. Keep the steps that matter, and delete the rest. A clean recipe beats a fresh manual cleanup each Monday.

Speed is the weak spot. A 2020 Microsoft Q&A post from a Windows user reported about 6 minutes for the first connection to one table in a 25 MB .xlsb workbook. Each transformation step added about 6 more minutes. A Microsoft employee in that thread confirmed a bug where importing one sheet from legacy formats could read every sheet. The fix may not have reached that user's build. Treat those numbers as one Windows report, not a Mac benchmark.

> **Filter early:** Remove unused columns and filter rows near the top of the query. Fewer rows in each step means each step has less work to do.

Load to a table only when you need one. The Data Model has a hard size limit on 32-bit Excel, roughly 500 to 700 MB. 64-bit versions have no hard file size limit beyond memory and system resources. Check which version you run before you load a large file into the Data Model.

## Lookups and formulas that break quietly

Most lookup errors come from the data, not the formula. A trailing space makes a match fail. A number stored as text will not match a real number. Check both sides of the lookup before you blame the formula. The fix is usually one cleaning step.

- Clean the lookup key with =TRIM() or Text to Columns before you match.
- Set the match type on purpose. Exact match is the safe default for IDs and codes.
- Lock the table range with $ signs, so a copied formula still points at the right table.
- Press F2 to see hidden text in a cell, such as a trailing space. On a MacBook, F2 may need fn, depending on your keyboard setting.

Size limits matter when you paste raw logs. A worksheet holds 1,048,576 rows and 16,384 columns. A log with more rows than that will not fit on one sheet. Load it with Power Query or split the file by date. The limit is on Microsoft's specification page, so check it before you plan a big import.

## Which Excel build to run, and the PivotTable crash report

Check your build first. Open Excel > About Excel, then compare the number with Microsoft's release notes. Microsoft lists 16.114.3 (Build 26102720) from October 27, 2026. Its Excel entry reads only quality and performance improvements. Earlier, version 16.111 changed PivotTables so they can collapse into one cell that shows the #SPILL error.

A Microsoft Q&A thread posted November 3, 2026 reports Excel for Mac crashing when a PivotTable is created or its fields change, on 16.114.x builds. Several users say rolling back to 16.113.4 (Build 26100421) fixed it. No Microsoft staff reply appears in the thread, and the accepted answer is labeled as an AI answer. Treat the report as unconfirmed.

> **Save before you rebuild a pivot:** Save a copy of the workbook before you change PivotTable fields on 16.114.x. A crash can cost you changes you have not saved yet.

If you hit the crash, write down the build number, the steps you took, and the file type. Post those details in the Microsoft Q&A thread or to Microsoft support. Clear notes help more than a guess about the cause. They also help your IT team if the problem spreads.

## Where Lazur fits in your Excel workflow

Lazur is a voice assistant for the Mac, and it lives in the notch. Hold ⌥⌘, which is Option plus Command, and ask about anything on screen. Lazur answers out loud. It looks at your screen only while you hold the shortcut.

Two modes matter most in Excel. Ask where something 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, edit files, or operate other apps for you. You stay in control of the workbook.

Lazur is not a plugin or add-in for Excel. It works in any app on your Mac, so the same help works in Excel, Numbers, and your browser. That matters when your data comes from a web dashboard and your cleaning happens in a spreadsheet.

## Dictation for notes, comments, and summaries

Hold fn to dictate clean, formatted text at the cursor in any app. Analysts use it for cell notes, email summaries, and the text above a chart. Snippets, a personal dictionary, and clipboard slots handle the phrases you type again and again. Dictation is included with Lazur.

Use the personal dictionary for names your spellcheck flags. Client names, product codes, and team names are good candidates. Once a name is in the dictionary, dictation spells it the same way every time. That keeps a summary consistent across a month of reports.

## Privacy when Lazur looks at a spreadsheet

Client spreadsheets often hold personal data, so know what leaves your Mac. Lazur listens and looks at the screen only while you hold the shortcut. Audio and the screenshot go to AI providers so Lazur can answer you. They are not stored. Content is never used to train AI models.

Check your company's rules before you ask about a file that holds client or employee data. Some teams block AI tools on confidential files. Your policy comes first. If the rule is unclear, ask your admin before you hold the shortcut.

![Office worker typing on a MacBook while checking numbers in a spreadsheet](https://lfobmih1tpj3sgpo.public.blob.vercel-storage.com/content-images/role-app/data-analysts-excel/excel-for-data-analysts-on-mac-2-e95811.jpg)

## Mac keyboard quirks that waste analyst time

Mac keyboards send function keys as system keys by default. Excel expects F keys for many commands. On a MacBook, Page Down is fn+↓, and many F-key shortcuts need fn. The fix sits in Apple's keyboard settings. Turn on the option that uses all F1, F2 keys as standard function keys.

- Open System Settings > Keyboard and turn on the standard function keys option.
- Check Microsoft's Mac shortcut page before you use a Windows shortcut from memory.
- Some macOS utilities grab shortcuts. Quit them if a shortcut does nothing in Excel.
- Power Pivot has no section on the Mac shortcut page. Use the menus for it, not memorized keys.

## Mistakes data analysts make on Mac

- Building a pivot on a range with blank header cells. Excel names the fields badly, and you rebuild the pivot later.
- Pasting raw data onto the report sheet. Keep raw data on its own tab and never edit it in place.
- Trusting a total without comparing the row count with the source. A filtered view can hide rows.
- Ignoring a #SPILL error after a PivotTable change. Read what the pivot shows now before you copy it anywhere.
- Following a Windows video step by step on a Mac. Menus and shortcuts differ, so match each step to the Mac layout.

## Start from a template when the layout is standard

For a monthly report or a tracker, start from a template. On Mac, go to File > New from Template, search for what you need, then click Create. Microsoft also publishes accessible templates on its support site, which help if a screen reader or larger text matters to you.

Templates save layout time. They do not fix a messy export. Clean the data first, then paste it into the template's input sheet. Keep formulas on the report sheet and inputs on the data sheet, so the template keeps working when the data changes.

## Research and reports while you keep working

Lazur can run research in the background. It works in Lazur's own Chrome tabs, through the Lazur for Chrome extension, then comes back with a summary and a report. Use it to check a vendor's export format, confirm a limit, or look up a formula reference. Ask a specific question. Vague questions get vague reports.

Verify every number before you use it. Open the source page yourself and compare the figure. Lazur saves you the search, not the check. That habit matters most when a figure goes into a report that someone else will rely on.

![Laptop screen showing charts and a data table for an Excel report on Mac](https://lfobmih1tpj3sgpo.public.blob.vercel-storage.com/content-images/role-app/data-analysts-excel/excel-for-data-analysts-on-mac-3-4f6e46.jpg)

## Next steps for this week

Pick one recurring task and time it. Clean it once with Power Query or Text to Columns, then save the steps. Next, build one PivotTable on a new sheet and check its row count against the source. Then ask Lazur for the shortcut you forget most.

Accountants who work in the same files can read [Excel for accountants on Mac](https://www.lazur.app/for/accountants/excel) for closing-month habits. When you are ready, [download Lazur for Mac](https://www.lazur.app/download). Pricing is on the [pricing page](https://www.lazur.app/pricing), and the Pro trial starts when you first open the app.

## With Lazur

Lazur is a voice assistant for the Mac that lives in the notch. Hold ⌥ ⌘ and ask; it sees your screen, answers out loud, points at buttons and walks you through apps. Hold fn to dictate. Free for 3 days, then $18/month: https://www.lazur.app/download

- “Where is Remove Duplicates?”: Lazur circles the Remove Duplicates button on the Data tab and labels it. You click it yourself. (Points at it)
- “Where is Remove Duplicates?”: Lazur circles the button on the Data tab and labels it. You click it yourself.
- “Why does my pivot total come out a cent off?”: Lazur explains stored decimals and rounding, then names the cells and formats to check.
- “Walk me through grouping these dates by month in the PivotTable.”: Lazur guides you one click at a time and waits for each click before the next step.
- “Remind me Monday morning to refresh the customer export.”: Lazur adds an Apple Reminders item for the time you name.
- “Draft a note to the team saying the quarter numbers are final.”: Lazur types a draft you can edit before you send it anywhere.
- “Dictate: the refund rate excludes cancelled orders.”: Hold fn and speak. Lazur types clean, formatted text at the cursor in any app.
- “Research the Microsoft page on Excel row and column limits.”: Lazur runs the search in its own Chrome tabs and returns a summary and a report.

## FAQ

### Does Lazur work inside Excel for Mac as an add-in?

No. Lazur is a voice assistant for the Mac that works across apps. It is not a plugin or add-in for Excel, and it does not operate Excel for you. It points, explains, and guides you one click at a time.

### Does Lazur click buttons or change my workbook?

No. Lazur points at things, explains them, answers out loud, and drafts text. When you ask how to do something, it guides you one click at a time and waits for each click. You do the clicking and the editing.

### Is my spreadsheet data sent to AI providers?

Lazur listens and looks at your screen only while you hold the shortcut. Audio and the screenshot go to AI providers so Lazur can answer you. They are not stored, and content is never used to train AI models. Check your company's rules for client data.

### Which Excel for Mac version should data analysts run right now?

Open Excel > About Excel and compare your build with Microsoft's release notes. The newest listed build is 16.114.3. A November 2026 thread reports PivotTable crashes on 16.114.x, and some users went back to 16.113.4. Microsoft has not confirmed that issue.

### How many rows can Excel for Mac hold?

One worksheet holds 1,048,576 rows and 16,384 columns. A PivotTable can use up to 256 value fields and 256 report filters. For bigger data, load it with Power Query or split the file before you import.

### Why is Power Query slow on my Mac?

Speed depends on file size, the source, and how many steps you add. A 2020 Windows user reported about 6 minutes for the first connection to one table in a 25 MB workbook. Remove unused columns early, and load only the sheets you need.

### Does Lazur work on Windows too?

No. Lazur runs on macOS, on both Apple silicon and Intel Macs. Windows is planned but not available. The Pro trial starts when you first open the app, with no credit card.

## Sources

1. [Release notes for Office for Mac](https://learn.microsoft.com/en-us/officeupdates/release-notes-office-for-mac), Microsoft Learn, checked 2026-11-06
2. [Excel specifications and limits](https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3), Microsoft Support, checked 2026-11-06
3. [Keyboard shortcuts in Excel](https://support.microsoft.com/en-us/accessibility/excel/keyboard-shortcuts-in-excel), Microsoft Support, checked 2026-11-06
4. [Video: Start with an accessible Excel template](https://support.microsoft.com/en-us/office/video-start-with-an-accessible-excel-template-918db751-1585-41c5-9ddf-5d7a0915afaf), Microsoft Support, checked 2026-11-06
5. [Forbes: Data preparation most time-consuming, least enjoyable data science task, survey says](https://www.forbes.com/sites/gilpress/2016/03/23/data-preparation-most-time-consuming-least-enjoyable-data-science-task-survey-says/), Forbes (Gil Press), checked 2026-11-06
6. [Microsoft Q&A: Power Query Super Slow](https://learn.microsoft.com/en-us/answers/questions/167293/power-query-super-slow), Microsoft Q&A, checked 2026-11-06
7. [Microsoft Q&A: Pivot Table causes Excel to crash on macOS](https://learn.microsoft.com/en-us/answers/questions/6059112/pivot-table-causes-excel-to-crash-on-macos), Microsoft Q&A, checked 2026-11-06