How to use Goal Seek in Excel

Here's how you can use Goal Seek in Excel!

The microsoft excel logo on a red background demonstrates the power of spreadsheets.

Last Updated on

If you want to know how to use Goal Seek in Excel, you’ve landed on the right page.

Goal Seek is a handy function to use in your Microsoft Excel spreadsheets to find out the desired input value for a formula when you already know the output value. This can prove to be extremely useful, especially when dealing with a large data set. However, not a lot of people know that this feature exists, and those who do struggle to use it.

To help you out, we’re going to explain how to use Goal Seek in Excel, along with what to do in case you face any errors related to it.

How you can use Goal Seek in Excel

Scenario on hand: We have a dataset with a student’s marks and names. 

What we want to accomplish: Explore how to use Goal Seek in Excel by:

  1. Understanding the Goal Seek parameters.
  2. Operating Goal Seek on a dataset.
  3. Getting a hang of the Goal Seek errors and problems.

Goal Seek parameters

When you want to work with Goal Seek, it is important to first understand the parameters needed to complete this process:

Set cell

In this box, you will have to enter the cell address you want corrected or changed. 

To value

This box requires your desired output. For instance, 70% of the total marks could be the desired output. 

By changing cell

In this box, you have to enter the cell address that needs to be adjusted. If a student requires 70% of the total marks, the ‘by changing cell’ will have the cell address of the final project marks that the student needs to secure to get to this 70%. 

Note: The ‘By changing cell’ is one variable input. Goal Seek works with only one variable input. If you have multiple input values, the Solver is a valuable Excel tool.

Step

1

Organize your data

To use Goal Seek, you first need to open up your Microsoft Excel spreadsheet, which features all the data and information you want to use.

For our demonstration, we have the marks that the student has secured throughout the term, except the final project marks.

A screenshot of a spreadsheet in Excel demonstrating the use of Goal Seek.

Once your data is organized, you will need to select the cell that you want to change. In our case, this is cell C10, which refers to the final project marks of the student.

Step

2

Select Data tab

After selecting the cell, head up to the top ribbon menu on your window. From the tab menu, you will need to choose the ‘Data’ tab.

On the Data menu, you need to find the ‘What if Analysis’ button and select ‘Goal Seek’ listed under it.

How to create a table in excel using Goal Seek.

Clicking this will bring up a Goal Seek dialog box near your selected cell.

Step

3

Type the desired value

In the ‘To value’ box, you will need to type in the desired result. For example, in our demonstration, we enter 70% in the ‘To value’ box. In the set cell, we select the cell where the percentage is calculated.

This implies that we want to change 42% to 70%. Next, we enter the cell address that we want to change. In our case, we want to determine the final project marks that the student needs to secure to get an overall 70% mark on the course.

So, in the By changing cell’ box, we enter cell C10 for the final project marks.

Here’s what our Goal Seek dialog box looks like:

An example of how to use Goal Seek in Excel.

Step

4

Final result

Once that is done, click ‘OK,’ and Excel will automatically overwrite your cell’s value to calculate the result you need to achieve your desired outcome.

Once it has found the solution you need, you can choose to accept it by selecting ‘OK’ once more. This will keep the solution result in your spreadsheet.

Alternatively, you can select ‘Cancel’ to return your spreadsheet to its original state.

How to create and utilize a spreadsheet in Excel.

Since we got the answer we needed, we clicked ‘OK’ to keep the answer.

Goal Seek errors and problems

Unfortunately, there can be errors and issues that crop up when trying to use the Goal Seek function in Microsoft Excel. Let’s take a look at a few of them to see how you can fix them.

Error 1: Cell must contain a formula in Goal Seek

If the error ‘Cell must contain a formula’ comes up in Goal Seek when trying to calculate your solution, this means that there is no formula written in the cell you want to change.

This means that for Goal Seek to work, you need to be using formulas in your spreadsheet.

To solve this, you need to change the data in the cell you want to change from a written format to a formula.

Excel will automatically turn this formula into the answer, but Goal Seek requires the formula for it to function.

Let’s consider our example once again. In our data, we used a simple formula to calculate the current percentage of marks secured by the student. If we change it to a static 42% instead of a formula that calculates the percentage, we get a ‘Cell must contain a formula error’:

An example of how to create a spreadsheet in excel using Goal Seek.

Error 2: Simplified results

Another issue with Goal Seek is irregularities or simplified results.

A lot of people who use Goal Seek complain that their answers were rounded up or not accurate – this is because Goal Seek cannot take into account too many variables or formulas.

So, only use Goal Seek for very simple spreadsheets. Anything too complicated will only result in misleading solutions.

For complicated formulas and spreadsheets, the add-on Excel Solver is more than capable of finding accurate solutions.

Error 3: Reference must be a single cell

The Goal Seek function requires one cell with a formula, one desired value, and one variable value. If you choose more than one variable value, Goal Seek will return the error ‘Reference must be a single cell.’ 

In our case, if the student has not yet given Test 3 and the final project and we wish to use Goal Seek to calculate the marks for both assessments, it will give this error:

An example of how to create a spreadsheet in Excel, showcasing the use of Goal Seek.

Wrapping up

Goal Seek is a valuable function in Excel that simplifies a lot of calculations. However, to use Goal Seek, one must understand its parameters and know about the common errors it returns. To make things easier for you, we have talked about how to use Goal Seek in Excel and have explained what to do in case you face any errors.

Learn more about Excel through these helpful guides:

'),b.insertAdjacentHTML(d,e));return document.getElementById(a)};c.prototype._repeatDynamicAds=function(b){var a= [],d=this._removeRecipeCapExperiment.result&&"Recipe"===b.location?99:this.locationMaxLazySequence.get(b.location),e=b.lazy?u(d,0):0;d=b.max;var f=b.lazyMax;e=Math.max(d,0===e&&b.lazy?d+f:Math.min(Math.max(e-b.sequence+1,0),d+f));for(f=0;f=d;a.push(y(y({},b),{sequence:g,lazy:h}))}return a};c.prototype._locationEnabled=function(b){b=this._clsOptions.enabledLocations.includes(b.location);var a=this._clsOptions.disableAds&&this._clsOptions.disableAds.all|| document.body.classList.contains("adthrive-disable-all"),d=!document.body.classList.contains("adthrive-disable-content")&&!this._clsOptions.disableAds.reasons.has("content_plugin");return b&&!a&&d};return c}(),Za=function(c){function b(a,d){var e=c.call(this,a,"ClsVideoInsertion")||this;e._videoConfig=a;e._clsOptions=d;e._IN_POST_SELECTOR=".adthrive-video-player";e._WRAPPER_BAR_HEIGHT=36;e._playersAddedFromPlugin=[];d.removeVideoTitleWrapper&&(e._WRAPPER_BAR_HEIGHT=0);return e}v(b,c);b.prototype.init= function(){this._initializePlayers()};b.prototype._wrapJWPlayerWithCLS=function(a,d,e){void 0===e&&(e=0);return a.parentNode?(d=this._createGenericCLSWrapper(.5625*a.offsetWidth,d,e),a.parentNode.insertBefore(d,a),d.appendChild(a),d):null};b.prototype._createGenericCLSWrapper=function(a,d,e){var f=document.createElement("div");f.id="cls-video-container-".concat(d);f.className="adthrive";f.style.minHeight="".concat(a+e,"px");return f};b.prototype._getTitleHeight=function(a){a.innerText="Title";a.style.visibility= "hidden";document.body.appendChild(a);var d=window.getComputedStyle(a),e=parseInt(d.height,10),f=parseInt(d.marginTop,10);d=parseInt(d.marginBottom,10);document.body.removeChild(a);return Math.min(e+d+f,50)};b.prototype._initializePlayers=function(){var a=document.querySelectorAll(this._IN_POST_SELECTOR);a.length&&this._initializeRelatedPlayers(a);this._shouldRunAutoplayPlayers()&&this._determineAutoplayPlayers()};b.prototype._createStationaryRelatedPlayer=function(a,d,e){var f="mobile"===this._device? [400,225]:[640,360],g=G.Video_In_Post_ClicktoPlay_SoundOn;d&&a.mediaId&&(e="".concat(a.mediaId,"_").concat(e),d=this._wrapJWPlayerWithCLS(d,e),this._playersAddedFromPlugin.push(a.mediaId),d&&this._clsOptions.setInjectedVideoSlots({playerId:a.playerId,playerName:g,playerSize:f,element:d,type:"stationaryRelated"}))};b.prototype._createStickyRelatedPlayer=function(a,d){var e="mobile"===this._device?[400,225]:[640,360],f=G.Video_Individual_Autoplay_SOff;this._stickyRelatedOnPage=!0;this._videoConfig.mobileStickyPlayerOnPage= "mobile"===this._device;if(d&&a.position&&a.mediaId){var g=document.createElement("div");d.insertAdjacentElement(a.position,g);d=document.createElement("h3");d.style.margin="10px 0";d=this._getTitleHeight(d);d=this._wrapJWPlayerWithCLS(g,a.mediaId,this._WRAPPER_BAR_HEIGHT+d);this._playersAddedFromPlugin.push(a.mediaId);d&&this._clsOptions.setInjectedVideoSlots({playlistId:a.playlistId,playerId:a.playerId,playerSize:e,playerName:f,element:g,type:"stickyRelated"})}};b.prototype._createPlaylistPlayer= function(a,d){var e=a.playlistId,f="mobile"===this._device?G.Video_Coll_SOff_Smartphone:G.Video_Collapse_Autoplay_SoundOff,g="mobile"===this._device?[400,225]:[640,360];this._videoConfig.mobileStickyPlayerOnPage=!0;var h=document.createElement("div");d.insertAdjacentElement(a.position,h);d=this._wrapJWPlayerWithCLS(h,e,this._WRAPPER_BAR_HEIGHT);this._playersAddedFromPlugin.push("playlist-".concat(e));d&&this._clsOptions.setInjectedVideoSlots({playlistId:a.playlistId,playerId:a.playerId,playerSize:g, playerName:f,element:h,type:"stickyPlaylist"})};b.prototype._isVideoAllowedOnPage=function(){var a=this._clsOptions.disableAds;if(a&&a.video){var d="";a.reasons.has("video_tag")?d="video tag":a.reasons.has("video_plugin")?d="video plugin":a.reasons.has("video_page")&&(d="command queue");q.error(d?"ClsVideoInsertionMigrated":"ClsVideoInsertion","isVideoAllowedOnPage",Error("DBP: Disabled by publisher via ".concat(d||"other")));return!1}return this._clsOptions.videoDisabledFromPlugin?!1:!0};return b}(function(c){function b(a, d){var e=c.call(this)||this;e._videoConfig=a;e._component=d;e._stickyRelatedOnPage=!1;e._relatedMediaIds=[];a=void 0;void 0===a&&(a=navigator.userAgent);a=/Windows NT|Macintosh/i.test(a);e._device=a?"desktop":"mobile";e._potentialPlayerMap=e.setPotentialPlayersMap();return e}v(b,c);b.prototype.setPotentialPlayersMap=function(){var a=this._videoConfig.players||[],d=this._filterPlayerMap();a=a.filter(function(e){return"stationaryRelated"===e.type&&e.enabled});d.stationaryRelated=a;return this._potentialPlayerMap= d};b.prototype._filterPlayerMap=function(){var a=this,d=this._videoConfig.players,e={stickyRelated:[],stickyPlaylist:[],stationaryRelated:[]};return d&&d.length?d.filter(function(f){var g;return null===(g=f.devices)||void 0===g?void 0:g.includes(a._device)}).reduce(function(f,g){f[g.type]||(q.event(a._component,"constructor","Unknown Video Player Type detected",g.type),f[g.type]=[]);g.enabled&&f[g.type].push(g);return f},e):e};b.prototype._checkPlayerSelectorOnPage=function(a){var d=this;a=this._potentialPlayerMap[a].map(function(e){return{player:e, playerElement:d._getPlacementElement(e)}});return a.length?a[0]:{player:null,playerElement:null}};b.prototype._getOverrideElement=function(a,d,e){a&&d?(e=document.createElement("div"),d.insertAdjacentElement(a.position,e)):(d=this._checkPlayerSelectorOnPage("stickyPlaylist"),a=d.player,d=d.playerElement,a&&d&&(e=document.createElement("div"),d.insertAdjacentElement(a.position,e)));return e};b.prototype._shouldOverrideElement=function(a){a=a.getAttribute("override-embed");return"true"===a||"false"=== a?"true"===a:this._videoConfig.relatedSettings?this._videoConfig.relatedSettings.overrideEmbedLocation:!1};b.prototype._checkPageSelector=function(a,d,e){void 0===e&&(e=[]);return a&&d&&0===e.length?("/"!==window.location.pathname&&q.event("VideoUtils","getPlacementElement",Error("PSNF: ".concat(a," does not exist on the page"))),!1):!0};b.prototype._getElementSelector=function(a,d,e){if(d&&d.length>e)return d[e];q.event("VideoUtils","getPlacementElement",Error("ESNF: ".concat(a," does not exist on the page"))); return null};b.prototype._getPlacementElement=function(a){var d=a.pageSelector,e=a.elementSelector;a=a.skip;var f=da(d),g=f.valid,h=f.elements;f=S(f,["valid","elements"]);var k=ca(e),l=k.valid,p=k.elements;k=S(k,["valid","elements"]);return""===d||g?l?this._checkPageSelector(d,g,h)?this._getElementSelector(e,p,a)||null:null:(q.error("VideoUtils","getPlacementElement",Error("".concat(e," is not a valid selector")),k),null):(q.error("VideoUtils","getPlacementElement",Error("".concat(d," is not a valid selector")), f),null)};b.prototype._getEmbeddedPlayerType=function(a){(a=a.getAttribute("data-player-type"))&&"default"!==a||(a=this._videoConfig.relatedSettings?this._videoConfig.relatedSettings.defaultPlayerType:"static");this._stickyRelatedOnPage&&(a="static");return a};b.prototype._getMediaId=function(a){return(a=a.getAttribute("data-video-id"))?(this._relatedMediaIds.push(a),a):!1};b.prototype._createRelatedPlayer=function(a,d,e,f){"collapse"===d?this._createCollapsePlayer(a,e):"static"===d&&this._createStaticPlayer(a, e,f)};b.prototype._createCollapsePlayer=function(a,d){var e=this._checkPlayerSelectorOnPage("stickyRelated"),f=e.player;e=e.playerElement;var g=f?f:this._potentialPlayerMap.stationaryRelated[0];g&&g.playerId?(this._shouldOverrideElement(d)&&(d=this._getOverrideElement(f,e,d)),d=document.querySelector("#cls-video-container-".concat(a," > div"))||d,this._createStickyRelatedPlayer(y(y({},g),{mediaId:a}),d)):q.error(this._component,"_createCollapsePlayer","No video player found")};b.prototype._createStaticPlayer= function(a,d,e){this._potentialPlayerMap.stationaryRelated.length&&this._potentialPlayerMap.stationaryRelated[0].playerId?this._createStationaryRelatedPlayer(y(y({},this._potentialPlayerMap.stationaryRelated[0]),{mediaId:a}),d,e):q.error(this._component,"_createStaticPlayer","No video player found")};b.prototype._shouldRunAutoplayPlayers=function(){return this._isVideoAllowedOnPage()&&(this._potentialPlayerMap.stickyRelated.length||this._potentialPlayerMap.stickyPlaylist.length)?!0:!1};b.prototype._determineAutoplayPlayers= function(){var a=this._component,d="VideoManagerComponent"===a,e=this._config;if(this._stickyRelatedOnPage)q.event(a,"stickyRelatedOnPage",d&&{device:e&&e.context.device,isDesktop:this._device}||{});else{var f=this._checkPlayerSelectorOnPage("stickyPlaylist"),g=f.player;f=f.playerElement;g&&g.playerId&&g.playlistId&&f?this._createPlaylistPlayer(g,f):q.event(a,"noStickyPlaylist",d&&{vendor:"none",device:e&&e.context.device,isDesktop:this._device}||{})}};b.prototype._initializeRelatedPlayers=function(a){for(var d= new Map,e=0;e