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

This tutorial will demonstrate 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 to automatically produce the desired result in another cell.

Goal Seek

To change the contents of a cell by specifying what the value of an alternative cell should be, 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 required 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 will open 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 will be placed (i.e., C4 – the purchase price of the fridge will change).

 

 

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 pop up is shown and the result is displayed on the screen.