Excel Goal Seek Macro: VBA Code and How to Run It Safely

An Excel Goal Seek macro is VBA code that asks Excel to change one input cell until a formula reaches a specified result. In Microsoft Excel for Microsoft 365 on Windows, the essential call is Range("B4").GoalSeek Goal:=10000, ChangingCell:=Range("B3"); the example below turns that operation into a complete, reusable macro.

What a Goal Seek macro does

Goal Seek works backward from a result you want. You provide a formula cell, a target value for that formula, and one input cell Excel is allowed to change. Excel tries different values in the input cell and recalculates the formula until it reaches the target closely enough, or cannot find a suitable answer.

A macro automates those instructions in Visual Basic for Applications (VBA). That is useful when you repeat the same calculation, run it for multiple worksheets, or want a button that performs a routine calculation. The macro does not replace the worksheet formula: it changes an input value, while the formula continues to calculate the result.

For example, a sales worksheet might calculate revenue from a unit price and a number of units. If the price is fixed and you want to know how many units would produce $10,000 in revenue, Goal Seek can change the units cell until the revenue formula equals $10,000. It is a single-variable calculation, not a general-purpose optimizer.

Set up the worksheet example

Use a small, clear layout before you run a macro. Put the unit price in B2, the starting estimate of units in B3, and revenue in B4. For this example, enter 25 in B2 and 100 in B3. With the price in B2 and units in B3, enter this formula in B4:

=B2*B3

The formula initially returns 2,500 because 25 multiplied by 100 is 2,500. Goal Seek will change B3, not B2 or B4, to make B4 equal 10,000. The arithmetic answer is 400 units: 25 multiplied by 400 equals 10,000. In a real model, the result depends on the formulas and starting assumptions in that workbook.

Keep the changing cell as a value rather than a formula. Goal Seek needs permission to adjust that cell directly. The target cell, by contrast, must contain a formula that depends, directly or indirectly, on the changing cell. If B4 is a typed number instead of a formula, there is nothing for Excel to recalculate as B3 changes.

VBA macro for the example

The following macro operates on the active worksheet. It checks that B4 contains a formula and that the two input cells are numeric, then asks Goal Seek to make B4 equal 10,000 by changing B3. Adjust the cell references and target to match your own layout.

Sub GoalSeekRevenue()
    ' Use the active worksheet for this example.
    Dim ws As Worksheet
    Dim solved As Boolean
    Const TARGET_REVENUE As Double = 10000

    Set ws = ActiveSheet

    ' Goal Seek needs a formula cell and a numeric changing-cell value.
    If Not ws.Range("B4").HasFormula Then
        MsgBox "Cell B4 must contain a formula."
        Exit Sub
    End If

    If Not IsNumeric(ws.Range("B2").Value) Or _
       Not IsNumeric(ws.Range("B3").Value) Then
        MsgBox "Enter numeric values in B2 and B3 first."
        Exit Sub
    End If

    ' Change B3 until the formula in B4 reaches the target.
    solved = ws.Range("B4").GoalSeek( _
        Goal:=TARGET_REVENUE, _
        ChangingCell:=ws.Range("B3"))

    If solved Then
        MsgBox "Goal Seek found a result. Units: " & _
               Format(ws.Range("B3").Value, "0.00")
    Else
        MsgBox "Goal Seek could not find a solution. Check the model."
    End If
End Sub

The method returns a Boolean value: the code stores it in solved so it can report whether Excel found a solution. A successful result in this example is 400.00 in B3. The test is a useful safeguard, but it does not prove that the model represents a sensible real-world outcome; check the resulting value and the assumptions behind the formula.

Add and run the macro in Excel

On Windows, save a copy of the workbook before adding or running VBA. A macro-enabled workbook should be saved as an Excel Macro-Enabled Workbook (.xlsm), rather than as a standard .xlsx file, which does not retain VBA code.

If the Developer tab is not visible, go to File > Options > Customize Ribbon, select Developer in the main tabs list, and choose OK.
Choose Developer > Visual Basic. In the Visual Basic Editor, choose Insert > Module.
Paste the code into the module, then return to the workbook. Make sure the example cells are on the active worksheet.
Choose Developer > Macros, select GoalSeekRevenue, and click Run. Review B3 and B4 after it finishes.

Excel may show a security notification when a workbook contains macros. Only enable macros in files you trust and expect to contain code. Excel for the web can open many macro-enabled workbooks, but it cannot create, edit, or run VBA macros; use the desktop Excel app for this procedure.

On Mac, VBA macros are also available in desktop Excel. The Developer tab can be enabled through Excel > Preferences > Ribbon & Toolbar, then selecting Developer. The Visual Basic Editor and macro controls are on the Developer tab, although some interface details differ from Windows. The code above is ordinary VBA and does not use a Windows-only object.

Change the target, cells, or worksheet

To reuse the macro, replace the example references with the cells in your model. The target cell must be the one containing the formula to solve; the changing cell must be the input Excel is allowed to adjust. Update the constant TARGET_REVENUE if you want another fixed target. If the target changes from run to run, a cell reference or an input prompt can make the macro more flexible, but keep the target and changing-cell roles clear.

The sample uses ActiveSheet, which makes it convenient to run on whichever worksheet is selected. It also means that running the macro from the wrong sheet can use the wrong B2:B4 cells. For a repeatable workbook, you can instead name a worksheet explicitly, for example by assigning Set ws = ThisWorkbook.Worksheets("Plan") and using the actual sheet name. If a sheet is renamed, update that reference.

Goal Seek changes the input cell as part of the calculation. It does not preserve the original value automatically. If you need to compare the starting estimate with the solved value, record the original value in another cell or save it in a VBA variable before calling Goal Seek. If no solution is found, inspect the value left in the changing cell rather than assuming it was restored.

Goal Seek limits and common problems

Goal Seek changes only one cell. If a model needs two inputs adjusted together, or must satisfy several constraints at once, Goal Seek is not sufficient. It also uses an iterative numerical search, so it may return an approximate value rather than an exact one, especially when formulas are nonlinear or sensitive to small input changes.

The result does not move: Confirm that the formula cell depends on the changing cell. A broken reference or a formula that ignores B3 leaves Excel with no effective path to the target.
Excel reports that it cannot find a solution: The target may be outside the possible range, or the model may not converge from the current starting value. Check the formula, target, and initial estimate.
The answer is unexpected: A formula can have more than one possible solution. Try a different starting value and check whether the returned result makes sense for the model.
The answer is fractional: Goal Seek does not automatically enforce whole numbers or business rules. If units must be integers, review the result and the model requirements; do not treat rounding as proof that the rounded value still reaches the target.
The macro cannot run: Confirm that the workbook is saved as .xlsm, that macros are allowed under your organization’s security settings, and that you are using desktop Excel rather than Excel for the web.

For calculations whose answers depend on small numerical tolerances, results can also be affected by Excel’s iterative-calculation settings and the behavior of the worksheet formulas. Do not change calculation settings casually just to force a result: settings can affect other formulas in the workbook. Verify the output by recalculating the model and checking the result cell against the requested target.

Use the built-in command or a macro?

For an occasional calculation, the built-in Goal Seek dialog is usually simpler and avoids VBA. On Excel for Microsoft 365 for Windows, select Data > Forecast > What-If Analysis > Goal Seek. Enter the formula cell in Set cell, enter the desired result in To value, and enter the adjustable input in By changing cell. Choose OK, review the proposed result, and accept it if it is suitable. Ribbon placement can vary with window size or customization, but Goal Seek is part of What-If Analysis.

The macro is helpful when you repeat the same target calculation or want it built into a larger VBA procedure. The dialog is better for one-off work, experimentation, or cases where a user should inspect and choose the changing cell each time. Both approaches use Goal Seek’s one-input limitation and numerical search; wrapping the command in VBA does not make it more powerful mathematically.

For more complex models, Excel’s Solver add-in can handle multiple changing cells and constraints. Solver is a separate tool, with its own setup and model assumptions, so it is not simply a more accurate version of Goal Seek in every situation. If the calculation can be rearranged algebraically, a direct formula may be clearer and more reliable than either tool.

Safety and good workbook practice

VBA macros can change workbook data, and Excel does not provide the usual Ctrl+Z undo for changes made by a macro. Save a separate copy before running unfamiliar code, and inspect the code before enabling it. The sample is deliberately limited to one active sheet and one input cell, but it still overwrites the starting value in B3.

For Mac users, the usual undo shortcut is Command+Z rather than Windows Ctrl+Z, but neither shortcut reverses actions performed by a VBA macro. Use a saved copy or a recorded original value if you need a reliable way to restore the starting state. Avoid enabling macros in downloaded files merely to test a calculation; the built-in Goal Seek dialog can solve the example without VBA.

Frequently Asked Questions

Can I run Goal Seek automatically whenever a cell changes?
It is possible to call Goal Seek from a worksheet event macro, but that can trigger repeated recalculations or unexpected changes as users edit the sheet. For most workbooks, run a dedicated macro explicitly with a button or from the Macros dialog so the user knows when the input will be changed.

Can Goal Seek change more than one cell?
No. Goal Seek adjusts one changing cell to make one formula cell reach a target. Use Solver when the model needs multiple adjustable cells or constraints, or restructure the calculation if a direct formula can solve it.

Why does Goal Seek return a decimal when I need whole units?
Goal Seek searches for a numerical value and does not impose an integer restriction. You can inspect nearby whole-number values, but verify the resulting formula because rounding may move the result away from the target. Solver can apply an integer constraint in a suitable model.

Does Goal Seek work in Excel for the web?
Excel for the web does not run VBA macros. Open the workbook in desktop Excel to use this macro. If you only need a one-time calculation, use the desktop Goal Seek command instead.

Can a Goal Seek macro be undone with Ctrl+Z?
No. Excel’s normal undo history does not undo VBA macro actions. Save a backup copy before running the macro, or have the code retain and restore the original input value if that behavior is required.

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.