Automatically Vary the Contents of a Cell in Excel & Google Sheets

This tutorial demonstrates how to automatically vary the contents of a cell, based on a target value for another cell, in Excel and Google Sheets.

 

 

There are a few ways to vary a cell’s contents without editing the cell directly. For example, you can automatically copy another cell. Read on to see how to change a value cell then automatically produce the desired result in another cell.

Goal Seek to Automatically Vary Cell

To change the contents of a cell by specifying the value of another cell, use Goal Seek.

1. In the Ribbon, select Data > What-If Analysis > Goal Seek.

 

 

2. Then set three values:
   • Set cell – the calculated cell whose value you want to specify.
     In the example below, there is a payment formula that works out the repayment amount required for the purchase price of a fridge over a term of 12 months at an interest rate of 6%.
   • To value – the target value for the Set cell.
   • By changing cell – the cell you want to vary automatically.

 

 

3. Once you’ve filled in these three amounts, click OK.

 

 

Goal Seek iterates through a number of scenarios until it reaches the target value. Here, this shows how much you can pay for a fridge if the monthly payment is increased to $200 a month.

Automatically Vary Cell in Google Sheets

To automatically vary the contents of a cell in Google Sheets, you need the Goal Seek add-on. If you don’t already have it installed, see: How to Add Goal Seek to Google Sheets.

1. Open the Google sheet where you wish to use Goal Seek. In the Menu, select Add-ons > Goal Seek > Open.

 

 

2. Goal Seek opens on the right-hand side of your screen. Set the cell that contains the formula (e.g., C7) and then in the To Value field, enter the value you wish it to change to (i.e., 200). Finally, select the cell where the result is placed (i.e., C4 – the purchase price of the fridge changes).

 

 

As with Excel’s Goal Seek, the cell you are setting (C7) must contain a formula, while the cell you are changing must contain a value.

3. If needed, amend the Options if to set the maximum iterations or the tolerance or time limit values.

 

 

4. Click Solve.

 

Once Goal Seek has found a solution, a Goal Seek Complete message pops up, and the result is displayed on the screen.