Excel for investor relations officers on Mac: formulas, consensus and voice help in 2026

What an investor relations officer does in Excel

A NIRI job posting for a Director of Investor Relations says investor relations is about 75% of the role. The rest is financial analysis, about 25%. The listed duties include the earnings release, a consensus model, 13-F and shareholder analysis, peer analyses, and the investor CRM. The posting does not mention Excel at all. That tells you Excel is assumed. Teams often skip listing the tools they use every day.

A second posting, for an investor relations associate, lists Excel as a required skill. It sits next to financial modeling and investor relations software. The associate maintains the IR database of shareholder records and tracks stock ownership trends. So the spreadsheet is part of the daily routine. It is not a side tool you open once a quarter.

Build a consensus model in Excel on Mac

Consensus work depends on Excel more than any other IR task. Sell-side analysts send their own models, often as Excel files by email. Q4 Inc, a consensus software vendor, says IR teams receive raw Excel models that way. The same vendor says consensus tracking often relies on manual, ad hoc spreadsheet updates. Treat that as a vendor view, not a measured study. It still explains why these files pile up.

A plain setup works well on a Mac. Keep one tab for raw estimates, with one row per broker update. Keep a second tab for the summary. Each raw row should carry the broker name, the date received, and the metric. The summary then pulls from the raw list with formulas. Nobody retypes numbers from email.

Turn the estimates into a Table

Click inside the raw estimates and press ⌘T. Excel turns the range into a Table with headers and filter buttons. When you paste a new broker row under the last row, the Table grows to include it. Formulas that point at Table columns grow with it. That removes the step most people forget, which is extending the range by hand. Rename the Table in the Table Design tab so the formulas read clearly.

Stale estimates are the usual trap. An estimate from months ago can sit in the list and drag the average down. Filter by date before you average. One pattern works well. Put a cutoff date in a labeled cell, then use AVERAGEIFS. The formula looks like this: =AVERAGEIFS(Estimates[EPS], Estimates[Date], ">="&Cutoff). Change the cutoff cell and the consensus updates.

1. Paste each broker's estimate as a new row. Include the broker, the date and the metric.
2. Check that the Table grew to include the new row.
3. Set the cutoff cell to the date you want the average to start from.
4. Compare the new consensus with the last version you saved.
5. Save a dated copy before any big paste, so you can roll back.

Compare peers and shareholders with Excel on Mac

Peer tables are a grid of companies by metric. Keep the ticker in one column. Put each metric in its own column. Pull peer data with XLOOKUP on the ticker, so new rows fill in without copy and paste. The pattern is =XLOOKUP(Ticker, Peers[Ticker], Peers[Multiple]). Replace the names with the ones in your own sheet.

Shareholder analysis starts with a list of holders and their positions. Most IR teams pull that list from a data vendor or from a summary of 13-F filings. Sort by position size first. Then group by holder type. A PivotTable answers how much stock sits with each holder type, with no extra formulas. Start one from Insert > PivotTable.

Mac limits to plan for before a deadline

Some Excel features work differently on Mac. Power Pivot and the Data Model are not available in Excel for Mac. A Microsoft Q&A community answer from Aug 29, 2025 says so. The same answer says Power Query on Mac can load only to a worksheet table. That answer comes from a community responder, not from Microsoft documentation. Confirm it on your own build before you depend on it.

Most of these gaps matter only for large data pulls. A consensus model and a peer table rarely need them. Plan around the gaps before a deadline, not during one. If a colleague uses Windows, agree on one file format you both can open.

Power Query and data import on Mac

Power Query on Mac can import Excel workbooks, Text and CSV files, XML, JSON, SharePoint Online lists, and OData. It can also refresh those imports. That covers most of what IR receives from a data vendor or a shared drive. Open the Data tab and look at Get Data to start.

Local file import became generally available on Mac in version 16.57, build 22011100. Microsoft's Tech Community post dates that to May 19, 2021. Microsoft 365 subscribers can open the Power Query Editor on version 16.69, build 23010700, or later. Microsoft still says authoring in the editor is not available on Mac, so queries are written with VBA. Check your version under Excel > About Excel.

Excel for Mac release notes that change your workflow

Excel for Mac updates often focus on Copilot. Version 16.112, released Aug 11, 2026, lets Copilot work in cloud-saved workbooks with AutoSave off. Excel asks you to turn AutoSave on, but you can keep working without it. Version 16.113, released Sept 16, 2026, added change-history and chart and PivotTable skills to Copilot.

Version 16.111, from July 14, 2026, changed how a blocked PivotTable behaves. It collapses into one cell that shows a spill error instead of failing outright. That helps when a pivot runs into another range. The latest build in Microsoft's notes is 16.114.3, from Oct 27, 2026. Microsoft lists it as quality and performance updates only.

Excel tips for IR work on Mac

These habits cover most of the daily work. They cost little time, and they prevent most of the rework. Start with the list below, then add the ones that fit your team.

• Keep one folder per quarter. Name each file with the broker and the date received.
• Turn every raw range into a Table with ⌘T, so formulas and filters grow with new rows.
• Use XLOOKUP for peer pulls. Avoid copying values from one tab to another.
• Use Formulas > Trace Precedents to see where a consensus cell gets its inputs.
• Use Formulas > Show Formulas to check every formula on a sheet at once.
• Use Formulas > Error Checking to find error values before a model goes out.
• Protect the summary tab with Review > Protect Sheet, so nobody pastes over a formula.
• Use Data > Data Validation to limit ticker cells to a list, so a typo cannot pull an empty row.

Store dates as real dates, not as text. Text dates sort in the wrong order, and they break date filters. If a date filter returns nothing, the column is probably text. Select the column, set the number format to Date in the Home tab, and retype one value to test it.

Mistakes that break IR spreadsheets

Spreadsheet errors are common, and they are not only a beginner problem. Panko's audits found errors in 83% of operational spreadsheets, a weighted average across nine audits covering 163 spreadsheets. Those audits ran from 1987 to 2008. They cover operational spreadsheets, not IR files. Read the number as a warning, not a forecast for your model.

Where audits reported a cell error rate, it ran from 0.2% to 0.9%. Most audits did not report one. A single bad cell in a consensus summary can still move a headline number. That is why the checks below matter.

• Pasting values over a summary formula, so the next update does not flow through.
• Averaging old estimates without a date filter.
• Deleting a broker row that a formula still points at, which shows a reference error.
• Storing numbers as text, which SUM skips.
• Copying a formula down without checking whether the references moved.

A simple habit helps. After each update, compare the count of brokers in the raw list with the count in the summary. Then compare the consensus with the last saved version. If the change looks odd, trace the precedents before you send anything.

Ask Lazur about Excel while you work

Lazur is a voice assistant for the Mac. Hold Option and Command to ask about anything on screen. Lazur answers out loud. Ask where a button lives, 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 for you.

That helps with the Excel steps you rarely repeat. Think of a PivotTable from a new data pull, or a formula you inherited from a colleague. Paste the formula into a cell, hold the keys, and ask what it does. You get the answer out loud, with the cell still in view.

Lazur listens and looks at the screen only while you hold the shortcut. Audio and the screenshot go to AI providers to answer you, and they are not stored. Content is never used to train AI models. Lazur is not a plugin or add-in for Excel or any other app.

Hold fn to dictate clean, formatted text at the cursor in any app. That fits a note to a CFO or a reply to an analyst. Dictation is included on both paid plans. For Excel itself, read the Excel page to see what Lazur can explain and point at.

Plans and the free trial

Lazur starts with a free trial, and it needs no credit card. The trial starts when you first open the app. Plan prices and included AI actions are on the pricing page. Check there before you pay, because prices can change.

What to do next

Start with one file. Open your current consensus model and check whether the raw estimates sit in a Table. If they do not, select the range and press ⌘T. Point the summary formulas at the Table columns. Then confirm your build under Excel > About Excel.

For the pivot steps, read How to make a pivot table in Excel on Mac. If you want Lazur on your Mac, download it here. Then compare the plans on the pricing page before the trial ends.