Excel Macro Goal Seek: Automate a Target-Value Search

VBA & macros · Updated November 19, 2026

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.

  1. Confirm that the worksheet is named Model and that B2, B3, and B4 have the roles shown in the example.
  2. Save a copy of the workbook before running the macro. VBA actions cannot be undone with Ctrl+Z.
  3. Run FindInputForTarget from the VBA editor, or choose Developer > Macros, select the macro, and choose Run.
  4. 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 GoalSeek must 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

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.