Excel for computer systems analysts on Mac: formulas and pivots in Microsoft 365

What computer systems analysts do with Excel

Computer systems analysts help a business decide how its IT systems should work. They weigh options, cost out upgrades, and make sure new systems run. The Bureau of Labor Statistics lists the core duties, and several of them produce tables of numbers.

The BLS page does not mention Excel or spreadsheets. So the link between this job and Excel is our inference, not a BLS claim. Still, the work is full of lists, costs, and test results. That is where a spreadsheet earns its place.

• Cost-benefit analysis: one sheet that compares license, labor, and hosting costs for each option.
• Test tracking: a log of test cases with owners, dates, and pass or fail results.
• Requirements lists: a table of needs, sources, priorities, and current status.
• Training material: a sheet of user roles and the tasks each role must learn.
• System inventory: a list of systems, versions, owners, and the teams that depend on them.

Most of these sheets share three traits. They pull data from other files, they total things, and other people read them. The sections below cover each trait with the Mac steps that matter.

Set up a workbook other people can read

Start with a table. Click one cell inside your data, then press ⌘T. Excel asks for the data range and whether the first row holds headers. Tick the headers box if it does, then click OK. Tables grow when you add rows, and formulas that point at them keep working.

Name the table so your formulas read clearly. Click any cell in the table. Go to Table Design > Table Name, type a short name such as Inventory or TestLog, and press Return. Short names with no spaces work best in formulas.

Build lookups between inventory and cost sheets

A lookup pulls one value from one table into another. Analysts use this to match a system to its owner or its annual cost. In Microsoft 365 for Mac, XLOOKUP is the function to learn first. It reads more clearly than the older INDEX and MATCH pair for most jobs.

Here is the pattern. Your cost table has a System column in column A. You want the owner from the Inventory table. In the owner column, type =XLOOKUP(A2,Inventory[System],Inventory[Owner],"Not listed") and press Return. The first argument is the value to find. The second is where to search. The third is what to return. The fourth is the text shown when nothing matches.

Check your results after each refresh. Count the rows that say Not listed. If that count jumps, the source changed or a name was typed differently. Trailing spaces cause most silent misses. Wrap the lookup value in TRIM when names arrive from email or chat.

When a formula returns an error, select the cell and ask Lazur what it does. Lazur explains each argument out loud. It does not change the formula for you, so you still type the fix.

Model costs without hiding your assumptions

A cost-benefit sheet has three parts: inputs, calculations, and results. Keep inputs on their own tab. Label each input with its unit and its source. A reviewer should be able to change one input and watch the result move.

Name your key inputs. Click a cell, click the Name Box at the left end of the formula bar, type a name such as LicenseCost, and press Return. Then write =LicenseCost*SeatCount instead of =Inputs!$C$4*Inputs!$B$9. The formula reads like a sentence, so the next person can check it fast.

Keep numbers out of formulas. A formula that multiplies by a typed value hides an assumption. Put the value in a labeled cell and point to it. Months per year, tax rate, and hourly rate all belong on the inputs tab.

Cost model mistakes to catch early

• Totals that are typed in by hand and never update.
• Assumptions buried inside formulas.
• Monthly and yearly prices mixed in the same column.
• No owner or date on the inputs tab.
• Results pasted as values, so nobody can trace them back.

Summarize test results with a PivotTable

A PivotTable counts test results by owner, system, or status in a few clicks. Start from your test log table, not a loose range. The table keeps the PivotTable current as you add rows. Here is the sequence to follow.

1. Click any cell in the test log table.
2. Go to Insert > PivotTable and choose New Worksheet.
3. Drag Owner to Rows.
4. Drag Status to Columns.
5. Drag Test ID to Values. Excel counts text fields by default.

Refresh the PivotTable after new rows arrive. Right-click inside it and choose Refresh, or go to Data > Refresh All. A PivotTable that looks stale is usually a refresh problem, not a data problem.

Power Pivot is a different matter. Some third-party sites say it is missing from Excel for Mac. I could not confirm that from Microsoft's own pages during research, so check your copy before you plan a data model around it. Excel > About Excel shows your version.

Spreadsheet errors and how to catch them

A 2024 literature review led by Prof. Pak-Lok Poon reported that 94% of spreadsheets used in business decision-making contain errors. The review summarized earlier research. It did not audit spreadsheets directly. Read the figure as a warning, not as a precise rate.

Build checks into the file. Add a control cell that sums the raw export and a second cell that sums the report. Use a check that returns TRUE when they match. If the check fails, stop before you send anything.

Use Formulas > Trace Precedents to see which cells feed a result. Arrows show the path. Press ⌘` to show formulas in every cell, then look for a row where the pattern breaks. If ⌘` does nothing on your keyboard, use Formulas > Show Formulas.

Ask Lazur for help while you work in Excel

Hold ⌥⌘ and ask about anything on your screen. Lazur sees the screen only while you hold the shortcut. It answers out loud. Ask where a button is, and it draws a circle and a label around it. Ask how to do something, and it guides you one click at a time, waiting for each click.

Hold fn to dictate text at the cursor in any app. Use it for cell notes, log comments, or a short summary of a test run. Dictation supports snippets, a personal dictionary, and clipboard slots, so phrases you repeat are quick to insert.

Research runs in Lazur's own Chrome tabs, using the Lazur for Chrome extension. It comes back with a summary and a report. Use it to collect public vendor pages before you build a cost model. You still check every figure yourself.

Check Mac limits before you plan the workbook

For most daily work, Excel for Mac and Windows share the same core formulas. The gaps show up in data models, add-ins, and newer AI features. Check these points before you build anything that depends on them.

• Get Data sits under Data > Get Data in Microsoft 365 for Mac. Confirm it on your copy before you build an import.
• Power Pivot may not be available on Mac. This is unconfirmed, so verify it first.
• Copilot's skill recorder is still Insider-only on Mac, per Microsoft's October 2026 post. Do not plan around it.
• Microsoft's support pages do not label menu items as Mac-only. Check the ribbon on your own copy when a page and your screen disagree.

Habits that cause most spreadsheet trouble on Mac

Most problems come from habits, not from hard formulas. These four cause the most trouble for systems analysts.

• Saving over the raw export. Copy the export to a new tab first, then work from the copy.
• Sorting one column alone. Use a table, so every column moves together.
• Pasting data with the wrong region settings. Check System Settings > General > Language & Region before you import dates or decimals.
• Sending the file with old tabs and comments still inside. Clean the file before it leaves your Mac.

Keep the workbook useful for the next person

A good analysis workbook survives a handoff. Add a README tab with the purpose, the source files, the owner, and the date of the last refresh. Write it as short lines. Nobody wants a page of prose about a spreadsheet.

Save a dated copy before you change a formula. On Mac, choose File > Save As or press ⇧⌘S. Keep one version for the decision and one for your working draft, so you can always show what changed and why.

Next steps for your Excel workbook

Start with one file. Turn your test log or inventory into a table, add one lookup, and build one PivotTable. Then read the guides that match your next task: how to make a pivot table in Excel, the Excel assistant for Mac, and Excel for data analysts on Mac.

Lazur runs on macOS, on Apple silicon and Intel. Windows is planned, not available. The Pro trial lasts three days, needs no credit card, and starts when you first open the app. Pro is $18 per month, or $15 per month billed annually, with 150 AI actions. See pricing for details.