In short
- If a function already works backwards, use it:
RATE,NPERorPVfor loans. It’s the fastest and most exact way. - If the formula is simple, solve for the unknown by hand: it’s one line.
- For everything else, Google’s free “Goal Seek” add-on or the Goal Seek panel in our template.
Is there Goal Seek in Google Sheets?
Not built in. Excel has it under What-If Analysis; Google Sheets has no menu or function by that name. But you can do it in four free ways, explained below with the same example: with a function that works backwards, by solving it by hand, with Google’s Goal Seek add-on, or with the panel in our template.
What Goal Seek does
Normally you type in the data and the formula gives you the result. Goal Seek goes the other way: you say what result you want and it finds the input that produces it.
It always needs three things:
- The cell with the formula: the result you want to fix. In Excel, “Set cell”.
- The value you want that formula to show.
- The cell that can change: an input typed by hand that the formula depends on. In Excel, “By changing cell”.
The example: what interest rate gives a $280 payment?
You borrow $15,000 over 5 years (60 monthly payments). At 5% a year, the monthly payment is $283.07. Your limit is $280 a month. What interest rate would you need?
| Row | A | B |
|---|---|---|
| 6 | Loan amount | $15,000 |
| 7 | Term (months) | 60 |
| 8 | Annual interest rate | 5.00% |
| 9 | Monthly payment | $283.07 |
PMT. In green, the cell we want to change.=PMT(B8/12,B7,-B6)We divide the rate by 12 because payments are monthly, and the loan amount is negative because, for the bank, it’s money going out. The answer is 4.55%. Here are the four ways to get there.
1. With a function that works backwards
For loans and investments, Google Sheets already has “backwards” functions. If you’re after the interest rate, RATE gives it directly:
=RATE(60,-280,15000)*12Result: 4.55% (exactly, 4.552%). We multiply by 12 because RATE returns the monthly rate. The other two most useful ones:
| If you want… | Function | Example |
|---|---|---|
| How much you can borrow for a given payment | PV | With $280 a month, 60 months and 5%: =PV(5%/12,60,-280) → $14,837.40 |
| How many payments it takes | NPER | With $300 a month: =NPER(5%/12,-300,15000) → 56.2, so 57 payments |
| The interest rate | RATE | The example above → 4.55% |
If your case has a function like these, you don’t need anything else: it’s exact and updates automatically when the inputs change.
2. Solving it by hand
When the formula is simple arithmetic, solving for the unknown is the cleanest way. An online store example:
A product costs you $18.40, shipping is $4.60 and the marketplace keeps 20% of the price. What price do you need to make $6 per unit?
Profit = Price × (1 − 0.20) − 18.40 − 4.60
You want 6 = Price × 0.80 − 23
So Price = (23 + 6) / 0.80 = $36.25
In the sheet, with the cost in B2, shipping in B3, the fee in B4 and the profit you want in B5:
=(B2+B3+B5)/(1-B4)Solving it by hand has an advantage Goal Seek doesn’t: the formula stays. If shipping goes up tomorrow, the price updates by itself.
3. With Google’s Goal Seek add-on
In 2019 Google released a free add-on, “Goal Seek”, that does the same as Excel’s. It has millions of installs, but it hasn’t been updated since May 2023.
- Install it. In your sheet, , search for “Goal Seek” (the one by Google) and click .
- Open it. In the Extensions menu, find Goal Seek and click “Open”. A panel opens on the right.
- Fill in the three fields. Select the payment cell and, next to “Set cell”, click the button to capture it. Type 280 in “To value”. Select the interest rate cell and capture it in “By changing cell”.
- Click “Solve”.
Other free add-ons do this too, such as SheetWhiz.
4. With the template
Our free template includes a Goal Seek panel. It comes with the loan and the price examples ready to try.
- Make a copy of the template in your Google Drive.
- Open the panel from .
- Fill in the three fields: “Formula cell” (
B9), “Target value” (280) and “Cell to change” (B8). “Use selected cell” copies whatever cell you have selected. - Click “Find”. In a couple of seconds the cell changes to 4.55% and the payment becomes $280.00. If you don’t like it, “Undo” puts it back.
If your calculation lives in another spreadsheet, copy it into the template: right-click the tab, .
No install at all: trial and error with a table
If you just want a quick idea, type several values in a column and calculate the formula next to them. Where the result crosses your target, that’s the answer:
| Annual rate | Monthly payment | |
|---|---|---|
| 4.0% | $276.25 | below 280 |
| 4.2% | $277.60 | |
| 4.4% | $278.96 | |
| 4.6% | $280.33 | this is where it passes 280 |
| 4.8% | $281.70 | |
| 5.0% | $283.07 |
The answer is between 4.4% and 4.6%. Narrowing down between those two values gets you to 4.55%, which is exactly what Goal Seek and the template do under the hood, only much faster.
Common errors
“The cell to change contains a formula”
The cell that changes has to be a number typed by hand. If it’s a formula, find the cell it depends on and use that one.
“It can’t find a value”
The result you ask for may be impossible: no payment on a $15,000 loan over 60 months can go below $250, because that would be interest-free. Check that your target makes sense.
“The formula doesn’t change when the cell changes”
The formula doesn’t depend on that cell, or depends on it through a rounding that keeps it fixed. Check that you picked the right two cells.
“It gives me a strange value”
Some formulas have two solutions (for example, those with a square). Goal Seek returns the one closest to the starting value: type a sensible starting value and try again.
Frequently asked questions
Is there Goal Seek in Google Sheets?
Not built in. In 2019 Google released a free add-on called “Goal Seek” that works like Excel’s. You can also use functions that work backwards, solve the formula by hand, or use the Goal Seek panel in our template.
Is there a Goal Seek function in Google Sheets?
There’s no function with that name. For loans and investments, some functions give the answer directly: RATE (the interest rate), NPER (the number of payments) and PV (how much you can borrow). For any other formula you need an add-on or the template.
Why is the Goal Seek add-on not working?
First check that the “Set cell” really contains a formula and that the “By changing cell” is a number typed by hand. Recent reviews of the add-on mention slowness and errors when opening the panel. If that happens to you, use RATE or NPER for loans, or the template’s panel.
Can I use Goal Seek on mobile (Android or iPhone)?
Functions such as RATE, NPER and PV work on any device, including the Google Sheets app. Google’s instructions for its Goal Seek add-on are for a computer, and the template is used on a computer too.
Can Goal Seek change multiple cells?
No: Goal Seek changes a single cell. If you need to change several at once to get the best result within limits, what you’re looking for is Solver. See the Solver in Google Sheets guide.
Is Goal Seek the same as Solver?
No. Goal Seek changes one cell until a formula hits an exact value. Solver changes several cells at once to reach the highest or lowest value while respecting limits.
Always review the result. Goal Seek finds the value that makes your formula show what you asked for; if the formula is wrong, so is the result.