By Luis Lopez, AI transportation consultant, CEO of Go Hub.io Holdings Corp and subsidiaries, and host of the Freight Guru Podcast
Most freight analysis still happens in spreadsheets. Shipment history comes out of a TMS as an export, carrier invoices arrive as files, and someone on the pricing or procurement team spends hours cleaning columns before they can answer a simple question like “what did we really pay on this lane last quarter?”
AI spreadsheet assistants change that work. They are built into many spreadsheet tools now, or available as general chat assistants you can paste data into. You describe what you want in plain language, and the assistant writes the formula, builds the pivot, or suggests how to clean the data. For freight analysts who never learned to code, this is one of the most practical uses of AI available today. It also comes with real ways to get the wrong answer quickly.
Start With Cleaning the Shipment Export
Raw exports are rarely ready to analyze. Common problems include:
- City and state in one column, or the same city spelled three different ways
- Dates stored as text, or mixed date formats from different sources
- ZIP codes that lost their leading zeros
- Linehaul, fuel, and accessorials combined in one total, or split inconsistently
- Duplicate rows from rebills, corrections, or split shipments
- Weight in pounds in some rows and kilograms in others
An AI spreadsheet assistant is good at writing the formulas or steps to fix these. Ask it to split a combined origin column into city and state, standardize state abbreviations, convert text dates, or pad ZIP codes back to five digits. Ask it to flag possible duplicates rather than delete them, so you can decide which row is correct.
As an AI transportation consultant, the first thing I look at in any lane analysis is whether the cleaning step was documented. If you cannot explain how the raw file became the clean file, you cannot defend the conclusions. Ask the assistant to list every transformation it suggested, and keep that list with the workbook. The fields that matter most are covered in the top data fields to clean before buying AI.
Pivoting Lanes Without Writing Code
Once data is clean, the useful questions are usually about lanes. Plain-language prompts work well here:
- “Build a pivot of shipment count and total spend by origin state and destination state.”
- “Add a column for cost per mile using the miles column, and show the average by lane.”
- “Show which lanes had the widest range between lowest and highest cost per shipment.”
- “Group shipments by month and show volume by lane so I can see seasonality.”
Define your lane carefully before you pivot. State-to-state lanes hide a lot of variation. Three-digit ZIP or metro-area lanes are often more useful for pricing, but they produce many more lanes with fewer shipments each. Tell the assistant which definition you are using, and ask it to show shipment counts next to every average so you do not over-trust a lane with only a handful of loads.
For a deeper look at what to do with lane results, see freight lane analysis using shipper data.
Getting Formula Help You Understand
Lookup formulas, conditional sums, date math, and nested logic are where many analysts get stuck. An AI assistant can write them, but the goal is a formula you understand, not just one that returns a number.
Useful habits:
- Ask for an explanation. Have the assistant explain each part of the formula in plain words.
- Test on a small sample. Pick five rows where you know the right answer and confirm the formula matches.
- Watch for blanks and errors. Ask how the formula handles empty cells, zero miles, or missing rates. Division by zero and silent blanks distort averages.
- Prefer simple over clever. A two-step calculation in helper columns is easier to audit than one long nested formula.
Checking the AI’s Math
This is the part people skip. AI assistants can produce confident summaries that are wrong. The usual failure points:
- Summarizing without calculating. If you paste data into a chat assistant and ask for totals, it may estimate or misread rows rather than compute. Prefer assistants that actually run formulas or code against the data, and check whether they did.
- Averaging averages. Average cost per mile across lanes is not the same as total cost divided by total miles. Ask which one the assistant used.
- Truncated data. Large files may be partially read. Confirm row counts before and after each step.
- Units. Mixed weight units or currencies will quietly break comparisons.
A simple control: reconcile the grand total. Total spend in your pivot should match total spend in the raw export, minus any rows you deliberately excluded. If the numbers do not tie, stop and find out why before sharing anything. The broader warning signs are covered in how to catch AI hallucinations in freight.
Data Privacy Comes First
Shipment exports often contain customer names, consignee addresses, contract rates, and margins. Before you paste anything into an AI tool, know where that data goes:
- Use the AI features inside tools your company has already approved, where possible.
- Check whether a public chat tool retains or trains on what you upload, and follow your company policy.
- Remove or mask columns you do not need, such as customer names or contact details, before analysis.
- Never paste in banking details, tax identifiers, or login information.
- Treat rate and margin data as confidential, because it usually is under your contracts.
Writing Better Prompts for Spreadsheet Work
Specific prompts get better results. Tell the assistant what each column means, which units are used, how you define a lane, and what output you want. “Analyze this data” will produce generic commentary. “Using the total_cost and miles columns, calculate cost per mile for each origin state to destination state lane with at least ten shipments, and sort by highest average” produces something you can check. More examples are in prompt writing for freight teams.
What This Does Not Replace
An AI spreadsheet assistant speeds up mechanics. It does not know that a lane spiked because of one emergency load, that a carrier changed its fuel method mid-year, or that a customer moved a distribution center. That context comes from the people who run the freight. The best results come from analysts who use AI to get through the cleanup faster and spend the saved time asking better questions about what the numbers mean.
For more conversations on freight and AI, subscribe to the Freight Guru Podcast.
About the author: Luis Lopez is a Miami-based AI transportation consultant and logistics entrepreneur, the CEO of Go Hub.io Holdings Corp and subsidiaries, and host of the Freight Guru Podcast.