Excel Solver for business problems: a complete guide
every solopreneur eventually hits an optimization problem. you have a fixed marketing budget and three channels, each with a different return profile. you need to maximize revenue. or you have ten products, limited inventory, and a varying margin per product. you need to figure out which mix to push.
Excel Solver answers exactly these questions. it is a free add-in that ships with Excel, runs linear and nonlinear optimization, and can chew through problems that would take you hours to solve by hand. once you learn the four-step setup, the same pattern applies to budget allocation, scheduling, pricing, and inventory.
this tutorial walks through Solver from zero. we use realistic small-business problems (marketing mix, staff scheduling, product portfolio) so the patterns translate to your own work. by the end you will know how to install Solver, frame an optimization problem, set constraints, and interpret the results.
Excel Solver is a free Excel add-in that finds the optimal value of a target cell by changing other cells under specified constraints. Workflow: define decision variables (cells Solver can change), an objective cell (what to maximize, minimize, or set to a value), and constraints (rules the solution must satisfy). Solver supports linear (Simplex), nonlinear (GRG), and discrete (Evolutionary) methods. Available in Excel for Windows and Mac in all modern versions, just enable from File → Options → Add-ins.
what Solver is and when to use it
Solver answers questions of the form: “given these resources and these rules, what is the best possible outcome?”
four signals you have a Solver problem:
- you have several decisions to make (how much of A vs B vs C)
- there is one number you want to maximize, minimize, or hit exactly (revenue, cost, total)
- you have rules the solution must obey (budget cap, capacity limit, minimum quantity)
- trying combinations by hand would take forever
if any business question fits that frame, Solver beats trial-and-error. and unlike machine-learning optimization, Solver is fully transparent: every constraint is visible, every result is reproducible, every model lives in your spreadsheet.
installing Solver in Excel
Solver ships with Excel but is disabled by default.
Excel for Windows
- open Excel.
- go to File → Options → Add-ins.
- at the bottom, in the Manage dropdown, select Excel Add-ins. click Go.
- tick the “Solver Add-in” box and click OK.
- you will now see “Solver” in the Data tab on the far right.
Excel for Mac
- open Excel.
- go to Tools → Excel Add-ins.
- tick “Solver Add-in” and click OK.
- Solver appears in the Data tab.
Google Sheets
Sheets has a built-in Solver alternative called the Linear Optimization add-on (Extensions → Add-ons → Get add-ons). it covers linear problems but lacks the nonlinear and evolutionary engines Excel Solver includes. for serious optimization work, Excel is the better tool.
[SCREENSHOT: Excel Data tab with Solver button highlighted at the far right of the ribbon]
the four ingredients of every Solver problem
every Solver model has the same four components. learn to spot them and the setup becomes mechanical.
| ingredient | role | example |
|---|---|---|
| decision variables | cells Solver changes | how much budget to put into Facebook, Google, and TikTok |
| objective cell | the number to optimize | total expected revenue from the marketing mix |
| constraints | rules the solution must satisfy | total budget ≤ $10,000; each channel ≥ $0 |
| model type | how Solver searches | Simplex LP for linear, GRG Nonlinear for curved, Evolutionary for discrete |
if you can list these four for any problem, you can solve it.
example 1: marketing budget allocation
setup: you have $10,000 monthly marketing budget across three channels. each channel has a different expected ROAS based on past data.
| channel | min spend | max spend | expected ROAS |
|---|---|---|---|
| $500 | $5,000 | 2.5x | |
| $1,000 | $7,000 | 3.2x | |
| TikTok | $0 | $4,000 | 1.8x |
constraint: total spend must equal exactly $10,000.
goal: maximize total revenue.
setting up the worksheet
build this layout in Excel:
| cell | content |
|---|---|
| A1 | Channel |
| B1 | Spend |
| C1 | ROAS |
| D1 | Expected Revenue |
| A2 | |
| B2 | (decision variable, leave blank) |
| C2 | 2.5 |
| D2 | =B2*C2 |
| A3 | |
| B3 | (decision variable, leave blank) |
| C3 | 3.2 |
| D3 | =B3*C3 |
| A4 | TikTok |
| B4 | (decision variable, leave blank) |
| C4 | 1.8 |
| D4 | =B4*C4 |
| A6 | Total Spend |
| B6 | =SUM(B2:B4) |
| A7 | Total Revenue |
| B7 | =SUM(D2:D4) |
[SCREENSHOT: Excel sheet with the layout above, showing channels, spend cells empty, and the Total Revenue cell at B7]
running Solver
- click Data → Solver.
- Set Objective: click in the Objective box, then click cell B7 (Total Revenue).
- To: select Max.
- By Changing Variable Cells: click in the box, then drag-select B2:B4.
- Subject to the Constraints: click Add for each constraint:
– $B$2 >= 500 (Facebook min)
– $B$2 <= 5000 (Facebook max)
– $B$3 >= 1000 (Google min)
– $B$3 <= 7000 (Google max)
– $B$4 >= 0 (TikTok min)
– $B$4 <= 4000 (TikTok max)
– $B$6 = 10000 (total budget exact) - Select a Solving Method: choose Simplex LP (this is a linear problem).
- click Solve.
interpreting the result
Solver finds: Facebook $3,000, Google $7,000, TikTok $0, total revenue $30,100.
the result respects every constraint, maxes out Google (highest ROAS), uses Facebook for the remainder, and zeroes out TikTok (lowest ROAS). that matches intuition, which is how you sanity-check a Solver run.
example 2: product mix with capacity constraints
setup: a small leather-goods maker produces wallets, belts, and bags. each item uses leather and labor at different rates and earns a different margin.
| product | leather (sqft) | labor (hours) | margin |
|---|---|---|---|
| wallet | 0.5 | 0.5 | $25 |
| belt | 1.0 | 0.3 | $30 |
| bag | 3.0 | 2.0 | $90 |
constraints:
– leather available this month: 200 sqft
– labor hours available: 120 hours
– demand caps: max 80 wallets, 60 belts, 30 bags
– minimums: at least 10 of each product
goal: maximize total margin.
Solver setup
decision variables: cells holding the quantity of wallets, belts, bags produced.
objective cell: total margin = sum of (quantity × margin per item).
constraints:
– total leather used ≤ 200
– total labor used ≤ 120
– each quantity within min and max bounds
– all quantities are integers (cannot make 4.7 wallets)
run Solver with Simplex LP. Solver finds the integer mix that maxes margin under capacity. typical result: produce close to the max-demand belts (high margin per sqft), some wallets, fewer bags (high resource usage).
this is the same pattern as ecommerce inventory allocation, restaurant menu mix, manufacturing scheduling, and consultant time allocation. learn it once, reuse it forever.
example 3: workforce scheduling
setup: a coffee shop needs 4 baristas Mon-Wed, 5 Thu-Fri, 6 Sat-Sun. each barista works 5 consecutive days. you want the minimum number of baristas needed to cover all shifts.
decision variables: number of baristas starting each day (Mon, Tue, Wed, Thu, Fri, Sat, Sun).
objective cell: total baristas (sum of all start counts) — minimize.
constraints: for each day of the week, the number of baristas working that day (sum of those who started in the prior 5 days) ≥ daily requirement.
run Solver with Simplex LP and integer constraints. Solver finds the minimum total headcount that covers every shift. powerful for any 24/7 operations problem (call centers, gym staffing, salon coverage).
three Solver methods and when to use each
| method | type of problem | example |
|---|---|---|
| Simplex LP | linear: variables only added, multiplied by constants, summed | budget allocation, product mix, scheduling |
| GRG Nonlinear | curved: variables raised to powers, multiplied together, in functions | pricing optimization with elasticity, portfolio variance |
| Evolutionary | discrete or non-smooth: IF, INDEX, choose-from-list logic | location selection, route planning, combinatorial problems |
start with Simplex LP. if Solver returns “the linearity conditions are not satisfied,” switch to GRG Nonlinear. if your model uses IF or lookup logic on the decision variables, use Evolutionary.
constraint patterns you will reuse
| constraint type | syntax in Solver | example |
|---|---|---|
| less than or equal | <= | total budget <= 10000 |
| greater than or equal | >= | min orders >= 100 |
| equal | = | total spend = 10000 (forces full use) |
| integer | int | producing whole units only |
| binary | bin | yes/no decisions (build factory or not) |
| difference | reference cell | item A spend ≤ 2 × item B spend |
binary and integer constraints unlock yes/no and either/or modeling. Solver’s Evolutionary method is required for large binary problems but Simplex handles small ones.
interpreting Solver results
after Solver runs, you see one of three messages:
“Solver found a solution. All constraints and optimality conditions are satisfied.” the result is optimal under the model. trust it.
“Solver could not find a feasible solution.” your constraints are mutually impossible. example: total budget = $10,000 but channel minimums sum to $11,000. relax a constraint and rerun.
“Solver could not improve the current solution.” Solver hit a local optimum but could not prove it is global. common with GRG Nonlinear. try the Evolutionary method, or run multiple times with different starting values.
after a successful run, click “Sensitivity Report” in the result dialog (Simplex LP only) to see how much each constraint binds the solution. constraints with high “shadow prices” are the bottlenecks. relax one of those by 10% and you may unlock significantly more revenue or savings.
common Solver mistakes and fixes
mistake 1: forgetting non-negativity
every decision variable should typically have a >= 0 constraint, or check “Make Unconstrained Variables Non-Negative” in Solver Options.
mistake 2: model is nonlinear but you picked Simplex LP
if the formula in your objective or constraints multiplies decision variables together (like price × quantity where both are variable), the model is nonlinear. Simplex will fail. switch to GRG Nonlinear.
mistake 3: too many constraints make Solver slow
every constraint adds work. for a 5-variable problem with 30 constraints, Solver can take minutes. simplify by combining redundant constraints or by reducing the variable count.
mistake 4: the result looks crazy
always sanity-check Solver output. if it suggests spending all budget on one channel, ask why. if your ROAS data is wrong, Solver happily optimizes against bad numbers. garbage in, garbage out.
related tutorials on DRAC
- Excel Power Pivot tutorial: multi-table analysis — for problems too big to fit in a single sheet
- revenue forecasting Excel and Sheets 2026 — feed Solver with forecasted ROAS or demand
- Excel macros for non-coders — automate Solver runs across multiple scenarios
- pricing analysis tutorial — when Solver helps find the optimal price point
conclusion: Solver turns Excel into a decision engine
most Excel users use spreadsheets to track and report. Solver turns Excel into a decision engine. when you can frame a question as “what is the best mix of X, Y, Z under these rules,” Solver gives you the answer in seconds.
the four-step setup never changes: decision variables, objective cell, constraints, solving method. master that pattern on the marketing-mix example, then graduate to product mix and scheduling. by the third real Solver problem, you will start spotting optimization questions in your business that you had previously been answering by gut feel.
next action: pick one decision you have been making by intuition this month (budget split, inventory mix, time allocation). build the four ingredients in Excel, run Solver, and compare the optimal answer to your gut. the gap between the two is what Solver is worth to you.