Excel VBA Goal Seek: Automate What-If Analysis

To run Goal Seek in Excel VBA, call the GoalSeek method on a cell containing a formula, and identify the one input cell Excel should change. For example, Range("B4").GoalSeek Goal:=1000, ChangingCell:=Range("B3") asks Excel to make the formula in B4 equal 1,000 by adjusting B3.

Goal Seek is useful when you know a desired result but not the input that produces it. VBA lets you repeat that calculation, connect it to a larger macro, or run it across many scenarios without opening the Goal Seek dialog each time.

What Goal Seek does in Excel

Goal Seek is Excel’s one-variable what-if analysis tool. You provide three things: a formula cell, the result you want that formula to return, and one input cell that Excel is allowed to change. Excel adjusts the input and recalculates the worksheet until it finds a value that makes the formula reach the target, or determines that it cannot find a suitable solution.

It does not calculate a general optimum, compare multiple outcomes, or choose among several changing inputs. It solves a narrower question: “What value in this one cell makes this formula equal this target?” For a problem involving several decision variables or constraints, Solver is usually a better fit.

In the desktop version of Excel for Microsoft 365 on Windows, you can access the manual tool through Data > What-If Analysis > Goal Seek. Excel for Mac also has Goal Seek in desktop Excel, though menu placement can differ by release. Excel for the web does not run VBA macros; use desktop Excel to run the code in this article.

Set up the worksheet first

Goal Seek only works when the target cell contains a formula that depends, directly or indirectly, on the changing cell. The changing cell should be a single input cell, not a formula cell. In the example below, B2 holds a unit price, B3 holds a quantity input, and B4 calculates revenue. Put 25 in B2 and 30 in B3.

With the unit price in B2 and quantity in B3, enter this formula in B4: =B2*B3. At the starting quantity of 30, the formula returns 750 because 25 × 30 = 750. A target of 1,000 therefore requires Goal Seek to increase B3 to 40, since 25 × 40 = 1,000.

Set cell: B4, the formula whose result matters.
To value: 1000, the desired result.
By changing cell: B3, the quantity input Excel may alter.

Keep the formula and changing cell on the same worksheet for a straightforward Goal Seek setup. Make sure the formula actually refers to the input; a formula that does not depend on B3 cannot be driven to the target by changing B3.

Run Goal Seek with a VBA macro

In the VBA object model, GoalSeek is a method of a Range object. The range it is called on is the formula cell. The method takes a target value and a changing cell, and returns a Boolean value indicating whether Excel found a solution. This macro uses the worksheet named Model and the cell layout described above:

Sub SetRevenueTarget()
    Dim ws As Worksheet
    Dim goalReached As Boolean

    Set ws = ThisWorkbook.Worksheets("Model")

    ' Make the formula in B4 equal 1000 by changing B3.
    goalReached = ws.Range("B4").GoalSeek( _
        Goal:=1000, _
        ChangingCell:=ws.Range("B3"))

    If goalReached Then
        MsgBox "Quantity required: " & ws.Range("B3").Value
    Else
        MsgBox "Goal Seek did not find a solution."
    End If
End Sub

Before running the macro, save a copy of the workbook. Macros can change cell values, and their actions cannot be undone with Ctrl+Z. The worksheet name in the code must match the actual sheet name; if your model uses another name, change "Model". The target cell must already contain its formula, and the changing cell should contain a starting value.

Where to put and run the code

In Excel for Windows, open the Visual Basic Editor with Alt+F11, choose Insert > Module, and paste the procedure into the new module. You can run it from the editor with the cursor inside the procedure, or return to Excel and press Alt+F8, select SetRevenueTarget, and choose Run. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon.

On Mac, VBA is available in desktop Excel, but keyboard shortcuts can depend on the keyboard and function-key settings. Open the Visual Basic Editor from Excel’s Developer tools or the Tools menu, insert a standard module, and run the macro from the editor or the Macros dialog. The sample uses standard worksheet and range methods rather than Windows-specific automation, but macros still cannot run in Excel for the web.

To keep the VBA code, save the workbook in a macro-enabled format such as Excel Macro-Enabled Workbook (.xlsm). Excel may display a security warning when you open it; only enable macros in files from a source you trust. If you save as a regular .xlsx file, Excel does not preserve VBA code in that workbook.

How the search behaves and what the result means

Goal Seek uses numerical searching and recalculation rather than rearranging your formula algebraically. It starts from the current value in the changing cell and tests adjustments. The starting value can influence which solution it finds when a formula has multiple possible solutions, and a poor starting point can make a difficult problem harder to solve.

The returned Boolean matters in automated workflows. A value of True means Excel reports that it found a solution; False means it did not. Even when the method succeeds, inspect the resulting input and recalculated formula. A numerical search may produce a value that is mathematically acceptable but impractical for the model, such as a negative quantity, or one that is too precise for a real-world decision.

Goal Seek does not impose bounds or business rules. If quantity must be a whole number, or a rate must remain between specified limits, the method does not enforce those conditions by itself. You can check the result in VBA after the call and reject or flag an out-of-range value. For explicit constraints and multiple changing cells, Solver provides more suitable controls.

Use cases and a batch example

Common uses include finding the sales volume needed to reach a revenue target, the interest rate that produces a particular payment, the break-even price for a cost model, or the input required for a desired forecast result. These are examples of spreadsheet mechanics, not financial recommendations: for loan or investment decisions, verify the actual terms and assumptions with the lender or a qualified professional.

For example, the revenue worksheet can be reused for several target amounts. A macro could loop through targets, set each one as the Goal Seek target, record the resulting quantity in a results table, and then move to the next target. Each run still changes only one input cell, so you should preserve the original input or restore it deliberately if the original scenario needs to remain intact.

For a batch process, plan how the macro should handle a failed search before adding more targets. It can record the Boolean result beside each target, skip unsuccessful cases, or stop and alert the user. Avoid assuming every target has a valid solution: a target may be outside the range the model can produce, or the formula may be discontinuous or otherwise difficult to solve.

Common problems and fixes

“Set cell” is not a formula: Goal Seek needs a formula result to evaluate. Put the calculation in the target cell, then call GoalSeek on that cell.
The changing cell is a formula: Use a separate input cell containing a value. Goal Seek needs permission to change that input, not a calculated result.
The target does not depend on the input: Check the formula references and worksheet links. If changing the input does not affect the formula result, Goal Seek has no path to the target.
The macro says the worksheet is missing: The sheet name in ThisWorkbook.Worksheets("Model") must match the workbook tab exactly. ThisWorkbook refers to the workbook containing the code, which is usually safer than relying on whichever workbook happens to be active.
The result is unexpected: Check units, starting values, formula logic, and whether multiple solutions are possible. Try a more sensible starting value and inspect the worksheet after the run.
The macro does not run: Confirm you are using desktop Excel, the workbook is macro-enabled, and the macro is permitted by your organization’s security settings.

Goal Seek compared with Solver and formulas

Use Goal Seek when there is one target formula and one changing cell. Use Solver when the task involves multiple variable cells, minimum or maximum objectives, or constraints such as “quantity must be at least zero.” Solver is an Excel add-in, so it may need to be enabled; it is not interchangeable with the VBA GoalSeek method.

A direct algebraic calculation can be simpler if the formula can be rearranged reliably. For instance, if revenue is price multiplied by quantity and price is nonzero, quantity can be found by dividing target revenue by price. But real worksheets may contain taxes, tiered pricing, nested formulas, or lookup logic that makes rearranging inconvenient. Goal Seek is useful when the existing model is the source of truth and you want Excel to adjust an input against that model.

A data table is another option when you want to compare formula results across many candidate inputs rather than solve for one target. It lays out multiple what-if cases, while Goal Seek searches for a value corresponding to a single desired result.

Frequently Asked Questions

Can VBA Goal Seek change more than one cell?
No. A single GoalSeek call adjusts one changing cell. Use Solver for models where several decision cells must change together.

Does Goal Seek work if the target cell contains a number?
No. The set cell should contain a formula. Goal Seek changes the specified input and recalculates that formula to approach the target.

Can I use Goal Seek in Excel for the web?
Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to run a VBA Goal Seek procedure.

Why does Goal Seek return False?
Excel may be unable to find a solution from the starting value, or the target may not be attainable through that input. Check the formula’s dependency, target, starting value, and result range.

Can Goal Seek enforce a minimum or maximum?
No. Goal Seek does not provide input bounds or other constraints. Check the result in your macro, or use Solver when constraints are part of the problem.

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.