Yes. Excel Solver can allocate limited resources by choosing values for decision cells that maximize an objective, such as profit or output, while obeying constraints such as labor hours, budgets, and capacity. In Excel for Microsoft 365 on Windows, open Data > Solver; define the objective and changing cells, add constraints, choose a solving method, and select Solve.
What resource allocation means in Excel
Resource allocation is deciding how much of each activity, product, project, or task to fund or produce when resources are limited. The resources might be staff time, machine hours, raw materials, cash, floor space, or a combination. Solver is useful when the choices affect one another: assigning more time to one project may leave less for another, while each project may have a different return and resource requirement.
A typical allocation model has three parts:
- Decision variables: the quantities Excel is allowed to change, such as units produced or hours assigned.
- Objective: a single value to maximize or minimize, such as total contribution, completion time, or total cost.
- Constraints: limits or requirements the solution must satisfy, such as no more than 100 labor hours or at least 20 units of a product.
Solver does not infer these rules from a spreadsheet. You must express the objective and each important limit in worksheet cells, then connect those cells in Solver. A neat table and clearly labeled units make the model easier to check and update.
How Solver models an allocation
For a linear allocation problem, the objective and constraints are built from linear relationships. For example, if product A earns $40 per unit and product B earns $30, total contribution is 40 times the number of A units plus 30 times the number of B units. A labor limit might be 2A + B ≤ 100. These relationships can be calculated in worksheet cells and supplied to Solver.
“Linear” means that each decision variable has a constant coefficient and variables are not multiplied by one another or raised to a power. A model such as 2A + B is linear; A × B is not. Linear models are often easier to solve and interpret, and the Simplex LP method is designed for them.
Resource limits are usually upper bounds, but allocation models can also include minimum commitments, equality requirements, or logical choices. For example, a minimum service level can be expressed as a lower-bound constraint. If an activity must either happen or not happen, a binary decision variable can represent that choice.
Build a worked allocation example
Suppose a small workshop makes two products. Product A contributes $40 per unit and uses 2 labor hours, 1 machine hour, and 3 kilograms of material. Product B contributes $30 and uses 1 labor hour, 2 machine hours, and 2 kilograms of material. The workshop has 100 labor hours, 80 machine hours, and 150 kilograms of material available. The aim is to maximize contribution.
Create a table with product names in B1:C1, the decision quantities in B2:C2, contribution per unit in B3:C3, labor per unit in B4:C4, machine hours per unit in B5:C5, and material per unit in B6:C6. Put the labels in column A. Use D3:D6 for calculated contribution and resource usage, and enter the available capacities in E4:E6: 100, 80, and 150. Start B2:C2 at zero; Solver will change those cells.
With decision quantities in B2:C2 and per-unit contribution in B3:C3, enter this objective formula in D3:
=SUMPRODUCT($B$2:$C$2,B3:C3)
It multiplies each quantity by its contribution and adds the results. With the optimal solution, D3 returns 2075. For labor usage, with the same decision cells and labor coefficients in B4:C4, enter this formula in D4 and copy it down through D6:
=SUMPRODUCT($B$2:$C$2,B4:C4)
The copied formulas calculate labor, machine, and material use. For the solution below, D4:D6 show 92.5, 80, and 150 respectively. The model therefore respects all three capacities. The dollar figures and capacities are illustrative, not a recommendation about a real business.
Set up Solver step by step
If Solver is missing from the Data tab in Excel for Microsoft 365 on Windows, enable the add-in: select File > Options > Add-ins. In the Manage box, choose Excel Add-ins and select Go. Check Solver Add-in, then select OK. On a Mac, enable the Solver Add-in through Tools > Excel Add-ins; after it is enabled, open Solver from the Data tab. The built-in Solver add-in is a desktop Excel feature; do not assume it is available in Excel for the web.
For the workshop example, open Data > Solver and enter the model as follows:
- Set Objective to D3 and choose Max.
- Set By Changing Variable Cells to B2:C2.
- Add the constraints D4:D6 <= E4:E6.
- Ensure the decision quantities cannot be negative. You can use the non-negative variables setting or add B2:C2 >= 0.
- Choose Simplex LP as the solving method, then select Solve.
For this continuous example, Solver’s best solution is 35 units of A and 22.5 units of B, for a contribution of $2,075. The resource checks are 2(35) + 22.5 = 92.5 labor hours, 35 + 2(22.5) = 80 machine hours, and 3(35) + 2(22.5) = 150 kilograms of material. Machine time and material are fully used; labor has 7.5 hours left. If units must be whole numbers, add an integer restriction to B2:C2 and solve again. The result can differ because fractional quantities are no longer allowed.
Choose the right solving method
Solver offers different methods because models have different mathematical shapes. Selecting the wrong method can produce a poor result or make solving fail. Match the method to the formulas in the objective and constraints:
- Simplex LP: for linear objectives and constraints. It is the natural choice for many product-mix, staffing, shipping, and budget allocation models.
- GRG Nonlinear: for smooth nonlinear models, where formulas change continuously but are not linear. Results can depend on starting values, so check whether the solution makes practical sense.
- Evolutionary: for models with discontinuities or relationships that are not smooth. It may search more broadly, but can require more time and may not find the same quality of solution as a well-structured linear model.
Integer or binary restrictions are useful when decisions must be whole numbers, such as people, machines, or yes/no project choices. They can make a problem harder to solve. For a linear model with integer decisions, select Simplex LP and add the appropriate integer or binary constraint; Solver then has extra work to enforce those restrictions. The standard Solver add-in has a limit of 200 changing variable cells, so a larger model may need a different tool.
Check and interpret the result
A Solver result is a mathematical answer to the model you supplied, not proof that the model captures every real-world requirement. Review the variable values, objective value, and each constraint after solving. In the example, it matters that machine time and material are binding while labor is not. Unused labor may be unavoidable under the other limits, or it may suggest that a capacity or coefficient was entered incorrectly.
When Solver reports a solution, choose whether to keep it or restore the original cell values. For linear models with continuous variables, Solver can also provide a Sensitivity Report. It helps explain how changes in objective coefficients or resource limits may affect the solution, within the report’s assumptions and ranges. Sensitivity analysis is not available for integer-constrained models in the same way, so do not interpret a report from a different model type as if it were one.
Useful checks before relying on a result include confirming units, recalculating the worksheet, and testing a few constraints by hand. If the model uses dollars in one place and thousands of dollars in another, or hours in one row and minutes in another, Solver can optimize the wrong quantities while still returning a mathematically valid answer.
Common problems and ways to fix them
- Solver says there is no feasible solution: inspect lower and upper bounds, minimum requirements, and resource capacities. Two constraints may conflict, or a formula may use the wrong cell reference.
- Solver returns zero or an unexpected allocation: verify the objective points to the intended total and that the changing cells are the actual decision cells, not formulas or labels.
- Cells exceed a capacity: confirm that each resource-use formula includes every decision variable and that the constraint compares the correct calculated-use range with the capacity range.
- The answer contains fractions: this is normal for a continuous model. Add integer restrictions only when the real decision cannot be fractional; unnecessary integer conditions can slow solving.
- The solution changes after editing the sheet: Solver stores a solution in worksheet cells; it does not continuously optimize every time an input changes. Run Solver again after changing relevant inputs.
When a model is difficult to debug, temporarily simplify it: remove optional constraints, test the remaining formulas, and add the constraints back one at a time. Keep a copy of the original model and record any assumptions, such as whether overtime is allowed or whether a project must receive a minimum allocation.
When Excel Solver is enough—and when it is not
Excel Solver is a practical option for a small or moderate one-sheet model that a person can inspect and maintain. It is particularly helpful for learning optimization, trying scenarios, and answering questions such as “what if material capacity rises by 10%?” A copied workbook can preserve a baseline while another version tests revised assumptions.
For larger or repeatedly run models, specialized optimization software may offer higher limits, more control, or easier automation. Power Query can help prepare and combine data, but it is not itself a Solver replacement. A manual allocation table or spreadsheet formulas may be sufficient when the decision is simple and there are few constraints; Solver becomes useful when trade-offs make trial and error unreliable. Excel for the web users can open and edit many workbooks, but should use desktop Excel for the built-in Solver workflow described here.
Frequently Asked Questions
Can Excel Solver allocate employees across shifts?
Yes. Use employee or shift assignments as decision variables, staffing requirements as constraints, and a suitable objective such as minimizing labor cost. If an employee must be assigned wholly to one shift, use binary variables and account for the added complexity.
Can Solver maximize profit while staying within a budget?
Yes. Put calculated profit in the objective cell, total spending in another formula cell, and constrain spending to be no greater than the budget. Include any other important limits, such as labor or production capacity, so the result reflects the actual problem.
Why does Solver give me a fractional number of products?
By default, decision variables can be fractional. Add an integer constraint to the quantity cells if products must be whole units, then solve again. For decisions that are strictly yes or no, a binary constraint is more appropriate.
Is Excel Solver available in Excel for the web?
The built-in Solver add-in workflow is for desktop Excel. If you need Solver, open the workbook in the Windows or Mac desktop application and enable the add-in there.
Does Solver automatically update its answer when I change a capacity?
No. The worksheet formulas recalculate, but Solver does not automatically rerun the optimization. After changing capacities, costs, or other inputs, open Solver and solve the model again.
Related
- Excel Remove Duplicates Not Available? Fixes to Try
- Excel Remove Duplicates: Why No Duplicates Are Found and What to Check
- Excel Remove Duplicates: Which Rows Stay and Which Go?
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Heat Maps: Create, Format, and Read Color ScalesIn Excel, a heat map is usually a range of cells colored by value using Conditional Formatting. Select…
- PowerPoint Presenter View: How to See Notes While PresentingTo have notes while presenting in PowerPoint, add them to each slide, then turn on Presenter View so…
- Excel Text to Columns: Split at the Last SpaceNo—Excel’s Text to Columns command cannot split at the last space only; when you choose space as a…
- How to Add Just a Header in WordTo add only a header in Word, open the document and choose Insert > Header > Edit Header,…
- Change the Zoom Level in Excel: A Step-by-Step GuideTo change the zoom level in Excel for Microsoft 365 on Windows, use the zoom slider at the…
- Weekly Meal Planner Spreadsheet: Build a Useful PlanA weekly meal planner spreadsheet is a simple grid for deciding what to eat, organizing recipes and turning…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.