problem solving on excel

Define and solve a problem by using Solver

Solver is a Microsoft Excel add-in program you can use for what-if analysis. Use Solver to find an optimal (maximum or minimum) value for a formula in one cell — called the objective cell — subject to constraints, or limits, on the values of other formula cells on a worksheet. Solver works with a group of cells, called decision variables or simply variable cells that are used in computing the formulas in the objective and constraint cells. Solver adjusts the values in the decision variable cells to satisfy the limits on constraint cells and produce the result you want for the objective cell.

Put simply, you can use Solver to determine the maximum or minimum value of one cell by changing other cells. For example, you can change the amount of your projected advertising budget and see the effect on your projected profit amount.

Example of a Solver evaluation

In the following example, the level of advertising in each quarter affects the number of units sold, indirectly determining the amount of sales revenue, the associated expenses, and the profit. Solver can change the quarterly budgets for advertising (decision variable cells B5:C5), up to a total budget constraint of $20,000 (cell F5), until the total profit (objective cell F7) reaches the maximum possible amount. The values in the variable cells are used to calculate the profit for each quarter, so they are related to the formula objective cell F7, =SUM (Q1 Profit:Q2 Profit).

1. Variable cells

2. Constrained cell

3. Objective cell

After Solver runs, the new values are as follows.

Define and solve a problem

Note:  If the Solver command or the Analysis group is not available, you need to activate the Solver add-in. See: How to activate the Solver add-in.

In the Set Objective box, enter a cell reference or name for the objective cell. The objective cell must contain a formula.

Do one of the following:

If you want the value of the objective cell to be as large as possible, click Max .

If you want the value of the objective cell to be as small as possible, click Min .

If you want the objective cell to be a certain value, click Value of , and then type the value in the box.

In the By Changing Variable Cells box, enter a name or reference for each decision variable cell range. Separate the non-adjacent references with commas. The variable cells must be related directly or indirectly to the objective cell. You can specify up to 200 variable cells.

In the Subject to the Constraints box, enter any constraints that you want to apply by doing the following:

In the Solver Parameters dialog box, click Add .

In the Cell Reference box, enter the cell reference or name of the cell range for which you want to constrain the value.

Click the relationship ( <= , = , >= , int , bin , or dif ) that you want between the referenced cell and the constraint.If you click int , integer appears in the Constraint box. If you click bin , binary appears in the Constraint box. If you click dif , alldifferent appears in the Constraint box.

If you choose <=, =, or >= for the relationship in the Constraint box, type a number, a cell reference or name, or a formula.

To accept the constraint and add another, click Add .

To accept the constraint and return to the Solver Parameter s dialog box, click OK . Note     You can apply the int , bin , and dif relationships only in constraints on decision variable cells.

You can change or delete an existing constraint by doing the following:

In the Solver Parameters dialog box, click the constraint that you want to change or delete.

Click Change and then make your changes, or click Delete .

Click Solve and do one of the following:

To keep the solution values on the worksheet, in the Solver Results dialog box, click Keep Solver Solution .

To restore the original values before you clicked Solve , click Restore Original Values .

You can interrupt the solution process by pressing Esc. Excel recalculates the worksheet with the last values that are found for the decision variable cells.

To create a report that is based on your solution after Solver finds a solution, you can click a report type in the Reports box and then click OK . The report is created on a new worksheet in your workbook. If Solver doesn't find a solution, only certain reports or no reports are available.

To save your decision variable cell values as a scenario that you can display later, click Save Scenario in the Solver Results dialog box, and then type a name for the scenario in the Scenario Name box.

Step through Solver trial solutions

After you define a problem, click Options in the Solver Parameters dialog box.

In the Options dialog box, select the Show Iteration Results check box to see the values of each trial solution, and then click OK .

In the Solver Parameters dialog box, click Solve .

In the Show Trial Solution dialog box, do one of the following:

To stop the solution process and display the Solver Results dialog box, click Stop .

To continue the solution process and display the next trial solution, click Continue .

Change how Solver finds solutions

In the Solver Parameters dialog box, click Options .

Choose or enter values for any of the options on the All Methods , GRG Nonlinear , and Evolutionary tabs in the dialog box.

Save or load a problem model

In the Solver Parameters dialog box, click Load/Save .

Enter a cell range for the model area, and click either Save or Load .

When you save a model, enter the reference for the first cell of a vertical range of empty cells in which you want to place the problem model. When you load a model, enter the reference for the entire range of cells that contains the problem model.

Tip:  You can save the last selections in the Solver Parameters dialog box with a worksheet by saving the workbook. Each worksheet in a workbook may have its own Solver selections, and all of them are saved. You can also define more than one problem for a worksheet by clicking Load/Save to save problems individually.

Solving methods used by Solver

You can choose any of the following three algorithms or solving methods in the Solver Parameters dialog box:

Generalized Reduced Gradient (GRG) Nonlinear     Use for problems that are smooth nonlinear.

LP Simplex     Use for problems that are linear.

Evolutionary     Use for problems that are non-smooth.

Important:  You should enable the Solver add-in first. For more information, see Load the Solver add-in .

In the following example, the level of advertising in each quarter affects the number of units sold, indirectly determining the amount of sales revenue, the associated expenses, and the profit. Solver can change the quarterly budgets for advertising (decision variable cells B5:C5), up to a total budget constraint of $20,000 (cell D5), until the total profit (objective cell D7) reaches the maximum possible amount. The values in the variable cells are used to calculate the profit for each quarter, so they are related to the formula objective cell D7, =SUM(Q1 Profit:Q2 Profit).

Click Data > Solver .

In Set Objective , enter a cell reference or name for the objective cell.

Note:  The objective cell must contain a formula.

In the By Changing Variable Cells box, enter a name or reference for each decision variable cell range. Separate the nonadjacent references with commas.

The variable cells must be related directly or indirectly to the objective cell. You can specify up to 200 variable cells.

In the Subject to the Constraints box, add any constraints that you want to apply.

To add a constraint, follow these steps:

On the <= relationship pop-up menu, select the relationship that you want between the referenced cell and the constraint.If you choose <= , = , or >= , in the Constraint box, type a number, a cell reference or name, or a formula.

Note:  You can only apply the int, bin, and dif relationships in constraints on decision variable cells.

Click Solve , and then do one of the following:

To interrupt the solution process, press ESC . Excel recalculates the sheet with the last values that are found for the adjustable cells.

To create a report that is based on your solution after Solver finds a solution, you can click a report type in the Reports box and then click OK . The report is created on a new sheet in your workbook. If Solver doesn't find a solution, the option to create a report is unavailable.

To save your adjusting cell values as a scenario that you can display later, click Save Scenario in the Solver Results dialog box, and then type a name for the scenario in the Scenario Name box.

After you define a problem, in the Solver Parameters dialog box, click Options .

Select the Show Iteration Results check box to see the values of each trial solution, and then click OK .

Click Options , and then in the Options or Solver Options dialog box, choose one or more of the following options:

In the Solver Parameters dialog box, click Solve or Close .

Click Load/Save , enter a cell range for the model area, and then click either Save or Load .

Tip:  You can save the last selections in the Solver Parameters dialog box with a sheet by saving the workbook. Each sheet in a workbook may have its own Solver selections, and all of them are saved. You can also define more than one problem for a sheet by clicking Load/Save to save problems individually.

On the Select a Solving Method pop-up menu, select one of the following:

Note:  Portions of the Solver program code are copyright 1990-2010 by Frontline Systems, Inc. Portions are copyright 1989 by Optimal Methods, Inc.

Because add-in programs aren’t supported in Excel for the web, you won’t be able to use the Solver add-in to run what-if analysis on your data to help you find optimal solutions.

If you have the Excel desktop application, you can use the Open in Excel button to open your workbook to use the Solver add-in .

More help on using Solver

For more detailed help on Solver contact:

Frontline Systems, Inc. P.O. Box 4288 Incline Village, NV 89450-4288 (775) 831-0300 Web site: http://www.solver.com E-mail: [email protected] Solver Help at www.solver.com .

Portions of the Solver program code are copyright 1990-2009 by Frontline Systems, Inc. Portions are copyright 1989 by Optimal Methods, Inc.

Need more help?

You can always ask an expert in the Excel Tech Community  or get support in  Communities .

Using Solver for capital budgeting

Using Solver to determine the optimal product mix

Introduction to what-if analysis

Overview of formulas in Excel

How to avoid broken formulas

Detect errors in formulas

Keyboard shortcuts in Excel

Excel functions (alphabetical)

Excel functions (by category)

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Microsoft 365 subscription benefits

Microsoft 365 training

Microsoft security

Accessibility center

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Ask the Microsoft Community

Microsoft Tech Community

Windows Insiders

Microsoft 365 Insiders

Was this information helpful?

Thank you for your feedback.

• Ablebits blog
• Financial functions

How to use Solver in Excel with examples

The tutorial explains how to add and where to find Solver in different Excel versions, from 2016 to 2003. Step-by-step examples show how to use Excel Solver to find optimal solutions for linear programming and other kinds of problems.

Everyone knows that Microsoft Excel contains a lot of useful functions and powerful tools that can save you hours of calculations. But did you know that it also has a tool that can help you find optimal solutions for decision problems?

In this tutorial, we are going to cover all essential aspects of the Excel Solver add-in and provide a step-by-step guide on how to use it most effectively.

What is Excel Solver?

Excel Solver belongs to a special set of commands often referred to as What-if Analysis Tools. It is primarily purposed for simulation and optimization of various business and engineering models.

The Excel Solver add-in is especially useful for solving linear programming problems, aka linear optimization problems, and therefore is sometimes called a linear programming solver . Apart from that, it can handle smooth nonlinear and non-smooth problems. Please see Excel Solver algorithms for more details.

How to add Solver to Excel

The Solver add-in is included with all versions of Microsoft Excel beginning with 2003, but it is not enabled by default.

To add Solver to your Excel, perform the following steps:

• In Excel 2010 - Excel 365, click File > Options . In Excel 2007, click the Microsoft Office button, and then click Excel Options .

To get Solver on Excel 2003 , go to the Tools menu, and click Add-Ins . In the Add-Ins available list, check the Solver Add-in box, and click OK .

Where is Solver in Excel?

Where is Solver in Excel 2003?

Now that you know where to find Solver in Excel, open a new worksheet and let's get started!

How to use Solver in Excel

Before running the Excel Solver add-in, formulate the model you want to solve in a worksheet. In this example, let's find a solution for the following simple optimization problem.

Problem . Supposing, you are the owner of a beauty salon and you are planning on providing a new service to your clients. For this, you need to buy a new equipment that costs $40,000, which should be paid by instalments within 12 months.

Goal : Calculate the minimal cost per service that will let you pay for the new equipment within the specified timeframe.

And now, let's see how Excel Solver can find a solution for this problem.

1. Run Excel Solver

2. define the problem.

The Solver Parameters window will open where you have to set up the 3 primary components:

• Objective cell

Variable cells

Constraints.

Exactly what does Excel Solver do with the above parameters? It finds the optimal value (maximum, minimum or specified) for the formula in the Objective cell by changing the values in the Variable cells, and subject to limitations in the Constraints cells.

The Objective cell ( Target cell in earlier Excel versions) is the cell containing a formula that represents the objective, or goal, of the problem. The objective can be to maximize, minimize, or achieve some target value.

Variable cells ( Changing cells or Adjustable cells in earlier versions) are cells that contain variable data that can be changed to achieve the objective. Excel Solver allows specifying up to 200 variable cells.

In this example, we have a couple of cells whose values can be changed:

• Projected clients per month (B4) that should be less than or equal to 50; and
• Cost per service (B5) that we want Excel Solver to calculate.

The Excel Solver Constrains are restrictions or limits of the possible solutions to the problem. To put it differently, constraints are the conditions that must be met.

To add a constraint(s), do the following:

• Click the Add button right to the " Subject to the Constraints " box.

• In the Constraint window, enter a constraint.
• Click the Add button to add the constraint to the list.

• Continue entering other constraints.
• After you have entered the final constraint, click OK to return to the main Solver Parameters window.

Excel Solver allows specifying the following relationships between the referenced cell and the constraint.

• Less than or equal to , equal to , and greater than or equal to . You set these relationships by selecting a cell in the Cell Reference box, choosing one of the following signs: <= , =, or >= , and then typing a number, cell reference / cell name, or formula in the Constraint box (please see the above screenshot).
• Integer . If the referenced cell must be an integer, select int , and the word integer will appear in the Constraint box.
• Different values . If each cell in the referenced range must contain a different value, select dif , and the word AllDifferent will appear in the Constraint box.
• Binary . If you want to limit a referenced cell either to 0 or 1, select bin , and the word binary will appear in the Constraint box.

To edit or delete an existing constraint do the following:

• In the Solver Parameters dialog box, click the constraint.
• To modify the selected constraint, click Change and make the changes you want.
• To delete the constraint, click the Delete button.

In this example, the constraints are:

• B3=40000 - cost of the new equipment is $40,000.
• B4<=50 - the number of projected patients per month in under 50.

3. Solve the problem

After you've configured all the parameters, click the Solve button at the bottom of the Solver Parameters window (see the screenshot above) and let the Excel Solver add-in find the optimal solution for your problem.

Depending on the model complexity, computer memory and processor speed, it may take a few seconds, a few minutes, or even a few hours.

The Solver Result window will close and the solution will appear on the worksheet right away.

• If the Excel Solver has been processing a certain problem for too long, you can interrupt the process by pressing the Esc key. Excel will recalculate the worksheet with the last values found for the Variable cells.
• To get more details about the solved problem, click a report type in the Reports box, and then click OK . The report will be created on a new worksheet:

Excel Solver examples

Below you will find two more examples of using the Excel Solver addin. First, we will find a solution for a well-known puzzle, and then solve a real-life linear programming problem.

Excel Solver example 1 (magic square)

I believe everyone is familiar with "magic square" puzzles where you have to put a set of numbers in a square so that all rows, columns and diagonals add up to a certain number.

For instance, do you know a solution for the 3x3 square containing numbers from 1 to 9 where each row, column and diagonal adds up to 15?

It's probably no big deal to solve this puzzle by trial and error, but I bet the Solver will find the solution faster. Our part of the job is to properly define the problem.

With all the formulas in place, run Solver and set up the following parameters:

• Set Objective . In this example, we don't need to set any objective, so leave this box empty.
• Variable Cells . We want to populate numbers in cells B2 to D4, so select the range B2:D4.
• $B$2:$D$4 = AllDifferent - all of the Variable cells should contain different values.
• $B$2:$D$4 = integer - all of the Variable cells should be integers.
• $B$5:$D$5 = 15 - the sum of values in each column should equal 15.
• $E$2:$E$4 = 15 - the sum of values in each row should equal 15.
• $B$7:$B$8 = 15 - the sum of both diagonals should equal 15.

Excel Solver example 2 (linear programming problem)

This is an example of a simple transportation optimization problem with a linear objective. More complex optimization models of this kind are used by many companies to save thousands of dollars each year.

Problem : You want to minimize the cost of shipping goods from 2 different warehouses to 4 different customers. Each warehouse has a limited supply and each customer has a certain demand.

Goal : Minimize the total shipping cost, not exceeding the quantity available at each warehouse, and meeting the demand of each customer.

Source data

Formulating the model

To define our linear programming problem for the Excel Solver, let's answer the 3 main questions:

• What decisions are to be made? We want to calculate the optimal quantity of goods to deliver to each customer from each warehouse. These are Variable cells (B7:E8).
• What are the constraints? The supplies available at each warehouse (I7:I8) cannot be exceeded, and the quantity ordered by each customer (B10:E10) should be delivered. These are Constrained cells .
• What is the goal? The minimal total cost of shipping. And this is our Objective cell (C12).

To make our transportation optimization model easier to understand, create the following named ranges:

The last thing left for you to do is configure the Excel Solver parameters:

• Objective: Shipping_cost set to Min
• Variable cells: Products_shipped
• Constraints: Total_received = Ordered and Total_shipped <= Available

How to save and load Excel Solver scenarios

When solving a certain model, you may want to save your Variable cell values as a scenario that you can view or re-use later.

For example, when calculating the minimal service cost in the very first example discussed in this tutorial, you may want to try different numbers of projected clients per month and see how that affects the service cost. At that, you may want to save the most probable scenario you've already calculated and restore it at any moment.

Saving an Excel Solver scenario boils down to selecting a range of cells to save the data in. Loading a Solver model is just a matter of providing Excel with the range of cells where your model is saved. The detailed steps follow below.

Saving the model

To save the Excel Solver scenario, perform the following steps:

• Open the worksheet with the calculated model and run the Excel Solver.

• Excel will save your current model, which may look something similar to this:

Loading the saved model

When you decide to restore the saved scenario, do the following:

• In the Solver Parameters window, click the Load/Save button.

• This will open the main Excel Solver window with the parameters of the previously saved model. All you need to do is to click the Solve button to re-calculate it.

Excel Solver algorithms

When defining a problem for the Excel Solver, you can choose one of the following methods in the Select a Solving Method dropdown box:

• GRG Nonlinear. Generalized Reduced Gradient Nonlinear algorithm is used for problems that are smooth nonlinear, i.e. in which at least one of the constraints is a smooth nonlinear function of the decision variables. More details can be found here .
• LP Simplex . The Simplex LP Solving method is based the Simplex algorithm created by an American mathematical scientist George Dantzig. It is used for solving so called Linear Programming problems - mathematical models whose requirements are characterized by linear relationships, i.e. consist of a single objective represented by a linear equation that must be maximized or minimized. For more information, please check out this page .
• Evolutionary . It is used for non-smooth problems, which are the most difficult type of optimization problems to solve because some of the functions are non-smooth or even discontinuous, and therefore it's difficult to determine the direction in which a function is increasing or decreasing. For more information, please see this page .

This is how you can use Solver in Excel to find the best solutions for your decision problems. At the end of this post, you can download the sample workbook with all the examples discussed in this tutorial and reverse-engineer them for better understanding. I thank you for reading and hope to see you on our blog next week.

Practice workbook for download

You may also be interested in.

• Using Excel Goal Seek for What-If analysis
• Excel Copilot with examples
• Linear regression analysis in Excel
• Microsoft Excel formulas with examples
• How to use VLOOKUP & SUM or SUMIF functions in Excel

Table of contents

Load the Solver Add-in

To load the solver add-in, execute the following steps.

1. On the File tab, click Options.

2. Under Add-ins, select Solver Add-in and click on the Go button.

3. Check Solver Add-in and click OK.

4. You can find the Solver on the Data tab, in the Analyze group.

Formulate the Model

The model we are going to solve looks as follows in Excel.

1. To formulate this linear programming model, answer the following three questions.

c. What is the overall measure of performance for these decisions? The overall measure of performance is the total profit of the three products, so the objective is to maximize this quantity.

2. To make the model easier to understand, create the following named ranges .

3. Insert the following three SUMPRODUCT functions.

Explanation: The amount of capital used equals the sumproduct of the range C7:E7 and OrderSize. The amount of storage used equals the sumproduct of the range C8:E8 and OrderSize. Total Profit equals the sumproduct of UnitProfit and OrderSize.

Trial and Error

With this formulation, it becomes easy to analyze any trial solution.

For example, if we order 20 bicycles, 40 mopeds and 100 child seats, the total amount of resources used does not exceed the amount of resources available. This solution has a total profit of 19000.

It is not necessary to use trial and error. We shall describe next how the Excel Solver can be used to quickly find the optimal solution.

Solve the Model

To find the optimal solution , execute the following steps.

1. On the Data tab, in the Analyze group, click Solver .

Enter the solver parameters (read on). The result should be consistent with the picture below.

You have the choice of typing the range names or clicking on the cells in the spreadsheet.

2. Enter TotalProfit for the Objective.

3. Click Max.

4. Enter OrderSize for the Changing Variable Cells.

5. Click Add to enter the following constraint.

6. Check 'Make Unconstrained Variables Non-Negative' and select 'Simplex LP'.

7. Finally, click Solve.

The optimal solution:

Conclusion: it is optimal to order 94 bicycles and 54 mopeds. This solution gives the maximum profit of 25600. This solution uses all the resources available. Try it yourself. Download the Excel file and find the optimal solution.

1/8 Completed! Learn much more about the solver > Go to Next Chapter: Analysis ToolPak

Learn more, it's easy

• Transportation Problem
• Assignment Problem
• Shortest Path Problem
• Maximum Flow Problem
• Capital Investment
• Sensitivity Analysis
• System of Linear Equations

Download Excel File

• solver.xlsx

Next Chapter

• Analysis ToolPak

Follow Excel Easy

Become an Excel Pro

• 300 Examples

Solver • © 2010-2024 Excel is Awesome, we'll show you: Introduction • Basics • Functions • Data Analysis • VBA

• Courses Business Charts in Excel Excel Essentials for the Real World Fast Track to Power BI View all Courses
• For Business

• Power Excel
• Dashboards, Charts & Features
• VBA & Scripts

Create Business charts that grab attention AND auto-update. Wow your coworkers and managers with smart time-saving techniques.

Learn time-saving hacks to work smarter in Excel. Our members also consistently report a newfound enthusiasm for using Excel after taking our course.

Stay ahead of the game in 2024. Get access to our best-selling Power BI course now and become a highly sought-after Power BI professional. This course gets you started in Power BI – Fast!

Excel Solver – Example and Step-by-Step Explanation

Tired of struggling with complex problems in Excel? Excel Solver can help.

This powerful tool makes finding the best solutions easy. Unlike Goal Seek or the What-If tool, Solver goes beyond simple scenarios, tackling more complex optimization problems.

In this blog post, you’ll learn how to use Excel Solver with a simple example. Discover how it saves you time and improves your results. Boost your productivity and make better decisions with Excel Solver.

Keep reading to see how this feature can transform your workflow!

Watch video tutorial

In this tutorial:

• What is Excel Solver?
• How to Add Solver to Excel
• How to Use Solver in Excel - Example
• Types of Solving Methods in Excel Solver
• Using Solver for Complex What-If Problems with Constraints
• Tips for Using Solver
• Download the Workbook

Grab our practice workbook 👉 HERE and follow along:

Imagine you want to maximize your profit by adjusting prices and costs while keeping prices and costs within specific limits. Or, you need to allocate your remaining budget across multiple projects with certain spending constraints. Excel Solver can help you achieve these goals efficiently.

Excel Solver is a powerful tool in Microsoft Excel that helps you find the best solutions by adjusting multiple inputs. Use Solver in Excel to maximize or minimize outcomes like profit, cost, or efficiency.

Key Features of Excel Solver

• Optimization : Solver helps you find the best values for your inputs to get the desired results. Increase profits, reduce costs, or meet specific targets easily.
• Multiple Variables : Adjust several inputs at once, making Solver perfect for complex problems.
• Constraints : Set limits for your inputs, such as keeping prices within a range or ensuring numbers are whole.
• GRG Nonlinear: Best for nonlinear problems, finding a local best solution.
• Simplex LP: Ideal for linear problems.
• Evolutionary: Great for complex problems, finding a global best solution.

Often our students ask: Where is Solver in Excel?

Did you know the Microsoft Solver add-in is included with all versions of Excel since 2003, but it’s not enabled by default? Here’s how you can quickly activate it:

• Open Excel: Start by opening your Excel application.
• Go to File : Click on the “File” tab in the top left corner.
• Select Options : Choose “Options” from the drop-down menu.
• Choose Add-ins : In the Excel Options window, click on “Add-ins”.
• Manage Add-ins : At the bottom of this window, find the “Manage” box. Select “Excel Add-ins” and click “Go”.

• 6. Enable Solver : Check the box next to “Solver Add-in”.
• 7. Confirm: Click “OK” to activate Solver.

Now, you can find Solver under the “Data” tab in the “Analyze” section.

How to Use Solver in Excel – Example

Goal Seek is great, but it only changes one input variable. For more complex problems with multiple variables, Solver is the tool you need.

Here’s a simple example:

You want to achieve a target income of 2,000 by adjusting the units sold and the price per unit.

• Units Sold:  Input variable.
• Price per Unit : Input variable.
• Revenue : Calculated as Units Sold * Price per Unit
• Cost per unit : A fixed number.
• Total Costs : Calculated as Units Sold * Cost per Unit.
• Income : Calculated as Revenue – Total Costs.

Constraints

• Units sold must be a whole number.
• Price per unit should be between 3 and 4.

Using Solver in Excel

When you open the Solver Parameters window, you need to set up three primary components: the objective cell, variable cells, and constraints. Here’s how to do it:

Set the Objective

Objective cell: This is the cell that contains the formula you want to optimize. For example, if you want to maximize profit or minimize costs, select the cell with that calculation.

In our example, the objective cell is $B$11, which calculates the income.

• Go to the Data tab, find the Analyze section, and click Solver. Set the objective cell ($B$11) to a value of 2,000.

Select Variable Cells

These are the cells that Solver will change to achieve the objective. Highlight the cells you want to adjust.

• For our example, these are the units sold and the price per unit, so we select $B$3:$B$4 and $B$8.

Add Constraints

Constraints are the limits you set for your variable cells. Click “Add” in the Solver Parameters window to specify these limits. For our example, set the units sold to be a whole number (integer) and the price per unit to be between 3 and 4.

• Click “Add” to set limits. Specify that units sold must be an integer and the price per unit should be between 3 and 4.

• Choose Solving Method: Select the appropriate solving method (e.g., GRG Nonlinear).

Click on Solve. The Solver Results window will pop up, saying that it has found a solution. You will notice that the values of the input cells have changed but the formulas in your calculated cells have been retained. You are given the option to accept the new values by clicking on OK, or reject them by clicking on Cancel.

Excel Solver offers three methods to find the best solution for your problem. Here’s a quick overview:

GRG Nonlinear

• Best For: Nonlinear problems.
• How It Works: Finds a local optimal solution.
• Default Method: This is the most commonly used method and is set as the default.
• Best For: Linear problems.
• How It Works: Efficiently solves problems with linear relationships.

Evolutionary

• Best For: Complex and non-smooth nonlinear problems.
• How It Works: Searches for a global optimal solution, which can take longer to run compared to GRG Nonlinear.

Solver can be a powerful tool when you need to allocate a budget among multiple projects, especially with specific constraints. Here’s an example to illustrate how to use Solver for this purpose:

You need to distribute a budget across several projects, with the following constraints:

• Extra costs should not exceed 1,000.
• Fixed costs must remain at 2,000.
• Total project cost for each project should not exceed 9,600.

Setting Up Solver

Set objective :.

• Go to the “Data” tab, find the “Analyze” section, and click on “Solver”. Set the objective cell ($B$14) to a value of 65,000.

Select Variable Cells:

Highlight the cells that Solver can change:

• Extra costs: $D$5:$D$13
• Production costs: $B$5:$B$13 (we choose Production Cost instead of Total Costs because the latter is a calculated field).

Add Constraints:

Click “Add” in the Solver Parameters window to set the following constraints:

• Extra costs ($D$5:$D$13) must be less than or equal to 1,000 ($E$18).
• Total project cost ($E$5:$E$13) must be less than or equal to 9,600 ($E$20).

Solve: Choose the appropriate solving method (e.g., GRG Nonlinear) and click “Solve”. Solver will find the best solution, adjusting the input cells within the defined constraints.

• Start Simple: Begin with straightforward scenarios and gradually add complexity.
• Experiment: Try different settings and constraints to see how they affect the outcome.

Enhance your learning experience by downloading our workbook. Practice the techniques discussed in real-time and master the Excel Solver tool with hands-on examples. Download the workbook here and start applying what you’ve learned directly in Excel.

Leila Gharani

I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.

My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.

Need help deciding?

Find your ideal course with this quick quiz. Takes one minute.

Featured Course

Business Charts in Excel

Excel Essentials for the Real World

Visually Effective Excel Dashboards

Master Excel Power Query – Beginner to Pro

Featured tutorials.

Black Belt Excel Package

You might also like...

How to Add, Subtract, Multiply and Divide in Excel

How to Merge Cells in Excel: Simple Steps and Best Practices

How to Use the Excel TRANSLATE Function

EXCLUSIVE FREE NEWSLETTER

Join between the sheets.

Kickstart your week with our free newsletter covering Excel hacks, Power BI tips, and the latest in AI. You get to stay updated and get all the insights you need, delivered straight to your inbox.

You can unsubscribe anytime of course.

Stay Ahead with Weekly Insights!

Dive into Excel, AI and other essential tech news:

carefully crafted for the modern professional.

Success! Now check your email to confirm your subscription.

There was an error submitting your subscription. Please try again.

• Create an account

• Business Analyst
• Mathematical Optimization
• Risk Analysis and Simulation
• Data Science Machine Learning
• Business Rules / Decision Tables
• Deployment - Business Value
• By Functional Area
• By Industry
• Analytic Solver Overview
• Analytic Solver Optimization
• Analytic Solver Simulation
• Analytic Solver Data Science
• Analytic Solver Academy
• RASON Decision Services
• Solver Engines
• Optimization and Simulation
• Forecasting and Data Mining
• Case Studies
• Optimization Webinar
• Simulation Webinar
• Data Science Webinar
• Optimization Tutorials
• Simulation Tutorials
• Data Science Tutorials
• Finance Examples
• Investment Examples
• Production Examples
• Distribution Examples
• Purchasing Examples
• Scheduling Examples
• Video Demos
• Technical Support
• Consulting Help
• Academy Courses
• Excel Solver Help
• Data Science Help
• Excel User Guides
• SDK User Guides
• Recommended Books
• Product Catalog
• Types of Licenses
• License Agreement
• Limited Warranty
• Standard vs Custom Terms
• Invoicing Payment

Excel Solver Tutorial - Step by Step Easy to use guide for Excel's Solver

Using excel's built-in solver - product mix example, understanding the excel solver dialogs.

To let the Excel Solver know which cells on the worksheet represent the decision variables, constraints and objective function, we click Solver button on the Excel Data tab, or the Premium Solver button on the Add-Ins tab, which displays the Solver Parameters dialog. In the Set Objective (or Set Target Cell) edit box, we type or click on cell F5, the objective function. In the By Changing Variable Cells edit box, we type B4:E4 or select these cells with the mouse. (Click on the image to see it full-size.)

To add the constraints, we click on the Add button in the Solver Parameters dialog and select cells F8:F11 in the Cell Reference edit box (the left hand side ), and select cells G8:G11 in the Constraint edit box (the right hand side ); the default relation <= is OK.  (Click on the image to see t full-size.)

We choose the Add button again (either from the Add Constraint dialog above, or from the main Solver Parameters dialog) to define the non-negativity constraint on the decision variables. (Alternatively, we can check the Make Unconstrainted Variables Non-Negative option in the Solver Parameters dialog.)

When we've completely entered the problem, the Solver Parameters dialog appears as shown below. This is the Excel Solver dialog from Excel 2010; the Solver in earlier versions of Excel have similar elements.  Frontline's Analytic Solver products can emulate either style, and they also offer a new Ribbon-based user interface.  (Click on the image to see it full-size.)

Finding and Using the Solution

To find the optimal solution, we simply click on the Solve button . After a moment, the Excel Solver returns the optimal solution in cells B4 through E4. This means that we should build 23 pallets of Tahoe panels, 15 pallets of Pacific panels, 39 pallets of Savannah panels, and 0 pallets of Aspen panels. This results in a total profit of $58,800 (shown in cell F5). (Click on the image to see it full-size.)

The message "Solver found a solution" appears in the Solver Results dialog, as shown above. (Click on the image to see it full size).  We now click on "Answer" in the Reports list box to produce an Answer Report, and click OK to keep the optimal solution values in cells B4:E4.

After a moment, the Solver creates another worksheet containing an Answer Report , like the one below, and inserts it to the left of the problem worksheet in the Excel workbook.  (Click on the image to see it full-size.)

This report shows the original and final values of the objective function and the decision variables, as well as the status of each constraint at the optimal solution. Notice that the constraints on glue, pressing, and pine chips are binding and have a slack value of 0. The optimal solution would use up all of these resources; however, there were 28,000 pounds of oak chips left over. If we could obtain additional glue, pressing capacity, or pine chips we could further increase total profits, but extra oak chips would not help in the short run.

There's a whole world beyond Excel Solver: Upgrade to Analytic Solver and get greater solving power, wizards and Guided Mode, an AI Agent that answers your questions, plus over 200 example models, extensive Help and user guides, access to our Solver.Academy courses, expert tech support -- plus Monte Carlo simulation and risk analysis, decision trees, data mining, text mining and machine learning, and more! It's an incredible offer, and you can try it for free . Learn more about Analytic Solver Click Here

Learning More

If you've gotten to this point, congratulations! You've successfully set up and solved a simple optimization problem using Microsoft Excel.  If you'd like, you can see how to set up and solve the same Product Mix problem using Risk Solver Platform in Excel or using a Visual Basic .NET program that calls Frontline's Solver Platform SDK.  If you haven't yet read the other parts of the tutorial, you may want to return to the Tutorial Start and read the overviews "What are Solvers Good For?", "How Do I Define a Model?", "What Kind of Solution Can I Expect?" and "What Makes a Model Hard to Solve?"

This was an example of a linear programming problem.  Other types of optimization problems may involve quadratic programming, mixed-integer programming, constraint programming, smooth nonlinear optimization, and nonsmooth optimization.  To learn more, click Optimization Problem Types .  For a more advanced explanation of linearity and sparsity in optimization problems, continue with our Advanced Tutorial .

How to Use Microsoft Excel Solver – A Comprehensive Guide

Microsoft Excel Solver is a powerful tool that can help you solve complex problems by finding the optimal solution. It’s like having a personal assistant that can crunch numbers and find the best answer to your question. Whether you’re trying to figure out the most cost-effective way to ship products or how to allocate resources in a project, Solver can help. All you need is a basic understanding of Excel and a clear idea of the problem you want to solve.

Step by Step Tutorial: How to Use Microsoft Excel Solver

Before we dive into the steps, let’s briefly talk about what we’re going to achieve. We’ll set up a problem in Excel, define our objective, set constraints, and let Solver find the best solution. Ready? Let’s go!

Step 1: Open Solver

Open Solver from the Data tab in Excel.

Solver is not displayed by default in Excel. If you don’t see it, you may need to enable it from the ‘Add-ins’ options.

Step 2: Set Your Objective

Select the cell with the formula you want to optimize.

This is what Solver will work towards improving, whether it’s maximizing profit, minimizing cost, or achieving a specific value.

Step 3: Define the Variable Cells

Choose the cells that Solver can change to reach the objective.

These are usually the inputs or decision variables of your problem that you’re willing to adjust.

Step 4: Add Constraints

Click ‘Add’ to set constraints for the variable cells.

Constraints are the rules that your solution must follow, like not exceeding a budget or ensuring a minimum production quantity.

Step 5: Solve the Problem

Press the ‘Solve’ button and let Solver do its magic.

Solver will run various calculations to find the optimal solution within your set constraints.

After completing these steps, Solver will present you with a solution. If it finds an optimal solution, it will ask if you want to keep the new values or restore the original ones. If no solution is found, it will let you know that as well.

Tips for Using Microsoft Excel Solver

• Always start with a clear understanding of the problem you want to solve.
• Make sure your objective function and constraints are correctly set up in Excel.
• Use the ‘Evolutionary’ solving method for problems that are not linear.
• If Solver is taking too long to find a solution, try simplifying the problem or constraints.
• Remember to save your work before running Solver, just in case.

Frequently Asked Questions

What is microsoft excel solver used for.

Solver is used to find the optimal solution for complex problems in Excel, such as resource allocation, budgeting, and scheduling.

Can Solver handle non-linear problems?

Yes, by using the ‘Evolutionary’ solving method, Solver can handle non-linear problems.

Is Solver available in all versions of Excel?

Solver is available in most versions of Excel, but it might need to be enabled from the ‘Add-ins’ menu.

What do I do if Solver can’t find a solution?

Try checking your constraints and objective function for errors, or consider simplifying the problem.

Can Solver handle multiple objectives?

No, Solver is designed to handle one objective at a time. However, you can run Solver multiple times with different objectives.

• Open Solver from the Data tab.
• Set your objective by selecting the target cell.
• Define the variable cells.
• Add constraints for the variable cells.
• Solve the problem.

In essence, Microsoft Excel Solver is like a Swiss Army knife for problem-solving. It’s an indispensable tool that can take your Excel skills to a whole new level, helping you make data-driven decisions efficiently. With the step-by-step tutorial you’ve just gone through, you should now feel confident in tackling problems that seemed daunting before. Remember, like any tool, practice makes perfect. So, why not challenge yourself with a real-world problem you’ve been wrestling with and let Solver come to the rescue? And if you ever find yourself in a tough spot, just come back to this article for a quick refresher on how to use Microsoft Excel Solver. Happy solving!

Matthew Burleigh has been writing tech tutorials since 2008. His writing has appeared on dozens of different websites and been read over 50 million times.

After receiving his Bachelor’s and Master’s degrees in Computer Science he spent several years working in IT management for small businesses. However, he now works full time writing content online and creating websites.

His main writing topics include iPhones, Microsoft Office, Google Apps, Android, and Photoshop, but he has also written about many other tech topics as well.

Read his full bio here.

Share this:

Join our free newsletter.

Featured guides and deals

You may opt out at any time. Read our Privacy Policy

Related posts:

• How to Make All Columns the Same Width in Excel 2013
• How to Use Variables in Excel: A Step-by-Step Guide
• How to Merge Cells in Word 2016 Tables
• How to Use Excel’s Functions to Easily Solve Systems of Equations
• How to AutoFit All Columns in Excel 2013 (An Easy 4 Step Guide)
• How to Merge Cells in Excel 2010
• How to Calculate Percentage in Excel
• How to Use the AutoFit Excel Shortcut for Columns and Rows
• How to Subtract in Excel 2013 with a Formula (An Easy 4 Step Guide)
• How to Color Cells in Excel
• What Are Three Ways to Change Column Width in Excel?
• How to Remove First Character in Excel 2013 Cell
• Can I Convert a Microsoft Word Table to Excel?
• How to Do a Multivariate Regression in Excel: 2024 Guide
• How to Expand Cells Horizontally in Excel 2013
• How to Expand All Rows in Excel 2013
• How to Enlarge Cells in Excel 2010
• Microsoft Excel Skills to Know When Job Hunting
• How to Combine Three Columns Into One in Excel
• How to Merge Cells in Google Sheets

100 Most Popular Courses For September

Harvard and MIT’s $800 Million Mistake: The Triple Failure of 2U, edX, and Axim Collaborative

The future of Coursera’s only credible alternative for universities rests in the hands of 2U’s creditors.

• 1000s of Free Digital Marketing Courses
• 10 Best Data Analysis Courses for 2024: From Data to Insights
• Replacing Bad Study Habits with Effective Learning Techniques
• [2024] 1800+ Coursera Courses Still Completely Free
• 7 Best Autodesk Maya Courses for 2024: Exploring 3D Animation

600 Free Google Certifications

Most common

Popular subjects.

Communication Skills

Project Management

Popular courses

Educación de calidad para todos: equidad, inclusión y atención a la diversidad

Early Childhood Care and Education

Introduction to Financial Accounting

Organize and share your learning with Class Central Lists.

View our Lists Showcase

Class Central is learner-supported. When you buy through links on our site, we may earn an affiliate commission.

Problem Solving with Excel

PwC via Coursera Help

• Learn How to Sign up to Coursera courses for free
• 1800+ Coursera Courses That Are Still Completely Free
• Overview of Excel
• In this module, you will learn the basics of Excel navigation and Excel basic functionality. You will learn how to navigate the basic Excel screen including using formulas, subtotals and text formatting. We will provide you an opportunity to perform a problem solving exercise using basic Excel skills. Note: We recommend viewing videos in full-screen mode by clicking the double arrow in the lower right hand corner of your screen.
• vLookups and Data Cleansing
• In this module you will learn about VLookup, value cleansing and text functions. We will also introduce you to PwC's perspective on the value in cleansing data and using the appropriate functions. Finally, we will provide you an opportunity to perform a problem solving exercise using VLookup, value cleansing and text function. Note: We recommend viewing videos in full-screen mode by clicking the double arrow in the lower right hand corner of your screen.
• Logical Functions & Pivot Tables
• In this module, you will learn about logical functions and pivot tables. We will show you how to create and use pivot tables to solve business problems. We will give you an opportunity to practice creating and using a pivot table to solve a business problem. Finally, we will share some insight on PwC’s perspectives on the impact of Excel on your career and work. Note: We recommend viewing videos in full-screen mode by clicking the double arrow in the lower right hand corner of your screen.
• More Advanced Formulas
• In this module you will learn more advanced Excel formulas. We will show you how to create statistical formulas, perform an index match, and lastly, build financial formulas. We will provide you with an opportunity to problem solve using statistical formulas. Finally, we will give you an opportunity to practice what you have learned through a final project. Note: We recommend viewing videos in full-screen mode by clicking the double arrow in the lower right hand corner of your screen.

Alex Mannella

• united kingdom

Related Courses

Data visualization with advanced excel, data-driven decision making, data analysis and presentation skills: the pwc approach, effective business presentations with powerpoint, excel at microsoft excel - the complete beginner to advanced excel mastery course -, excel skills for business: intermediate i, related articles, 12 best microsoft excel courses for beginners for 2024, 1800+ coursera courses that are still completely free, 250 top free coursera courses of all time, massive list of mooc-based microcredentials.

5.0 rating, based on 5 Class Central reviews

4.7 rating at Coursera based on 5345 ratings

Select rating

Start your review of Problem Solving with Excel

• احمد العنزي 2 years ago . أتمنى ان احصل عليها مستقبلاً. لانها دوره عمليه يحتاجها جميع الناس الكبير والصغير الرجل والمرأة فيها من العلوم والتطبيقات مايغني عن جميع الدورات الاخرى Helpful
• RP Rafael Santamaria Paredes 2 years ago The excel program is the best way to calculating the cost of any construction. You can calculate cost of materials, cost of workforce, Yields, Transportation cost and add 5% of waste and between 10 to 20% of Profits. Helpful
• SS Sandesh Sharma 2 years ago I think its a good course to learn and enrol into. thanks. Its a basic to advance level course and has all the important features and functionalities. Helpful
• Philiswa Chivale 3 years ago Learning how to solve problems and listen to one another by doing that is the way we developed our communication skills so that even if we teach the children we can understand their differences and their developing of learning and understanding in class. Helpful
• NN Nieves 7 years ago Helpful

Never Stop Learning.

Get personalized course recommendations, track subjects and courses with reminders, and more.

15 Excel Formulas That Will Help You Solve Real Life Problems

Your changes have been saved

Email is sent

Email has already been sent

Please verify your email address.

You’ve reached your account maximum for followed topics.

This Note-Taking System Finally Made Schoolwork Easier for Me

How i send google forms data to google sheets for powerful analysis, this task management tool is the best i've found for actually getting things done.

A lot of people view Microsoft Excel as a tool that's only useful in business. Truth is, there are a lot of ways it can benefit you at home as well. The key to finding uses of Excel in daily life is picking the right formulas that solve problems.

Whether you're shopping for a new car loan, want to figure out which mutual fund investment is best for you, or if you're just trying to make sense out of your bank account, Excel is a powerful tool that can help.

We picked out 15 formulas that are simple, powerful, and help you solve complex issues.

Financial Formulas

Shopping for a new home and confused by all the mortgage lingo? Looking for a new car and getting confused by the car loan terms the salesperson keeps throwing at you?

Have no fear. Before you take out a loan, do your research with Excel by your side!

1. PMT---Payment

Whenever you're comparing any loan terms and want to quickly figure out your actual monthly payment given different variations in terms, take advantage of the powerful (and simple) PMT formula.

Here's what you need to use this formula:

• The interest rate of the loan
• The term of the loan (how many payments?)
• The starting principle of the loan
• Future value, if for some reason the loan will be considered paid off before it reaches zero (optional)
• Type of loan---0 if payments due at the end of each month, or 1 if they're due at the beginning (optional)

Here's a cool way to quickly compare a variety of loans to see what your payments will look like. Create an Excel sheet that lists every potential loan and all available information about them. Then, create a "Payments" column and use the PMT formula.

Just grab the lower right corner of the PMT cell you just created, and drag it down so it calculates the payment total for all the loan terms listed in the sheet. The Excel autofill feature is one feature you will use a lot with these tricks.

Now you can compare monthly payments for different kinds of loans.

(A very big thank you to Mark Jones (@redtexture on Twitter) who pointed out that for PMT and FV formulas, you've got to be very careful about using the same period---in this case using monthly payments requires dividing the interest term by 12 months)

This is why our readers are so great. Thanks for helping with this fix Mark!

2. FV---Future Value

The next formula comes in handy when you are looking to invest some money into something like a Certificate of Deposit (CD), and you want to know what it will be worth at the end of the term.

Here's what you need to know to use the FV formula :

• Number of payments (or investment term in months)
• The payment for each period (usually monthly)
• Current starting balance (optional)

So let's compare several CDs using the terms that you know from the information the banks have given you. In the example below, let's say you have a $20,000 inheritance to invest in a CD.

Interest rates are again represented in decimal format (take the interest rate the bank gave you and divide by 100). Payments are zero because CD's are typically based on a starting value and a future value paid out.  Here's what the comparison looks like when you use the FV formula for every CD you're considering.

Without a doubt, the higher interest CD over a longer period of time pays out much more. The only drawback is that you can't touch any of your money for three whole years, but that's the nature of investing!

3-4. Logical Formulas---IF and AND

Most banks these days give you the ability to download nearly a year's worth of bank transactions to a format like CSV. This is a perfect format to analyze your spending using Excel, but sometimes the data you receive from banks is very disorganized.

Using logical formulas are a great way to spot overspending.

Ideally, the bank either automatically categorizes your spending or you've set up your account so that things are placed into spending categories. For example, any restaurants we go to get labeled with the DiningOut label.

This makes it easy to use a logical formula to identify whenever we've gone out to eat and spent over $20.

To do this, just create a logical formula in a new column looking for any value where the category column is "DiningOut" and the transaction column is larger than -$20

Note: The comparison below shows "<", less than, because the values in column C are all negative.

Here's what that looks like:

Using IF and AND together in one formula looks tricky, but it's actually quite simple. The IF statement will output the dollar amount (C2) if the AND statement is true, or FALSE if it isn't. The AND statement checks whether the category is "DiningOut" and the transaction is greater than $20.

There you have it! Without having to manually sift through all those transactions, you now know exactly those times when you've overspent in a certain category.

Making Sense of Lists

Lists are a big part of everyday life. If you're managing a household, you're using lists constantly. Excel has some pretty powerful tools for being productive with checklists , as well as other kinds of list formats.

5-6. COUNT and COUNTIF

Excel can help you quickly organize and sort values is a list.  Let's take the PTC example. Here's a list of donations from community members.

We want to see how many times a person's name shows up on the list. To do this, you can combine the COUNT formula with an IF formula. First, create a column to check if the person is Michelle or not. The formula will use an IF statement to fill the cell with a "1" if this is true.

Next, create another column that counts how many times you've found Michelle Johnson on the list.

This gives you the count of every place in Column E where there's a 1 rather than a blank.

So, this is the simplest way to do this kind of thing, but it does require two steps.

6-8. SUMIF, COUNTIF, AVERAGEIF

If you don't mind using a slightly more advanced formula, you might consider using one of the many combined "IF" formulas like SUMIF , COUNTIF , or AVERAGEIF . These allow you to perform the formula (COUNT, SUM or AVERAGE) if the logical condition is true. Here's how it works using the above example.

This formula looks at column A, which contains all the donor names, and if the cell within the range matches the criteria in quotes, then it counts up by one. This gives you a count of all the times the donor name equals "Michelle Johnson" in a single step.

It's much faster than using two columns, but is a little complex - so use the approach that works best for your situation.

The  SUMIF  and  AVERAGEIF  formulas work the very same way, just with different mathematical results. Using  SUMIF  in this example would give you the total donation dollars for Michelle Johnson if you use it instead.

Another formula that you can use creatively sometimes is the LEN formula. This formula is one of many Excel text formulas that tells you how many characters are in a string of text.

One interesting way to use this in the example above would be to highlight donors who donated over $1,000 by counting the number of digits in the donation column. If the length of the number is 4 or greater, then they donated at least $1,000.

Now you can add additional formatting to make it easier on the eyes.

To do this, you need to highlight all the cells in the Donation column, select the Home tab in the menu, and click on Conditional Formatting  in the toolbar. Then select Use a formula to determine which cells to format .

Set the range under Format values where this formula is true: to the column/range where all your LEN formula outputs are displayed.

In this example, if you make the condition ">3", then anything over $1,000 will receive the special formatting. Don't forget to click the Format... button and choose what kind of special formatting you want for these.

Also, a quick note. You'll notice my range is defined as "$E2:$E11", not "$E$2:$E$11". When you select the range, it defaults to the former, which won't work. You need to use relative addressing as shown in the picture above. Then, your conditional formatting will work based on the condition of the second range.

Organizing Bank and Financial Downloads

Sometimes, when you download information from businesses---whether it's your bank, or your health insurance company, the format of the incoming data doesn't always match what you need it to be.

For example, let's say that in the exported data from your bank and you're given the date in the standard format.

If you want to add a new column of your own with your own that's prefaced by the year and includes the Payee information (for your own sorting purposes), extracting pieces of information from a column is really easy.

10-14. RIGHT, LEFT, TEXT, and CONCATENATE

You can pull the year out of the text in that column using the RIGHT formula.

The formula above is telling Excel to take the text in column D and extract the four characters from the right side. The CONCATENATE formula pieces together those four digits, with the Payee text from column E.

Keep in mind that if you do want to extract text from a date, you will need to convert it to text format (instead of date) using the "= TEXT (D2,"mm/dd/yyyy")" formula. Then you can use the RIGHT formula to pull out the year.

What if your information is on the left? Well, instead use the  LEFT  formula and you can pull text from left to right.

CONCATENATE really comes in handy when you have some text from a bunch of different columns that you want to piece together into one long string. You can find out more in our guide on how to combine Excel columns .

There are also a few ways to separate text in Excel if you want to want to learn how to fully manipulate strings.

Picking Random Names from a Hat

15. randbetween.

One last fun formula is one you may use if you have to do something like pick some names out of a hat for a Christmas party. Put that hat and those scraps of paper away and instead pull out your laptop and launch Excel!

Using the formula RANDBETWEEN, you can have Excel randomly select a number between a range of numbers you specify.

The two values you need to use are the lowest and highest numbers, which should be at the ends of the range of numbers you've applied to each person's name.

Once you hit the Enter key, the formula will randomly select one of the numbers within the range.

It's about as random and tamper-proof as you can possibly get. So instead of picking a number from a hat, pick a number from Excel instead!

Using Excel for Everyday Problems

As you can see, Excel isn't just for data-analysis gurus and business professionals. Anyone can benefit from the many formulas that you'll find tucked away in Excel . Learn these formulas and you can start solving real-life problems in Excel.

Don't stop learning Excel. There is a long list of Excel formulas and functions that you can learn to use, you might find some neat little tricks you never thought Excel could do.

Image credit:  Goodluz via Shutterstock.com

• Productivity
• Spreadsheet
• Microsoft Excel

Practice And Learn Excel Online For Free

Welcome to Excel Practice Online!

Now you can practice Excel everywhere! You can even practice on your mobile phone!

Every function and tool has an explanation followed by an online excel exercise which can be solved within the page itself, no need to download anything – All thanks to the amazing powers of Excel Online!

The tutorials are sorted from beginner level to advanced level. If you like this site please share it with your friends! 🙂

Tip for mobile phone users – tap twice on the cell you want to edit in order to edit it.

• Free Excel Courses and Resources
• Excel Self-Assessment Tool
• Free Excel Online Exercises
• Excel Basics – Zero to Hero
• Excel Tests
• Top 10 formulas and functions in Excel
• Vlookup – Tutorial with Example and Exercise Sheet
• Pivot Tables Tutorial
• Excel Shortcuts – Windows and Mac
• HOT! – Excel Mortgage Calculator – Calculate your mortgage payments and get the payment schedule for the entire period of the loan – Step-by-step tutorial on how to build a Mortgage Calculator in Excel.
• New! Excel Online Cheat Sheet – A Quick Guide To Excel’s Web Version
• Can’t find what you’re looking for? Suggest a tutorial here!

• Excel Basics – Start here if you are new to Excel! Learn how Excel works, how to perform basic calculations, and how to use cell references to save time and increase efficiency!
• Addition (Plus)
• Subtraction (Minus)
• Multiplication
• Excel Shortcuts for Windows – Master Excel Shortcuts to save time and increase efficiency!
• Excel Shortcuts for Mac – Learn how to make the most of Excel on your Mac!

Formulas/Functions

• SUM function – Sum multiple values in Excel
• MAX – find the maximum value in a range
• MIN – find the minimum value in a range
• COUNT – Count numeric values in a range
• COUNTA – Count numeric and textual values
• AVERAGE – Calculate average of a range
• Filtering in Excel – Learn how to filter your data using Excel’s Filter Tool
• Excel Sort – Learn how to sort your data in Excel.
• Flash Fill – Excel’s hidden gem for auto-completing data based on a pattern
• Remove Duplicates – Remove duplicate values in a single column or multiple columns!

Intermediate

Conditional.

• IF function – check if a condition is met
• NESTED IF – Multiple if conditions
• Conditional Formatting – Format Excel Cells based on criteria
• COUNTIF – Count cells in range which meet a certain criteria
• SUMIF – Sum range based on criteria
• AVERAGEIF – Calculate the average of a range based on criteria
• SUMIFS – Sum cells using multiple criteria
• COUNTIFS – Count cells using multiple criteria
• MAXIFS – Find maximum value in a range based on criteria
• MINIFS – Find minimum value in a range based on criteria
• AND/OR – Check if multiple criteria are met (Works great when combined with an IF function!)
• ISBLANK – Check if a cell is blank or not

• VLOOKUP – lookup value and return corresponding value from a table
• HLOOKUP – lookup value and return corresponding value from a table
• Hot!!! XLOOKUP – Excel’s next generation lookup function which combines the best features from VLOOKUP, INDEX MATCH, HLOOKUP and IFERROR/IFNA

Pivot tables

• Pivot Table – Quickly Analyze and Summarize your data using Excel’s most powerful tool!

Text Formulas

• LEFT, MID, RIGHT – Basic Text Functions
• HOT! – TEXTBEFORE & TEXTAFTER – Extract text before or after a delimiter using Excel’s brand new powerful functions!
• HOT! – TEXTSPLIT – Split your text into multiple cells using this super powerful new function!
• TEXTJOIN – Easily combine multiple cells using delimiter
• CONCAT – Combine range of cells without delimiter
• CONCATENATE – Combine two cells or more into one cell
• LEN – Find the length of a cell
• FIND – Find the position of a text within another text (Case-sensitive)
• SEARCH – Find the position of a text within another text (Case-insensitive)
• SUBSTITUTE – Replace text with another text in a cell/expression
• TRIM – Remove extra spaces from the text
• LOWER, UPPER, PROPER – Convert text to lowercase, uppercase and proper case
• VALUE – Convert data stored as text into values
• TEXT – Convert and format numbers into text
• Text to Columns – Quickly split a column into multiple columns using a delimiter. Bonus – Quickly change date formats or convert text to numbers!
• FORMULATEXT – display a formula in another cell as text

Date functions

• DAY, MONTH, YEAR – Extract day, month and year from a date in Excel
• DATE – Create a date from individual values
• WEEKDAY – Return the number of the day of the week
• EOMONTH – Return the date of the last day of the month based on a specific date

Index & Match lookup

• INDEX – Retrieve cell in nth position in a range
• MATCH – Find position of value in a range
• INDEX MATCH – Just like VLOOKUP, only better.

Other advanced tools

• SUMPRODUCT – Sum the products of Excel ranges
• Excel Wildcards – Advanced searching and matching in Excel
• Advanced Filter – Filter by multiple criteria in the same column, or even in different columns!

Power Query

• Combine data from multiple Excel workbooks using Power Query
• Column from Examples tool – Learn the secret to mastering Power Query without any prior knowledge!
• Unpivot columns easily using Power Query

Secret Excel Functions

This section covers Excel functions that are not available in most of Excel’s versions. These functions will unlock a new set of capabilities such as fining only unique values, sorting, and filtering – the tutorials below will help you with mastering Excel’s new functions!

• UNIQUE – Extract unique values from a range
• SORT Function – Sort range dynamically
• SORTBY – Sort range dynamically by using another range
• FILTER Function – Filter range by specific criteria
• RANDARRAY – Create an array of random numbers 
• SEQUENCE – Create a range of sequential values
• LET – Assign values and calculations to names to improve your formula’s ease of use, readability, and performance!
• HOT! – LAMBDA – The mother of all functions that will help you create amazing and powerful custom functions for your own need! 
• VSTACK – Vertically stack arrays/ranges in Excel
• HSTACK – Horizontally stack arrays/ranges in Excel
• CHOOSEROWS – Return specific rows from a range or array
• CHOOSECOLS – Return specific columns from a range or array
• TOROW – Convert a range/array into a single row
• TOCOL – Convert a range/array into a single column

Financial Functions

Learn how to use Excel to make financial calculations!

• Excel Financial Calculator – quickly calculate PV, FV, PMT, NPV, IRR
• PMT – Calculate the periodic payment amount of a loan, mortgage, or another financial instrument
• PPMT & IPMT – Find the Principal and Interest portion of a certain payment
• PV – Find the Present Value of a loan, mortgage, or any other financial instrument 

Excel Macros – VBA (Visual Basic for Applications)

• Start here – How to run your first VBA Macro in Excel without knowing VBA? 

Excel Data Sheets for Practice

Want to do some freestyle practice? Create your own Excel playground with our blank excel Worksheet!

• Excel-Online Blank Worksheet
• Excel Practice Data

How to Calculate in Excel – Excel-Online Calculators

• How to Calculate GPA in Excel
• How to Calculate BMI in Excel
• How to Calculate Density in Excel
• How to Calculate Weighted Average in Excel

Lists in Excel (Download Link Available)

• List of Numbers in Excel –  Generate sequential or custom patterns of numbers for your Excel projects.
• ABC List in Excel –  Easily create alphabetical lists from A-Z for indexing, sorting, and categorization.
• List of U.S. States   in Excel –  Access a comprehensive list of all 50 U.S. states.
• Days of the Week in Excel –  A quick list of the days of the week, essential for scheduling and planning.
• Months of the Year in Excel –  Download a list of the 12 months of the year for time-based analyses.
• List of Colors in Excel –  Explore a list of standard color names for data visualization and UI design.
• Country List in Excel –  A global list of countries for international datasets or geographic studies.
• Holiday List in Excel –  A curated list of standard holidays for planning and project management.
• List of Currencies : A list of world currencies for financial modeling or international business.

Terms and Conditions - Privacy Policy

• Get started with computers
• Learn Microsoft Office
• Apply for a job
• Improve my work skills
• Design nice-looking docs
• Getting Started
• Smartphones & Tablets
• Typing Tutorial
• Online Learning
• Basic Internet Skills
• Online Safety
• Social Media
• Zoom Basics
• Google Docs
• Google Sheets
• Career Planning
• Resume Writing
• Cover Letters
• Job Search and Networking
• Business Communication
• Entrepreneurship 101
• Careers without College
• Job Hunt for Today
• 3D Printing
• Freelancing 101
• Personal Finance
• Sharing Economy
• Decision-Making
• Graphic Design
• Photography
• Image Editing
• Learning WordPress
• Language Learning
• Critical Thinking
• For Educators
• Translations
• Staff Picks
• English expand_more expand_less

Excel Formulas  - Solving Real-Life Problems in Excel

Excel formulas  -, solving real-life problems in excel, excel formulas solving real-life problems in excel.

Excel Formulas: Solving Real-Life Problems in Excel

Lesson 6: solving real-life problems in excel.

/en/excelformulas/functions/content/

Solving real-life problems in Excel

Excel can be used to solve all kinds of real-life problems. But how do you turn those problems into formulas that Excel can understand? All it takes is a little bit of planning (and some basic math). To learn more, check out the video below!

/en/excelformulas/doublecheck-your-formulas/content/

• Dashboard templates
• Data Charts
• Analyses & Reports
• Master Class

Excel Solver Example problems with variables or constraints

Excel users have applied long and successfully this program to solve various types of tasks in different areas.

Excel is the most popular program in every office in the world. Its facilities allow you to quickly find effective solutions in a wide range of activities. The program is capable for solving various kinds of tasks: financial, economic, mathematical, logical, optimization and many others. For clarity we will see each of the described tasks with solving in Excel and consider it with an examples.

Solving tasks in data optimization problems

Optimization models are applied in the economic and technical sphere. Their goal is to choose a balanced solution and optimal in specific conditions (the amount of sales to receive a certain revenue, the best bill of fare, the quantity of flights, etc.).

The following commands are used to solve optimization problems in Excel:

• The Script Manager (“DATA”- "What-if Analysis" - “Scenario Manager”) analyzes several variants of the original values, creates and evaluates sets of scenarios.

• Selection of parameters (“DATA”- "What-if Analysis" - "Goal Seek") helps to find the values that will ensure the desired result.

• Solution search (Microsoft Excel add-in, "DATA" - "Solver") calculates the optimal value taking into account variables and constraints. Go to the link and find out how to plug in the "Solver" setting.

The command "Parameter Selection" is using to solve the simplest tasks. You use "Scenario Manager" for the most complex tasks. Let’s consider an example of solving an optimization problem using the "Solver" add-on.

Condition. The company produces several brands of yogurt. Let’s call them "1", "2" and "3". The enterprise receives 200 rubles having sold 100 jars of yoghurt "1". Brand "2" brought 250 rubles. "3" - 300 rubles. The sales are well-established, but the amount of available raw materials is limited. You need to find what kind of yogurt and how much you need to do to get the maximum revenue from sales.

Known data (including the rate of consumption of raw materials) will be listed in the table:

We will compile a working table based on these data:

• We still do not know the number of products. These are variables.
• The following formulas are written in the column "Profit": C11:= 200*B11, C12:= 250*В12, C13:= 300*В13, C14: =SUM(C11:C13).
• The consumption of raw materials is limited (these are restrictions). The following formulas are added to the cells: milk A18: kefir B18: egg C18: sugar D18: That is, we have multiplied the consumption rate by the quantity.
• The goal is to find the maximum possible profit. This is C14 cell.

Activate the "Solver" command and enter the parameters.

The program issues its decision after clicking the "Solve" button.

The best option is to concentrate on the making yogurt "3" and "1". Kefir "2" is not worth producing.

Solving financial tasks in Excel

Most often financial functions are used for this purpose. Let's consider an example.

Condition. Let’s calculate how much to deposit, so that in four years 400,000 rubles will be formed. The interest rate is 20% per annum. Interest is accrued on a quarterly basis.

Let’s make a table with initial data:

Since the interest rate does not change during the whole period, we use the function of the =PV (Rate, Nper, Pmt, Fv, Type) to cell B7.

Filling in the arguments:

• Rate – Interest rate is 20%/4 because Interest is accrued on a quarterly basis.
• Nper – Total number of periods is 4*4 (total term of deposit*number of accruing periods per year).
• Pmt – Recurring payments is 0. We do not write anything. The deposit will not be replenished.
• Fv – Future Value is the amount of money we want to receive at the end of the deposit period.

The investor needs to deposit this money so the result is a negative number.

We use the formula To verify the correctness of the solution: PV = Fv/(1+Rate)*Nper. We substitute the values: PV=400 000 / (1 + 0.05) 16 = 183245.

Solving the econometric tasks in Excel

Mathematical and statistical methods and models are used to establish quantitative and qualitative interrelations.

There are two ranges with values:

The values of X will play the role of a factor characteristic, Y is the resultant one. The task is to find the correlation coefficient.

The function CORREL (array 1, array 2) is provided to solve this task.

Solving the logic tasks in Excel

The table processor has built-in logic functions. Any of them must contain at least one comparison operator which will determine the relation between the elements (=,>, <,> =, <=). The result of the logical expression is a Boolean value of TRUE or a Boolean value FALSE.

Task for example. The students passed the test. Each of them received a grade. The test is handed over if there are more than 4 points. If less it’s not handed over.

• Put the cursor in C1 cell. Press the function icon. Choose "IF".
• Fill in the arguments. The logical expression is B1> = 4. This is the condition under which the logical value is TRUE.

• If TRUE - "Passed the test". FALSE - "Did not pass the test".

Solving the mathematical challenges in Excel

Using the program arsenal, you can solve not only the simplest mathematical problems, but more complex (operations with functions, matrices, linear equations, etc.).

The condition for the training task. Find the inverse matrix B for the matrix A.

• We make a table with the values of matrix A.

• We highlight on the same sheet the area for the inverse matrix.
• Go to "FORMULAS". Category - "Math and Trig". Type - "MINVERSE".

• In the “Array” argument field enter the range of the matrix A.

• Press Shift + Ctrl + Enter at the same time because this is an obligatorily for entering arrays.

Download of examples

The possibilities of Excel are not unlimited. But the program can handle with the most of tasks. Especially there is no description of capabilities that can be expanded with macros and user settings.

• Excel Formula Examples
• Create table
• Excel Functions
• Formulas and ranges
• Sort and filter
• Charts in Excel
• Pivot Tables
• Printing sheets
• Databases and XML
• Excel features
• Settings and Options
• Excel Classes
• Download Examples

Privacy Policy Contact

Continuous Improvement Toolkit

Effective Tools for Business and Life!

A3 Problem Solving Template

A3 thinking is a logical and structured approach to problem solving adopted by Lean organizations around the world. It can be used for most kinds of problems and in any part of the business. This A3 template uses a four stages model that is based on the PDCA management philosophy. It makes the problem-solving progress visible to the entire team while allowing the lessons to be learned by others.

This template is a Microsoft Excel spreadsheet that you can use and modify to meet your specific requirements. For example, you may expand the implementation or follow-up plans by increasing the number of rows. The template is available in two variations: a user-friendly straightforward version, and a more detailed one that requires providing in-depth information.

A3 Template (32 KB)

A3 Template – Simple (216 KB)

A3 Template – Detailed (340 KB)

Related Templates

Written by:

CIToolkit Content Team

Problem Solving with Excel

This course explores Excel as a tool for solving business problems. In this course you will learn the basic functions of excel through guided demonstration. Each week you will build on your excel skills and be provided an opportunity to practice what you’ve learned. Finally, you will have a chance to put your knowledge to work in a final project. Please note, the content in this course was developed using a Windows version of Excel 2013.

This course was created by PricewaterhouseCoopers LLP with an address at 300 Madison Avenue, New York, New York, 10017.

What's inside

Good to know, save this course, reviews summary, excel solution mastery.

• Purchase or borrow the book
• Read the book
• Take notes on the key concepts
• Go to the Microsoft Excel website.
• Click on the 'Support' tab.
• Read through the documentation for the specific functions you need to review.
• Watch tutorials on YouTube or other video-sharing sites.
• Find a tutorial on data cleaning in Excel
• Watch the tutorial and follow the instructions
• Practice the techniques on your own dataset
• Find a study group that meets your schedule and learning style.
• Attend study group meetings regularly.
• Participate in discussions and ask questions.
• Work together on problem sets and assignments.
• Form a study group with other Excel learners.
• Meet regularly to discuss course materials and practice exercises.
• Collaborate on projects and provide feedback to each other.
• Attend group study sessions led by a facilitator.
• Access the online Excel exercises
• Choose an exercise to work on
• Follow the instructions to complete the exercise
• Check your answers
• Download practice exercises from the course materials.
• Work through the exercises step-by-step.
• Check your answers against the provided solutions.
• Download the provided dataset.
• Follow the step-by-step instructions to perform VLOOKUP and data cleansing.
• Check your results against the provided answer key.
• Find a guided tutorial on creating and using pivot tables.
• Follow along with the tutorial, practicing the steps on your own dataset.
• Create a pivot table to solve a real-world business problem.
• Find a non-profit organization that uses Excel
• Contact the organization and inquire about volunteer opportunities
• Volunteer for a project that uses Excel
• Use your Excel skills to help the organization
• Find an Excel workshop that focuses on the topics you need to learn.
• Register for the workshop and pay the registration fee.
• Attend the workshop and participate actively.
• Take notes and ask questions during the workshop.
• Find a workshop on advanced Excel techniques
• Register for the workshop
• Attend the workshop
• Gather the necessary data
• Build the financial model
• Validate the model
• Present the model to stakeholders
• Identify a problem that you can solve using Excel.
• Design a workbook that includes the necessary worksheets and formulas.
• Enter data into the workbook and apply formulas.
• Format the workbook and create charts and graphs.
• Share the workbook with others for feedback.
• Gather financial data from various sources.
• Cleanse and prepare the data for analysis.
• Create charts, graphs, and tables to visualize the data.
• Develop formulas and calculations for financial analysis.
• Present the dashboard to stakeholders.
• Find a junior Excel user to mentor
• Meet with your mentee regularly
• Answer their questions
• Provide them with feedback
• Help them develop their Excel skills
• Identify a repetitive task that can be automated.
• Learn the basics of Excel macro programming.
• Write and record a macro to automate the task.
• Test and refine the macro.
• Document the macro for future use.
• Find a networking event for Excel users
• Register for the event
• Attend the event
• Network with other Excel users
• Learn about their experiences with Excel

Career center

Reading list.

Similar courses

• Problem Solving with Excel (viewing)
• Data Analysis and Presentation Skills: the PwC Approach Final Project
• Effective Business Presentations with Powerpoint
• Data Visualization with Advanced Excel
• Data-driven Decision Making

What you'll learn

OpenCourser helps millions of learners each year. People visit us to learn workspace skills, ace their exams, and nurture their curiosity.

Our extensive catalog contains over 50,000 courses and twice as many books. Browse by search, by topic, or even by career interests. We'll match you to the right resources quickly.

Find this site helpful? Tell a friend about us.

We're supported by our community of learners. When you purchase or subscribe to courses and programs or purchase books, we may earn a commission from our partners.

Your purchases help us maintain our catalog and keep our servers humming without ads .

Thank you for supporting OpenCourser.

Excel Linear Programming (Using the Solver and Graphical Methods)

The Solver Add-in can solve linear and non-linear programming problems with multiple variables and constraints, whereas the graphical method can only be used to solve problems with two variables.

Click the image to get a detailed view

Download Practice Workbook

How to perform Linear Programming using the Solver in Excel

How to add excel solver add-in.

The Excel Solver Add-in is not present in the Data tab by default.

• To add the Solver add-in from Excel Add-Ins, go to Data tab >> check if the Solver add-in is present >> go to the File tab if the Solver tool is not present.

• Click  Options .

• In Excel Options , click  Add-ins   >> click Go .

• In Add-ins , check Solver Add-in >> click OK . The Solver Add-in will be added to the Data tab.

How to Define and Formulate the Linear Programming Problem?

A linear programming problem consists of an objective function and some constraints. The objective function can be maximized or minimized.

To solve the following linear programming model which has an objective function Z , which you want to maximize, and 3 different constraints for the X 1 , X 2 , and X 3 variables.

How to Tabulate Linear Programming Problems in Excel

• Tabulate the linear programming model in the following format to input the objective functions and constraints in the Excel Solver Add-in. Here, the Light Green colored boxes are kept empty for calculations and solutions.

• Use the following formula in F5 . The range C5:E5 is empty, so F5 shows 0 . This value indicates the current value of the objective function.

• For the constraints, enter the following formula in  F8 >> press Enter >>  drag down the Fill Handle .

How to Solve a Problem Using the Excel Solver

• Go to the Data tab >> click Analysis >> select Solver .

Click the image for a detailed view

• In Solver Parameters , enter $F$5 in Set Objective   >> In To, click  Max  >>  In By Changing Variable cells , select the range $C$5:$E$5 >> click Add  to add constraints.

• In Add Constraint , enter $F$8 in Cell Reference >> select >= operator for the first constraint >> Enter $H$8 in  Constraint >> click Add to add constraints 2 and 3 >> click OK .

• Constraints were added to Subject to the Constraints . In Select a Solving Method >> select Simplex LP >> click Solve .

The required values for the variables and the objective function for the linear programming maximization problem will be displayed.

Here, the maximized value of the objective function is 71, and the values for the variables X 1 , X 2 , and X 3 are 0, 11.76, and 4.12.

Read More: How to Find Optimal Solution with Linear Programming in Excel

How to Save and Load Solver Scenarios in Excel

• To save a linear programming model, select a range to input the variable values >> go to the Data tab >> click Solver .

• In Solver Parameters , you can see the last solved model. Click Load/Save .

• In Load/Save Model , provide the reference of the first cell in the selected range ( $J$3 , here) >> click Save .

You will see the linear programming model variable values in the selected range.

How to Load the Saved Model

• In Solver Parameters , click Load/Save >> select the range containing the previous model ( $J$3:$J$9 , here) >> click Load .

• In Load Model , click Replace .

The Solver Parameters user form will open and replace the existing model parameters with the loaded model parameters. Click Solve  to recalculate it.

How to perform Linear Programming Using the Graphical Method in Excel

How to define and formulate the problem.

• Define an objective function and constraints for the linear programming problem. Here, the linear programming model has an objective function Z, which you want to maximize, and 2 different constraints for the X and Y variables.

How to Tabulate the Linear Programming Problem in Excel

• Tabulate the linear programming model in the following format using the variable coefficients.

Read More: How to Do Linear Programming in Excel

How to Plot a Chart Using the Constraints

• Calculate the X and Y-axis intersection points for each constraint by setting one variable equal to zero. For example, for the first constraint, if Y = 0 then X = 7 , and if X = 0 then Y = 7 .
• List these intersection points for each constraint in the following format.

• Select the intersection points of constraint 1 >> go to the Insert tab >> Select Scatter   >> choose Scatter with Smooth Lines .

A chart like the following will be displayed.

• Select the chart and right-click >> click Select Data .

• In Select Data Source , select Series 1 and click Edit .

• In Edit Series , set the Series Name to range $B$9:$C$9 and click OK .

• Click Add .

• Set the Series Name to $E$9:$F$9 >> Series X Values to $E$11:$E$12 >> Series Y Values to $F$11:$F$12 >> click OK .

• In Select Data Source , click OK .

The chart for the constraint lines is displayed.

• Select the chart and click the plus (+) icon to view the Chart Elements >> check Data Labels >> click  the right side of Data Labels to see additional options >> click Data Callout option to see X and Y-axis intersection points >> uncheck Gridlines to remove grids.

How to Determine the Coordinates of Each Corner Point in the Feasible Region

Since both the constraints have a <= (less than or equal to) operator and X, Y values are greater than or equal to zero, the marked ABCD region is your feasible region. (Here, A B C D were manually placed to show the corner points)

• List all the corner points (A, B, C, and D) of the feasible region and calculate the objective function value in those points. You can get the X and Y values for A, B, and D from Data Callouts.
• To calculate X and Y values for point C, use the following formula in C17 .

Here, the MINVERSE function calculates the inverse matrix of the matrix formed with X and Y value coefficients in constraints (range C6:D7 ). And the MMULT function multiplies that inverse matrix with another matrix (range F6:F7 ). Finally, the TRANSPOSE function converts the rows into columns and columns into rows for the output returned by the MMULT function.

How to Calculate the Value of the Objective Function for Each Corner Point?

• Enter the following formula in E15 >> press Enter  >> use the Fill Handle tool.

The maximum value occurred in point C. And the value of the maximized objective function is 23.

Read More: How to Solve Blending Linear Programming Problem with Excel Solver

Frequently Asked Questions

1. What are the limitations of using the Excel Solver for Linear Programming?

Answer : Although Excel Solver Add-in is a powerful tool, it has limitations:

>> it is suited for small to medium-sized linear programming problems (up to 200 variables)

>> it operates with limited precision (up to 15 decimal points)

>> it operates with very few numbers of algorithms (Simplex Method for Linear Programming Problems), etc.

2. Can I use the graphical method to solve Linear Programming Problems with 3 variables?

Answer : If we apply the graphical method for Linear Programming problems with 3 variables, the feasible region will be a 3-dimensional space. Since representing 3-dimensional space on a two-dimensional plane can be complex and visually overwhelming, the graphical method is not suitable for Linear Programming problems with more than two variables.

3. Are there any alternative tools or software besides Excel Solver to solve Linear Programming problems?

Answer : There are some alternative tools or software:

>> IBM ILOG CPLEX optimizer

>> MATLAB Optimization Toolbox

>> GNU Linear Programming Kit

>> PuLP, etc.

Excel Linear Programming: Knowledge Hub

• How to Solve Integer Linear Programming in Excel
• How to Calculate Shadow Price Linear Programming in Excel
• How to Perform Mixed Integer Linear Programming in Excel
• How to Do Linear Programming with Sensitivity Analysis in Excel
• How to Graph Linear Programming in Excel

<< Go Back to Solver in Excel  |  Learn Excel

What is ExcelDemy?

Tags: Solver in Excel

Seemanto Saha graduated in Industrial and Production Engineering from Bangladesh University of Engineering and Technology. He has been with ExcelDemy for a year, where he wrote 40+ articles and reviewed 50+ articles. He has also worked on the ExcelDemy Forum and solved 50+ user problems. Currently, he is working as a team leader for ExcelDemy. His role is to guide his team to write reader-friendly content. His interests are Advanced Excel, Data Analysis, Charts & Dashboards, Power Query,... Read Full Bio

Excel Linear Programming (Using Solver and Graphical Methods)

I thought this article was made extremely well. It showed how to install the necessary tools, how to use linear programming on excel (step by step) and also showed other excel solver alternatives such as IBM ILOG CPLEX optimizer. The only thing that I would like added to this article is an example along with the step-by-step tutorial just to add context to the steps. I would like to apply these models in figuring out problems such as how many units of product A and B should we produce to maximize profit?

Hello Martin ,

We are glad to hear that you found this article useful! Including a real-life example in tutorials can indeed make the steps more relatable and easier to understand. Applying these models to specific scenarios like maximizing profit by determining the production units for products A and B is a great way to leverage linear programming. We will include this in our next update.

Regards ExcelDemy

Leave a reply Cancel reply

ExcelDemy is a place where you can learn Excel, and get solutions to your Excel & Excel VBA-related problems, Data Analysis with Excel, etc. We provide tips, how to guide, provide online training, and also provide Excel solutions to your business problems.

Contact  |  Privacy Policy  |  TOS

• User Reviews
• List of Services
• Service Pricing

• Create Basic Excel Pivot Tables
• Excel Formulas and Functions
• Excel Charts and SmartArt Graphics
• Advanced Excel Training
• Data Analysis Excel for Beginners

Advanced Excel Exercises with Solutions PDF

Excel is so powerful that you can even use it to solve Sudoku puzzles

Your changes have been saved

Email is sent

Email has already been sent

Please verify your email address.

You’ve reached your account maximum for followed topics.

Microsoft Excel is a ridiculously powerful spreadsheet program, and you can do more than just track data in it. It has a ton of macros , a programming language called VBA (that I'd argue is akin to an Object Oriented Programming language, though not in a similar way to Java or C#), and a lot of powerful functions . As an example of Excel's strength, a Reddit user even made a Sudoku solver using just Excel, and how it works is magnificent.

How you can make interactive dashboards in Microsoft Excel (and why you should)

Transform your data into insights by harnessing the power of pivot tables to make interactive dashboards

Excel is incredibly powerful

A weird, but practical way to solve sudoku puzzles.

Back in 2020, Reddit user /u/KrakenOfLakeZurich created a Sudoku solver in Microsoft Excel. It only uses formulae and conditional formatting, and it's available for both Excel and LibreOffice Calc. One example function that the user shows off is the following:

one_to_nine: =MOD(COLUMN(Sudoku!A1)-1, 3) + 1 + MOD(ROW(Sudoku!A1)-1, 3) * 3 The above formula is used to map one cell from a 9x9 Sudoku grid to one of the positions within a smaller 3x3 sub-grid. In Sudoku the larger grid is divided into nine smaller 3x3 grids and each cell needs to be placed within those smaller grids. The formula takes the row and column numbers of a specific cell (A1 in the above example) and performs calculations in order to figure out which position (from 1 to 9) it belongs to within its respective 3x3 sub-grid.

It does this via the MOD function, which calculates the remainder when dividing the row and column numbers by 3. This can then identify the cell's position within the 3x3 grid, and the formula combines the results from both row and column to give a single number. This single number represents the position within the sub-grid. It's a pretty complex formula, but gives you an idea of just how powerful Excel is.

5 ways to up your Excel game with Microsoft Copilot

Essential tricks to transform your Excel workflow with Microsoft Copilot

Solving a Sudoku puzzle with Excel

Number by number.

Solving a Sudoku puzzle with this spreadsheet is fairly simple. The board in the top left is the input board, the board in the bottom left are the solutions it calculates as you enter numbers, and the large board is a calculation board. The calculation board shows all of the possible values that can fit into a cell, and when there is only one possible value left, this value is then represented in the calculation board in black.

Avid Sudoku fans might be aware that some puzzles will require you to make guesses and potentially backtrack if those guesses are wrong. In those instances, you should look for a cell that has the fewest possible answers, and then go ahead with entering that number into the input board. If it solves, great, but if you hit a wall where it can no longer calculate anymore, backtrack and go for the other number.

While this is a practical application for solving Sudoku puzzles, it's practical in the sense that someone can learn a lot about Excel from it. There are countless Sudoku solvers out there that don't require consistent manual input, but they're nowhere near as fun as an application like this that can stretch the limits of what you thought was possible in Excel. In truth, this only scratches the surface, especially with the recent integration of Python with Excel !

5 reasons why I use Python instead of Excel for visualizing data

A data visualization upgrade you can’t miss

• Productivity & Creativity Tools