Can You Automate Goal Seek in Excel? Methods That Work

How-to basics · Updated November 18, 2026

Yes. In Excel for Microsoft 365 on Windows, you can automate Goal Seek with a VBA macro; in supported Microsoft 365 environments, an Office Script can also call Goal Seek. For a one-off calculation, use Data > What-If Analysis > Goal Seek. Automation is useful when you need to repeat the same calculation, but Goal Seek still changes only one input cell at a time.

What Goal Seek does—and what it does not do

Goal Seek works backward from a desired result. You give it a formula cell, the value you want that formula to return, and one input cell that Excel may change. Excel repeatedly recalculates the workbook while adjusting that input until it finds a value that brings the formula close to the target.

For example, a sales model might calculate profit from units sold, price, unit cost, and fixed costs. You can ask how many units are needed to reach a profit target. Goal Seek changes the unit count; it does not rewrite the profit formula or provide a general-purpose optimization model.

Goal Seek is not a worksheet function that recalculates automatically as part of an ordinary formula. Excel’s built-in command is interactive, while VBA and Office Scripts automate the command. It also has a one-variable limit: if you need to change several inputs together or impose constraints, Solver is usually a better fit.

Set up a model before automating it

A reliable automation starts with a model that already works when you change its input manually. Keep inputs, assumptions, and calculated results in clear cells, and make sure the target formula actually depends on the changing cell. A formula can depend on that input through other formulas, but a disconnected input cannot affect the target.

For a simple example, assume B2 contains units sold, B3 contains a selling price of 25, B4 contains a variable cost per unit of 15, and B5 contains fixed costs of 300. With those values in place, the profit formula is:

With units in B2, price in B3, variable cost in B4, and fixed costs in B5, put this formula in B6:

=B2*(B3-B4)-B5

At 100 units, this model returns 700: 100 × (25 − 15) − 300. To reach a profit of 1,000, Goal Seek should set B6 to 1000 by changing B2. The answer is 130 units because 130 × 10 − 300 = 1,000. This is an illustrative spreadsheet calculation, not a forecast; real models depend on the assumptions entered.

Run Goal Seek manually first

On Windows, open Data > What-If Analysis > Goal Seek. In the dialog, enter B6 as Set cell, 1000 as To value, and B2 as By changing cell, then select OK. Excel reports when it has found a solution and offers to keep the resulting input or restore the previous value.

The target cell must contain a formula, and the changing cell must be a single cell—not a range. Start with manual Goal Seek before writing automation: it confirms the cell references, target, and model behave as expected. The corresponding command is available in desktop Excel versions that include Goal Seek; ribbon placement can vary slightly by version or platform. Excel for the web may not expose the same desktop dialog, so check the options available in your environment.

Automate repeated calculations with VBA

VBA is a practical choice when the workbook is used in desktop Excel and the same calculation needs to run on demand. In this example, the worksheet is named Model, B6 is the formula cell, and B2 is the input cell. The macro asks Goal Seek to make profit 1,000, then reports whether Excel found a solution.

Sub SolveRequiredUnits()
    Dim ws As Worksheet
    Dim solved As Boolean

    ' Use the model worksheet and solve for units.
    Set ws = ThisWorkbook.Worksheets("Model")
    solved = ws.Range("B6").GoalSeek( _
        Goal:=1000, _
        ChangingCell:=ws.Range("B2"))

    If solved Then
        MsgBox "Goal Seek found a solution. Units: " & ws.Range("B2").Value
    Else
        MsgBox "Goal Seek did not find a solution. Check the model and starting value."
    End If
End Sub

To add it, open the Visual Basic Editor, insert a standard module, and paste the code. In desktop Excel for Windows, Alt+F11 opens the editor on many keyboards; on Mac, the shortcut can vary by keyboard and function-key settings, so use the Visual Basic Editor command from the Developer tab if needed. Save a macro-enabled workbook (.xlsm), enable macros only when you trust the file, and run the procedure from Developer > Macros. You can also add a Form Control button from Developer > Insert and assign the macro to it.

Make a copy of the workbook before running a macro. VBA changes are not undone with Ctrl+Z, and a macro may overwrite the input value with the solution. On Mac, VBA is available in desktop Excel, but keyboard shortcuts and some interface details differ. Macros do not run in Excel for the web.

Use Office Scripts for supported Microsoft 365 workflows

Office Scripts provide another automation route where the feature is available and enabled by an organization or Microsoft 365 plan. A script can call the Range goalSeek method. For the same Model worksheet and B6/B2 layout, the central operation looks like this:

function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getWorksheet("Model");
  const solved = sheet.getRange("B6").goalSeek(1000, sheet.getRange("B2"));

  if (!solved) {
    throw new Error("Goal Seek did not find a solution.");
  }
}

Office Scripts are suited to repeatable tasks that can be run from Excel’s Automate tools and, in supported setups, called from Power Automate. Availability depends on the account, tenant settings, and the Excel environment. If you plan to run a script on a schedule or from a flow, test it with a copy of the workbook and confirm that the file location and permissions work. VBA and Office Scripts are different technologies: a VBA macro is not automatically converted into a script.

Choose the right automation method

For an occasional calculation, manual Goal Seek is simplest and makes it easy to inspect the result. For a desktop workbook with a button or an established macro workflow, VBA is direct. For cloud-oriented Microsoft 365 processes, Office Scripts may fit better when the required capabilities and permissions are available.

  • Manual Goal Seek: Best for a one-time question or for checking a model before automating it.
  • VBA: Best for desktop workflows that need a macro, button, or other VBA procedures. It requires macro-enabled files and suitable macro security settings.
  • Office Scripts: Best for supported Microsoft 365 automation, especially when a script needs to be run or coordinated with a cloud workflow.
  • Solver: Better when the model needs multiple changing cells, constraints, or an optimization objective rather than one target value.

A common misconception is that automating Goal Seek makes it a formula that updates continuously. It does not: a macro or script runs when invoked. If an input changes afterward, the automation must be run again unless you deliberately build a separate trigger or workflow. Automatic triggers should be designed carefully so they do not repeatedly call themselves or make unexpected changes.

Run many scenarios without losing track

Repeatedly solving different targets is possible, but a robust process needs to record each target and its result. For example, a macro can loop through a list of profit targets, call Goal Seek for each one, and write the resulting unit count to a results table. Reset the changing cell to a sensible starting value between runs if the starting point affects convergence, and record failures rather than treating every run as successful.

If you need answers for a fixed set of input values rather than a reverse calculation, consider a data table instead. Excel’s What-If Analysis Data Table can show how one or two input variables affect a formula across a range of scenarios. A data table does not solve for an input that hits a target, but it can be easier to review when you want a sensitivity grid or a side-by-side comparison.

For more complex models, Solver can search multiple variable cells and apply constraints, such as nonnegative quantities or resource limits. Solver is an Excel add-in and may need to be enabled. It is not simply a faster version of Goal Seek: it addresses a broader optimization problem and requires you to define an objective and constraints appropriately.

Common problems and how to diagnose them

Goal Seek can fail or return a result that is mathematically valid but unsuitable for the model. Check the model and the assumptions before concluding that automation is broken.

  • The target is not a formula: Set cell should calculate a result, not contain a fixed number or text.
  • The input has no effect: Verify that the changing cell flows into the target formula, directly or through dependent formulas.
  • The result is implausible: Add sensible limits or validation to your process. Goal Seek itself does not enforce business rules such as whole units, minimum prices, or nonnegative values.
  • It does not converge: The target may be unreachable, the formula may have discontinuities, or the starting value may lead the search in an unhelpful direction. Try a reasonable starting value and inspect the formula’s behavior.
  • Several answers are possible: A nonlinear formula can cross the target more than once. Goal Seek returns a solution it finds, not necessarily the only solution or the one that makes most sense operationally.
  • Rounding hides the answer: Display formatting can round a value without changing its underlying precision. Inspect the actual cell value and decide whether the model needs a whole-number or tolerance check.

Goal Seek is a numerical search, not a proof that a unique answer exists. If a solution must meet a tolerance, be an integer, or remain within business limits, add checks after the run and reject results that violate those requirements.

Frequently Asked Questions

Can Goal Seek run automatically when a cell changes?
Not by itself. Goal Seek is a command, not a worksheet function. A VBA event procedure or an Office Script workflow can invoke it, but automatic triggers should be tested carefully to avoid repeated execution, slow recalculation, or unwanted edits.

Can Goal Seek change more than one cell?
No. It uses one changing cell to reach a target in a formula cell. Use Solver when several inputs must change together or when you need constraints on those inputs.

Why does Goal Seek say it could not find a solution?
The target may be unreachable, the changing cell may not affect the formula, or the calculation may not converge from its starting value. Confirm the references, try a sensible starting value, and check whether the model can produce the requested result at all.

Does Goal Seek work in Excel for the web?
The desktop Goal Seek dialog and VBA macros are not available in the same way in Excel for the web. In supported Microsoft 365 environments, Office Scripts can automate Goal Seek; availability depends on the account and organizational settings.

Can I undo a macro that ran Goal Seek?
Do not rely on Ctrl+Z to undo VBA changes. Save a copy before running the macro, or have the automation store the original input and provide a deliberate way to restore it.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.