Excel for freight brokers on Mac: loads, margins, and carrier invoices with Lazur

What freight brokers track in Excel on a Mac

Most freight brokers use Excel for four jobs. They track loads from tender to delivery. They work out margin on each load. They match carrier invoices to the agreed rate. They chase money owed to them. Your workbook probably covers all four, split across tabs and exported files.

Excel for Mac can do all four. PivotTables, formulas, dynamic arrays, and XLOOKUP work on both Mac and Windows, according to MacCove's 2026 comparison. The gaps sit in data modeling and add-ins. Most broker sheets never touch either, so those gaps matter less than people fear.

Build the load sheet with one row per load

Your load sheet is the source for everything else. Keep one row per load and one column per fact. Avoid merged cells and subtotal rows inside the data. They break sorting, filters, and pivot tables. A clean sheet also makes the lookups in this guide work the first time.

Use real Excel dates, not text that looks like a date. Text dates sort in the wrong order and break aging reports. Type a date and check that it right-aligns. Text stays left. Dates from a TMS export often arrive as text, so convert them before you build anything on top of them.

Turn the load data into an Excel Table on Mac

An Excel Table grows when you add a row. Formulas and pivot tables that point at it grow too. That fixes most of the complaints about missing new loads. Make the table first, then build everything else on it.

1. Click one cell inside your load data. Keep the data free of blank rows.
2. Press ⌘T. Leave My table has headers ticked, then click OK.
3. Open the Table Design tab and type a clear name in the Table Name box, such as Loads.
4. If you are on a current build, name the table when you create it. Naming tables at creation reached Excel for Mac in October 2026.

Margin formulas you will copy every day

Margin is the first number most brokers check. Put one margin formula in a Table column, and the whole column follows. Excel fills the formula down as you add loads, so you do not copy it by hand.

• Margin dollars: =[@[Customer rate]]-[@[Carrier cost]]
• Margin percent: =IFERROR([@Margin]/[@[Customer rate]],"")
• Total margin for one lane: =SUMIFS(Loads[Margin],Loads[Lane],[@Lane])

Averaging the margin percent column gives a misleading answer. A big load and a small load count the same. Divide total margin dollars by total customer rate instead. That is the weighted margin, and it is the number most owners ask for.

Watch for loads with a customer rate of zero, such as a reroute with no billing. The IFERROR wrapper keeps those rows blank instead of showing a divide error.

Check carrier invoices against the rate confirmation

This is where broker money leaks. Laneproof analyzed 8,600 document sets and found that roughly 1 in 12 carrier invoices has at least one line item that does not match the rate confirmation. That is vendor analysis, so test the rate on your own files before you trust it.

Add a variance column next to carrier cost. Subtract the rate confirmation amount from the invoice amount. Round both sides to cents first. Otherwise tiny cent differences flag every row.

1. Add a column named Invoice variance to the Loads table.
2. Enter =ROUND([@[Invoice amount]],2)-ROUND([@[Rate con amount]],2) in that column.
3. Select the variance column. Go to Home > Conditional Formatting > New Rule.
4. Choose Format only cells with, set the rule to Cell Value, not equal to, enter 0, and pick a red fill.

If the 1 in 12 rate held across a 200 load month, you would see about 17 mismatched invoices to review. Laneproof's own cost example uses 200 loads, so treat that as an illustration, not a forecast.

Pivot tables for lanes, carriers, and receivables

Pivot tables answer the questions your boss asks on Monday. Which lanes make money? Which customers owe the most? Which carriers invoice late? Build one PivotTable per question so each one stays simple.

1. Click any cell in the Loads table.
2. Go to Insert > PivotTable. Excel fills in the table name for you.
3. Drag Lane to Rows, then drag Margin to Values and set it to Sum.
4. For receivables, drag Invoice status to Filters and Customer to Rows.

A pivot built on a Table picks up new loads when you refresh. Use Data > Refresh All after you paste in a new week. If the numbers still look old, the pivot may point at a fixed range. The pivot refresh fix covers that case, and the step by step pivot guide covers the build.

Track receivables and aging without a finance system

Most brokers chase payment from memory. A short aging column fixes that. Calculate days open from the invoice date, then group the days into buckets that match your payment terms. A pivot table can count the loads in each bucket in seconds.

Use a formula like =IF([@Status]="Paid","",TODAY()-[@[Invoice date]]). Paid loads show blank, and open loads show their age in days. Put the result in a column and filter on it.

Only 2% of brokerages have fully automated accounts receivable, according to FreightWaves. Another 43% have partial automation, with many manual steps still left. A clean aging column is a practical step for most desks.

Mac limits to plan around before you build

Excel for Mac skips a few features that Windows brokers rely on. Know them now, so the file you build on your laptop still works in the office.

• Power Pivot is not currently available on Excel for Mac. A 2019 Microsoft answer also says the Data Model option is unavailable there.
• Power Query runs on Mac from version 16.69, with fewer data sources. From Web is not listed on Mac.
• ActiveX controls do not run. Use Form controls for buttons and checkboxes instead.
• Third-party COM and VSTO add-ins are Windows-only, so a Windows rate add-in will not load on your Mac.
• Hard-coded Windows file paths in VBA break on Mac. Use relative paths, or let the user pick the file.
• Python in Excel is available to Business and Enterprise users on the Current Channel from version 16.96.

None of these stops a load sheet, margin math, or a pivot. They stop a fancy data model, a Windows add-in, or a macro that expects a Windows drive letter. Test any macro on a Mac before you share it.

Dates, CSV files, and the 1904 date system

Dates cause more arguments than formulas do. A workbook can use the 1904 date system. If that file moves between Mac and Windows, dates can shift by four years and one day. Check the date system before you send a file to a carrier or customer on Windows.

CSV exports cause the second problem. Accents and carrier names can turn into garbage when a file opens in the wrong encoding. Go to File > Save As, open the format menu, and pick CSV UTF-8 before you save the export.

Keyboard shortcuts that change on Mac

Mac Excel uses ⌘ where Windows uses Ctrl for most shortcuts. A few pairs split, and some keys need setup. These are the ones brokers reach for most.

• ⌘T turns the selected data into an Excel Table.
• Control and the semicolon key enter today's date. Command and the semicolon key enter the current time, according to MacCove.
• ⌥ Return starts a new line inside a cell, which helps when you note a carrier's pickup instructions.
• On a MacBook, function keys need Fn or the Function Keys setting in System Settings.
• Turn on KeyTips in Excel > Preferences > Accessibility and set the activation key to Option. KeyTips are off by default.

In MacCove's tests on version 16.114.1, Alt-style ribbon sequences did not finish from the keyboard, even with KeyTips on. Use the mouse for ribbon commands, and keep your shortcuts to the ⌘ set.

Time savings brokers can measure

Brokers ask whether fixing the spreadsheet is worth the afternoon. The strongest numbers come from vendors. They are self-reported, and they describe automation tools, not Excel. Still, they show how big the gap is between re-keying and automated entry.

Laneproof uses a cost model you can copy. It assumes about 6 minutes of TMS entry per load. At 200 loads, that is 20 hours a month. At the BLS truck transportation average of $31.94 an hour, that time costs about $638.80 a month. Laneproof calls the 6 minutes its own assumption, not a measured figure.

Run the same math on your own desk. Count loads per month, minutes per load, and your loaded hourly cost. Then decide how much a cleaner sheet is worth to you.

Where Lazur fits in your Excel workflow

Lazur is a voice assistant for the Mac. Hold ⌥ ⌘ to ask about anything on screen, and Lazur answers out loud. Ask where a button is, and Lazur draws a circle and a label around it. Ask how to do something, and it guides you one click at a time.

Lazur does not click buttons, edit files, or run the workbook for you. You make each change. Hold fn to dictate notes, such as a carrier call summary, straight into the cell you are in. The Excel assistant page lists what it covers in Excel.

Mistakes brokers make in Excel

• Merged cells in the load table, which break sorting and filters.
• Dates stored as text, which sort in the wrong order.
• Averaging margin percent instead of dividing total margin by total rate.
• A pivot pointing at a fixed range that stops at last week. The pivot refresh fix covers it.
• A lookup returning #N/A because the key has a trailing space. The VLOOKUP #N/A fix steps cover that case.
• Saving an export without CSV UTF-8, then wondering why carrier names look broken.

Most of these come from building too fast. Clean the table first, then add formulas and pivots.

What to do next with this workbook

1. Build the Loads table with the columns from the table above.
2. Add the margin dollars and margin percent columns as Table formulas.
3. Add a variance column and highlight every nonzero difference.
4. Make one PivotTable by lane and one by invoice status.
5. Hold ⌥ ⌘ when a formula breaks and ask Lazur what it sees on screen.

When you want voice help on your Mac, download Lazur. The free trial needs no credit card. Plans are on the pricing page.