Use Goal Seek Using Macros In Excel

Ever heard of the Goal Seek feature in Excel? It is a very nice feature wherein it takes out the guesswork for you and determines the input value needed to achieve a specific goal. For example, you have a goal / result in mind, but you are unsure what the starting amount should be for an investment. Let us use Excel Macros to execute the Goal Seek feature! Make sure your Excel has the Developer Tab enabled following this tutorial. I explain how you can do this below step by step!

Key Takeaways

• Automate Repetitive Goal Seek Tasks – Macros help apply Goal Seek across multiple cells or scenarios without manual input.

• Goal Seek Adjusts Inputs to Reach a Target – It works by finding the input value that gives you a specific result in a formula.

• Macros Make Goal Seek Scalable – Instead of using Goal Seek manually each time, macros can loop through different rows or data sets.

• Easily Reuse with Personal Macro Workbook – Save your Goal Seek macro to the Personal Macro Workbook for access across all Excel files.

• Useful for What-If Analysis – Great for forecasting, budgeting, and scenario planning with minimal effort.

Quick Overview

What does it do?

Executes Goal Seek in Excel

Copy Source Code:

'Make sure the worksheet is selected to execute the Goal Seek on
Sub GoalSeekVBA()

Dim TargetGoal As Long

'Get the target value from the user
TargetGoal = InputBox("Enter the target value", "Enter Goal")

'Make sure to change the cell that you want to be changed with the goal
ActiveSheet.Range("E9").GoalSeek _
   Goal:=TargetGoal, _
   ChangingCell:=Range("A9")

End Sub

Final Result: 

How to Use Goal Seek Using Macros In Excel

This is our scenario. We have a formula calculating our target goal:

• The initial amount is $5000
• Interest rate is 4%
• Number of years is 10
• Monthly additional investment is $1000
• With these parameters, after 10 years, your investment will be equivalent to $154,703.97

Let us now assume that, given we want to achieve a goal of $250,000. What will our initial amount be?

Take note of the following:

• Initial Amount – Cell A9
• Total Amount (Goal) – Cell E9

STEP 1: Go to Developer > Code > Visual Basic

 

STEP 2: Paste in your code and Select Save. Close the window afterwards.

Do take note that we are referencing these two cells in the code:

• Initial Amount – Cell A9
• Total Amount (Goal) – Cell E9

What goal seek will do, is it will adjust the initial amount (Cell A9), to achieve the target goal that you specify (Cell E9) which is $250,000.

 

STEP 3: Let us test it out!

Open the sheet containing the data. Go to Developer > Code > Macros

 

Make sure your macro is selected. Click Run.

Type in the target value of $250,000. See how the initial amount will change.

 

With just one click, Goal Seek computed that you need an initial amount of $68,921.35 to achieve your goal of $250,000!

 

Frequently Asked Questions

What is Goal Seek in Excel?
Goal Seek is a tool that finds the input needed to achieve a specific value in a formula cell.

Why use a macro for Goal Seek?
A macro automates the process, which is especially useful when applying Goal Seek to many cells or datasets.

Do I need to know coding to use a Goal Seek macro?
Basic macro recording or simple VBA knowledge is enough—you can also record a macro while using Goal Seek manually.

Can Goal Seek be used on multiple cells at once?
Not directly, but with a macro, you can loop through cells to apply Goal Seek individually to each one.

Is Goal Seek available in all versions of Excel?
Yes, Goal Seek is available in all modern desktop versions of Excel, including Excel 2013 and later.