Excel-based optimisation
Your team plans in Excel, and not every problem needs a Python application. We build the optimisation into the spreadsheet they already use.
- Free 30-minute call
- No commitment
- You own the code
In short: An Excel-based optimisation builds a planning model inside a spreadsheet, using Excel Solver or the OpenSolver add-in. The spreadsheet then computes the best plan itself, which suits small and medium problems and teams that already plan in Excel.
The problem
You keep the weekly production plan in a spreadsheet. You know every tab, every formula and every exception. Each week you try combinations by hand until the numbers look right. A new tool would mean training and licences. And the plan would move out of the sheet your team knows.
What changes
Today, the plan is as good as the combinations you had time to try. With our solution, one click runs the optimisation inside the same workbook. It returns the best plan it finds. The tabs and the data stay where they were. Your team keeps working the way it does today. The plan is now the best one the model found among all combinations.
How it works
We build optimisation models in the tools your team already uses. That means Excel Solver and OpenSolver, two add-ins that compute the best plan inside a spreadsheet. When a custom solver is needed, we write it in VBA, the programming language built into Excel. Excel is the right choice when problems are small to medium and users aren’t technical. It also suits a prototype, or a tool you want to pass on to colleagues as a plain Excel file.
When Excel Solver runs out of room
The built-in Solver stops at 200 decision variables, the cells it may change. The Solver’s maker states the limit on its website. Ten products across four weeks and five production lines already make 200. Its Simplex LP method handles linear models, and for whole-number decisions it adds branch and bound, a systematic search through the yes-or-no choices.
OpenSolver lifts the cap. It’s free, open source under the GPL licence, and runs the COIN-OR CBC solver from inside the same workbook. Its site claims no artificial size limits. How large a model can get then depends only on your computer’s memory and your patience. Check your Excel version first, though: it was built for Windows Excel 2007 to 2016, with limited support on the Mac.
Excel also has its limits, and we name them up front. The honest boundary: Excel stops working with large problems, real-time needs and data flowing in from many systems. Those belong in a Python application or a hosted optimisation service. We recommend the format that fits your problem, not the one we would prefer to build.
When it isn’t the answer
Excel also has its limits, and we name them up front. The honest boundary: Excel stops working with large problems, real-time needs and data flowing in from many systems.
Common questions.
Something else? Ask us directly.
The built-in Solver allows 200 decision variables, the cells it may change. The Solver’s maker documents that limit. You can work around it by fixing some cells and trying their values by hand, which gets tedious fast. OpenSolver has no artificial cap, and the Solver’s maker sells larger versions too.
If every formula in the model is linear, pick Simplex LP. It finds the true best plan, and with whole-number cells it adds branch and bound. GRG Nonlinear handles smooth curves but can stop at a local best: a plan that beats all similar plans but isn’t the best overall. Evolutionary is a genetic search for messy models with IF formulas. It’s the slowest, and it can’t prove that its plan is the best.
Yes, and the work isn’t lost. The formulation, meaning the decisions, the goal and the rules, carries over almost as it is. Your data can stay in the workbook, and Python can read it and write the plan back. Many projects start in Excel and move once the problem outgrows it.
Sometimes they can, and then we’ll say so. Standard tools are built for an average problem, though. With Excel-based optimisation, we start from yours: its size, its rules and the exceptions nobody wrote down. That is why the plan fits your operation better.
You don’t need either to start. We build the model from the data you’ve got, messy or not, and cleaning it up is part of the job. You get a documented system your team can maintain, and we train them until they can run it long after we’ve left.
It starts with a free 30-minute call about your operation and the model that would fit it. Then we build a working prototype and run it on your numbers. You’ll see results before you commit to anything bigger.
Similar pages.
Terms on this page
Every abbreviation explained in plain words, in our article “Metrics and methods”.
- OROperations research
- LPLinear programming
- Branch and bound
- CBCCOIN-OR CBC
- GRGExcel Solver, GRG Nonlinear and OpenSolver
- VBAVisual Basic for Applications
Tell us what you’re planning.
In a free 30-minute call with a specialist, you find out whether optimisation can help.
- Free, no commitment
- You talk to a specialist directly
- A clear go or no-go