Table of Contents
What is Excel Solver
Excel Solver is a whatif analysis tool for optimization in Microsoft Office Excel. It is an addin with Excel since Excel 97. Excel Solver helps to find an optimal value in one cell, called the Objective Cell (target cell), on your worksheet. It works by changing a group of cells related to the Objective Cell (target cell) to find an optimal value subject to the constraints that you set. You can use Solver, to solve Finance problem, Investment problem, Production problem, Distribution problem, Purchasing problem, Scheduling problem etc.
How To Install Excel Solver Addin
The Excel Solver Addin is not need to download and install. It's is available when you install Microsoft Office or Microsoft Excel. And, It's not load default, to use the Excel Solver Addin, you first need to load it in Excel.
For Excel 2003
 Start Microsoft Excel 2003.
 On the Tools menu, click AddIns.
 In the AddIns available box, select the Solver Addin check box, and then click OK. If Solver Addin is not listed, click Browse to locate it.
 If you see a message that tells you the Solver Addin is not currently installed on your computer, click Yes to install it.
 Click Tools on the menu bar. When you load the Solver Addin, the Solver command is added to the Tools menu.
For Excel 2007
 Start Microsoft Excel 2007.
 Click the Microsoft Office Button, and then click Excel Options.
 Click AddIns, and then in the Manage box, select Excel Addins.
 Click Go.
 In the AddIns available box, select the Solver Addin check box, and then click OK.
 After you load the Solver Addin, the Solver command is available in the Analysis group on the Data tab.
For Excel 2010
 Start Microsoft Excel 2010.
 Click File tab, and then click Options.
 In the Excel Options window, Click AddIns from the left sidebar, and then in the Manage box (at the bottom of the window), select Excel Addins.
 Click Go.
 In the AddIns available box, select the Solver Addin check box, and then click OK.
 If Solver Addin is not listed in the AddIns available box, click Browse to locate the addin.
 If you get prompted that the Solver Addin is not currently installed on your computer, click Yes to install it.
 After you load the Solver Addin, the Solver command is available in the Analysis group on the Data tab.
Before You Use It
Before you use the Excel Solver, it’s important to understand the basic concept of what it does and how it works. The Excel Solver has three primary components: the Objective Cell, the Variable Cells, and the Constraints.
 Objective Cell (Target Cell). This is the cell that represents the goal or objective of the problem. We want to either minimize or maximize the target cell.
 Variable Cells (Changing cells or Adjustable Cells) are the cells that can be modified or adjust to optimize the Objective cell.
 Constraints. This are restrictions or limitations to what Solver can do to solve the problem.
Excel Solver Examples
Excel Solver Examples: Style Box
Range B2:D4 is a Style Box, you can copy A1:F6 to a new worksheet to understand this example. Now, we use Solver to fill with the Style Box in Excel 2010.


 click Data tab, then click Solver in the Analysis group.
 Then, the Solver Parameters window opens. This is where you enter the criteria needed to solve the Style Box problem. see below image.
 set Objective: in this case, we don't need to set Objective, leave it blank.
 set Changing Variable Cells: Click and hold on cell B2 and dragselect to cell D4, or enter $B$2:$D$4.
 Constraints: In the constraint window, click the Add button, enter a constraint, then click Add button, this will add your constraint to the list, and then allow you to enter another Continue entering all the constraints, and after you have entered the final one on the list, click the Cancel button return to the main Solver window. In this example, we use the constraints listed below:
 $B$5:$D$5 = 15
 $F$2:$F$6 = 15
 $B$2:$D$4 = AllDifferent
 Tips: AllDifferent constraint is a new constraint in Excel 2010. It requires that these cells must be integers in the range 1 to N (N = 9 in this example), with each variable different from all the others at the solution.
 click Solver.
 Solver may take a few seconds, or a few minutes (Depending on how much memory your computer has and the processor speed). When it’s finished processing, Solver will return a solution. When Excel reports a successful run, select Keep the Solver Solution and then click OK.
 Then, you will see the Style Box solver results.
 If you change Objective in step 3, select a cell in range B2:D4, and set value to Max or Min, click Solver, it will return a different solution.
 You can download this example: Style Box with Excel Solver
Excel Solver Examples: Finance
Download Excel Solver Finance Examples for Excel 2007 or later
Download Excel Solver Finance Examples for Excel 2003 or earlier
Excel Solver Examples: Investment
Download Excel Solver Investment Examples for Excel 2007 or later
Download Excel Solver Investment Examples for Excel 2003 or earlier
Excel Solver Examples: Production
Download Excel Solver Production Examples for Excel 2007 or later
Download Excel Solver Production Examples for Excel 2003 or earlier
Excel Solver Examples: Distribution
Download Excel Solver Distribution Examples for Excel 2007 or later
Download Excel Solver Distribution Examples for Excel 2003 or earlier
Excel Solver Examples: Purchasing
Download Excel Solver Purchasing Examples for Excel 2007 or later
Download Excel Solver Purchasing Examples for Excel 2003 or earlier
Excel Solver Examples: Scheduling
Download Excel Solver Scheduling Examples for Excel 2007 or later
Download Excel Solver Scheduling Examples for Excel 2003 or earlier
Very helpful article, thank you very much
gr8!
Can we have a similar formula for 4*4 grid (using 116 nos.)?