Where Is Solver In Excel ? Manual Steps Into

Speaking of optimization, Excel Solver is at the center of it.

I plan to write a series of posts on this topic, but for starter here is a video on how to load Solver into your computer.

If there is a lot of extra code and you are worried about breaking it, you can always rerecord.

One of the topics I received the most questions on is the automating the solver to solve for multiple values.

There was a brief explanation in the previous article, but nothing dedicated to how to implement it from start to finish.

In this article well go in depth from start to finish covering the concepts and methods step by step.

By the end youll be able to automate any of your own personal models and scale your analysis.

To get the most out of this article you should be familiar with the basics of Excel Solver.

If you need to brush up, check out the latest webinar by this author, Jacob Fairclough, Excel Solver: Optimize Your Budgets Across Campaigns.

How well do it Well leverage the macro recorder in Excel to handle most of the actual code.

Where Is Solver In Excel ? Manual Steps Into

The record macro tool will allow Excel to convert our manual steps into VBA code.

Then we will create a loop to dynamically select and replace these values and run the solver again.

This will be semi-technical but through this style of development we can focus on the process and the concepts instead of focusing on how to write code.

Setting up the macro recorder The macro record functionality is found in the developer tab in the ribbon.

If you navigate to your ribbon options you will see a checkbox for the developer tab.

The screenshot below is for Mac but PC based Excel is similar.

If you have not installed the solver add-on you can install it via the Add-ins menu.

The Microsoft support site has instructions for all platforms, Load the Solver Add-in in Excel.

Recording the macro Now that the set up is complete we we are ready to record Set up your workspace appropriately with all your formulas and references.

Youll want to set up everything to the point before you build the solver model.

Give your macro a name and fill in the description if youd like.

Once this is complete go back to the developer tab and stop recording.

Itll be the same location as the button you used to start the recording.

Now that we have the base code we can clean it up and prep it for the next steps.

Clean up the code Now we can edit the code, remove any unnecessary parts, and make sure we are only using the essential pieces of the macro recording.

Now that we have our code editor open we can start making tweaks.

Removing Unnecessary Code If you happened to click through a bunch of other things while recording you can clear those out now and be left with something similar to below.

Where Is Solver In Excel For Mac?