Excel for procurement specialists on Mac: spend cleanup and pivots in Microsoft 365

Most procurement work in Excel on Mac is one job, repeated. A finance system exports every transaction for a period. You clean the supplier names, sort each line into a category, and summarize the result for a review. Microsoft 365 for Mac covers all of it. The hard part is the structure of the file, not any single feature.

What a procurement workbook looks like on Mac

A workable layout keeps raw data apart from logic. Suplari's spend analysis guide recommends a tab order where corrections happen only in mapping tabs, never in the raw export. Only one tab calculates. The others read from it. That habit alone prevents most of the errors covered later in this guide.

Name each tab with the words your team already uses. A tab called Working makes sense to a buyer. Keep names short, because long names get cut off in the tab bar. Put the export date and the owner of each mapping tab in README, so the next person knows what they are looking at.

Turn the export into an Excel table before any formulas

Start with the table, not the formulas. Click one cell inside the export and press ⌘T. Excel opens the Create Table dialog. Tick My table has headers, then click OK. A table grows when you add rows beneath it, and formulas that point at its columns keep working.

Name the table while you create it. Microsoft's October 2026 update says naming a table at creation now works in Excel for Mac, after a month as an Insider feature. Call the table Spend or Transactions. Then a formula reads =SUM(Spend[Amount]) instead of a fixed range that breaks when the export changes size.

Clean supplier names with TRIM, SUBSTITUTE, and a mapping table

Supplier names cause the most damage. One supplier can show up as Acme Corp, ACME CORP, Acme Corporation, and Acme Corp with a trailing space. Each variant splits the spend. Pivots then show several suppliers where you have one. Clean the name once, then map every variant to a master name.

Start with a helper column in Working. =TRIM(A2) removes extra spaces at both ends and between words. Exports from ERP systems often add non-breaking spaces, which TRIM does not remove. Use =TRIM(SUBSTITUTE(A2,UNICHAR(160)," ")) instead. UNICHAR is the safer choice on Mac, because CHAR maps codes differently in Mac Excel than in Windows.

Next, build the mapping. Put each cleaned variant in Supplier_Map, one row per variant, with the master name beside it. Then pull the master name in Working with XLOOKUP, assuming the mapping is a table named Supplier_Map: =XLOOKUP(B2,Supplier_Map[Variant],Supplier_Map[Master Name],"Unmapped")

The fourth argument is what appears when nothing matches. Unmapped makes new suppliers visible. A blank or a zero hides them inside the totals. XLOOKUP ignores capital letters, so Acme and ACME match. It does not ignore a trailing space, so the trim step still matters.

• Editing the raw export. Corrections vanish when the next export lands. Put them in Supplier_Map instead.
• Duplicate keys in Supplier_Map. Run Data > Remove Duplicates on the variant column before you trust any lookup.
• Using VLOOKUP with a column number. Insert a column and it reads the wrong field. XLOOKUP names the column, so it holds up better.
• Leaving the fallback blank. A missing supplier then looks like zero spend, and the review never sees it.

Classify spend by category with rules you can audit

Category is the second cleanup job. Most teams start with GL codes, then add keyword rules for lines where the code is blank or wrong. A keyword rule can catch IT Services on a line with no code. It can also catch lines it should not, so every rule needs a test.

Suplari's guide says classification can stay under 80% when you rely only on manual rules at category level. That figure comes from one vendor, so treat it as a warning, not a benchmark. Track the share of spend left unclassified each cycle, and show that share on the Dashboard tab.

On Mac, a simple keyword rule looks like this: =IF(ISNUMBER(SEARCH("laptop",C2)),"IT Hardware","Other"). SEARCH ignores capital letters. It also treats question marks and asterisks as wildcards, so a keyword with either one gives odd results. Keep the rules in Category_Map and test each one on real lines.

Watch for substring traps. A keyword such as pay matches payroll, payment, and paying. Put longer, more specific keywords first. Then check the first few matches for each rule before you trust the category column.

Pivot tables and slicers for spend by supplier

Build pivots from the table, never from Raw_Data. Select any cell in Working, then go to Insert > PivotTable. Excel fills the source from the table name. Drag Master Name to Rows, Amount to Values, and Category to Filters. Numeric fields sum by default, so check the grand total against Working.

Add a slicer when a buyer needs to click through categories. With the pivot selected, go to PivotTable Analyze > Insert Slicer and tick Category. Place the slicer on the Dashboard tab, next to the chart it controls.

• Run Data > Refresh All after you paste a new export.
• Compare each pivot's grand total with the total of Working before you share it.
• Name each pivot sheet for the question it answers, such as Spend by Supplier.
• Rebuild a pivot only when the question changes, not when the data does.

Repeat work is the real cost. Suplari lists it as a main pain point. Each refresh repeats the cleanup and the pivot setup. A team that rebuilds by hand every cycle pays for the same work again and again.

A worked example: cleaning one period of spend

Take one period of transactions as a worked example. Paste the export into Raw_Data and leave it alone. Copy the columns you need into Working, convert that copy to a table named Spend, and add the helper columns there. The raw tab stays intact for the audit trail.

Next, scan for unmapped names. Filter Master Name for Unmapped. Add each new variant to Supplier_Map, then refresh the lookup. Run the keyword rules after that, and filter for lines still unclassified. Those lines go to a short list for a buyer to review. They do not go into the pivot as a catch-all.

Last, build the pivots from Spend. Check that the grand total matches the sum of Amount in Spend. If it does, save a dated copy with File > Save As. Next month's cleanup then starts from a clean file, and this period's numbers stay as they were reviewed.

Edge cases that change your totals

• Credit notes and returns arrive as negative amounts. Decide whether to net them against the supplier or report them on their own line.
• One invoice can split across categories. Keep one row per line, not per invoice, or category totals will double count.
• Re-exports can repeat rows. Compare the row count with the previous export before you append anything.
• Parent companies and subsidiaries often appear as separate suppliers. Map them to one master name only when the category team agrees.
• Blank GL codes are not the same as wrong GL codes. Track each one as its own category so both stay visible.
• Currency columns need a rule. Convert before you total, or total each currency on its own line.

Row limits, large exports, and Power Query on Mac

Excel's worksheet grid has 1,048,576 rows and 16,384 columns. A spend export with more lines than that will not fit on one sheet. Microsoft's specification page does not mention Mac, so treat the published limit as the figure to check against your own file. Suplari says enterprises above roughly $1B in spend can run past it.

Power Query handles the bigger jobs. It loads an export, changes column types, removes columns, and appends files from more than one system before the data reaches the grid. Microsoft's Power Query limits page says loaded data fills at most 1,048,576 rows on a worksheet. Old .xls and .xlsb files cap imports at 255 columns. Newer .xlsx files have no stated column limit.

On Mac, look for Data > Get Data. If the ribbon does not show it, check your build under Excel > About Excel and update Microsoft 365 before you give up on the approach.

Keyboard moves that save time on Mac

• ⌘T creates a table from the current range.
• ⌘Z undoes a bad paste, which is the most common fix in cleanup work.
• ⌘↓ jumps to the last filled cell in the current column.
• ⌘⇧↓ selects down to that cell. Then ⌘D fills a formula down the selection.
• ⌘F opens Find, so you can search a supplier name across the workbook.

Symptoms, causes, and fixes in the mapping workbook

The date system problem is the sneaky one. Dates from a file that uses a different date system can land a few years off. They still look like dates, so the error is easy to miss. Check the setting before you trust a date filter on a file from another machine.

Where Lazur fits in this workflow

Lazur is a voice assistant that lives in the notch of your Mac. Hold ⌥⌘ and ask about anything on your screen. It answers out loud. It points at things, but it does not click buttons, edit your workbook, or run other apps for you. You make every change yourself.

Ask where a command 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. That suits a spend workbook, where one wrong click can move a whole column.

Dictation works separately. Hold fn to dictate clean, formatted text at the cursor in any app. Use it for a note to a category manager, a supplier email, or a comment on the Dashboard tab. A personal dictionary helps with supplier names that speech tools often get wrong. Dictation is unlimited on both plans.

Lazur starts with a 3-day Pro trial, and you do not need a credit card. The trial starts when you first open the app. Pro costs $18/month, or $15/month billed annually, and includes 150 AI actions. Power costs $120/month, or $96/month billed annually, and includes 1,000 AI actions. Dictation is unlimited on both. Check pricing for current terms, or download the app to try it on your Mac.

Checks before you send a spend pack

1. Compare every pivot grand total with the total of Spend.
2. Filter Master Name for Unmapped and confirm nothing is left unexplained.
3. Check the largest suppliers for name variants that slipped through.
4. Confirm the date range matches the period in the file name and the email subject.
5. Remove any tab that holds personal data or rates the audience should not see.

Keep a README tab with the export date, the owner of each mapping tab, and a short change log. That log lets a colleague pick up the file without a phone call. It also answers the question nobody asks until a number changes.

What Excel can and cannot tell you about savings

Excel can total spend, show concentration, and flag off-contract buying. It cannot confirm that a saving happened. Suplari says Excel cannot confirm savings were realized. That gap matters when a category manager asks what the team actually saved last period.

Use Opportunities as a list of claims. Add the source line, the owner, and the date each claim was made. Track realized savings in the finance system or a contract register, and link each figure back to its source.

How common is Excel in procurement work?

Excel is still common in this work. A 2019 LevaData study found that half of procurement organizations use legacy tools such as Excel to store and analyze data. The article gives no sample size or method, so read it as a rough signal. A 2022 ThoughtSpot survey found that 92% of office workers must reshape spreadsheet data before it is understandable. The release says that averages about an hour per dataset. That survey covers office workers in general, not procurement.

Most features in this guide exist in both Mac and Windows versions of Microsoft 365. Check any feature you rely on against your own build under Excel > About Excel. Then try the workflow on one period of data before you move a full year into the new layout.