Excel for researchers on Mac: cleaning, charting and checking data in 2026

Most researchers do not open Excel because they enjoy spreadsheets. A survey export, an instrument log or a lab results file arrived as a spreadsheet, and now someone has to make it usable. This page covers that job. It is written for people who use a Mac every day and need Excel for research without a data science course behind them.

What researchers actually do in Excel on Mac

The daily work is smaller than the word data suggests. You rename columns, drop test rows, fix dates and merge two exports that almost match. Then you summarize, chart and check the numbers before they reach a draft. Most of that work is reshaping, not math. The ThoughtSpot survey of 1,001 office workers in Australia, the UK and the US found the same pattern. Those people were not researchers, but the habits look familiar.

Two numbers from the Acuity Training survey frame the rest of this page. People keep about 2.6 spreadsheets open at once, and they spend about 38% of their working time in one. Both are averages from office workers, not from academic labs. I found no Mac-specific survey of researchers, so treat these as signals, not benchmarks.

A clean research workbook usually has four kinds of sheet. Keep them in one file, so the chain of work is easy to follow.

• Raw: the export exactly as the instrument or survey tool wrote it. Never edit this sheet.
• Clean: a copy with fixed headers, set data types and flagged exclusions.
• Analysis: pivot tables, summary formulas and a few helper columns.
• Output: the tables and charts you paste into Word, Pages or slides.

Set up Excel on Mac once, so every new workbook starts right

You can make Excel start every new workbook from a template you control. Microsoft's support page describes the method. Open a workbook with your headers, styles and any standard named ranges. Then choose File > Save as Template, name the file Book and save it as an .xltx file. Use .xltm if the template holds macros.

The template folder sits inside your Library. Finder hides the Library by default. Press ⇧⌘G in Finder, paste the path and go there. Then save the template in the Start-up folder for Excel.

1. Set up a workbook with the headers and styles you reuse.
2. Choose File > Save as Template.
3. Name the file Book, then save it as .xltx, or .xltm if it has macros.
4. Save it to ~/Library/Group Containers/UBF8T346G9.Office/User Content/Start-up/Excel.
5. Quit Excel, reopen it and check that a new workbook uses your template.

The same start-up settings sit in one place. Open Excel > Preferences > Authoring > General. There you set Sheets in new workbook, plus the default font and size. The default body font is 12-point. Changes apply only to workbooks you create after you reopen Excel. Set them before a project starts, not halfway through one.

Clean raw data without ruining the original

Cleaning is where most research workbooks go wrong. The usual mistake is editing the raw export in place. Once you save over it, you cannot show what the instrument recorded. Keep the raw sheet untouched. Build the clean sheet next to it, with formulas that point back to the raw values.

Excel makes one mistake that causes trouble in almost every study. A participant ID with a leading zero loses the zero when the file opens. Excel treats the column as a number, and numbers do not keep leading zeros. Your match against the consent log then fails for every affected ID, and the error looks like missing consent.

Run Remove Duplicates only after you decide what counts as a duplicate. Two rows can share an ID and still hold different responses. Keep both, or flag the second one and filter it out. Write the rule in your methods, because a reviewer will ask about it.

A worked example: reaction times across conditions

Here is a full pass on one clean sheet. The raw sheet holds one row per trial. Columns hold the participant ID, the condition, the trial order, the reaction time in milliseconds and a flag for dropped trials. Filter out dropped trials in the clean sheet, never in the raw one.

Select the clean range and press ⌘T to turn it into a table. Give the table a short name, such as Trials. Then choose Insert > PivotTable. Put condition in Rows and reaction time in Values. Change the value setting from Sum to Average. Add a Count of reaction time beside it, so you can see how many trials sit under each mean.

Write the same mean as a formula, so the paper and the workbook agree. In a cell away from the pivot, use =AVERAGEIFS(Trials[ReactionTime],Trials[Condition],B2), where B2 holds a condition label. If the two numbers differ, check the Filters area of the pivot first. A filter left switched on is the most common cause.

Last, write the method down while you still remember the rule. Record the filter, the number of dropped trials and the formula you used. Hold fn and dictate it at the cursor. Speaking the note is often faster than typing it after the analysis is done.

Pivot tables and named tables for summaries

A pivot table answers most first questions without a single formula. You can follow the full steps in how to make a pivot table in Excel on Mac. The short version is simple. Select your clean range or table, choose Insert > PivotTable, and drag fields into Rows, Columns and Values.

Microsoft's October 2026 update made Name Tables available on Mac and Windows. When you name a table, your formulas read in plain words. A formula like =AVERAGE(Trials[ReactionTime]) keeps working when you insert a row. A plain cell range can drift when rows move. Name the table before you write the first formula that uses it.

Check formulas before you trust a number

Errors in research spreadsheets rarely look like errors. They look like plausible numbers. Start with Show Formulas. Press ⌃` to see every formula on the sheet at once, then press it again to hide them. Scan for ranges that stop one row short or run into a total row.

• A #DIV/0 error means you divided by zero or by an empty range. Check the range before you change the formula.
• #N/A in a lookup means the value is not in the lookup range. Spaces and leading zeros are the usual causes.
• AVERAGE skips blank cells but counts zeros. In most studies, a blank and a zero mean different things.
• Select a formula cell and press ⌘[ to select the cells it reads from. Use it when a result looks wrong.

IFERROR can hide the problems you need to see. Wrap a formula in it only after you know why the error appears. Hold ⌥⌘ and ask about the error cell. Lazur reads the formula and points at the range it uses, which is usually faster than hunting through the formula bar.

Charts for a paper, a poster or a slide

Charts are where many people lose time. The ThoughtSpot survey found 87% of office workers build graphs and charts, but only 44% find it easy. The same survey says a single chart can take an average user 20 minutes or more. Knowing the usual steps makes the job shorter.

Start with the question, then pick the chart type. Use Insert > Recommended Charts to see Excel's options for your data. Column charts compare groups. Line charts show change over time. Skip 3D effects, because they distort bar lengths. Add axis titles with units, and check the chart at the size it will print.

To use a chart in Word or Pages, select the chart, copy it, then choose Edit > Paste Special and paste it as a picture. A picture stays fixed when the workbook changes. Note the workbook name and sheet name in your figure notes, so you can rebuild the chart later.

Limits you will hit with large research files

Excel for Mac suits most lab and survey files. It still has hard limits. Each worksheet holds 1,048,576 rows and 16,384 columns. A single cell holds up to 32,767 characters. Microsoft lists no hard file size limit in 64-bit environments. That does not mean a very large workbook stays fast.

Open-text answers hit the cell limit more often than you expect. A long free-text response can run past 32,767 characters. Scan the longest responses in your export. If the cap appears, keep the full text in a separate file and link it by participant ID.

When a dataset comes close to the row limit, move the raw data into a database or a statistics package. Keep Excel for summary tables and final checks. If your data lives in Numbers, you can export it to Excel first.

Where Lazur helps in Excel, and where it does not

Lazur is a voice assistant for the Mac. It lives in the notch, and you hold a key to talk to it. Hold ⌥⌘ to ask about anything on screen. Lazur answers out loud. It does not click buttons, edit your workbook or run formulas for you. It is not a plugin or add-in for Excel.

• Ask where a command is, and Lazur draws a circle and a label around it on screen.
• Ask how to do a task, and Lazur guides you one click at a time, waiting for each click.
• Hold fn to dictate a methods note, a figure caption or a comment at the cursor.
• Ask for a background search, and the work runs in Lazur's own Chrome tabs. It comes back with a summary and a report.
• Say a reminder out loud, and Lazur adds it to Apple Reminders.

Privacy matters with research data. Lazur listens and looks at the screen only while you hold the shortcut. Audio and the screenshot go to AI providers to answer, and they are not stored. Content is never used to train AI models. Still, check your ethics approval before you show identifiable participant data on screen, even for a moment.

Sharing a research workbook with your lab

Research spreadsheets rarely stay with one person. A CSCW '25 study interviewed 31 professional spreadsheet users about sharing their expertise. The authors found that people hold back. They weigh the timing of an interruption, and they worry that colleagues will dismiss spreadsheet knowledge. The authors link those barriers to feature-rich software that is easy to start with and hard to master over time.

Make your workbook easier to hand over. Add a Notes sheet as the first tab. List the purpose of each sheet, the exclusion rules and who changed what. A short README saves a colleague a lot of guesswork. It also gives a reviewer a clear path back to your numbers.

What changed on Mac in October 2026

Microsoft's October 2026 post says Name Tables is now available on Mac and Windows, after it was an Insider feature the month before. The same post says the clean data skill and skill builder in Copilot in Excel are rolling out to general availability on all platforms, Mac included. The skill recorder remains Insiders-only on Mac.

Feature timing differs by build. Before you plan a workflow around a new feature, choose Excel > About Excel and compare your version with Microsoft's release notes. If a feature you read about is missing, that is usually the reason.

Mistakes researchers make in Excel on Mac

• Editing the raw sheet. Keep it untouched, and build every clean step on a second sheet.
• Reporting a mean without its count. Place a Count next to every average.
• Letting Excel guess data types. Set IDs to Text, and check dates after every import.
• Merging cells in a data table. Merged cells break sorting, filtering and pivot tables.
• Forgetting that preference changes wait until Excel restarts. Reopen Excel before you test a new template.
• Hiding errors with IFERROR before you know what caused them.

Next steps for your first clean workbook

Pick one data set you know well. Copy the raw sheet, run the table fixes, then build one pivot table you can explain to a colleague. Ask Lazur to point at each field as you add it, and to explain any formula you do not recognize. For the basics of the app, read the Excel page. The download page has the installer, and the pricing page covers the free trial and plans. The researcher overview lists other tools for your kind of work.