- slide 1 of 6
Excel 2013’s Goal Seek tool uses existing formulas and data to calculate required future performance to meet a specified goal. As an example, say you have sales data for the first three quarters of a startup business and wanted to know how much in sales you need in the fourth quarter to reach $50,000 for the year.
- slide 4 of 6
In the example, you are trying to calculate what’s needed in the fourth quarter to reach a goal of $50,000 total for the year. Therefore, Set Cell needs to reference cell F7 (or $F$7, it doesn’t matter in this example), because F7 is where the year’s total is calculated.
The Set Cell field must contain a calculable formula that references the projection cell (that is, the cell referenced in the By Changing Cell field) for Goal Seek to work.
3. Enter the target reference in the By Changing Cell field and click OK. This target reference should be the cell whose value you’re anticipating to reach a particular goal.
- slide 5 of 6
In this example, you’re trying to calculate the total fourth quarter value needed to reach your annual goal of $50,000, so cell E7 is the cell to be changed.
Note that this cell must either be blank or contain a value. It cannot contain a formula, even if that formula is valid. So in this example, the total 4th quarter calculation had to be changed to zero (or deleting the formula entirely would also work). This cell must also be referenced in some way by the goal calculating formula, i.e., the cell referenced by the Set Cell field.
4. Click OK to accept the new values in the cells referenced by the Set Cells and By Changing Cell fields. Alternatively, if you’re already satisfied with the projection, click Cancel to keep the original data in the referenced cells.