Excel Transport Problem: Solve Shipping Costs with Solver

If you searched “excel transport,” you most likely want to solve a transportation problem in Excel: working out how many units to ship from each source (factory, warehouse) to each destination (store, customer) so total shipping cost is as low as possible. Excel does this with its free Solver add-in using the Simplex LP method. The model itself needs only a cost table, a shipment table, a few SUM formulas and one SUMPRODUCT.

Two other things people mean by this search are covered at the end: the TRANSPOSE function (a very common typo) and trucking companies that happen to be called Excel Transport.

What the transportation problem is

A transportation problem has four ingredients:

• Sources with a fixed supply (for example, units available at each plant).
• Destinations with a fixed demand (units each store needs).
• A cost per unit for every source-to-destination route.
• Decision variables: how many units travel on each route.

The goal is to minimize total cost while never shipping more than a source has and always meeting each destination’s demand. Because cost grows in a straight line with units shipped, this is a linear program, which is exactly what Solver’s Simplex LP engine is built for. A University of Notre Dame Solver handout describes Solver as quite good at linear programming problems like this one.

Step 1: Turn on Solver

Solver ships with Excel but is switched off by default. According to Microsoft’s add-in instructions:

1. Go to File > Options > Add-ins.
2. In the Manage box, choose Excel Add-ins and click Go.
3. Tick Solver Add-in and click OK.
4. Solver now appears on the Data tab, in the Analysis group.

If you work in Excel for the web, the desktop add-in is not available. Microsoft offers a Solver add-in on AppSource that supports Simplex LP, GRG Nonlinear, Evolutionary and integer constraints. Its listing says solving runs on a backend server that currently limits solve time to 30 seconds, which is fine for typical transportation models. Treat that limit as “as of October 2026”; it may change.

Step 2: Lay out the model

A clean layout makes Solver setup almost mechanical. Use three blocks on one sheet.

Cost table

Put sources down the rows and destinations across the columns. Each cell holds the cost of shipping one unit on that route.

Shipment table (decision variables)

Copy the same shape directly below the cost table and leave the cells empty or set to 0. These are the cells Solver will change. To the right of each row, add a Total shipped column with =SUM(...) across that row. Below each column, add a Total received row with =SUM(...) down that column.

Next to the row totals, enter each source’s supply. Under the column totals, enter each destination’s demand.

Objective cell

In one cell, calculate total cost by multiplying the two tables cell by cell:

=SUMPRODUCT(CostRange, ShipmentRange)

The two ranges must be the same size. This single formula is what Solver will minimize.

Step 3: Configure Solver

Open Data > Solver and fill in the dialog. The field names below follow Microsoft’s Solver guide.

1. Set Objective: select your total-cost cell. It must contain a formula.
2. Choose Min.
3. By Changing Variable Cells: select the whole shipment table.
4. Click Add to create constraints:
   • Row totals <= supply (you cannot ship more than a source holds).
   • Column totals >= demand (or = demand if every destination must receive exactly what it ordered).
5. Make sure Make Unconstrained Variables Non-Negative is ticked. Microsoft explains that this gives every variable without its own lower bound a lower bound of zero, which stops Solver from “shipping” negative units to cut costs.
6. In Select a Solving Method, choose Simplex LP. Don’t rely on the default: a Dummies guide to Solver notes that Solver defaults to GRG Nonlinear. Simplex LP is the right engine for a linear model, and it will also tell you if your model accidentally isn’t linear.
7. Click Solve, then choose Keep Solver Solution. You can also generate an Answer or Sensitivity report from the results dialog.

The shipment table now holds the cheapest routing, and the objective cell shows the minimum total cost.

Handling unbalanced supply and demand

Real data rarely balances perfectly.

• Supply exceeds demand: the setup above already works. The <= supply constraints let some stock stay at the source.
• Demand exceeds supply: with >= demand constraints, Solver will report that it could not find a feasible solution, because the demand can’t all be met. The standard fix is to add a dummy source row whose supply equals the shortfall and whose route costs are zero (or a penalty cost if some customers matter more). Units “shipped” from the dummy source show you which demand goes unfilled.

Common mistakes and how to fix them

• Simplex LP complains the model isn’t linear. Something in the objective or constraints isn’t linear, such as an IF, MAX or ROUND formula touching the shipment cells. Keep the model to SUM and SUMPRODUCT.
• Negative shipments. The non-negative checkbox was cleared. Tick it, or add a >= 0 constraint on the shipment table.
• Too many variables. Microsoft’s guide says Solver accepts up to 200 variable cells, and the AppSource listing gives the same 200-variable, 100-constraint limit (bounds on variables don’t count toward the 100). Each route is one variable, so 10 sources by 20 destinations is already 200. Larger networks need an upgraded solver from Frontline Systems, the company that develops Excel’s Solver, or a dedicated optimization tool.
• Fractional units. If you need whole trucks or pallets, add a constraint on the shipment range using int. Expect slower solves on bigger models.
• Ranges that don’t line up. SUMPRODUCT returns #VALUE! if the cost and shipment ranges are different sizes. Keep both tables exactly the same shape.

If you meant TRANSPOSE

Plenty of “excel transport” searches are really after TRANSPOSE, which flips rows into columns. Microsoft’s TRANSPOSE page describes two ways:

1. TRANSPOSE function: type =TRANSPOSE(A1:D3) in the top-left cell of the destination. In Microsoft 365 press Enter and the result spills to the right size automatically. In older versions, select the full output range first and confirm with Ctrl+Shift+Enter as an array formula. The result stays linked to the source.
2. Paste Special > Transpose: copy the range, right-click the destination, and choose the Transpose paste option. This is quick, but Microsoft notes it duplicates the data, and the copy won’t update when the original changes.

If you meant a trucking company

Several unrelated freight carriers use the name Excel Transport or Excel Transportation, including firms in British Columbia, Ontario and Idaho. They have nothing to do with Microsoft Excel. Check the company’s own website or your shipping paperwork to make sure you’ve found the right one.

Bottom line

For the spreadsheet version of “excel transport,” the setup is: a cost table, a matching shipment table, SUM totals for supply and demand, one SUMPRODUCT objective, and Solver set to Min with Simplex LP and non-negative variables. Once the layout is built, you can update costs or demand and re-run Solver in seconds. Menu names and add-in limits are current as of October 2026 and may shift in future Excel updates.