Solver for optimization in Excel - Step-by-Step Guide

Introduction

Solver helps you find the best answer to a problem by changing numbers in your spreadsheet. It is useful when you want to maximize, minimize, or reach a specific value by adjusting some cells.

When you want to find the highest profit by changing prices or quantities.

When you need to minimize costs by adjusting resource use.

When you want to reach a target sales number by changing marketing spend.

When you want to balance a budget by changing expenses.

When you want to optimize a schedule by adjusting task times.

Steps

Step 1: Click

- Data tab > Solver button

The Solver Parameters window opens

Step 2: Click

- Set Objective box

You can type or select the cell with the formula you want to optimize

Step 3: Select

- To: options

You choose whether to Maximize, Minimize, or set a Value for the objective cell

Step 4: Click

- By Changing Variable Cells box

You select the cells that Solver can change to reach the goal

Step 5: Click

- Add button in Constraints section

You can add rules that the solution must follow, like limits on values

Step 6: Click

- Solve button

Solver runs and shows the best solution it found

Step 7: Choose

- Solver Results dialog

You decide to keep the solution or restore original values

Before vs After

Before

A spreadsheet with sales price, quantity, and profit formula but no optimized values

After

The sales price and quantity cells have new values that maximize profit according to Solver

Settings Reference

Set Objective

📍 Solver Parameters window

Defines the goal cell to maximize, minimize, or set to a value

Default: Empty

To

📍 Solver Parameters window

Chooses whether to maximize, minimize, or reach a specific value

Default: Max

By Changing Variable Cells

📍 Solver Parameters window

Cells that Solver can change to find the solution

Default: Empty

Constraints

📍 Solver Parameters window > Add button

Rules that limit the values Solver can use

Default: None

Solving Method

📍 Solver Parameters window > Options

Chooses the algorithm Solver uses to find the solution

Default: GRG Nonlinear

Common Mistakes

Not selecting the correct objective cell with a formula

Solver needs a formula cell to optimize, not just any number

Make sure the objective cell contains a formula that calculates what you want to optimize

Not adding constraints when needed

Without constraints, Solver might suggest impossible or unwanted values

Add constraints to keep values within realistic or allowed ranges

Selecting cells with fixed values as variable cells

Solver cannot change cells that are locked or contain constants

Select only cells that you want Solver to change and that are unlocked

Summary

Solver finds the best solution by changing selected cells to maximize, minimize, or reach a value.

You must set an objective cell with a formula and choose variable cells Solver can change.

Adding constraints helps keep the solution realistic and valid.