Start the new decade with Goal Seek in #Excel

Happy New Year of 2020!Β  Wish you all an Excellent year decade ahead! πŸ˜‰

Have you made your new year resolutions?

Or you are relying on Excel to set your goals of the year?

Did you know that Excel has a feature called Goal Seek?

Let’s start the year with a simple example to demonstrate how to use Goal Seek in Excel!

The situation:

Let’s say I want to set a saving plan for the year.Β  My saving plan is simple: On week 1, I started my saving with $10.Β  Following each week, I will save just 1% more than the previous week’s saving.

With Excel, I can calculate the total savings at the end of week 52 easily.

You may download a Sample FileΒ to follow along.

The Set up:

Excel tip - Goal Seek Sample 1

B2 = Input value: the weekly increment percentage that I am committed to
B5 = Input value: my saving on week 1
B6 = B5*(1+$B$2)
B4 = SUM(B5:B56)

Pretty easy and straight-forward… 😁

By starting with $10, and save just 1% more every week, I am expected to save $677.69 in total by the end of the year.


What if…

  • my goal is to save $1000 in total by the end of the year?
  • by changing my initiate saving amount ($X)
  • with the weekly incremental percentage remains constant

To solve the equation, I can either

1) Trial and Error by changing the value in B5 manuallyExcel tip - Goal Seek demo

2) or using Goal SeekExcel tip - Goal Seek demo1


Well, I believe the choice is obvious!Β  Isn’t it?

So the question is, where is “Goal Seek”?

It’s under

  1. Data Tab
  2. What-if Analysis
  3. Goal Seek

Excel tip - Goal Seek Sample 2

There we go!

Then input the parameters correctly:

  1. Set (target) cell – reference input
  2. To value (your gold) – numeric input
  3. By changing cell – reference input
  4. OK

Excel tip - Goal Seek Sample 3


The target cell set in step 1 should always be a formula; its result is dependent (directly / indirectly) on the changing cell set in step 3


If you don’t care to see the weekly figures, you can use FV function to obtain the result – total savings in the end of the year.

Excel tip - Goal Seek Sample 4

The formula is simple:

= - FV(B2, 52, B5) 'where
B2 is the interest rate of each period
52 is the number of period
B5 is the amount of first investment (savings)

You can visit the following page for more details on this function.

From now on, will you rely Excel on finding your goals?  😁

About MF

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Excel Tips and tagged , . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.