Excel users typically turn to Solver for optimization, but let’s face it—Solver isn’t exactly user-friendly, with its confusing menus, tricky constraints, and cryptic error messages. Luckily, there’s another option: Python in Excel’s Advanced Analysis.
But here’s the catch: Advanced Analysis expects neatly structured datasets with clear rows and columns. Optimization problems, however, usually involve exploring relationships between variables, calculations, and constraints, making them quite different from typical data analysis tasks.
That means we’ll need a creative workaround, but it’s also a great chance to rethink optimization and discover what’s possible with Excel and Python together. Ready? Open a blank workbook and type “Advanced Analysis with Python” into Copilot to launch Advanced Analysis and get started.
Example 1: Product optimization
Product optimization is a classic analytics problem, and Python in Excel gives us a surprisingly clean way to model it without Solver. In this example we’re working with a simple two-product scenario. I frame it almost like a word problem: lay out the parameters, constraints, and objective in plain language, then hand the entire setup to Copilot.
Copilot interprets the problem and returns the optimal solution:
The entire optimization model is translated into and then executed in Python. It’s helpful to look at the code Copilot generates behind the scenes:
If you know Python, you’ll notice it uses plain lists, tuples, and NumPy arrays rather than DataFrames, even though Python in Excel normally wraps most inputs and outputs as DataFrames. That difference introduces a little friction when you’re doing optimization inside Excel. You’ll may notice in the following examples that important final values like total profit or total cost don’t always appear in the results, because they don’t fit cleanly into the output DataFrame Copilot creates. When that happens, you may need to ask Copilot for those numbers directly or print them from the code yourself.
Still, the workflow is straightforward: describe the optimization problem clearly, pass it to Copilot, and let Python build and solve the model directly inside Excel.
Three-product optimization with added constraints
Here’s a slightly more realistic scenario. Instead of choosing between two products, we now have three: A, B, and C. Each one has different profit margins and different demands on labor and material, and now we add real-world constraints like minimum production and maximum capacity. Again, I describe the entire optimization problem in plain language, just as you’d see it in a textbook or case study, and hand that straight to Copilot.
Copilot interprets the structure, builds a Python optimization model underneath, and returns the production plan.
Python decides that the best mix is roughly 13 units of A, 49 units of B, and zero units of C. That result fully respects the labor and material limits, meets the minimum production requirement for A, and stays under the cap for C.
As with the previous example, you don’t automatically see the total profit or resource utilization in the output table. Those values exist in the Python environment (or could be easily produced), but they’re not displayed unless you explicitly ask for them.
Minimizing shipping costs across locations
This last example shifts from production planning to a classic transportation problem. We have two warehouses and three retail stores, each with its own shipping cost, capacity, and demand. Again, I describe the entire problem as a word-problem: the costs, the capacities, the store requirements, and the objective of minimizing total shipping cost.
Copilot takes that plain-language description, builds the underlying optimization model in Python, and returns the shipment plan.
Warehouse 1 fully supplies Store 1 and part of Store 3, while Warehouse 2 picks up the remaining demand for Stores 2 and 3. Some routes end up with zero shipments because the optimizer naturally avoids expensive or unnecessary paths. And just like before, the output appears as a small DataFrame because that’s the default format for Python in Excel. If you want additional information like the total minimized cost you’d need to ask Copilot for it or extract it directly from the Python code.
Want to check your results against mine? Download the solution workbook below:
In summary, while Excel users traditionally turn to Solver for optimization, Python in Excel’s Advanced Analysis offers an attractive alternative. This approach simplifies model building by allowing users to frame optimization scenarios clearly in everyday language, bypassing Solver’s often complex interface. Leveraging Python’s powerful analytical capabilities directly within Excel makes this method intuitive and accessible.
However, it’s important to recognize some limitations. The optimization packages available within Python in Excel—primarily SciPy, NumPy, and pandas—can effectively handle many scenarios similar to Solver’s linear and nonlinear optimization tasks. Yet, advanced capabilities such as integer programming (available via Solver’s GRG or Evolutionary engines) or convex optimization tasks typically handled by specialized libraries like PuLP or CVXOPT aren’t supported within the current Python environment in Excel.
Additionally, users may encounter issues where essential outputs, like total profits or minimized costs, aren’t immediately visible and require manual extraction from the Python environment or additional prompts to Copilot.
By clearly defining optimization problems in plain language and using Python libraries available in Excel, you can streamline complex modeling tasks and gain practical insights without leaving the familiar Excel interface.
Leave a Reply