Excel Solver for Transportation Problems: Setup and Tips

How-to basics · Updated November 21, 2026

Yes. Excel Solver can solve a transportation problem by choosing shipment quantities that minimize total shipping cost while meeting supply and demand constraints. In Microsoft Excel for Microsoft 365 on Windows, arrange unit costs and shipment quantities in matching grids, calculate total cost with SUMPRODUCT, then use Data > Solver with the Simplex LP method.

What a transportation problem is

A transportation problem is a linear optimization model for deciding how much of a product to send from several sources to several destinations. Sources might be factories, warehouses, or suppliers; destinations might be stores, customers, or distribution centers. Each route has a cost per unit, and each source has a limited supply while each destination has a required demand.

The goal is usually to minimize the total cost of all shipments. The decision is not simply which route is cheapest: a low-cost route may have limited supply at its origin or may serve a destination whose demand is already covered. Solver finds a combination that satisfies all the constraints together.

A basic model assumes that route costs are known and constant per unit, shipments can be divided into the quantities allowed by the model, and total cost is the sum of each route’s quantity multiplied by its unit cost. Those assumptions make the standard version a linear program.

How the model works

Let each changing cell represent the number of units shipped on one route. The objective is the sum of each route’s cost multiplied by its shipment quantity. Supply constraints prevent a source from shipping more than it has. Demand constraints ensure each destination receives at least its required amount; in a balanced model, the constraints are usually equalities.

For a balanced transportation problem, total supply equals total demand. A common formulation sets each origin’s shipments equal to its supply and each destination’s receipts equal to its demand. If the totals differ, the model needs a deliberate treatment of the excess or shortage; simply forcing every source and destination to equality would make the problem infeasible.

Shipment quantities are normally constrained to be nonnegative. They do not need to be integers when the product can be split into fractional units, such as liquid or bulk material. If the decision is in whole pallets or vehicles, add integer constraints—but integer restrictions can make a larger model harder to solve.

Arrange the worksheet

Use one grid for costs and another grid of the same shape for shipment quantities. For a three-source, four-destination example, place the route costs in B3:E5, with source names in A3:A5 and destination names in B2:E2. Put available supplies in F3:F5. Put the changing shipment quantities in B9:E11, with the same source and destination order. Enter starting values such as zero in those cells; Solver will change them.

Calculate each source’s shipped total in F9:F11 and each destination’s received total in B12:E12. Put destination requirements in B13:E13. Keep labels clear and aligned: a swapped row or column can produce a mathematically valid answer for the wrong routes. It also helps to format cost and quantity cells consistently and to check that every cost is expressed per the same unit.

With shipment variables in B9:E11, enter this formula in F9 and fill it down through F11 to calculate each source’s total shipments: =SUM(B9:E9)

With shipment variables in B9:E11, enter this formula in B12 and copy it across through E12 to calculate each destination’s receipts: =SUM(B9:B11)

With unit costs in B3:E5 and shipment quantities in B9:E11, put this formula in B15 to calculate total cost: =SUMPRODUCT(B3:E5,B9:E11) The two ranges must have matching dimensions so that every cost is multiplied by the shipment on the corresponding route.

Set up Solver in Excel

If Solver is not available on the Data tab in Excel for Microsoft 365 on Windows, enable the add-in: choose File > Options > Add-ins. At the bottom, set Manage to Excel Add-ins, select Go, check Solver Add-in, and select OK. Then open Data > Solver. In Excel for Mac, enable Solver through Tools > Excel Add-ins, select Solver Add-in, and then open Data > Solver. Solver is a desktop add-in; Excel for the web does not provide the same built-in Solver workflow for running this model.

  1. In the Solver Parameters dialog, set the objective to the total-cost cell, such as $B$15, and choose Min.
  2. Set the changing variable cells to the shipment grid, such as $B$9:$E$11.
  3. Add a supply constraint: shipment totals in $F$9:$F$11 must be less than or equal to available supplies in $F$3:$F$5. For a balanced model where all supply must be used, use equality instead.
  4. Add a demand constraint: destination receipts in $B$12:$E$12 must be greater than or equal to requirements in $B$13:$E$13. Use equality when each destination must receive exactly its stated demand.
  5. Make the shipment cells nonnegative, either by adding a constraint or by selecting Make Unconstrained Variables Non-Negative in Solver Options.
  6. Choose Simplex LP as the solving method, then select Solve. Review the Solver Results dialog and keep the solution if it is feasible and suitable.

These cell references are examples, not fixed requirements; if your worksheet uses different locations, select the corresponding ranges. For the standard model, Simplex LP is appropriate because both the objective and the constraints are linear. Solver’s GRG Nonlinear method is intended for nonlinear models, not a better version of the transportation method.

Worked example: three sources and four destinations

Suppose three warehouses have supplies of 30, 40, and 20 units. Four stores need 20, 25, 30, and 15 units. Both totals equal 90, so the model is balanced. The following table gives the cost per unit shipped:

Warehouse Store X Store Y Store Z Store W Supply
A 4 6 8 13 30
B 5 4 3 8 40
C 9 7 6 5 20
Demand 20 25 30 15 90

One minimum-cost allocation is for A to send 20 units to X and 10 to Y; B to send 15 to Y and 25 to Z; and C to send 5 to Z and 15 to W. All other route quantities are zero. The sources ship 30, 40, and 20 units, and the stores receive 20, 25, 30, and 15, so every requirement is met.

The total is 20 × 4 + 10 × 6 + 15 × 4 + 25 × 3 + 5 × 6 + 15 × 5 = 380 cost units. Solver may return a different allocation with the same total if the model has multiple optimal solutions. That does not necessarily indicate an error; multiple routes can sometimes be exchanged without changing the objective.

When supply and demand do not balance

If available supply exceeds demand, one practical model is to allow unused supply: constrain each source’s shipments to be less than or equal to its supply and require each destination to receive its demand. With positive shipping costs, the cost-minimizing solution will generally avoid sending unnecessary units. If all supply must be accounted for, add a dummy destination for the surplus and give it a cost that represents the actual consequence of unused capacity, storage, or disposal.

If demand exceeds available supply, the ordinary model cannot meet every requirement and Solver should report that it cannot find a feasible solution. A dummy source can represent a shortage, but its route costs should reflect the modeled penalty or lost-sales cost. Assigning a zero cost to shortage would tell Solver that failing to serve customers is free, which is usually not a meaningful assumption.

Dummy rows or columns are modeling devices, not automatic fixes. Decide what unused capacity or unmet demand means in the real situation, and use costs and constraints that reflect that decision. A separate maximum delivery, minimum order, or route-capacity restriction can also be added when the basic supply and demand totals are not enough.

What Solver can and cannot represent

The standard transportation model handles per-unit costs and linear capacity rules well. It does not automatically account for a fixed fee each time a route is opened, a discount that changes at certain shipment thresholds, or a requirement that a truck must be dispatched as a whole. Those features introduce binary, integer, or nonlinear decisions. Some can be modeled with additional variables and constraints, but the setup becomes more involved than a basic transportation grid.

Also distinguish route cost from total route cost. If a route costs $5 per unit, multiplying the cost by shipped units is linear. If using a route triggers a separate $100 charge regardless of quantity, multiplying shipment quantity by $5 does not capture that fixed charge. The model needs a separate yes-or-no route variable and linking constraints, and Solver may need an integer method. For detailed operational planning, a dedicated optimization tool may be more practical.

The built-in Excel Solver add-in has a limit of 200 variable cells for its standard Solver engine. A model with many origins and destinations can exceed that limit because the changing grid has one cell per possible route. In that case, consider a specialized optimization add-in or software, or restructure the problem only if the omitted routes are genuinely impossible—not merely inconvenient to include.

Common problems and checks

  • Solver reports no feasible solution: Check whether total required demand exceeds available supply, whether a constraint points to the wrong range, and whether any route or capacity restriction blocks necessary shipments.
  • Shipments are negative: Add a nonnegative constraint or enable Make Unconstrained Variables Non-Negative. A cost-minimization model without that condition can use negative quantities mathematically.
  • The cost looks too low or too high: Confirm that the cost grid and shipment grid are aligned and that costs use a consistent unit, such as dollars per case rather than dollars per pallet in some cells.
  • Demand is exceeded unexpectedly: If destinations must receive exact quantities, use equality constraints rather than greater-than-or-equal constraints. If overdelivery is allowed, verify that the model’s costs and supply constraints make that choice intentional.
  • Solver gives a result but the worksheet totals do not match: Check the SUM formulas, objective range, and constraint ranges. Recalculate the workbook and confirm that the objective formula references the same cells used in the Solver dialog.

After solving, inspect the shipment grid and recalculate the total independently. Compare every row total with its supply and every column total with its demand. Solver optimizes the model as entered; it cannot tell whether a cost was mistyped, a demand figure is outdated, or a route is not actually available.

Excel Solver versus other approaches

For a small or medium-sized cost-allocation problem, Solver is convenient because the model and its inputs remain visible in the workbook. A specialized transportation algorithm can be more efficient for large networks, and dedicated optimization software can provide more control over integer decisions, multiple periods, or complex business rules. The right choice depends on the number of routes, the model’s complexity, and how often the data must be updated.

Manual methods such as the northwest-corner rule or least-cost allocation can produce a feasible starting plan, but they do not always produce the minimum-cost result. They are useful for learning the structure of the problem or checking a small example. Solver is the more direct choice when the objective is to find the minimum under the entered constraints, rather than merely to construct a valid allocation.

Frequently Asked Questions

Can Excel Solver solve a transportation problem?
Yes. Use shipment quantities as changing cells, calculate total cost with SUMPRODUCT, add supply and demand constraints, and select Simplex LP for the standard linear model.

Which Solver method should I use?
Choose Simplex LP when route costs are per unit and the objective and constraints are linear. Use another method only when the model has genuinely nonlinear relationships or other special decision requirements.

Should supply and demand constraints use equality?
Use equality when all supply must be shipped and each destination must receive exactly its demand in a balanced model. Use inequalities when unused supply is allowed or when the model intentionally permits overdelivery.

Can Solver handle integer shipments?
Yes, you can add an integer constraint to shipment cells when quantities must be whole numbers. Integer restrictions can make solving slower, especially as the number of routes grows.

Why does Solver say the transportation model is infeasible?
Common causes include demand exceeding total supply, a mistaken cell range, nonmatching row or column order, or restrictions that prevent enough units from reaching a destination. Check the worksheet totals and constraints first.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.