This video and transcript complement the How to Use Excel Goal Seek tutorial.
In this video, I’ll show you how to use the Goal Seek function in Microsoft Excel. This demo is using Excel for Microsoft 365, but this feature can be found in versions going back to Excel 2010. While this feature isn’t as robust as Excel solver, it can help you answer various “what if” scenarios by changing one’s cell value.
For this example, I’m using some election data and wondered how many more yes votes were needed. If you’d like to play along, you can download the sample spreadsheet found on the tutorial. It has basic formulas to determine vote totals and percentages. I already have it open.
Also, I have formatted column C to be whole numbers since you can’t have a portion of a person voting. In addition, column D is formatted as a percent with two decimal places. The first step is to reference your Set cell. This is the cell where you want to change the outcome. In my example, I’ll click cell D4.
Now from the Data menu, look to the right and select What If analysis from the Forecast group. From the dropdown menu, select Goal Seek. The Goal Seek dialogue box opens with the Set cell pre-populated. Cell D4 was our starting point.
Next, we have to tell Excel what value we should change our set cell to. In our case, that new value would be 67% as that’s needed to win the election. I’ll use YES votes, and we’ll click on cell C4 and click okay. Excel will go off and do the math and update the value in C4 so that we get our 67%.
If I like the answer of 5,123, I can click OK. While Goal Seek is a great tool, it has some requirements and issues. For example, my D4 Set cell has to contain a formula. I have a formula that divides the number of YES votes by the Total votes. Also, I can’t add any constraints.
If you look above, you may have noticed that the total number of voters increased along with my Yes vote count. It did not stay constant. Another issue is the rounding. If you look at my results, Excel didn’t give me 67%, but 66.94%.
The feature works great for situations where you want to reduce the number of house payments or adjust the payment amount. But a better solution for this problem might be the Excel solver add-in.
Thanks for watching and check out our Excel solver tutorial to see how it differs.