online excel problem solving

Get instant live expert help with Excel or Google Sheets

“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

• CONDITIONAL FORMATTING
• PIVOT TABLES
• LIVE EXCEL HELP
• ONLINE EXCEL HELP
• EXCEL HELP CHAT
• FREE EXCEL HELP
• EXCEL EXPERT HELP
• BOX AND WHISKER
• CONCATENATE
• TIME AND DATE
• DATA VALIDATION
• DATE SERIES
• MAX AND MIN
• ENGINEERING
• STATISTICAL

Here are some problems that our users have asked and received explanations on

Practice Excel

Like never before.

Solve real-life problems in a fun & interactive way for FREE !

What's in Store for You?

Hint: You'll be Amazed!

Learn Like a Pro!

Hands-On with problems in a structured way & enhance your data analytics skills. No more Toy problems!

Practice Excel Anywhere & Anytime

Practice with an in-browser Excel (from basics to advanced) in a fun & interactive way.

Grab Solution For Every Problem

Well explained solution for every problem if you get stuck. No more time hassles!

Trusted & Verified

Vouched by our Beta users (Pms, Data Analysts, Markets,

HRs, Students etc). Hear them Applauding!

Who can use ExcelPractice?

Hint: Literally Everyone!

✅ Aspiring Data/Business Analysts

✅ Product Managers / Product Analysts

✅ Project Managers

✅ Students (Eng./Comm./MBA etc)

✅ Anyone who wants to hone data skills with Excel

Say 'Hi' to the 3 Musketeers behind the scenes for pulling it off! 🤩

Rohan Rashinkar

Product / Development

Achalesh Lakhotiya

Product / Marketing

Atharv Nagpure

Development

Have a feedback for us? Your Command, Our Action!

Excel Exercises helps regular people learn Excel as quickly as possible.

Becoming the "spreadsheet wizard" of the office used to require years of industry experience and endless hours of watching excel training videos and tutorials online. excel exercises is the new method to learn excel that's faster, easier, and a lot more fun..

Trusted by users from these organizations (& many more!)

"I finished up all the lessons, they really helped me understand Excel logic better. Can't wait for more!"

"i stumbled across your website with excel exercises, and it's seriously an amazing resource [...] i seriously believe your platform proves to be one of the most efficient ways to learn excel", "when i first started the practice exercises at your site i just found it really acclerated my comprehension of excel. i started to understand it so much better than any other prior learning platform that i had visited.", a better way to learn excel skills.

Welcome to the fun, hands-on way to learn Excel! My name is Jake and I'm known as a spreadsheet wizard around my office, but it wasn't a fast or easy process to get here. I watched several hours worth of Excel training videos, but found I didn't really master a formula until I actually got my hands dirty and used the formula at work. It took me a few years of working in finance and consulting- using Excel nearly every day- to internalize all the keyboard shortcuts and functions. I started wondering why there wasn't an easier and faster way to master Excel.

Like I mentioned before, I spent hours watching Excel training videos, but they were honestly kind of boring. I'd find myself zoning out and would need to rewind and re-watch each video two or three times. And without immediately putting the techniques from the videos into practice, I'd forget the techniques right after watching the video.

Excel Practice For Real People

Practice Excel the right way - in small, bite-sized, engaging lessons, rather than falling asleep reading a lengthy article or daydreaming through a long video. This is Excel practice for those who learn by doing, not by watching. There's a reason thousands of people are choosing to get their Excel practice from Excel Exercises: because it works. Each lesson is designed to keep you engaged and entertained while walking you through new concepts, so that you can't advance if you're not absorbing the information. That's just one of the ways Excel Exercises helps you learn more efficiently and maximize your practice time.

Excel Exercises Solves the "Boring Video" Problem

We walk you through all the Excel functions that you need to know, forcing you to type through practice exercises to get hands-on and commit them to memory. You'll also practice keyboard shortcuts on your own keyboard to build muscle memory and get faster at manipulating spreadsheets. Through hands-on repetition and smart skills targeting, I've distilled all the skills I've learned from years of working with Excel to a program that can be completed in a matter of days.

Have Fun While You Learn With Excel Exercises

These practice exercises aren't just engaging; they are actually fun. Score points by answering questions correctly and advance through the levels as you learn, rather than relying on boring memorization. You'll start by practicing some easy skills and work your way up to practicing more advanced techniques. By gradually introducing new concepts for you to practice, we make it easy to learn all the techniques you need to become an Excel master. Whether you're searching for easy Excel practice exercises or more advanced formula practice, Excel Exercises offers a fun learning experience for all skill levels - it doesn't even feel like learning!

Thousands of people have already used Excel Exercises to practice Excel skills and advance their careers. Solid Excel skills are critical for most finance, accounting, consulting, and other data-oriented jobs. And let's be honest - if your shortcut game is on point you'll impress anyone watching over your shoulder. Learn Excel the fun way today and get your career moving in the right direction.

Use Excel Exercises to Excel-erate Your Career

Almost any job in Finance, Accounting, Data Science, Consulting, or any other quantitative industry will require you to use spreadsheets. By mastering Excel you can give yourself an edge by completing your work faster and better than your peers. Many jobs today also require an Excel test as part of the application and interview process. Whether you're applying for an internship or you're already an executive, Excel mastery is a tangible and noticeable skill that can help you get the offer, earn more money, and make yourself indispensable.

Today's job market demands strong spreadsheet skills like never before. Everything from simple data entry to advanced data analysis will require proficiency with Excel in order to pass the interview process and perform with the speed and quality necessary to thrive in your new job. Luckily, there are now more resources than ever available to help you learn Excel online. Excel Exercises is the first web-based Excel practice resource that simulates real Excel practice exercises right in your browser. Let Excel Exercises be your new secret weapon to master Excel and get a new job or move up in your current role.

No sign up necessary. If you love it, you can create an account and join thousands of others who are already using their new Excel skills to stand out and get ahead in their careers.

Hands-on Exercises

Most people can't learn new skills simply by watching. There's a reason you hear that the best way to learn Excel is just to use it at work for a few years. But when you don't have years to learn Excel, your best option is the curated simulations offered by Excel Exercises. We walk you through new skills and let you write the actual formulas and tap out the keyboard shortcuts to build muscle memory and learn by doing. By focusing on one skill at a time, you'll get enough reps to commit the skill to memory before moving on.

Excel Exercises for Beginners and Experts

Whether you already have some Excel experience or you've never written a "sum" function in your life, this site will teach you the tools to stand out at work as the Excel Master. It starts easy with simple functions to build a solid foundation. Then it becomes more challenging as the lessons incorporate new concepts, shortcuts, and advanced functions to build your skills, boost your efficiency, and expand on what you thought was possible with Excel. Whether you're a beginner or advanced, there is always a new Excel skill you can learn.

• 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

Practice And Learn Excel Online For Free

Here you can practice dozens of Excel exercises with full solutions and explanations. If you are new to excel, please check out our  Excel Basics Tutorial!

The explanations can be found in the Answer tab in each of the exercises. Have fun 🙂

New! Excel Self Assessment – Test your Excel skills and understand what to learn next to be even better!

• Basic Math and Percentages Exercise

SUM Function

• SUM Tutorial and Practice
• SUM Function – Exercise 1
• SUM Function – Exercise 2
• SUM Function – Exercise 3

COUNT, COUNTA & COUNTBLANK Functions

• COUNT Tutorial and Practice
• COUNTA Tutorial and Practice
• COUNT & COUNTA Functions – Exercise 1
• COUNT & COUNTA Functions – Exercise 2
• COUNT, COUNTA & COUNTBLANK Functions – Exercise 3

AVERAGE Function

• AVERAGE Tutorial and Practice
• Average Function – Exercise 1
• Average Function – Exercise 2
• Average Function – Exercise 3

MIN/MAX Function

• MIN Tutorial and Practice
• MAX Tutorial and Practice
• MIN/MAX Function (combined with AVERAGE)
• MIN/MAX Function (combined with IF)

IF Function

• IF Tutorial and Practice
• IF Function – Exercise 1
• IF Function – Exercise 2
• IF Function – Exercise 3
• IF Function – Exercise 4

Nested IF Function

• Nested IF Tutorial and Practice
• Nested IF – Exercise 1

VLOOKUP Function

• VLOOKUP Tutorial and Practice
• VLOOKUP Function – Exercise 1
• VLOOKUP Function – Exercise 2
• VLOOKUP with Approximate Match – Exercise 3

HLOOKUP Function

• HLOOKUP Function – Exercise 1

SUMIF Function

• SUMIF Tutorial and Practice
• SUMIF Function – Exercise 1
• SUMIF Function – Exercise 2

COUNTIF Function

• COUNTIF Tutorial and Practice
• COUNTIF Function – Exercise 1
• COUNTIF Function – Exercise 2

SUMIFS Function

• SUMIFS Tutorial and Practice
• SUMIFS Function – Exercise 1
• SUMIFS Function – Exercise 2

COUNTIFS Function

• COUNTIFS Tutorial and Practice
• COUNTIFS Function – Exercise 1
• COUNTIFS Function – Exercise 2

Pivot Table

• Pivot tables Tutorial and Practice
• Pivot Table basics – Exercise 1

Practice Excel Exams and Tests

Want to test yourself? check out our Excel Practice Tests Page

Terms and Conditions - Privacy Policy

#1 Excel tutorial on the net

Load the Solver Add-in | Formulate the Model | Trial and Error | Solve the Model

Excel includes a tool called solver that uses techniques from the operations research to find optimal solutions for all kind of decision problems.

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

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.

Excel Problems & Challenges

If you are looking for a quick challenge in Excel, then you have come to right place. Train your Excel muscles by answering these questions & quizzes.

• How well do you know your Lookups? [Quiz]
• How fast can you run [Excel game]
• Can you find Easter eggs in this workbook?

Latest Challenges:

• Can you calculate vacation days in a period?
• Can you find that pattern?
• Check if two dates are in same month
• Check if few cells have same value
• How many values are common in 2 lists?
• Waiter friendly pricing & round to nearest Monday
• Can you extract dates from text?
• Highlight employees by performance
• An IF formula challenge for you
• Finding next Friday the 13th…
• Calculate Payment Schedule
• Check if a word or phrase is palindrome…
• Writing bit-wise AND, OR & XOR formulas
• Amount donated vs. pledged
• Calculating sum of digits in a number
• Make nuts without going nuts
• Find the average of closest two numbers
• Mutant cell modeling problem
• When does next thanksgiving day occur on same date?
• VLOOKUP Formula challenges + a joke
• How to lookup values to left?
• Show zebra lines when values change
• How to calculate working hours between 2 dates?
• How many hours did Johnny work?
• Find out days overlapped between 2 ranges of dates?
• Calculate average of top 5 values

All the best! 🙂

Comments are closed.

• 3D Maps (non Power Maps)
• 3D Maps (Power Maps)
• advertising
• All Time Hits
• artificial intelligence
• calvin fund
• Chandoo.org Podcast Sessions
• Charts and Graphs
• Complex Numbers
• Cool Infographics & Data Visualizations
• Excel Challenges
• Excel Howtos
• Excel on iPad
• Excel Workshops
• Financial Modeling
• food and drink
• Formula Challenges
• Formula Forensics
• Google Spreadsheets – Tip & Tricks
• Powerpoint Cartoons
• Interview Questions
• Keyboard Shortcuts
• excel links
• Master Class
• Movies-Books
• Office Scripts
• Office Tips
• personal finance
• photographic fridays
• Pivot Tables & Charts
• Posts by Faseeh
• Posts by Hui
• Posts by Jeff
• Posts by KV
• Posts by Luke
• Posts by Sajan
• Power Automate
• Power Pivot
• Power Query
• Project Management
• R programming
• Training Programs

Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free. 

Get Started

• Excel Basics
• Advanced Excel
• 100+ Formulas
• Free Crash Course
• Pivot Tables

Online Classes

• Excel School
• VBA Classes
• Power BI Classes
• 50 ways to analyze data
• Mini Courses
• Welcome Message
• Forum & Community
• 20+ Excel Templates
• 200+ Dashboards
• 40+ VBA Code samples
• Ebooks & More

© All rights reserved.

OpenSolver for Excel

The Open Source Optimization Solver for Excel

About OpenSolver

Welcome to OpenSolver , the Open Source linear, integer and non-linear optimizer for Microsoft Excel.

The latest stable version , OpenSolver 2.9.3 (1 Mar 2020) is available for download; this adds support for using Gurobi 9.0 as a solver. OpenSolver 2.9.4 Beta Release version is now also available for download. Refer to the release blog for the new 2.7 ,  2.8 , 2.8.3, 2.8.4 , 2.8.5 , 2.8.6 , 2.9.0 ,  2.9.3 & 2.9.4 improvements. View all releases.

OpenSolver for Google Sheets ; see our dedicated OpenSolver for Google Sheets page for more info on the Google Sheets versions of OpenSolver.

COIN-OR Cup Winner: We are pleased to announce that OpenSolver is the winner of the 2011 INFORMS COIN-OR Cup sponsored by IBM . Thanks, COIN-OR , for this honour.

OpenSolver is an Excel VBA add-in that extends Excel’s built-in Solver with more powerful solvers. It is developed and maintained by Andrew Mason and students at the Engineering Science department, University of Auckland, NZ. Recent developments are courtesy of Jack Dunn at MIT.

OpenSolver provides the following features:

• OpenSolver offers a range of solvers for use in Excel, including the excellent, Open Source, COIN-OR CBC optimization engine which can quickly solve large Linear and Integer problems.
• Compatible with your existing Solver models, so there is no need to change your spreadsheets
• No artificial limits on the size of problem you can solve – have as many variables and constraints as your computer memory allows (but be aware that large problems can be slow to solve)
• OpenSolver is free, open source software.

As well as providing replacement optimization engines, OpenSolver offers:

• A built-in model visualizer that highlights your model’s decision variables, objective and constraints directly on your spreadsheet
• A fast QuickSolve mode that makes it much faster to re-solve your model after making changes
• An algorithm to build and update the model only using information present on the sheet
• A modelling tool that we think improves on the built-in Solver window

OpenSolver has been developed for Excel 2007/2010/2013/2016 (including the 64bit versions) running on Windows, and supports Excel for Mac 2011 on Mac OS X, with limited support for Excel for Mac 2016. We currently test against Excel 2010/2013/2016 on Windows 7 and Windows 10, and Excel 2011/2016 on OS X 10.7 through 10.11. Note that we do not check our code against other versions of Excel or Windows/Mac than these. This means we cannot guarantee that the latest release will work on old versions. However, please give it a go and let us know of any problems so we can fix them.

You can download OpenSolver.zip (which is hosted on our Open Solver Source Forge site).  Version details (and dates of updates) are shown on the blog page .

SolverStudio is a free alternative to OpenSolver that is better suited to larger problems. Available as a free download, SolverStudio lets you use Excel to edit, save and solve optimisation models built using modelling languages such as the Python-based PuLP , AMPL , GAMS , GMPL, COOPR/Pyomo and Gurobi’s Python interface. The latest release allows GAMS and AMPL modesl to be solved in the cloud using the excellent free NEOS servers . The SolverStudio interface is fully Excel-based, with the model being edited and run from Excel and stored inside the Excel file. This approach provides a much better modelling solution for complex optimisation problems. Check out the screen shots to see how it works. SolverStudio is much better and faster for large problems. However, OpenSolver is still a great tool for simpler models, or spreadsheets that must be compatible with the built-in Solver.

OpenSolver is being developed by Andrew Mason in the Department of Engineering Science at the University of Auckland , and  Iain Dunning . Kat Gilbert also made valuable contributions to the code while working as a summer student. Current development is lead by Jack Dunn from MIT. Development of OpenSolver is made easier by the excellent Excel Name Manager which displays all the hidden worksheet names used to store an optimization model.

OpenSolver is released as open source code under the GPL . (This simply reflects the fact that distributing a spreadsheet, such as OpenSolver, with embedded VBA code makes that code available to end users by default; to satisfy with our license, you must not password protect that code.) This program is distributed in the hope that it will be useful, but without any warranty; without even the implied warranty of merchantability or fitness for a particular purpose. OpenSolver uses a range of solvers, information on these is available here .

Citing OpenSolver: Continued development of OpenSolver is only possible if we can demonstrate its impact. If you are publishing work that uses OpenSolver, please cite both this opensolver.org website and this paper :

Mason, A.J., “OpenSolver – An Open Source Add-in to Solve Linear and Integer Progammes in Excel”, Operations Research Proceedings 2011, eds. Klatte, Diethard, Lüthi, Hans-Jakob, Schmedders, Karl, Springer Berlin Heidelberg pp 401-406, 2012, http://dx.doi.org/10.1007/978-3-642-29210-1_64, http://opensolver.org

You may wish to download a pre-print of this paper .

[bg_faq_start]

Latex Reference

@INCOLLECTION{OpenSolver, author = {Mason, AndrewJ}, title = {OpenSolver – An Open Source Add-in to Solve Linear and Integer Progammes in Excel}, booktitle = {Operations Research Proceedings 2011}, publisher = {Springer Berlin Heidelberg}, year = {2012}, editor = {Klatte, Diethard and Lathi, Hans-Jakob and Schmedders, Karl}, series = {Operations Research Proceedings}, pages = {401-406}, note = {http://opensolver.org}, doi = {10.1007/978-3-642-29210-1_64}, isbn = {978-3-642-29209-5}, language = {English}, url = {http://dx.doi.org/10.1007/978-3-642-29210-1_64} }[bg_faq_end]

The Excel Solver is a product developed by Frontline Systems for Microsoft. OpenSolver has no affiliation with, nor is recommend by, Microsoft or Frontline Systems. All trademark terms are the property of their respective owners.

Full Guide to Solver in Excel: How to Use + Install (2024)

We make many assumptions when creating financial models in Microsoft Excel.

These assumptions also have limitations 🚧

If you want to find optimal solutions from a model, you must change these assumptions.

It will take hours to get the answer if you try to change these assumptions manually ⏳

That’s where the Excel Solver feature comes in handy!

In this lesson, I will explain this complex Excel feature in a very easy manner.

You can download the workbook and follow along with me.

Table of Contents

What is the Solver add-in?

How to add the solver add-in, how to use solver in excel, frequently asked questions.

The Solver add-in is an Excel optimization tool.

You can use the Excel solver to perform a what-if analysis with multiple variables in your model.

It will find an optimal solution for a formula by changing the related variables.

We can add constraints on how much one variable can vary when we change the others.

The solver is useful for Linear programming problems in business, programming, and engineering.

Because of this, Solver is also known as a “Linear Programming Solver.”

However, you can solve both non-smooth or continuous functions as well as smooth non-linear issues with the Excel Solver.

The Solver add-in, like the Analysis ToolPak of Excel, is a Microsoft Excel add-in program.

As a result, it is not immediately available in Excel by default.

We must install it 🤔

Let’s look at how to add the Solver add-in to our Excel workbook.

• Go to the file tab in your Excel file.
• Select options at the bottom of the left-hand sidebar.
• Select Add-ins from the Excel options window.
• Then in the Manage box, select excel add-ins and click “Go…”.
• Select the Solver Add-in check box from the Add-ins available box. Then, click OK.

Now the Excel Solver Add-ins are loaded to Microsoft excel 👌

Now, go to the Data tab.

You can see the Excel solver command in the analysis group of the Data tab.

You can click Solver to open the Solver parameters dialog box.

It is important to learn different parts of the Solver parameter dialog box before we use it 🧑🏻‍🎓

Set Objective

The set objective is the first input for the Excel Solver parameter dialog box.

In the set objective box, we have to select the cell reference for the objective cell.

The objective cell must have a formula.

Then you can set the objective cell to one of the following.

• Value of – If you select “Value of”, you must specify the target value in the box.

By changing variable cells

In this “by changing variable cells”, you have to select all the variable cells to get the optimal solution.

The variable cells are directly or indirectly related to the target cell.

Currently, you can include up to 200 variable cells for the Excel solver function.

If the variable cells are non-adjacent cells , you have to separate them with commas.

Subject to the constraints

Excel Solver can find the optimal solution within constraints or limits.

We can add constraints in this constraint box.

To add constraints, click add button.

Then “Add constraint” box will pop up.

You can select the variable cell for the cell reference box and add the limit in the constraint box.

You must specify the relationship between the cell reference box and the constraint box also.

Do you want to delete or change an existing constraint? 👇🏻 Select that constraint from the Excel Solver parameters dialog box. Then click the change button to change the existing constraint. Or click the delete button to delete that.

Select a solving method

In Excel solver, we can select one of the solving methods from the following 3.

• GRG non-linear – For solver problems that are smooth nonlinear.
• Simplex LP – For linear programming problems.
• Evolutionary – For solver problems that are non-smooth.

Now you are familiar with the Excel solver add-in options window 💪🏻

Let’s use Excel Solver to find a solution for a linear programming model.

Linear programming with Solver add-in

In the below example, we have to find how many units we have to sell in order to get the desired outcome of profit.

Let’s learn step by step how to use the Excel Solver solution to our linear programming problem.

• Go to the data tab and click solver to open the Excel Solver.
• Select the objective cell reference.

Our goal is to set the total profit in cell B7 to $900.

So, B7 is the objective cell.

As we need to match the value of the objective cell to $900, we have to select the option of “Value of”.

Then type 900, next to the “value of” option.

• Select the variable cells.

The variable for the target value is the quantity for each cell.

So, in this case, the variable cells are B2, C2, and D2.

• Click the “Add” tab to add constraints for each variable.

Choose each product’s quantity cell as a cell reference.

Then choose the referenced cell’s maximum amount as the constraint.

Next, choose the link between the referred cell.

Here we select “less than or equal sign” because the constraint is maximum quantity.

Again, we need the quantities as whole numbers.

So, we set another constraint for each variable cell as an Integer.

After entering all constraints, the solver displays all constraints for the particular solution.

• Select the “Simplex LP” as we are solving a linear solver problem and click the “Solve” button at the bottom of the window.
• Then, you can see the Solver results dialog box with options to “keep solver solution” and to “restore original values”.

Select the “keep solver solution” and click “OK”.

To get a total profit of $900, we have to sell 149 in vanilla flavour, 114 in chocolate flavour, and 75 in banana flavour.

That’s it – Now what?

Now you have learned Excel Solver a hidden gem of an Excel 💎

You can use this to solve many problems in financial models.

But, before you use Excel Solver for your model, you must make sure all your functions such as IF, SUMIF, and VLOOKUP are correctly applied in the model.

Otherwise, the Solver will take hours to get the solution and may give incorrect results.

Click here to access my free 30-minute online course where you can learn about IF, SUMIF, and VLOOKUP if you haven’t already.

Other resources

If you just want to find out the input value of one cell to get the desired result for the output, use Goal Seek.

And please read our articles on the 7 Best Excel Add-Ins, and How To Add The Analysis ToolPak in Excel to learn more about add-ins.

What can I use instead of Solver in Excel?

When you need to change only one input to get the desired outcome of a formula, you can easily use Excel’s inbuilt feature Goal Seek.

We have to specify the formula cell, the target value for the formula cell, and the cell to change in order to get the target value.

Are there some restraints to Solver?

Only 200 decision variables are currently supported by Excel solver.

This restriction applies to both linear and nonlinear models.

Another problem is that we cannot ensure that we will obtain the solver model’s optimal solution if it is not linear.

Top 20 Common Excel Problems Solved

This is a curated list of errors and common problems that every newcomer in Excel is likely... read more

Steps To Follow

Get Trainings

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

This is a curated list of errors and common problems that every newcomer in Excel is likely to face.

These errors can potentially slow or even stop your work , or simply just act as a hindrance to smooth functioning. To overcome these problems is the first step to advancing your Excel skills.

This article will illustrate the Top 20 Common Excel errors that you might face, or are currently facing and how to tackle them .

We have divided these problems into three categories for easier navigation and understanding:

Formula Issues

Visual and navigation issues, operational issues.

Let’s look at each of these errors one by one and learn how to fix errors in Excel!

1. #REF! Error

#REF! error stands of reference. Excel will display #REF! error when the cell that is referenced to in a formula  does not exist or is invalid . This is usually the case when:

• Referred sheet, column, or row has been deleted
• Formula contains an incorrect or invalid cell reference

Example 1: 

In the table below, you will spot multiple #REF! error within formulas used in the cell.

This has happened because you have deleted a range that contains an explicit cell reference within the formula used.

To get rid of this error message we have to select the cell(s) with this error, use the Find & Replace dialog box and do the following:

Find What: #REF!

Replace With:    (Leave this blank)

Press OK and it will clear the #REF error in Excel within the formula.

Let’s look at the step-by-step tutorial below to understand how to remove #REF in Excel.

Download workbook Replace-the-REF-error-2.xlsx

STEP 1: To check the cell containing the cell, simply click on the cell and press F2.

Here, since you have used an explicit cell reference and it was deleted, Excel is returning a #REF error.

STEP 2: Highlight the table containing the errors.

STEP 3: Press Ctrl + H to open the Find & Replace dialog box.

STEP 4: Under Find What, input #REF! and leave Replace as blank . This is done to replace all the #REF! error with a blank.

STEP 5: Click on Replace All.

This is how your replaced data will look like:

Let’s look at another example when this error occurs due to copy-pasting the formula from other cells.

In the table below, you have sales data for different customers for 4 quarters and a sum formula used to calculate the total sales. The formula used to calculate the total sales value is =SUM(B4, C4, D4, E4) .

If you try and delete Column E (Quarter 4), the sum formula will change to =SUM(B4, C4, D4,#REF!) and return an error – #REF.

This error is caused because the formula to calculate the total sales uses explicit cell reference. When one of the cell references used in the formula is deleted (here cell E4), Excel is unable to calculate the value and returns an error.

A simple fix to this problem is to use a range instead of an explicit cell reference. Let’s look at the step-by-step tutorial to learn how:

STEP 1:  Use formula =SUM(B4: E4)  in cell F4 and copy-paste the formula below to cells F5: F11.

STEP 2: Now delete Column E to get the total sales for only 3 quarters.

If you change the formula from =SUM(B4, C4, D4, E4)  to =SUM(B4: E4) , you will no longer to vulnerable to #REF in Excel. This formula recalculates the total sales value by removing the deleted cell.

Hence, it is advised to use range while writing a formula instead of an explicit cell reference.

Let’s take a look at another example when the error occurred due to VLOOKUP containing an invalid cell reference.

In the table below you have quarterly and total sales for different customers and using the VLOOKUP formula, you have tried to find out the total sales for the customer name mentioned.

The formula used to find the total sales for customers mentioned in cell H4 is =VLOOKUP(H4,$A$4:$F$11,7,0).

If you look into the formula used in detail, you will see that the value used to indicate the column index number is incorrect.

The arguments for a VLOOKUP function is:

• Lookup_value = The value you want to look up in the first column of the table.
• Table_array = The table from which you need to retrieve the data.
• Col_index_num = The column number in the table array from which matching value should be returned.
• Range_lookup = Value should be 1 if you want an approximate match or 0 if you want an exact match of the return value.

Excel is returning an error in this formula because VLOOKUP is looking to return a value from the 7th column but the reference $A$4:$F$11 contains only 6 columns.

To fix this error, use the formula =VLOOKUP(H4,$A$4:$F$11,6,0).

2. #VALUE! Error

Excel displays an #VALUE! error when the variable provided in the formula is not a supported type . This Excel error can occur because of the following reasons :

• Cell is left blank or contains hidden spaces ; or
• Cell contains text instead of the number ; or
• Date is treated as text , etc.

Download workbook Value-Error.xlsx

Here, we are trying to calculate the total sales amount by adding the sales achieved in both regions on different dates.

As you can see when cell D7 adds B7 and C7, it returns #VALUE! error. This is because cell B7 contains text instead of a number .

Let’s fix this!

Change the text nil to number 0 .

You can also use the function SUM instead of using the addition operator (+). As formulas with operators will not calculate cells with tex t and instead display VALUE error.

If you use functions they will simply ignore text values and calculate everything else .

Sometimes, VALUE error will be displayed when the cell contains hidden spaces . These spaces will look like the cell is blank but in fact, it contains a space instead.

In this example, you can see even though cell B7 looks like it is a blank, value is cell D7 is displaying an error . This is because cell B7 actually contains hidden space !

There can be numerous cells that may contain hidden spaces and it may be difficult to spot and remove them.

So, follow these steps below to find extra spaces and remove them!

STEP 1: Select the range that contains hidden spaces.

STEP 2: Press Ctrl + F to open the Find & Replace dialog box and select Replace tab.

STEP 3: Type in an extra space in Find what field and keep Replace field blank .

STEP 3: Press Replace All button. This is remove all the hidden spaces in the selected cells and leave them blank .

All the errors will now disappear!

In this example, we are trying to  add duration to the start day of the project in order to get the project’s end date

Here, cells C7 and C11 are displaying #VALUE! error as Excel cannot recognize the value as a date . This is because date is separated using the  decimal points as delimiters .

Let’s change the delimiter from decimal point (.) to hyphen (-) !

STEP 1: Select the cells containing dates.

STEP 2: Press Ctrl+H to open Find & Replace dialog box.

STEP 3: Type decimal point in Find What field and hyphen in Replace With field.

STEP 4: Press Replace All button.

This will replace decimal point with hyphen. Excel will now treat them as dates and make your formula work perfectly .

3. #DIV/0 Error

This error in Excel occurs when you attempt to divide a number with zero , any value equivalent to zero, or a blank cell .

download excel workbook   Div-error.xlsx

The Excel DIV 0 is a common and simple error that can easily be dealt with . To correct this error, all we have to do is make sure that the cell reference provided does not belong to an empty cell .

In the example below, the formula in column C is a simple dividing formula. But, cells B9 and B12 have blank cells respectively, and hence result from the division in column C returns the #DIV/0  Error.

Now if we simply correct our mistakes by editing the cells in column B , the errors will disappear.

In most cases, the referenced cell should actually contain the value 0 or be empty . A simple solution to this will be to use an IFERROR formula and get rid of the error message.

We can design the IFERROR formula in such a way that it either returns 0 or any value as desired if the result of the original formulas occurs to be an error .

What does it do?

It returns a value that you set if a formula has an error

Formula breakdown:

=IFERROR(Value, Value if Error)

What it means:

=IFERROR(The Formula, What do you want to show if The Formula has an error?)

If you have a calculation that results in an error like, #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, then you can clean it up by using the IFERROR function which allows you to replace the error it with a custom text .

In this example, cell B9 contains 0, and cell B12 is empty. The corresponding cells C7 and C12 are thus displaying #DIV error.

Let’s wrap the division formula around the IFERROR formula and see what happens.

=IFERROR(A7/B7,”-“)

Here, we specified in the formula that if the value in cell C7 returns an error , it will be replaced by our custom text – hyphen (-) .

Apply the same formula to the rest of the cells by dragging the lower right corner downwards.

Hence as cells B9 and B12 had blank or 0 values , the corresponding cells in column C should produce an error but IFERROR changes it to display hyphen (-) .

4. #NULL! Error

This error occurs when the range provided in the formula is not valid and you have provided an incorrect character instead of the required character in the range.

This can be mainly due to two reasons :

• Wrong input instead of a colon (:)
• Wrong input instead of a comma (,)

download excel workbook Null-error.xlsx

Sometimes in a formula, where there is supposed to be “:”(colon), you might have entered space . This can be the result of the error that we are getting as shown below.

In the formula bar, we can see that instead of =SUM(A7:A13), it is entered as =SUM(A7 A13) , which is the cause of the error.

So to correct this, we have to replace that space with a colon(:) .

Like so! As the typo has been corrected, the error has been removed and the formula is returning the correct value.

Sometimes in a formula, where there is supposed to be “,”(comma), you might have entered space .

In this example, we are trying to calculate the average speed in cell B11 based on the 3 entries mentioned in rows 7, 8, and 9. The formula used is:

=AVERAGE(C7,C8 C9)

This can lead to an error that we are getting as shown below:

Here, instead of =AVERAGE(C7,C8 C9) you should enter the formula as =AVERAGE(C7,C8,C9) .

There should have been a comma between C8 and C9 but instead, there has been a typing error and instead of the comma, space was inserted .

Simply removing the space and adding the comma rectifies this error.

5. #SPILL! Error

There are various formulas in Excel that populate results in multiple cells . It is termed as actually spilling the data into the neighboring cells .

The different scenarios when you can encounter this error are:

• Spill range isn’t blank
• Spill range contains merged cell
• Spill range is too big
• Spill range in a table

Excel is unable to fix these errors and display the output. Since the formula cannot populate all the cells it is supposed to, the first cell where the formula is entered shows the #SPILL! Error.

download excel workbook SPILL-error.xlsx

Example 1: Spill range isn’t blank

You want to find a unique list of customer names stated in column A using the UNIQUE function in Excel. But, Excel returns a #SPILL! error instead.

This is because the output range already contains data and Excel cannot overwrite it and populate the desired result.

Simply, clear any contents from the output range and you are good to go!

The spill error can have multiple causes. To understand the root of this problem, click on the small yellow! warning sign next to the error. It shows the cause of the problem .

Most commonly it is caused because there is a cell containing some value , but it needs to be populated by the formula. Removing the value in all such cells will remove the “blockage” and, in turn, the error.

There can be times when you get the SPILL error but you are unable to spot the cell that contains unwanted data .

Click on Select Obstructing Cells and it will take you to the cell that is creating the problem.

This is useful when you have hidden spaces and you are unable to find them.

Example 2: Spill range contains merged cells

SPILL error can also occur when the result range contains the merged cells .

In this example, you can see the spill range i.e. B7:B15 is empty but still, Excel returns #SPILL error .

To be sure of the reason , let’s click on the yellow warning sign and see what it says – Spill range has merged cell.

Now, click on Select Obstructing Cells and it will take you there.

Go to Home > Merge & Center to unmerge the cells .

The result is now visible and the error disappears!

Example 3: Spill range is too big

This error occurs when the output range is too big and the result goes beyond the boundary in Excel . There isn’t enough space to display the result.

In this example, we are trying to calculate the discount given to the customers based on the sales amount . Column C is used to get 5% of the sales amount.

As you can see, Excel is again and producing a SPILL error and this time it is because the range is too big .

Here, Excel is trying to multiply each cell in Column B by 5% and produce a million results . It will spill the result in Column C starting from cell C7 but it will reach the end of Excel.

Hence, the error!

To solve this, simply replace the column with the relevant range and copy the formula to the output range.

Example 4: Spill range in a table

Excel tables do not support spilled array formulas . If you try to insert an array formula in an Excel table, it will return a #SPILL error.

In this example, we are trying to sort the amount mentioned in Column A in ascending order using the SORT function. It will return a dynamic array as a result in column B.

Unfortunately, we are getting a #SPILL Excel error.  This is because range A7:B15 is in fact saved as a table in Excel and dynamic functions do not work within Excel tables.

We can easily convert it into a range for our SORT function to work properly.

To do so: Right Click on any cell in the Table range > Select Table  > Select  Convert to Range .

This will convert the table to range and now all array functions will work perfectly as shown below!

6. #NAME? Error

The #NAME? Error is a fairly common one that even seasoned Excel professionals can face. It occurs when there:

• Spelling error in Formula name
• Spelling error in Cell Range
• Spelling error in Named Range
• Text entered without quotes

download excel workbook Name-error.xlsx

Example 1: Formula Name

When entering the name of the formula if the name is misspelled , then that can lead to the occurrence of this error.

In the above example, you can see that instead of writing =LEN(A7), we have typed =lan(A7) . There is an error in the formula name, leading to the problem.

Now you can see, simply correcting the name removes the error displayed .

The best way to not get #NAME excel error  is to choose the formula in the drop-down list while typing the name of the formula. This makes sure that the formula name is not misspelled.

Example 2- Cell/Range name

When entering a formula that references a certain cell or range , misspelling the name of the cell or range can lead to this error.

Here we can see that the name of the cell is misspelled . Instead of =LEN(A7), it is typed as =LEN(AA) .

To rectify this, simply type in the correct cell name .

A better way of avoiding the error is to select the cell when referencing in the formula. This will lead to always entering the correct cell name.

NAME error in Excel  can occur when entering a range . Check and correct the range name or select the entire range when entering the formula to rectify the error and prevent it.

Example 3- Named range

There can be particularly important ranges in your workbook that you have assigned a name to. When referencing this range in a formula, misspelling it can lead to the #NAME error in Excel .

Here we can see that the range referenced to in the formula is spelled agee when the correct name is Age .

Correcting the name of the range or simply selecting the range when referencing it in the formula will correct this error .

Example 4- Text without quotes

Wherever a text is entered into a formula , it should be entered in quotes . When a text value is not confined between quotes , Excel reads it as either a formula or a named range /table.

If there is no formula or named range in your workbook that matches the text written in the formula, Excel will return the #NAME? Error.

Here we can see the text entered in the formula is not in quotes and hence Excel is returning it as an error.

Now that the text is written in quotes , Excel is returning the correct value of the formula.

7. #NUM! Error

Excel shows the #NUM! Error when values in formulas are invalid . It basically means that the calculation cannot be performed due to limitations or errors .

This can be due to the fact that the number is too big or small, impossible calculations , or when the data type is not supported in the argument.

download excel workbook Num-error.xlsx

Example 1 – Number size

Excel has a limitation to the size of the number that it can display. If you enter a number is too large or too small ,you will get Excel #NUM Error .

As you can see, in cell C7 we are trying to multiply 5 by itself 500 times . The result will be a really large number that Excel is unable to display. Hence. it returns NUM Excel.

In cell C10, we are trying to divide 1 by 10^750 . Since this will be a very small number Excel cannot perform calculations and hence returns #NUM error.

Excel is not able to show these values because of its inherent limitations. As shown, other smaller values are calculated and shown in C8 and C9.

Example 2 – Impossible calculations

Excel returns the #NUM! Error when it is faced with a calculation that is impossible to do.

As you can see, the square root or log of the positive numbers has results but the square root or log of the negative numbers is impossible to calculate .

In this example, we are trying to find the square root of -3 .

Since we are trying to calculate the square root of a negative number, we will get #NUM error in Excel. In this particular case, you can rectify this issue by taking the absolute value of the number.

ABS function returns the absolute value of a number . It converts negative numbers to positive numbers and keeps the positive numbers unaffected.

Including the ABS function into the formula uses the absolute value of the number in column A, so now Excel can calculate the number and return a valid answer.

Example 3 – IRR function not working

Excel might show the #NUM! error when using IRR function because of either one of the two reasons:

• Cash flow does not contain at least one negative and one positive value .
• Formula can’t find a result .

In this example, Excel results a #NUM error instead of proving a value .

This is because the cash flows provided in the range (B7:B15) are positive and IRR requires at least one negative value as the initial cost of business.

Initial payments are provided as negative value indicating outflow of cash and income payments are represented by positive values. Once you have incorporated this, the function will provide the correct result!

IRR function can also display #NUM! error when the function iterates and can’t find a result after several tries .

To overcome this, you can increase the maximum number of iterations that Excel performs to get a result.

To increase the number of iterations:

STEP 1: Go to File > Options > Formulas to arrive at the window below

STEP 2: Under the Calculation options group, check the Enable iterative calculation box.

STEP 3: In the Maximum Iterations box , type the number of times you want Excel to recalculate .

The higher the number of iterations, the more time Excel needs to calculate a worksheet. Increasing this can help give a result to your calculation.

STEP 4: In the Maximum Change box, enter the amount of deviation that you are willing to accept between the calculation results.

The smaller the number, the more accurate the result. Increasing this can help give an approximate answer to your calculations.

8. Excel #### error

Sometimes in Excel, especially when you open a file on which you have worked before , there may be some cells that are filled with “#” signs.

Excel #### error is a very simple but common issue that can easily be solved. There are three simple ways to tackle this problem.

download excel workbook Hash-Error.xlsx

Taking your cursor to the divider between two column heads, the cursor will change into a dragger . Dragging the column head divider will change the width of the column.

Now you can increase the width to fit all the characters of the cell, or to any width that you want.

Simply double-clicking on the rightmost edge of the column head, that contains the cell filled with # marks, will autofit the column so that it perfectly adjusts and shows all values in cells.

It will adjust to the length of the longest cell in the column.

Upon applying the above steps, your values will then be visible as shown.

Another way to overcome this problem is auto-shrinking text to fit in the column width. To accomplish this, follow the steps mentioned below:

STEP 1: Select the column that shows #### error.

STEP 2: Click on the Home Tab.

STEP 3 : Click on expand icon at the bottom right corner of the Alignment group.

STEP 4: Check the Shrink to fit box in the Format Cells dialog box.

Upon applying the steps, the size of the text will be reduced to fit in the column width and it will look like this:

9. Worksheet Tabs not Visible

A common issue faced while operating on Excel is that your worksheet tabs ( shown at the bottom left) are not visible to you .

This is a simple settings problem that can be easily undone.

download excel workbook Worksheet-tabs.xlsx

Ever encountered this situation where you open your workbook and said “Where are my worksheet tabs ??”.

A common issue faced while operating on Excel is that all the worksheet tabs, which are shown at the bottom left of the window, are not visible to you .

Just follow the steps below to learn how to display the missing worksheet tabs:

STEP 1: Go to Files

STEP 2: Click on Options

STEP 3: Click on Advanced

STEP 4: Under the Display options for this workbook, check Show sheet tabs .

Now your sheet tabs will be available ! Now you can easily navigate through the entire workbook , and work on all sheets.

This is what to do when worksheet tabs go missing and you are unable to switch between different tabs!

It can also be that some of your worksheets might be hidden , hence you will not be able to see them in the worksheet’s tab.

Here, we actually have two worksheets, but one of them is hidden .

To show the worksheet tabs:

STEP 1: Right-click on any visible tab and select Unhide.

STEP 2: Select the tabs you want to unhide and press on OK .

Now both the worksheets will be visible to you on the worksheet’s tab.

10. Break Line displayed on the Worksheet

While working on Excel, sometimes you may see solid or dashed break lines added and the worksheet is divided into multiples pages .

The solid lines are manually inserted ones whereas the dashed line is automatically generated .

These are basically page breaks inserted in Excel for printing purposes ! They may be useful when you are trying to print a document but other times are just visually distracting .

You may want to remove these lines before presenting your worksheet to someone!

In this article, we will cover the different ways to remove lines from the worksheet:

Solid Page Break Line

Dotted Page Break Line

download excel workbook Break-Lines-on-Worksheet.xlsx

In this screenshot, we have two solid lines inserted in the worksheet.

These are manually added page breaks and can easily be removed by following the  step-by-step tutorial below:

STEP 1: Select the cell just below the page break line.

STEP 2: Go to the Page Layout tab.

STEP 3: Click on Breaks in the Page Setup group.

STEP 4: Click on Remove Page Break .

This will remove the page break between row 9 and row 10!

Now, follow the same steps to remove the solid line between rows 15 and 16.

When you select the Page Break Preview in Excel, the dotted and solid lines dividing the worksheet into different pages become visible.

Now when you go to View > Normal , the page preview is returned to normal but the lines still visible.

To remove the solid lines, follow the steps mentioned below. But, this will not remove the dashed lines.

To remove the dashed line, follow the steps below:

STEP 1: Select the File tab.

STEP 2: Click Options from the menu on the left side

STEP 3: In the dialog box, click on Advanced from the left panel

STEP 4: Under display options for this worksheet, uncheck Show Page Breaks .

This will remove the dashed lines from the worksheet as well!

11. Clicking Enter key goes to the cell underneath

In general, when you press enter in Excel it solidifies the work done in a cell but it automatically takes you to the cell below the one you were working on . If this is a hindrance to you, you can easily change it.

To alter the direction in which Excel files after pressing enter, follow these steps:

• Click on the File tab on the ribbon above
• Click Options from the menu on the left side
• In the dialog box, click on Advanced from the left panel
• Under Editing Option, Select After Pressing Enter Move Selection, and from the drop-down menu the choose the direction in which you want Excel to move.

Now Excel will move in the direction you want after pressing enter. Now you try it!

12. Show column headers on each page when printing

If you have a large data file with headers on the top , you can freeze the columns to easily view the entire table without losing sight of the headers.

But the freeze feature will not be applied when you try to print the file.

To print Excel header row on every page , you can try one of the following methods as per your requirement:

• Repeat row on every page
• Repeat column on every page
• Print row numbers and column letters

download excel workbook Print-Row-and-Column-Headers.xlsx

Example 1: Repeat row on every page

If you have a large worksheet that spans over multiple pages, you can easily print the first row of the data as headers in Excel on every page .

Follow the steps below to print a row of every page:

STEP 1: Go to Page Layout tab > Select Print Tiles

STEP 2: In the Page Setup dialog box, select Sheet .

STEP 3: Select the collapse dialog box and pick the row you wish to repeat.

STEP 4: Click OK .

STEP 5: Press Ctrl + P to open Print Preview.

As you can see we are on page 5 but the 1st row is still visible!

Example 2: Repeat Column on every page

When your Excel worksheet has multiple columns and is spread across several pages, you can fix the first column.

Follow the steps below to fix the first column when printing:

STEP 1: Go to Page Layout > Select Print Tiles

STEP 3: Select the collapse dialog box and pick the column you wish to repeat.

STEP 4: Click OK

And it’s done! Column A is still visible when you go to page 2.

Example 3: Print row numbers and column letters

Excel denotes the worksheet’s columns as letters (A, B, C, etc) and rows as numbers (1, 2, 3, etc). But when you try to print your worksheet, it is not visible at all.

You can easily print numbers and column letters by following the steps below:

STEP 3: Check Row and column headings

STEP 5: Press Ctrl + P to view Print Preview.

The row numbers and column letters will be displayed in the print preview section!

13. Not using Quotation Marks for Text in Formulas

When writing formulas, Excel will identify a text only if it’s written within quotation marks . Excel e xtracts anything written within the quotation mark as text and discards the quotation mark.

Let’s use a formula to use to help better understand the use of quotation marks while writing a formula!

Download workbook Quotation-Mark.xlsx

In this example, we have tried to insert text like “Email ID of” and ” is” but Excel is returning an error . This is simply because Excel will not consider text if it is not provided within quotation marks in a formula.

Now, put them within quotes and try using this formula again.

And Voila, the error is resolved and we can now insert custom text within the formula easily.

14. How to Use Numbers as Column Headings

You may have faced a situation when you need to use numbers as column headings in the table . But, when you put in digits as heading, Excel will treat it as a number and not text .

As a result, when using auto sum or other similar features the heading will also be included with other values.

Download workbook Number-as-Column-Header.xlsx

This is not the result you were expecting ! Let’s fix it by forcing Excel to consider the number as text.

To do that, simply add a ‘ symbol in front of the number and it will be treated as text.

See, it is that simple! Try it now!

15. Unable to Directly Edit the Cell

If you are unable to edit data entered in your Excel worksheet and you are not sure how to fix it, you have come to the right place. There are several ways in which you can easily enable editing cells and learn how to edit cells in Excel.

• Excel file is read-only
• Excel worksheet is locked
• Editing in cell disabled in Excel

Example 1: Excel file is read-only

You may not be able to edit any cell in Excel because the Excel worksheet has been saved as read-only . This will force Excel to keep the worksheet in view mode and you can make no changes in the file.

To fix this, simply press the Enable Editing button on the top of the Excel worksheet.

On you press that button, you can easily edit any cell in Excel.

Example 2: Excel worksheet is locked

Another reason why you may not be able to edit cells is that your Excel worksheet has been locked . You have to unprotect your worksheet first and then you can make changes in your file.

To make changes in Excel, follow the steps below:

STEP 1: Go to the Review tab

STEP 2: Click on Unprotect Sheet

STEP 3: Enter the password in the unprotect sheet dialog box and click OK .

The password for this worksheet is “myexcelonline”.

This will unprotect the sheet and you will be able to make the edits you want.

Can’t remember the password that you had used to lock your Excel file? Click here to learn how to unprotect active worksheets using macros in Excel.

Example 3: Editing in cell disabled in Excel

You might not be able to directly edit the contents of a cell by pressing the F2 key.

This might be because it has been disabled in Excel. You can easily enable or disable this function by following the steps below:

STEP 1: Click on the File tab

STEP 4: Under Editing option, check Allow editing directly in cells. Click OK at the bottom.

That’s it! Now you can enable the editing cell feature in Excel. To disable Edit mode, clear the  Allow editing directly in cells check box.

16. Unable to use the Fill Handle

Excel has many tools that can automate your work and make it much easier and faster. One of these essential tools is the fill handle . It helps fill in a value in the adjoining cells in sequential order of data.

If you are unable to use this feature , it may have been disabled or the filled values are not as per your pattern:

• Fill Handle Excel – not visible
• Not selecting all values
• Auto Fill options

Download workbook Fill-handle.xlsx

Example 1: Fill Handle not visible

If you are unable to use this function , you can simply fix it using the steps below:

STEP 3: In the Advanced category, under Editing options, select the Enable fill handle and cell drag-and-drop check box.

Once you have enabled this, you can easily use Excel Fill handle feature!

Example 2: Not selecting all values

It can even be that you have not selected the entire range that has your sequential values . Just selecting the last cell will fill only that value in the cells where you drag the fill handle in Excel.

For example, if we drag the fill handle after only selecting cell B8, the entire range is filled only with the value “1002”.

Selecting the entire range and then using the fill handle will fill the new cells in sequential order .

Selecting B7 and B8 will help Excel recognize the pattern and correctly fill the subsequent cells.

Example 3: Auto Fill options

Suppose you have entered the date and you want to use the fill handle to get only weekdays . If you use the fill handle like before , it will just add the next dates in adjacent cells .

You can use the AutoFill option to get dates that are only falling on weekdays!

STEP 1: Select the entered values.

STEP 2: Go to the bottom right of your selection , you can drag the fill handle  to fill dates below.

This will fill the dates one after the other!

STEP 3: Click on the Fill Handle button at the bottom right.

STEP 4: Select Fill Weekdays .

This will just include the weekdays and ignore the weekends like 4-Jan-20 and 5-Jan-20.

You can use other options as well like Fill Formatting only, Fill Months, Flash Fill, etc.

17. Not able to understand the inner workings of a formula

Excel provides a lot of functions at your disposal for efficient working and data management. Formulas can be used widely in Excel to overcome or calculate all sorts of different informational data.

But these formulas can be tough to decipher at first, especially the working of a formula. Here is how you can better understand a formula:

When you enter a formula in the cell, you can press Ctrl+A to enter the function arguments window. Thi s shows a detailed explanation of each segment of the formula so that you know which data to feed in which segment and get accurate results.

To better understand this, let us take the example using the VLOOKUP formula. Here, we need to extract the salary of Rachael in cell D15 .

download excel workbook Understand-Excel-Formula.xlsx

Let’s open the arguments wizard for the VLOOKUP formula.

STEP 1: Enter the VLOOKUP function: =VLOOKUP(

STEP 2: Press Ctrl+A or click on the fx button in the formula bar to open the arguments wizard window.

This is how the formula wizard looks like.

It shows all the arguments in sequential order. It then explains the use of the function and the details of the argument selected.

STEP 3: Select the first argument i.e. Lookup_value . Here, cell C15 contains the name, Rachael.

You can also see the description of the first argument is written at the bottom of the dialog box.

STEP 4: Select the second argument i.e. Table_array  and select the table range from where you can search i.e. A7:C13 .

STEP 5: Select the third argument i.e. col_index_num and enter the column number from which to extract the data. In this example, it should be 3.

STEP 6: Lastly, select the fourth argument i.e. range_lookup . Since we want an exact match type FALSE or 0 .

After entering the details, the wizard would look like below. It would even show a preview of the argument values and the answer below it if the inputs were correct.

STEP 7: Press OK .

The formula will be entered in cell D15 and it will show the result, i.e., the salary of Rachael – $16,540 .

Ctrl+A opens the Function Arguments window immediately after typing the name of the function.

Now that you can easily understand each formula, have fun working on Excel!

18. Cells showing formulas instead of calculated values

While working on an Excel worksheet with a lot of formulas in it, it can become challenging to keep track of all your formulas .

It may happen that the formulas entered in each cell is displayed on the worksheet instead of the value/result .

You can easily reverse this and display the result instead of formula. Let’s see how it can be done!

download excel workbook Showing-Formula.xlsx

Ctrl + ` is the shortcut for the formula view, pressing it can change it back to normal view and show your calculated values.

Now you have your cells showing calculated values instead of formulas applied. Try it yourself!

19. Formula does not update when value changes

Excel provides dynamic formulas that update their calculated value as soon as the source data is altered.

An issue faced by people can be that the cells are not updating automatically ,i.e, they are showing the old values even when the dependent cell’s values have been changed. Here is how you fix this issue.

The issue that you are facing is most likely caused by an accidental change in the calculation setting from Automatic to Manual . Steps to change this are:

Go to Formula Tab > Calculation Options > Automatic .

Now your formulas will be getting updated which changes in the source data. Now you try it!

20. Formula not working due to text format in cells

Sometimes even when have entered a number in the cell, it is stored as text and thus produce error while computing in Excel.

download excel workbook Formula-not-working.xlsx

In this example, cell F7 uses sum function to calculate the total amount for all 4 quarters. Even though there are figures in range B7 to E7 the calculated amount in cell F7 is 0 .

This is because each of these number are actually stored as text .

Select the cells that are the source for the formula calculation. Click on the yellow triage icon and select Convert to text.

This will convert the text to number and now the calculated amount will also display the correct value!

It is that simple! Now you try it!

This completes our tutorial on the Top 20 common errors like REF error, NUM error, Excel #### error, etc. that we can encounter while working on Excel .

Now that these common issues are out of your way , we hope that your Excel operations have improved . If you want to continue this journey of improvement, you can further check out our blog as well as our YouTube channel. They offer a ginormous amount of content, a huge library of information provided to you for free.

See you next time!

Make sure to download our FREE PDF on the  333 Excel keyboard Shortcuts here :

John Michaloudis

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course .

Related Articles

The Ultimate Guide to Drag Formulas Down in Excel

The Ultimate Guide to Deleting Rows in Excel

How to Indent in Excel Cell – Step by Step Guide

Get Video Training

Dramatically Reduce Repetition, Stress, and Overtime! Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job! Learn in as little as 5 minutes a day or on your schedule.

Excel Exercises

Practice your excel formula skills with exercises that you can do straight from your browser, first time here.

Note: It appears you are using a small screen device (like a phone) to access this page. You may not have the best experience when you do an exercise on such a small device. Instead, you will have a better experience using a laptop or desktop computer. We are sorry for the inconvenience.

Making an If cell Contains text function

The excel isnoterror function: making your own formula, fixing text to be exact, practicing and, or, and if, counting blank rows, creating an ifnoterror function, basic iferror exercise, excel exercises to learn practically.

Spreadsheet Center was made to teach people about Microsoft Excel in a practical and easy-to-digest manner. Here you will find Excel exercises for free to learn several basic as well as advanced concepts.

Type of exercises available

We recommend using the beginner’s track for first-time visitors. It familiarizes you with basic concepts and gradually increases the difficulty level. It features several lesson plans with practical spreadsheet experience within the site window. Once you familiarize yourself with Excel, you can come to the Exercise section. Here you will find several exercises to practice different Excel concepts. The type of exercises here have different difficulty levels and also indicate the time required to practice them.

All these Excel exercises for students help them practicing and learning Excel by doing. The time needed for doing a single exercise is short, they take only 1-4 minutes of your time. The best part about these Excel exercises is that you can use a Spreadsheet Editor on the site itself to practice. You do not need to open Excel on your computer and get confused or overwhelmed with other options.

Easy Exercises

More straightforward exercises to boost your confidence and morale. Here are some of the activities available at your disposal that you can quickly practice in this section.

• My First Formula : Here, you learn how to use the = (equals) sign to create a formula using cell references.
• EXACT function : Work on your skills with a function that checks two text arguments to find if they are precisely the same or not.
• IFERROR function : Learn about an often overlooked function that returns a value if an error is present.
• COUNT function : An exercise where you count the number of blank rows in a range of cells.
• MIN and MAX functions : Find the minimum or maximum values in a range.
• CONCATENATE function : Combine two texts to make a new, larger, text.
• SUM/AVERAGE functions : You can use these to add or takes an average of a given range of numbers.
• SUBSTITUTE function : Excel exercise about a function that replaces occurrences of a specific text in another text.
• Create a new ISNOTERROR function : Using the ISERROR function, we create a new is not error function.

Exercise with Medium Difficulty

Excel exercises with medium difficulty take a while to solve. They improve your mastery and skill levels.

• TODAY, YEAR, and CONCATENATE functions : Combine three Excel functions to create an up-to-date copyright message.
• IFNOTERROR function : A twist on the IFERROR function: show a different value when an error is not present.
• IF function with AND and OR : Learn about these three powerful logical Excel functions. And how you can combine them.
• CONTAIN function : A function that is missing in Excel: CONTAINS checks if a cell contains a specific text or not.

We’re working hard to ensure that you get the best learning experience here. That’s why we keep updating the site with new content and Excel exercises so that you will find fresh new exercises and activities when you visit.

Why Would You Want To Do Excel Exercises?

There are many types of learners with varied objectives. People would want to do Excel exercises online for several motives:

• To practice : The idea of solving Excel exercises online is a game-changer as it helps you practice at your own pace and skill level.
• To determine your skill level : Some people experiment with online Excel exercises to check how proficient they are with the software.
• To brush up on your skills : Excel exercise for students, teachers, beginners, or any other category of users helps them brush up on their skills.
• To update your knowledge : For some people it may have been a while since they last used Excel. A quick scroll through different exercises allows them to find gaps in their knowledge that they can fill in.
• For a creative kick : Some people enjoy creatively solving exercises. Such people like to challenge themselves with exercises that are at or above their skill levels.

Who would want to do Excel Exercises?

• Students : School, college, or university students are prime candidates for doing Excel exercises online. Excel exercises for students can help them in their studies, project reports, course curriculum, or additional knowledge.
• Teachers : Why would a teacher want to do Excel exercises online? Teachers can teach their students properly if they stay up to date and learn different ways of solving the same problem.
• Beginners : Excel beginner exercises are quite effective in getting into Excel. As a beginner, you may expect to do a lot of reading and watching videos to learn Excel. But instead, you can also actively practice and build confidence to learn more. That’s what our beginner exercises are all about.
• White-collar workers : Some office workers want to learn the software to help with their routine jobs. They would like to do Excel exercises that help solve their work issues, remove repetitive work or improve their productivity.

How should you approach solving them?

There are several approaches to solve Excel exercises, but some of them are truly useful.

• Trial and error : A time-tested approach to solve problems. Never be afraid of trying new concepts. At most, you make mistakes, but you get to know something in the process. Try different combinations of possible methods to solve a problem. Build on your mistakes and find the solution by trial and error.
• Systematic approach : In this approach, you systematically solve Excel exercises. Start with the easy ones and move up gradually. If you are a beginner, begin with the first lesson, revise what you know, and learn holistically. Try to read the theory before jumping on to practice.
• Learning by doing : In this approach, practical experience is much more valuable than theory. You know something quickly when you do it yourself. Also, you don’t forget it easily.
• Thinking out-of-the-box : If everything else fails, you may be able to solve an Excel exercise by thinking differently. Who knows, you may invent something new in the process!

Spreadsheet Center was made as a platform to learn by doing. These Excel exercises help you to improve your skills in a practical way. You can enroll in an Excel course or try Excel exercises free here. We’ve got a whole lot of Excel beginner exercises along with more advanced stuff. There is something for everyone here. Whatever you choose to do, we hope you have a lot of fun doing it!

Excel Linear Programming (Using Solver and Graphical Methods)

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 Do Linear Programming with Solver in Excel?

Excel Solver Add-in is a built-in tool, which is used for solving linear or non-linear optimization problems in Excel. It is a four-step process: adding Excel Solver Add-in, formulating a linear programming problem, tabulating the problem, and finally, solving the problem using Excel Solver .

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. Here, we will solve the following linear programming model which has an objective function Z , which we 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?

We have to format the coefficients of the objective function and constraints in a tabular format to solve the linear programming model with the Excel Solver Add-in.

• 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.

Insert 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  in Subject to the Constraints . From the dropdown menu of 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 .

We 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 Do Linear Programming Using the Graphical Method in Excel?

If a linear programming problem consists of only two variables, we can use a graphical method.

How to Define and Formulate the Problem?

Define an objective function and some constraints for the linear programming problem. Here, the linear programming model has an objective function Z, which we 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?

Although the given constraints are inequality constraints, consider them as equality constraints while plotting charts. The inequality operators to find the feasible region will be then used.

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 press OK .

• Click Add .

• Set the Series Name to range $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 constraint lines is displyed.

• 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, we can apply 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

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

Super Charge Solver for Your Largest Problems

Discover a whole world of easy-to-use analytics software tools, solve models from thousands to millions of decision variables.

• Automatically Analyze and Transform Model Formulas for Better Solutions
• Find Optimal Present and Future Decisions Under Uncertainty
• Much More: Monte Carlo Simulation, Decision Trees, Data Mining, Text Mining

Get More Optimization Power in Solver and Support and Training from the Pros

• Excel Solver users : Solve much larger models, much faster, more easily with automatic model diagnosis - 100% compatible upgrade from the developers of Excel Solver.
• Use Tableau and Power BI dashboards to run Solver models you created in Excel.
• Build models once , use with many Solver Engines, including best-in-class optimizers such as Gurobi, Xpress, Knitro, OptQuest and Frontline's Evolutionary Solver.
• Pay for only what you need in capacity/performance, get everything else for free: Monte Carlo simulation, decision trees, powerful data mining and text mining!

Proven in Use over 25 years in over 9,000 organizations, including more than half of the companies in the world with $1 billion + revenue.

Free Trial: Fill out the form to register for a 15-day trial that lets you:

• Access Analytic Solver (Excel for Windows & Macintosh, and Excel for the Web)

• Immediately solve larger models and use new Solver Engines in Excel Online
• Access our User Guides, Reference Guides and 100+ Example Models
• Get Pro Support via Live Chat, Phone & Email, plus Training Options

To ensure you get value from your free trial, a Frontline rep will contact you to understand your goals and set problem size parameters for your trial license.

By submitting this form, you consent to certain follow-up communications as described in our Privacy Policy . Your information will not be shared with third parties.

Online, TechConnect: *Online: Excel Genius Class 9: String Functions and Goal Seek

This event is online only.

Must register with a valid email address.

This class will cover how to use functions to modify strings, a useful tool when scrubbing data, and how to use goal seek to solve common problems.

Join the class using this link:

https://nypl.zoom.us/j/88673605127?pwd=DmXZjzSh7Wb1KHYuBZatTRvSozBolB.1

• Audience: Adults