Excel for medical writers on Mac: tracking trials, tables and drafts in Microsoft 365

Most medical writers do not use Excel to model numbers. They use it to track work. A patient safety narrative guide from Quanticate describes Excel as the tool for milestone dates, protocol and subject IDs, and events of interest. The same guide mentions a comprehensive template and a project style guide shared with every writer. That is the job this page covers, from the first row to the final review.

What medical writers track in Excel

A good tracker has one row per deliverable. Each row names the document, the study it belongs to, the subject or event IDs it covers, and its current stage. Stages usually run from outline to draft, QC, client review and final. Keep one idea per cell. Do not pack three notes into one box.

Dates do the most work. Enter due dates as real dates, not as text. Excel sorts real dates in order and counts them with formulas. Text like next Friday sorts to the wrong place and counts as nothing. Pick one date format for the whole column. Use the same format for database lock dates and client deadlines.

The Quanticate guide also lists the hard parts of this work. Writers estimate narrative volume early. Some write before database lock and update later. Teams must keep consistency across several writers. Drafts change when client requirements change. QC timing gets decided under time pressure. A good tracker answers each of these with a column, not with memory.

• Deliverable ID and title, one row each
• Study or protocol name, so filters work across projects
• Subject or event IDs covered by the document
• Milestone dates for outline, draft, QC and final
• Owner, the writer responsible for the next step
• Status, picked from a fixed list you control
• Open client queries and the date each one was raised
• Notes for the next version, kept short

Set up a tracker that several writers can share

Start with a table, not a loose range of cells. A table keeps formulas attached to its columns. It also grows when you add a row at the bottom. Click a cell in your data and press ⌘ T. Excel asks whether the range has headers. Tick the box if it does, then click OK.

Name the table as you create it. Microsoft's October 2026 What's New in Excel post says naming tables at creation is now available on Windows and Mac. Before that, the feature was an Insider feature. Pick a plain name such as Tracker. Skip spaces. Formulas read more clearly with a short name.

The Table Name box sits on the Table Design tab. Click any cell inside the table, then open Table Design. Type the name in the Table Name box and press Return. If the Table Design tab is missing, your cursor is probably outside the table.

Freeze the header row so column names stay on screen. Go to View > Freeze Panes > Freeze Top Row. A long tracker scrolls past its headers, and frozen headers keep each column readable. This matters more when several writers open the same file.

Add a drop-down list to the Status column. Select the column, then go to Data > Data Validation. Under Allow, choose List. In the Source box, type the stages separated by commas. Now nobody types a stage with a spelling mistake, and your counts stay clean.

Add a Notes column at the far right. Keep each note to the next action, such as waiting on a client query. Longer history belongs in the document's own version log, not in one cell of the tracker. Short notes stay readable on a small screen.

Keep the project style guide close to the tracker. The Quanticate guide describes a project-specific style guide shared with all writers. Put a link to it on a second sheet called Style. A tracker that points to the guide answers fewer questions. It does not replace the guide itself.

Keyboard shortcuts and KeyTips in Excel for Mac

KeyTips let you reach ribbon commands with the keyboard. They started in Excel for Mac 16.86 as a feature for Beta Channel users. Microsoft said it would reach all users in the coming months. Check your version under Excel > About Excel before you rely on it.

Turn KeyTips on under Excel > Preferences > Accessibility. Option is the default key. Press Option once and letters appear over the ribbon. Type the letter for the command you need. This feels slow for the first few tries. It gets faster as the letters become familiar.

Expect rough edges. Commenters on Microsoft's KeyTips post report inconsistent behavior. Some report missing letters on dropdown menus. The post itself does not address those reports. If a KeyTip does not appear, use the mouse for that one step and move on.

Formulas for counting narratives by status

Counting is where a tracker earns its place. You want to know how many items sit in each stage. COUNTIFS handles this well. Suppose your table is named Tracker and the stage column is Status. Type =COUNTIFS(Tracker[Status],"QC") in an empty cell. Excel returns the number of rows marked QC.

Count distinct subjects with a formula that returns a list. In Microsoft 365, =COUNTA(UNIQUE(Tracker[Subject ID])) counts unique IDs. UNIQUE spills its results downward. Keep the cells below the formula empty, or it returns a spill error. Check for that error before you trust the count.

Estimating volume early is harder. Count the events of interest in the safety listing for each study. Multiply that total by how long your team usually takes per item. Use your team's own average. Averages borrowed from another project rarely fit your work.

Track days until due with plain subtraction. Add a column named Days Left and type =[@Due]-TODAY() in the first row. Format the column as a number, not a date. Negative results mean the item is late. Highlight those cells with Home > Conditional Formatting > Highlight Cells Rules > Less Than, and set the value to zero.

Here is a worked case. One writer owns the narratives, and a second writer owns the summary. Filter the table by owner, then look at the status counts for each. If one writer has most of the items in draft, you see the bottleneck before database lock.

Read each formula before you copy it down a column. A formula typed by hand can point at a fixed cell address, so the copy in row 40 may still point at row 2. Structured references such as Tracker[Status] avoid that trap. They name the column, not a cell address, so the formula follows the table as it grows.

Pivot tables for status and timing reports

A pivot table turns the tracker into a status report. Click any cell in Tracker. Go to Insert > PivotTable. Drag Status to Rows, Owner to Columns, and Deliverable ID to Values. Excel counts the items in each cell of the grid. The pivot can sit on its own sheet, away from the raw rows.

Refresh the pivot after every edit. Right-click the pivot and choose Refresh, or go to Data > Refresh All. A stale pivot looks correct and is not. That mistake shows up in status meetings, so build the refresh into your routine. For step by step pivot setup, read how to make a pivot table in Excel on Mac.

Power Query row limits in Excel for Mac

Most trackers stay small. Some grow when you import raw listings or safety data with Power Query. Microsoft's specifications page lists a maximum of 1,048,576 rows filled to a worksheet. It also lists 16,384 columns per table. Plan for those ceilings before a big import.

The Query Editor data preview shows up to 3,000 cells. Keep that in mind when you shape large tables. The same page notes about 1GB of data processing for the 32-bit version of Excel. It compares that with the 64-bit version, which uses available virtual memory.

The page does not list Mac-specific differences. It covers Excel for Microsoft 365, 2024, 2021, 2019 and 2016. Check the limits in your own copy before you plan a large import. Do not assume the Windows numbers match your Mac exactly.

Keep raw safety listings out of the tracker. Load them into a separate query or sheet, then bring only summaries into the tracker. The file stays easier to open. Writers scroll less, and the review stays focused on the rows that matter.

Mistakes that break a medical writing tracker

• Sorting one column on its own. Excel moves that column and leaves its neighbors behind. Click one cell inside the table, then sort.
• Typing dates as text. Text does not sort by date and does not count in date formulas. Use real dates in every due column.
• Merging cells in the header or status area. Merged cells break sorting, filtering and formulas. Use Format > Cells > Alignment and pick Center Across Selection instead.
• Hiding rows to remove finished items. Hidden rows still count in COUNTIFS. Filter the table instead, so the status stays visible.
• Keeping two copies of the tracker. One writer edits a local copy while the lead reads the shared file. Keep one shared file and log changes in a notes column.
• Renaming headers without checking formulas. A new header name can break formulas that pointed at the old one. Read the formulas after each rename.

Most of these mistakes show up slowly. A count is off by one, and nobody notices for a week. Check the totals against the raw rows each time you run a report. A quick check beats a corrected status meeting.

Where Lazur fits in this work

Lazur is a voice assistant for the Mac, and it lives in the notch. Hold ⌥ ⌘ to ask about anything on your screen. Lazur answers out loud. 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.

That fits tracker work. You keep your hands on the keyboard and the mouse. Lazur points at the Table Name box, explains a formula, or walks you through a sort. It does not click buttons, edit files or operate other apps for you. You make each change.

Hold fn to dictate clean, formatted text at the cursor. Use it for a status email, a comment in the tracker or a note for the lead writer. Snippets and a personal dictionary help with repeated study names and product terms.

A weekly check routine for the tracker

1. Refresh the pivot with Data > Refresh All, then read the status counts.
2. Filter Status to everything that is not Final, then look at the owners.
3. Click one cell in the table, then sort by Due, earliest first.
4. Check the Days Left column for negative values. Those items are late.
5. Write a short status note. Dictate the changes with fn, then send it from your mail app.

Run the same routine each week. A repeated order catches problems early. Writers and leads learn where to look, and the questions get shorter over time.

Patient data, privacy and AI tools

Trackers often hold details that identify people. Subject IDs, event dates and site names can be sensitive. Check your company's data rules before you put that data in any file, shared drive or AI tool. Your rules come first.

Lazur listens and looks at the screen only while you hold the shortcut. Audio and the screenshot go to AI providers to answer you. Lazur says they are not stored. Content is never used to train AI models.

A practical habit helps. Ask Lazur about formulas and menus while a sample tab is open. Close the subject-level rows before you hold the shortcut. That habit reduces what gets captured. It does not replace your data policy.

What Microsoft 365 costs for Excel on a Mac

Microsoft 365 Personal includes the Excel desktop app, and it can be used on a Mac. Microsoft lists it at $99.99 per year, or $9.99 per month. Microsoft 365 Family is $129.99 per year, or $12.99 per month. Check Microsoft's pricing page before you buy, because prices change.

Ask your IT team which Excel version they support before you buy. A second license can waste money if your company already supplies one. Lazur starts with a 3-day Pro trial, and no credit card is needed. Check pricing for current plans before you decide.

Next steps for medical writing teams

Start with one tracker and one routine. Name the table, add the Status list and freeze the header row. Then add a pivot for the weekly check. Once that works, ask Lazur to explain any formula you do not recognize. You can read more on the Excel for Mac page or download Lazur.