Goal Seek in Excel using VBA: Find Minimum Sales to Hit a Target

Goal Seek in Excel using VBA lets you answer a “how much do I need?” question with one button click instead of opening the What-If Analysis dialog every time. In this tutorial you use it on a real sales problem: your current win rate is 43%, your target is 60%, and you expect to win 80% of new pitches. How many more sales do you need to pitch? You will build the sheet, run Goal Seek manually, then automate it with a five-line macro and a button. The same method works for any target you want to hit.

⬇️  Download the Practice File
ZIP with one .xlsb macro workbook · 2 sheets, 2 macros · enable macros after opening

Watch the Video Tutorial

The video explains the sales example, rebuilds the sheet from scratch, runs Goal Seek from the Data tab and then writes the macro and assigns it to a button.

What You Will Build

• A sales target sheet with the current win/lose numbers and the actual sales %.
• Two inputs in yellow: the target sales % and the winning % you expect on new pitches.
• A projection table that shows the new win, lose and total numbers for any number of extra sales.
• A Goal Seek macro that finds the minimum extra sales needed to hit the target.
• A Get Minimum Sales button so anyone can run it without opening the VBA editor.

The Business Problem

Start with the simple case from the video. You pitched 100 sales: 25 were won and 75 lost, so your sales % is 25%. Your target is 50%. If you assume you will win 100% of any new pitches, how many more do you need? Goal Seek answers 50: win becomes 75, lose stays 75, the total is 150 and 75 out of 150 is exactly 50%.

The practice file uses more realistic numbers: 756 won and 989 lost (43%), a target of 60% and an expected win rate of 80% on new pitches. The answer is about 1,450 more pitches.

Part 1 – Build the Goal Seek Sheet

Step 1: Enter the inputs

On the Goal Seek sheet the yellow cells are the only ones you type into:

• E5 – Sales Target (60%).
• K5 – Winning % assumption (80%), the share of new pitches you expect to win.
• E9 and E10 – the current WIN (756) and Lose (989) counts.

Step 2: Calculate the current sales %

E11  =SUM(E9:E10)
E13  =E9/E11

E11 returns the total of 1,745 pitches and E13 the actual sales % of 43%.

Step 3: Add the changing cell

G10 under the Minimum Sales Required heading holds the number of extra sales. Leave it empty or type any number; Goal Seek will overwrite it. In the file it is formatted with a large font and a light orange fill so the answer stands out.

Step 4: Build the projection table

This table shows what happens if you pitch the number of sales in G10:

K9   =E9+($G$10*K5)
K10  =E10+($G$10*(100%-K5))
K11  =SUM(K9:K10)
K13  =K9/K11

How the formulas work

• K9 (new WIN) – the current wins plus 80% of the new pitches.
• K10 (new Lose) – the current losses plus the other 20% of the new pitches. 100%-K5 turns the win rate into the loss rate.
• K11 (new Total) – all pitches, old and new.
• K13 (New Sales %) – new wins divided by the new total. This is the cell Goal Seek must push to 60%.

Try it by hand first, as PK does in the video: type a guess into the changing cell and watch the new sales % move. On the practice sheet he builds, 110 gives 59% and 120 gives 60%. Guessing works, but it is slow. That is the job of Goal Seek.

Part 2 – Run Goal Seek Manually

1. Go to Data > What-If Analysis > Goal Seek.
2. Set cell: the new sales % cell (K13 on the Goal Seek sheet).
3. To value: the target, typed as a number, for example 60%.
4. By changing cell: the minimum sales cell (G10).
5. Click OK, then OK again to keep the result.

Excel tries values in G10 until K13 reaches the target. Remove the decimal places to read the answer as a whole number. The catch: To value only accepts a typed number, not a cell reference, so each time the target or the inputs change you must open the dialog and type it again. A macro fixes that.

Part 3 – Automate Goal Seek in Excel Using VBA

Step 5: Write the macro

Go to Developer > Visual Basic, choose Insert > Module and paste this code. It is the exact macro from the practice file:

Option Explicit

Sub Goal_Seek()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Goal Seek")

sh.Range("K13").GoalSeek goal:=sh.Range("E5").Value, changingcell:=sh.Range("G10")

sh.Range("G10").NumberFormat = "0"

End Sub

How the code works

• Set sh = ThisWorkbook.Sheets("Goal Seek") points to the sheet by name, so the macro works even if another sheet is active.
• sh.Range("K13").GoalSeek is the Set cell from the dialog: the formula cell you want to reach a value.
• goal:=sh.Range("E5").Value is the To value. Unlike the dialog, VBA can read it from a cell, so changing the target in E5 is enough.
• changingcell:=sh.Range("G10") is the By changing cell.
• NumberFormat = "0" shows the result without decimals.

When you type .GoalSeek followed by a space, the VBA editor shows the syntax GoalSeek(Goal, ChangingCell As Range) As Boolean, which is a handy reminder of the argument order.

Step 6: Add the button

1. Go to Insert > Shapes and draw a rectangle (or a rounded rectangle) on the sheet.
2. Right-click it, choose Edit Text and type Get Minimum Sales.
3. Right-click again, choose Assign Macro, pick Goal_Seek from This Workbook and click OK.

Change the yellow inputs, click the button and G10 updates. With 756/989, a 60% target and 80% winning assumption it shows 1450, and the projection table shows 1,916 wins out of 3,195 pitches: 60%.

Step 7: Save as a macro-enabled workbook

Save the file as .xlsm or .xlsb (the practice file is .xlsb). A normal .xlsx file drops the macro.

Practice: The Second Example

Sheet1 in the download is the version PK builds live in the video, with its own macro New_Goal_seek in Module2. Its cells are different: the target is in D3, the winning assumption in K2, the current numbers in D7:D8, the changing cell is F8 and the new sales % is K10:

sh.Range("K10").GoalSeek goal:=sh.Range("D3").Value, changingcell:=sh.Range("F8")

With 555 won, 789 lost (41%), a 50% target and a 75% winning assumption, it returns about 465 extra sales. Comparing the two sheets is a good way to see that only the three cell references change.

Formula Alternative (Addition)

This model is simple enough to solve with algebra, so you can also get the answer with a formula and no macro. If W is current wins, T the total, g the target and p the winning assumption, the extra sales needed are (g × T − W) / (p − g). On the Goal Seek sheet:

=ROUNDUP((E5*E11-E9)/(K5-E5),0)

This returns 1455. Goal Seek returned 1449.83, which displays as 1450 and gives 59.97%. Goal Seek stops when it is close enough to the target, so its result can be a little short. If you need a strict minimum, round up the exact formula. Goal Seek is still the better tool when the model is too complex to rearrange by hand. This section is an addition to the original tutorial.

Common Errors and Fixes

The button returns a strange or negative number

In the video the first click returned -28 because the goal pointed to E3 instead of D3, an empty cell. Check that every reference in the GoalSeek line points to the right cell on the right sheet.

Goal Seek cannot find a solution

The target must be lower than the winning % assumption. If you expect to win 60% of new pitches, no number of pitches can lift the total to 60% or above. Lower the target or raise the assumption.

Run-time error 9: Subscript out of range

The sheet name in Sheets("Goal Seek") does not match a tab name. Rename the tab or fix the name in the code.

The button does nothing

Macros are disabled. Click Enable Content on the yellow security bar, or unblock the file in its Properties if Windows blocks macros from the internet.

Tips

• Keep inputs in coloured cells and lock everything else, so users only change what they should.
• The set cell must contain a formula that depends, directly or indirectly, on the changing cell.
• Reuse the macro for any target: break-even units, required price, loan payment. Only the three references change.
• Microsoft’s guide to using Goal Seek in Excel explains the dialog in detail.

Want It Ready-Made?

For more what-if tools, download the free What-If Analysis (Goal Seek, Scenario Manager and Data Table) in Excel workbook on NextGenTemplates.com, or browse the sales and marketing Excel templates for ready-made trackers and reports.

Related Tutorials

• What-If Analysis in Excel: Goal Seek, Scenario Manager and Data Table
• Excel VBA Tutorials for Beginners
• 33 Useful VBA Shortcut Keys
• VBA InputBox vs Application.InputBox in Excel

Frequently Asked Questions

How do I use Goal Seek in VBA?

Call the GoalSeek method on the formula cell, give it the goal value and the changing cell. The macro in Step 5 does exactly this for K13, E5 and G10 on the Goal Seek sheet.

Can the Goal Seek target come from a cell?

Not in the dialog, where To value must be typed. In VBA it can: the macro reads the target with Range E5 Value, so you only change the cell and click the button.

Why is the Goal Seek answer slightly different from the exact answer?

Goal Seek stops as soon as the result is close enough to the target. In this file it returns 1449.83 while the exact answer is 1455. Use the formula alternative and round up when you need a strict minimum.

Why does Goal Seek return a negative number?

Usually a wrong reference: the goal points to an empty cell, which Excel treats as zero. It can also happen when the target is not reachable with the assumptions you entered.

Which file format do I need for the macro?

A macro-enabled format: .xlsm or .xlsb. The practice file is an .xlsb binary workbook. Saving as .xlsx removes the code.

About the Author

PK is a Microsoft Excel expert and trainer and the founder of PK: An Excel Expert and NextGenTemplates.com. He has been teaching Excel, VBA, Power Query and Power BI since 2016 on his YouTube channel.

Conclusion

Goal Seek in Excel using VBA turns a repeated dialog into a one-click answer. Build the projection formulas, point GoalSeek at the result cell, read the target from a cell and attach the macro to a button. Download the practice file, change the yellow inputs and see how many sales you need to hit your own target.