An Excel Goal Seek macro uses VBA’s Range.GoalSeek method to make a formula cell reach a target by changing one input cell. In Excel for Microsoft 365 on Windows, put the formula, input, and target in cells, then run a macro that calls GoalSeek on the formula cell and identifies the input cell to adjust.
What Excel Goal Seek does
Goal Seek is a what-if analysis tool: you specify a result you want, and Excel searches for an input value that makes a formula return that result. For example, a model might calculate the output of a process from one adjustable setting. If you know the desired output but not the required setting, Goal Seek can search for it.
The tool needs three things: a formula cell whose result you want to control, a numeric target, and one changing cell that the formula depends on. It changes the input cell, recalculates the workbook, and checks how close the formula’s result is to the target. A macro automates this operation, which is useful when you repeat the same search across many cases or want to run it as part of a larger VBA procedure.
Goal Seek does not design a model or decide which target is sensible. It only searches for a value that satisfies the relationship in your worksheet. The result depends on the formula, the starting value, and whether the calculation can converge to a solution.
Set up a simple worksheet example
Use a small algebra example to see the cell roles without involving a financial decision. In a worksheet named Model, enter an initial guess of 2 in B2, a target of 40 in B4, and put the formula below in B3. The formula layout assumes B2 is the changing input and B3 is the calculated output.
=B2^2+3*B2
When B2 is 2, B3 evaluates to 10. The target in B4 is 40. A solution is B2 = 5, because 5 squared plus three times 5 equals 40. The macro will change B2; it will not replace the formula in B3. This separation makes it easier to inspect and reuse the model.
For a manual check before automating, select Data > What-If Analysis > Goal Seek. Set the cell to B3, the value to 40, and the changing cell to B2. Excel should find 5 for this example. The exact menu wording can vary slightly by Excel release, but Goal Seek is a desktop Excel What-If Analysis command.
Write the VBA Goal Seek macro
In the VBA editor, choose Insert > Module and add the code below. It assumes the worksheet is named Model, B3 contains the formula, B2 is the input, and B4 contains the target value. The worksheet is explicitly assigned so the macro does not depend on whichever sheet happens to be active.
Option Explicit
Sub FindInputForTarget()
Dim ws As Worksheet
Dim goalReached As Boolean
Set ws = ThisWorkbook.Worksheets("Model")
' B3 contains the formula, B2 is the input, and B4 is the target.
goalReached = ws.Range("B3").GoalSeek( _
Goal:=CDbl(ws.Range("B4").Value), _
ChangingCell:=ws.Range("B2"))
If goalReached Then
MsgBox "Goal reached. B2 = " & ws.Range("B2").Value
Else
MsgBox "Goal Seek did not find a solution."
End If
End Sub
The method is called on the formula cell, B3. Its Goal argument is the value to reach, read here from B4, and ChangingCell identifies B2. The method returns a Boolean value: the macro stores that result in goalReached and displays a different message depending on whether Excel reports success. A successful search changes the value in B2, so the worksheet is modified when the macro runs.
Enable, save, and run the macro
On Windows, if the Developer tab is hidden, enable it through File > Options > Customize Ribbon, then select Developer. Open the VBA editor with Alt+F11, insert a standard module, and paste the code. Save the workbook as an Excel Macro-Enabled Workbook (*.xlsm); the ordinary .xlsx format cannot store VBA code.
- Confirm that the worksheet is named
Modeland that B2, B3, and B4 have the roles shown in the example. - Save a copy of the workbook before running the macro. VBA actions cannot be undone with Ctrl+Z.
- Run
FindInputForTargetfrom the VBA editor, or choose Developer > Macros, select the macro, and choose Run. - Check B2 and B3 afterward. B2 should be close to 5 and B3 should be close to 40.
Excel for Mac supports VBA macros, although the interface differs. Enable the Developer tab in Excel’s ribbon settings, then open the Visual Basic editor with Option+F11; some keyboards require Fn+Option+F11. On either platform, use a trusted workbook and do not enable macros from an unexpected file simply to run its code. Excel for the web does not run VBA macros, so use the desktop application for this procedure.
Adapt the macro to your own model
For another worksheet, change "Model" to its tab name. Replace "B3" with the address of the formula cell and "B2" with the one input cell Goal Seek is allowed to change. The target can be a fixed number, as in Goal:=40, or a cell value, as in the example. A target cell is convenient when a user needs to change the desired result without editing VBA.
For repeated use, keep the layout consistent and put each case’s target in a clearly marked cell. You can also call Goal Seek inside a loop to process multiple cases, but plan how each case’s input and formula cells will be reset. Otherwise, one search may start from the value left by the previous search. If the macro needs to process a list of cases, add checks for blank or nonnumeric targets before converting a target with CDbl.
Goal Seek changes one cell only. If you need the macro to adjust different inputs for different scenarios, make the changing-cell reference depend on the current scenario, or call a separate procedure for each model. Keep the formula cell and changing cell on the same worksheet for a straightforward setup, and make sure the formula actually depends on the cell being changed.
How Goal Seek searches for a result
Goal Seek repeatedly changes the input and recalculates the formula until it reaches the target within Excel’s calculation tolerance or can no longer find a suitable value. It is an iterative numerical search, not a symbolic algebra solver: it does not rearrange your equation and guarantee an exact mathematical root. The displayed result may also look rounded even when the stored value has more decimal places.
Because the search starts from the current input, nonlinear formulas can have more than one solution or behave differently around different starting values. For example, a squared expression may have both a positive and a negative input that produce the same output. A starting value near one solution can steer the search toward that solution; an unsuitable starting value may fail to converge. If the result matters, verify it by recalculating the formula and checking the difference from the target.
Goal Seek is most appropriate when the model is deterministic, the output depends on one adjustable cell, and the target is meaningful. It is not a substitute for checking assumptions, units, constraints, or the plausibility of an answer.
Common problems and practical checks
- Wrong cell roles: The first range in
GoalSeekmust be the formula cell, not the input or target cell. The changing cell must be the single input Excel is permitted to alter. - The formula does not depend on the input: If changing B2 cannot affect B3, Goal Seek has no route to the target. Trace the formula references and confirm that the input is part of the calculation.
- Nonconvergence or a false return: Try a more sensible starting value, inspect the formula for errors or discontinuities, and check whether the target is attainable. A successful return does not by itself prove the result is useful for the real-world problem.
- Unexpected starting point: The macro uses the current value in the changing cell as its starting guess. Set that cell deliberately before calling the method if repeatability matters.
- Wrong worksheet or missing sheet: The sample expects a tab named
Model. A renamed or deleted sheet causes the worksheet assignment to fail, so update the name or add error handling for workbook structure. - Target is text or blank: The sample converts B4 to a number. Check that the target cell contains a numeric value before the macro runs; a blank or text value can cause a conversion error.
For a robust workbook, make the macro report failure clearly, preserve the original input when a search fails if that matters to the workflow, and log the target and result for batch runs. Those safeguards are extra VBA logic; Goal Seek itself does not create a history or automatically restore the old input.
Goal Seek versus Solver
Goal Seek is the simpler choice when one formula must reach one target by changing one cell. It is built into desktop Excel and is exposed directly in VBA through Range.GoalSeek. It does not let you impose a collection of limits such as requiring several decision variables to stay within specified bounds.
Solver is better suited to optimization problems: for example, maximizing or minimizing an objective while changing multiple cells subject to constraints. Solver is a separate Excel add-in and may need to be enabled. Choose it when the question is not merely “what input gives this output?” but “what combination of inputs gives the best result under these limits?” Neither tool removes the need to validate the model and interpret its answer.
Macro safety and workbook behavior
A Goal Seek macro changes worksheet data, and the change is not reversed by Ctrl+Z. Save a backup or duplicate the workbook before running new VBA, especially when the macro processes many cells. On Windows, macro options are under File > Options > Trust Center > Trust Center Settings > Macro Settings. Prefer trusted files and an appropriate notification setting rather than enabling every macro globally. Organizations may manage these settings centrally.
On Mac, macro security is also managed in Excel’s settings, though labels and locations can differ by release. A workbook with VBA should remain in a macro-enabled format. If you share it, tell recipients what the code changes and provide the expected worksheet layout; they should inspect the source and only run it if they trust it.
Frequently Asked Questions
Can Excel Goal Seek be run automatically with VBA?
Yes. Call GoalSeek on the formula cell, provide the desired value with the Goal argument, and identify the input cell with ChangingCell. The input must be a single cell.
Does Goal Seek work without a formula?
No. The cell you ask Excel to set must contain a formula whose result can change when Excel adjusts the changing cell. A fixed value cannot respond to the search.
Why does my Goal Seek macro return False?
Excel reports that it did not find a solution within its search process. Check the target, formula dependencies, starting value, and whether a solution is attainable. Also confirm the code points to the intended cells.
Can Goal Seek change multiple cells?
No. Goal Seek adjusts one changing cell for one formula cell. Use Solver when a problem requires multiple changing cells or explicit constraints.
Can I run this macro in Excel for the web?
No. Excel for the web does not execute VBA macros. Open the macro-enabled workbook in desktop Excel for Windows or Mac to run the code.
Related
- Excel Import CSV as Text: Keep Every Value Unchanged
- Excel Import CSV Data: Steps, Settings, and Fixes
- Excel Flash Fill: How to Use It and Choose Options
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Delete Blank Rows in Excel VBA Safely and CorrectlyTo delete completely blank rows with VBA in Excel for Microsoft 365 on Windows, run a macro that…
- Option Button in Excel VBA: Add, Group, and Use ItYes. In Excel for Microsoft 365 on Windows, add an option button from Developer > Insert, then choose…
- VBA MsgBox OK: Syntax, Buttons, and ExamplesYes—VBA’s MsgBox can display a simple OK message, and OK is the default button when you do not…
- Excel VBA Active Worksheet Reference ExplainedIn Excel VBA, use ActiveSheet to refer to the sheet currently active in the active workbook. For a…
- VBA Sort Header: Keep Column Headings in Place When SortingTo keep a header row out of the sorted data in Excel VBA, set the sort option to…
- Easy Macro Recorder in Excel: How to Record and Use OneYes. Microsoft Excel for Microsoft 365 includes a built-in macro recorder: choose Developer > Record Macro, perform the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.