To run Goal Seek automatically in Excel, create a VBA macro that calls the formula cell’s GoalSeek method, then run it from a button or when a target cell changes. You need a formula cell, one input cell Excel can adjust, and a numeric goal. Goal Seek itself is normally started manually; a macro automates repeating the calculation.
What You’ll Need
- Excel for Microsoft 365 on Windows, or the Excel desktop app on Mac. Excel for the web cannot run VBA macros.
- A worksheet with a formula whose result you want to reach, a single numeric cell Excel can change, and a numeric target.
- A macro-enabled workbook saved as an
.xlsmfile. If you use a work or school device, check your organization’s macro rules.
The example below finds the quantity needed to reach a revenue target. Put a unit price of 12 in B1, a starting quantity such as 1 in B2, the revenue formula in B3, and a target of 1200 in B4. With the unit price in B1 and quantity in B2, enter =B1*B2 in B3. At a quantity of 1, revenue is 12; a target of 1200 requires a quantity of 100. This is an illustrative arithmetic example, not financial advice.
Step-by-Step
- Set up the calculation. Use the example layout above, or substitute your own cells. The goal cell must contain a formula that depends on the changing cell. For example, changing B2 must affect the result in B3. Put the desired result in B4 so you can edit the target without changing the macro. Make sure the input cell has a reasonable numeric starting value.
- Check that manual Goal Seek works. This confirms the worksheet is set up correctly before you automate it. Select the formula cell, then choose Data > What-If Analysis > Goal Seek. In the dialog, enter B3 for Set cell, 1200 for To value, and B2 for By changing cell, using the example. Select OK and review the result. Excel should change B2 to 100 and make B3 equal 1200. If it does not, see Troubleshooting before adding a macro.
- Save a macro-enabled copy. Choose File > Save As and select Excel Macro-Enabled Workbook (*.xlsm) as the file type. A regular
.xlsxfile cannot store VBA code. Keep an original copy, too: running a macro changes cell values, and Ctrl+Z cannot undo macro actions. - Open the VBA editor. In Windows, press Alt+F11. On Mac, press Option+F11; depending on your keyboard, you may also need Fn. You can also open the editor from the Developer tab by choosing Visual Basic. If the Developer tab is hidden in Windows, enable it through File > Options > Customize Ribbon. On Mac, look under Excel > Settings > Ribbon & Toolbar.
- Add a standard module. In the VBA editor, choose Insert > Module. A blank code pane appears. Paste the following macro into that pane. It assumes B3 is the formula cell, B2 is the changing cell, B4 contains the target, and B5 is available for a status message.
Public Sub RunGoalSeek()
Dim ws As Worksheet
Dim targetCell As Range
Dim changingCell As Range
Dim goalCell As Range
Dim success As Boolean
' Use the worksheet that is active when the macro runs.
Set ws = ActiveSheet
Set targetCell = ws.Range("B3")
Set changingCell = ws.Range("B2")
Set goalCell = ws.Range("B4")
If Not targetCell.HasFormula Then
ws.Range("B5").Value = "B3 must contain a formula."
Exit Sub
End If
If Not IsNumeric(goalCell.Value) Then
ws.Range("B5").Value = "Enter a numeric target in B4."
Exit Sub
End If
If Not IsNumeric(changingCell.Value) Then
ws.Range("B5").Value = "Enter a numeric starting value in B2."
Exit Sub
End If
success = targetCell.GoalSeek( _
Goal:=CDbl(goalCell.Value), _
ChangingCell:=changingCell)
If success Then
ws.Range("B5").Value = "Goal reached: " & Format(targetCell.Value, "0.00")
Else
ws.Range("B5").Value = "Excel could not find a solution."
End If
End Sub
- Run the macro once. Return to Excel, make the worksheet with the example active, and enter 1200 in B4. In Windows, press Alt+F8, select
RunGoalSeek, then choose Run. On Mac, use Option+F8 if available, or run it from the VBA editor. Excel should set B2 to 100, leave B3 at 1200, and show a status message in B5. The macro uses the active worksheet, so activate the correct sheet first. - Make it easy to run again. On Windows, show the Developer tab, choose Developer > Insert > Button (Form Control), draw the button on the sheet, and assign
RunGoalSeekwhen prompted. Give the button a clear label such as “Find quantity.” A button is a practical way to repeat Goal Seek after changing the target or other assumptions. Mac ribbon options can differ by build; if the Form Control button is unavailable, run the macro from the macro list or the VBA editor. - Optionally run it when the target changes. If you want Goal Seek to run as soon as B4 is edited, use a worksheet-change event. In the VBA editor’s Project pane, open the code page for the specific worksheet that contains the example—not the standard module—and add this code. It watches only B4, so changing B2 during Goal Seek will not start another run. This automatic trigger may be inconvenient if you are editing the target frequently; use the button instead if you want control over when the calculation runs.
Private Sub Worksheet_Change(ByVal Target As Range)
' Run Goal Seek only when the target cell changes.
If Intersect(Target, Me.Range("B4")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
RunGoalSeek
CleanUp:
Application.EnableEvents = True
End Sub
After adding the event code, save the workbook as .xlsm, close and reopen it if needed, and allow macros only if you trust the workbook and its source. Test with a copy first. If the event code produces an error, check that RunGoalSeek is in a standard module and that the event code is in the correct worksheet’s code page.
Common Mistakes
- Using a value instead of a formula as the Set cell. Goal Seek needs a formula result it can try to move toward the target. A typed number in B3 will not work as the goal cell.
- Choosing the wrong changing cell. The changing cell must affect the formula result. If B3 does not depend on B2, Excel cannot use B2 to reach a target for B3.
- Trying to change several inputs. Goal Seek adjusts one cell only. If your model needs multiple changing values or constraints, look at Solver instead; it is a separate Excel add-in.
- Expecting every target to have a solution. Some formulas cannot produce the requested value, or have a range of possible outputs that never includes it. A macro cannot create a solution that the model does not allow.
- Saving in the wrong format or running on the web. Save as
.xlsmto retain VBA. Excel for the web does not execute VBA macros, so open the workbook in desktop Excel.
Tips
- Keep the target in a worksheet cell, as B4 is here. That makes it easy to change the goal without editing VBA.
- Use labels beside the input, formula, and target cells, and keep the macro’s cell references aligned with your layout. If you move the model, update the references before running it.
- Start the changing cell near a plausible answer. Goal Seek is an iterative numerical calculation, so a poor starting value can make it converge slowly or fail to find a useful result.
- Review the final formula result as well as the changing input. Excel may return a close numerical match rather than a value displayed exactly as the target, especially when the worksheet uses rounding.
- For the optional event macro, consider limiting the trigger to a target cell that users edit deliberately. Automatic recalculation on every target edit can overwrite a previous input immediately.
- Save before running a macro and keep a backup. Macro changes are not undone by Ctrl+Z in Windows; on Mac, the same caution applies.
Troubleshooting
Goal Seek says it cannot find a solution: Check that the target cell is a formula, the changing cell affects that formula, and the target is achievable. Test the built-in Goal Seek dialog with a few plausible starting values. If the formula has a threshold, discontinuity, or complex nested logic, Goal Seek may not converge reliably.
The macro reports that B3 needs a formula or B4 is not numeric: Confirm you used the expected cell layout and entered a real number in B4. If your model uses other cells, edit the Range references in the macro. A number stored as text can also fail the numeric check; re-enter it as a number.
The macro is missing from Alt+F8: Confirm the code is in a standard module, the procedure is declared Public Sub RunGoalSeek(), and the file is saved as .xlsm. If Excel displays a security warning, enable macros only for a workbook you trust and in accordance with your organization’s policy.
The button or event does nothing: Confirm the button is assigned to RunGoalSeek, the correct sheet is active, and macros are enabled. For the event version, check that the code is in the worksheet module for the sheet where B4 lives. If events were left disabled after an error, close and reopen the workbook or run Application.EnableEvents = True from the Immediate window in the VBA editor.
You are using Excel for the web: Open the workbook in the desktop app to use VBA. The web app cannot run the macro, and features in the desktop Data tab may not be available there.
Frequently Asked Questions
Can Goal Seek run automatically whenever a cell changes?
Yes, with a worksheet-change event macro such as the optional B4 example. It runs only when the watched cell changes. For more predictable control, assign the macro to a button instead.
Does Goal Seek update continuously when I edit other cells?
No. The built-in Goal Seek command runs when you invoke it. A VBA button or event can run it again, but it is not a normal formula that recalculates automatically after every edit.
Can Goal Seek change more than one cell?
No. Goal Seek changes one input cell to make one formula cell reach a target. For multiple changing cells or additional constraints, use Solver where appropriate.
Can I run the macro in Excel for Mac?
Excel desktop for Mac supports VBA, though the ribbon and keyboard shortcuts may vary by version and keyboard. Excel for the web does not run VBA. Save a macro-enabled workbook and use the desktop application.
Will Goal Seek always return an exact answer?
Not necessarily. Its result depends on the formula, starting value, and Excel’s numerical calculation. Check the resulting formula value and whether it is close enough for your purpose; if there is no valid solution, Goal Seek cannot produce one.
Related
- Four-Quadrant Scatter Plot in Excel: How to Make One
- Flash Fill Settings in Excel: How to Enable or Disable
- Flash Fill in Excel: How to Fill Data by Pattern Correctly
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Scatter Plot by Third Column: Color Points by GroupYes—color an Excel scatter plot by a third column by making a separate chart series for each category…
- How to Archive Emails in Outlook Web App and Find ThemTo archive an email in Outlook on the web, select it in your message list and choose Archive…
- Excel File Hidden? How to Unhide Worksheets and WorkbooksTo unhide a worksheet in Excel for Microsoft 365 on Windows, right-click any visible sheet tab, choose Unhide,…
- How to Insert an Image in Google Sheets: Quick StepsTo insert an image in Google Sheets, open the sheet in a web browser, select a cell, then…
- Live Signature in Outlook: Create a Clickable Email Sign-OffTo create a live signature in Outlook, open the signature settings for your Outlook version, add your contact…
- Google Sheets Data Validation With Named Ranges ExplainedYes—Google Sheets can use a named range as the source for a data-validation dropdown. Name the cells that…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.