Solver Samples already included in Excel – Excel Tips

Most of you must be familiar with Solver. It’s a built-in tool in Excel which helps you solve optimization problems like maximization of profit, minimizing cost of transportation, minimum cost staff scheduling etc. These problems are so widely encountered that people have made and taught specific samples to use when solving a specific type of problem with Solver. These samples ensure that you are working with the tool as efficiently as is possible. You might think “where can I find those samples?”. Or maybe you are an Excel enthusiast who wants to learn the use of Solver. All the help that you need is already there on your PC. And Daniel Fylstra is here to guide you!

Let’s get started

Step 0: Enabling Solver

If you already have Solver under your ‘Data’ tab, proceed to Step 1. If you are not sure how to access Solver, please follow the steps below:

1. Go to ‘File’ on the top-left corner and select ‘Options’. A window will open up.
2. Now select ‘Add-Ins’. You can see a list of active and inactive add-ins here.
3. Select ‘Excel Add-ins’ from the dropdown menu at the bottom and click on ‘Go…
4. Tick the checkbox next to ‘Solver Add-in’ and press ‘OK’.

You should be able to access the tool from the ‘Data’ tab on the ribbon under the ‘Analysis’ section.

 Opening the Samples

 

Follow the steps below to access the samples:

1. While in Excel, press Ctrl+O.
2. Now locate the drive where Microsoft Office is installed. It should be C:\ in most of the cases.
3. Go to Program Files (x86) > Microsoft Office > Office14 > SAMPLES
4. Now open SOLVSAMP.XLS

And there you have it! This file contains samples and guidelines on different types of widely encountered Solver problems like Product Mix, Transportation Problem, Investment Problem, Scheduling Problem etc. Also, it teaches you how to use Solver.

 Getting Started with the Samples

1. Read through the explanations given on the Quick Tour sheet to understand what type of problems one can use Solver for.

 Setting up Solver

 

1. Go to Product Mix sheet.
2. Click on the Target cell as shown in the diagram.
3. Now click on Solver. You would find that the problem is already set up. The objective, variable and constraint cells have been properly selected. Click on ‘Solve’ to arrive at the maximal profit.  You can read the provided description and this set up in Solver to learn about Product Mix problems and efficient ways of setting such problems up in Excel.
4. Browse through other sheets and you would see that differing layouts have been used for each of them. Read them, explore them and learn from them!

What’s next?

Whenever you are working through a Solver problem, use this samples file. And, most importantly, do not forget to SHARE this post with your friends and colleagues!! Exercise your brain today by learning something new. Cheers!