Solver for Excel
Overview
This add-in provides advanced solver functions for Python, including optimization algorithms, calculus-based ODE solvers, numerical differentiation, and integration methods. These functions leverage the widely used and powerful SciPy Python library. Since the functions are Python, they can be run using either Excel’s integrated =PY() function or simply as a custom function which uses Pyodide locally in your browser. Either way you have full access to the Python source code for review and customization.
Features
🆓 Unlimited FREE use
🌐 Works in Excel for web and desktop
✅ Use Excel =PY() or local Python runtime
🔒 No data is shared outside Excel
🔍 Python source code available for review
You can use the functions directly in Excel cells, e.g. =BF.<function_name>(), just like native Excel functions, e.g. =SUM(). A list of the available solver functions is provided below. Another option is to use the Function Dialog from the add-in taskpane, which provides a guided experience for selecting functions and entering parameters, similar to Excel’s built-in “Insert Function” feature. This also gives you the following options:
- insert the function formula, the same as if you typed it into the cell, e.g.
=BF.MINIMIZE("x^2 + y^2", {1, 1}) - insert the static calculated result instead of the formula, e.g.
3.14 - insert a =PY() formula that uses Excel’s native Python runtime, e.g.
=PY("<python code>")if the package used is available in Excel’s Python environment.
See the Excel Python functions user guide for more details on these options.
The custom functions use Pyodide which runs in Excel’s add-in browser, so no installation of Python or packages is needed, it it works regardless of whether you have access to Excel’s native =PY() functionality. It also is capable of installing custom Python packages which are not available in Excel’s native Python runtime. For example, the HESSIAN function uses the CasADi package for symbolic differentiation, which is not included in Excel’s native Python environment.
Example Workbook
A workbook which demonstrates the use of several of the functions is shown below. Note that embedded Excel workbooks do not contain a formula bar so you’ll likely want to open this workbook in Excel online or download a copy using the download button in the bottom right corner of the embedded viewer.
Solver Functions
See links to detailed documentation for each function below.
Calculus
Differentiation
| Function | Description |
|---|---|
| HESSIAN | Compute the Hessian matrix (second derivatives) of a scalar function using CasADi symbolic differentiation. |
| JACOBIAN | Calculate the Jacobian matrix of mathematical expressions with respect to specified variables. |
| SENSITIVITY | Compute the sensitivity of a scalar model with respect to its parameters using CasADi. |
Integration
| Function | Description |
|---|---|
| DBLQUAD | Compute the double integral of a function over a two-dimensional region. |
| QUAD | Numerically integrate a function defined by a table of x, y values over [a, b] using adaptive quadrature. |
| TRAPEZOID | Integrate sampled data using the composite trapezoidal rule. |
ODE Models
| Function | Description |
|---|---|
| BRUSSELATOR | Numerically solves the Brusselator system of ordinary differential equations for |
| COMPARTMENTAL_PK | Numerically solves the basic compartmental pharmacokinetics system of ordinary differential equations. |
| FITZHUGH_NAGUMO | Wrapper around scipy.integrate.solve_ivp that numerically solves the FitzHugh-Nagumo system of |
| HODGKIN_HUXLEY | Numerically solves the Hodgkin-Huxley system of ordinary differential equations for neuron action potentials. |
| LORENZ | Numerically solves the Lorenz system of ordinary differential equations for chaotic dynamics. |
| LOTKA_VOLTERRA | Numerically solves the Lotka-Volterra predator-prey system of ordinary differential equations. |
| MICHAELIS_MENTEN | Numerically solves the Michaelis-Menten system of ordinary differential equations for enzyme kinetics. |
| SEIR | Numerically solves the SEIR system of ordinary differential equations for infectious disease modeling. |
| SIR | Solves the SIR system of ordinary differential equations for infection dynamics. |
| VAN_DER_POL | Numerically solves the Van der Pol oscillator system of ordinary differential equations. |
ODE Systems
| Function | Description |
|---|---|
| SOLVE_BVP | Solve a boundary value problem for a second-order system of ODEs. |
| SOLVE_IVP | Solve an initial value problem for a system of ODEs of the form dy/dt = A @ y. |
Optimization
Assignment Problems
| Function | Description |
|---|---|
| LINEAR_ASSIGNMENT | Solve the linear assignment problem using scipy.optimize.linear_sum_assignment. |
| QUADRATIC_ASSIGNMENT | Solve a quadratic assignment problem using SciPy’s implementation. |
Curve Fitting
| Function | Description |
|---|---|
| CURVE_FIT | Fit a model specified by model_id to xdata, ydata using scipy.optimize.curve_fit. |
Global Optimization
| Function | Description |
|---|---|
| BASIN_HOPPING | Minimize a single-variable expression with SciPy’s basinhopping algorithm. |
| BRUTE | Perform a brute-force grid search to approximate the global minimum of a function. |
| DIFFERENTIAL_EVOLUTION | Minimize a multivariate function using differential evolution. |
| DUAL_ANNEALING | Minimize a multivariate function using dual annealing. |
| SHGO | Find global minimum using Simplicial Homology Global Optimization. |
Linear Programming
| Function | Description |
|---|---|
| LINEAR_PROG | Solve a linear programming problem using SciPy’s linprog. |
| MILP | Solve a mixed-integer linear program using scipy.optimize.milp. |
Local Optimization
| Function | Description |
|---|---|
| MINIMIZE | Minimize a multivariate function using SciPy’s minimize routine. |
| MINIMIZE_SCALAR | Minimize a single-variable function using SciPy’s minimize_scalar. |
Root Finding
| Function | Description |
|---|---|
| FIXED_POINT | Find a fixed point x such that f(x) = x for a scalar function expression. |
| ROOT | Solve a square nonlinear system using SciPy’s root solver. |
| ROOT_SCALAR | Find a real root of a scalar function using SciPy’s root_scalar. |
Excel Resources
Excel provides several built-in optimization capabilities that complement this add-in. Understanding their strengths and limitations helps you choose the right tool for each problem.
Solver Add-in
What it does: The native Excel Solver Add-in lets you define an objective cell (to minimize/maximize), changing cells (decision variables), and constraint cells. You select a solving engine (Simplex LP, GRG Nonlinear, or Evolutionary) and click “Solve.” Solver then finds optimal variable values.
Strengths:
- Models are constructed using spreadsheet formulas (e.g.
=A1^2 + B1^2), not math formulas (e.g.x^2 + y^2) - Good for small-to-medium problems (up to ~200 decision variables)
- Three solving engines cover LP, smooth nonlinear, and non-smooth problems
Limitations:
- Spreadsheet formula setup can be tedious for large models
- Model size limits (~200 variables in standard Excel)
- Limited algorithm choices and no access to advanced features (stochastic programming, robust optimization)
- No support for advanced constraint types (conic, semi-definite)
- Slower than specialized solvers on large problems.
- No option to run on Excel’s Python server runtime for better performance.
When to use Excel Solver Add-in vs. this add-in: Use Solver Add-in for quick, small-to-medium optimization tasks directly in Excel without coding. Use this add-in’s functions for larger problems, advanced algorithms, custom models, or when you need programmatic control over the optimization process.
Goal Seek
What it does: Goal Seek adjusts a single input cell to make a formula cell reach a target value. It’s a simple, single-variable root-finding tool.
Example: “What price do I need to charge to achieve $1M annual revenue?” Goal Seek finds the price.
Limitations: Only one input variable; no constraints; no optimization.
When to use Goal Seek vs. this add-in: Goal Seek for simple one-variable target seeking; this add-in’s ROOT_SCALAR or MINIMIZE_SCALAR for more control, multiple solutions, or constrained problems.
Analysis ToolPak
What it does: Provides statistical analysis tools including regression (least-squares fitting), ANOVA, descriptive statistics, t-tests, and more.
Regression relevance: Linear regression is fundamentally a least-squares problem (minimize sum of squared residuals), which is a special case of optimization. The ToolPak’s regression function fits data to y = Xβ + ε.
Limitations: Only linear regression; no nonlinear regression, no constraint handling, no advanced fitting options.
When to use ToolPak vs. this add-in: ToolPak for basic linear regression; this add-in’s MINIMIZE for nonlinear curve fitting, constrained parameter estimation, or maximum likelihood estimation.
Data Tables and Scenario Manager
What they do: Data Tables systematically vary input cells and record outputs; Scenario Manager stores and switches between named sets of inputs.
Optimization relevance: Neither tool searches for optimal values; they visualize responses to predefined input combinations (sensitivity analysis, what-if analysis).
When to use vs. this add-in: Use Data Tables / Scenario Manager for exploratory analysis; use this add-in when you want to automatically find optimal values, not just evaluate grids of alternatives.
Related Microsoft Resources
For more information on Excel’s optimization tools: