How I built a global logistics tracker using just formulas, pivots, and a bit of imagination

A while ago, I found myself talking to a logistics manager who seemed completely overwhelmed.
He had containers spread across the globe — some sitting at ports, some in transit, some stuck due to delays.

He pulled out a spreadsheet. Or rather, a pile of spreadsheets.

“Elad,” he said, “I just want to know where my stuff is, how much it’s costing me, and what I need to deal with today. Is that too much to ask?”

And honestly… it isn’t. So I decided to build it.

🎯 Starting With the End in Mind

I imagined a simple dashboard — no fancy tools, just Excel.
It would show him exactly:

• Where each container is in its journey
• What shipping company was used
• Costs broken down by type (ocean, inland, detention)
• What’s coming in next week
• And most importantly — which containers require immediate action

No more digging through reports or scanning rows. Just answers.

🧱 Building the Base: Raw Data

I started by creating dummy data that mimicked a real logistics report — ports of origin and destination, shipping companies, container numbers, key dates (departure, discharge, delivery), and of course, costs.

If you’ve ever worked with freight forwarders, this might sound familiar. The data exists — just not in a useful format. So I brought it into Excel and cleaned it up.

I even added expected arrival dates, so we could do a bit of forecasting later.

🔧 Adding Structure: The Engine Room

Next came the formulas. This is where the real magic started:

• I calculated demurrage and detention costs based on free days and daily charges.
• I built logic to figure out container status — delivered, in transit, stuck, or returned.
• I added year/week breakdowns for expected arrivals to allow for future planning.

It’s not fancy VBA. Just smart use of INDEX, MATCH, some date math, and pivot logic.

📊 Then Came the Dashboard: The Command Center

Now the fun part.

The dashboard has:

• Slicers to filter by shipping company, port of load, destination, and year
• A timeline for discharge dates to view containers over time
• Visuals showing container flow (departure → delivery), monthly costs, and expected arrivals
• And my favorite — a Container Alert panel, showing the hottest containers that need to be returned fast

It’s clean. It’s responsive. It tells a story at a glance.

🔄 Why This Matters

If you manage global shipping, Excel can be more than just a passive tracker — it can be your daily logistics cockpit.

This dashboard can help you:

• Reduce detention costs by identifying stuck containers
• Improve warehouse planning with weekly arrival forecasts
• Make smarter decisions on which shipments need priority attention

📦 Final Thoughts

What started as a casual conversation turned into one of my favorite builds.
It’s not just a tool — it’s a reflection of how much can be done in Excel with the right structure and creativity.

If you’re dealing with similar logistics challenges, this file could be a game-changer for you or your team.

📩 Want the file?

Until next time —
Elad