Goal Seek: Excel’s Crystal Ball

Goal Seek is one of Excel’s most useful tools for creating predictive models. You can use Goal Seek to help optimize something as simple as a mortgage payment or incorporate it into a larger, more complicated data analysis. So, if you know what you want your mortgage payment to be and, you want to determine how much money you should put down (or what interest rate you need to obtain), Goal Seek makes this quick and easy! It allows you to target different variables within your data model and use the results to determine the best inputs. It allows you to quickly change the values of variables within your model so that you can easily see what the effects are on the final result.

Getting Started With Goal Seek

In order for Goal Seek to be effective, it is important to have the data in your spreadsheet laid out properly. You need to establish one “main” formula cell that contains the primary formula that you want to change. You will then need to have one or more feeder cells which feed into your primary formula. In the example below, the main formula is the Monthly Payment formula (bolded in Cell C1). Cells B2-B5 contain the four variables that make up the Monthly Payment Formula (=PMT(B4/12,B3,-B5).
Goal Seek Data Setup

From here, simply navigate to the Data Tab on the Ribbon, click the “What-If Analysis” button, and then select “Goal Seek”. You’ll then get a screen that looks like this:
Goal Seek Screen

Setting Up the Simulation

The “Set Cell” value should be set to the cell that contains your main formula (in this case, C1). The second field “To Value”, should be set to whatever number you want your main formula to produce. So, for example, if we wanted to lower the Monthly Payment result to $700 (from its current $859.35), you would enter “700” in this field. The last field, “By Changing Cell”, is the cell that Goal Seek will change in order to produce the desired result in the main formula (in our example, $700). So, if you wanted to lower your Monthly Payment by increasing your Down Payment, you would enter “B2” in this field.

Your screen should look like this:
Goal Seek Screen Final

Click “OK” to run the simulation. You’ll get a results screen that looks like this:
Goal Seek Result Screen

As you can see, Excel has adjusted the amount of the Down Payment to $53,377 (from its prior $20,000) in order to produce the desired monthly payment of $700. Click “OK” to save the results, or click “Cancel” to discard the changes. You can run this simulation for any of the other variables (such as Term and Interest Rate) to determine how long you would need to extend the payments or how low the interest rate would need to go in order to obtain the $700 monthly payment. Goal Seek is very flexible and a good way to quickly determine how much a variable needs to change in order to obtain the desired final result.

Learn About Excel’s Other Data Tools

Besides Goal Seek, there are several other Data Tools that you can use to simulate different data scenarios. You can learn all about them by signing up for our free email newsletter. You’ll get valuable Excel tips and tricks directly in your inbox and, you’ll also receive exclusive discounts on our training classes.


How to Find Duplicates in Excel (And What to Do With Them)

Being able to find duplicates in Excel is a very valuable skill to learn, as it enables you to quickly clean up data and troubleshoot problems. Sometimes you just need to locate the duplicates, other times you need to find and remove them. Regardless of your end-goal, this tutorial will teach you two different ways to find duplicates in Excel and how to quickly remove them (if you so desire).

1. Remove Duplicates Button

The most obvious way to find and remove duplicate values in Excel is to simply use the Remove Duplicates button (in the Data Tools group on the Data Tab). The functionality is actually pretty flexible, allowing you to select either a single column or a whole series of columns and then removing duplicates based on the criteria that you input. So, you can either remove duplicates from a single column (while leaving the rest of the table intact) or delete an entire row if it contains a duplicate value in one or more columns that you specify. So, if you just need to quickly clean up your data and completely remove any duplicate values the dataset, the Remove Duplicates button is the way to go.

2. Conditional Formatting

If you simply want to find duplicates in Excel (without necessarily deleting them), the best way to accomplish that is with Conditional Formatting. Simply use the Highlight Duplicate Values option. Once you’ve set the formatting, you can then apply a filter to the column with duplicate values and filter down to the duplicates. At that point, you can either edit, delete, or just ignore the duplicate values. The best part about using the Conditional Formatting method to find duplicates in Excel is that it’s dynamic! So, if you deciede to delete a few of your duplicates (or add in some more duplicates later), the Conditional Formatting will automatically update. Simply refresh the Filter to see the most current duplicates.

Learn to Find Duplicates in Excel and Much More!

Sign up now for our free email newsletter: Not only will you receive Excel tips and tricks directly in your inbox, you’ll also receive a free Gantt Chart template, which you can use to plan your next project.