How to Use Excel in Logistics: Practical Tools for Tracking, Routing, and Inventory

Most logistics teams still run on Excel. Not because they don’t have fancy software, but because Excel is fast, flexible, and always there. You don’t need a $50,000 system to track shipments, manage warehouse stock, or plan delivery routes. If you’re using paper, whiteboards, or disconnected spreadsheets, you’re losing time-and money. Excel, when used right, can handle 80% of daily logistics tasks without training or IT support.

Track Inventory Like a Pro

Inventory miscounts cost logistics companies an average of 5% in lost sales and overtime. In a medium-sized warehouse, that’s hundreds of thousands a year. Excel fixes this with simple, repeatable systems.

Start with one sheet: Inventory Log. Columns should include:

• Item ID (unique code, like WH-2026-001)
• Product name
• Location (aisle, shelf, bin)
• Current stock
• Reorder level
• Last received date
• Supplier

Use conditional formatting to highlight items below reorder level. Select the whole Current Stock column, go to Home > Conditional Formatting > New Rule > Format only cells that contain, set it to Cell Value less than the value in the Reorder Level column. Turn it red. Now you see what needs restocking at a glance.

Link this to your receiving sheet. When a new delivery comes in, just enter the Item ID and quantity. Use =SUMIF() to auto-add it to your inventory. No manual totals. No mistakes.

Optimize Delivery Routes Without GPS Software

Drivers waste 18% of their time on bad routing. That’s 90 minutes a day per driver. Multiply that by 10 drivers? That’s 15 hours wasted weekly. Excel can cut that in half.

Make a Route Planner sheet. List all delivery stops in one column. In the next, add the address. Then use Google Maps to get approximate driving times between each pair. Enter those as travel times in a grid.

Now use the Traveling Salesman Problem hack: sort stops by geographic cluster. Group deliveries by postcode area. Then within each group, sort by earliest delivery window. Use Excel’s Sort function on Postcode first, then Delivery Time.

For more precision, use a free add-in like OpenSolver (works with Excel 2016+). It’s a linear programming tool that finds the shortest path through all stops. Set your objective: minimize total distance. Constraints: each stop visited once, start and end at depot. It runs in seconds. No cloud software needed.

Manage Carrier Performance with Real-Time Metrics

Not all carriers are equal. Some miss deadlines. Some damage goods. Some charge extra for small deliveries. Track them.

Create a Carrier Scorecard. Columns:

• Carrier name
• Shipment ID
• Planned delivery date
• Actual delivery date
• On-time rate (use =IF(Actual<=Planned,1,0))
• Damage reported (Yes/No)
• Cost per shipment

At the bottom, add summary rows:

• Average on-time rate: =AVERAGE(On-time rate column)
• Damage rate: =COUNTIF(Damage column,"Yes")/COUNTA(Damage column)
• Average cost: =AVERAGE(Cost column)

Sort by on-time rate. If Carrier A is 92% on-time and Carrier B is 68%, you know who to keep and who to replace. No vendor meetings needed.

Automate Reordering with Alerts

Running out of packaging tape or pallets isn’t just annoying-it halts shipments. Set up automatic alerts.

In your Inventory Log, add a column called Alert?. Use this formula:

=IF(Current Stock <= Reorder Level, "ORDER NOW", "OK")

Now filter the sheet to show only rows where Alert? says "ORDER NOW". Print that list weekly. Or better yet, use Excel’s Power Automate (free with Microsoft 365) to send an email to your procurement team every morning if any item triggers the alert.

Set it up: Go to Automate > Power Automate > Create. Choose "When a row is added or modified" in Excel. Add condition: if Alert? equals "ORDER NOW". Then add action: "Send an email". Done. No more missed reorders.

Forecast Demand with Simple Trends

You don’t need AI to predict next month’s volume. Excel’s trend functions work fine.

Make a Demand Forecast sheet. List monthly shipment volume for the last 12 months. Select the data, insert a line chart. Right-click the line, choose Add Trendline. Pick Linear. Check Display Equation on chart.

That equation? Use it to predict next month. If it says y = 120x + 3500, plug in x=13 (for month 13) and you get your forecast: 5060 shipments.

Adjust for seasonality. If December always spikes 40%, multiply your forecast by 1.4. Add a column called Seasonal Factor and manually tag each month. Then multiply forecast by that factor.

This isn’t perfect. But it’s better than guessing. And it’s free.

Build a Real-Time Dashboard

Managers don’t need 20 spreadsheets. They need one screen that shows what’s working and what’s broken.

Create a Logistics Dashboard on a new sheet. Use pivot tables and charts pulled from your other sheets:

• Current inventory levels (from Inventory Log)
• On-time delivery rate (from Carrier Scorecard)
• Top 5 delayed destinations
• Weekly shipment volume trend
• Number of items needing reorder

Use Insert > PivotChart to link each chart to its source data. Lock the layout. Freeze the top row. Save it as Dashboard.xlsx. Open it every morning. You’ll spot problems before they become crises.

Why Excel Beats Cheap Logistics Software

Many companies buy $20/month logistics tools that lock them into rigid workflows. They can’t add a custom field. They can’t change a report. They can’t export raw data without paying more.

Excel doesn’t care what you need. You control it. Need to track temperature-sensitive goods? Add a column. Want to compare fuel costs by route? Add a formula. Want to send alerts to your phone? Use Power Automate.

And it’s always available. No login. No server down. No vendor support wait time. Just open it. Work. Save. Done.

Common Mistakes (and How to Fix Them)

People think Excel is messy. It’s not. It’s just used wrong.

• Mistake: Merging cells. Fix: Never merge. Use Center Across Selection under Alignment. Merged cells break formulas and sorting.
• Mistake: One sheet for everything. Fix: Separate sheets for Inventory, Routes, Carriers, Dashboard. Link them with formulas like =Inventory!B5.
• Mistake: Manual data entry. Fix: Use data validation. Select a column, go to Data > Data Validation > List, and type your options: "FedEx, DHL, UPS, Local Courier". Now people can’t spell "DHL" wrong.
• Mistake: No backups. Fix: Save to OneDrive or Google Drive. Turn on version history. You can restore last week’s version with one click.

Next Steps: From Excel to Automation

Excel won’t replace a full WMS forever. But it’s the best bridge to one.

Once you’re comfortable with these sheets, start thinking about integration:

• Use Power Automate to push delivery data from Excel into your accounting software.
• Export your Carrier Scorecard to CSV and import it into a free tool like Airtable for better visuals.
• When you hit 50+ daily shipments, consider upgrading to a $10/month tool like Sortly or Zoho Inventory-but only after you’ve proven your process in Excel.

Don’t rush. Master the basics. Then scale.