Click & Drag a Data Series to Goal Seek in Excel

Did you know that you can click on a Data Series in an Excel Chart and “drag it to reach a new value?” You can!

Changing the data value in your chart brings up the Goal Seek Dialog Box. Now, it is a simple matter to confirm your new value and tell Excel which cell reference to change in your formula. Instantly, your chart redraws and your data and formula are updated with the substitute value – and your NEW GOAL!

Here are the steps to follow in this Excel Video Lesson:

  1. IĀ use the =FV() Function to show the future value of my investment for each year that we invest. The =FV() Function requires a “fixed” interest rate and a constant investment value contributed each period.
  2. I inserted a “Line” type chart onto my worksheet – i.e. I “embedded” the chart on the sheet.
  3. I used a “Custom Format” on the Value Axis for our numbers (#,##0,K) to show the numbers as thousands ($80K)
  4. For this chart, I also changed the “Maximum Value” of the Value Series by typing in a new (higher) number.
  5. To reach a “new goal” on our chart, first click to select the data series. Then click again and drag the data line to a new point on the chart.
  6. This brings up the “Goal Seek” Dialog Box. Edit your new goal if necessary. Then click on the cell that you want to change in order reach your goal. That cell must be referenced in your formula. The chart redraws and your formula results are updated.

Find the Excel Video Lesson that you want – Index of all Excel Topics

News! My DVD, “The 50 Best Tips for Excel 2007” is now available to purchase. I invite you to visit my online bookstore for more details.

Excel Tells You the Future Value of Your Investment

Hey, want to know what your investment will be worth in 5 years? 10 years? Want a sure thing? Excel correctly calculates the future value of any investment! Provided …

  • You make a series of regular investments – even if it is just one “lump sum.”
  • The interest rate that you earn is “constant” – that is a “fixed annual interest rate.”

OK, so now you understand that neither I nor Excel are “touting sure thing” winners in the stock market. No one can do that. But, you can’t beat Excel when it comes to accurate – and easy to use – financial calculations.

In this video lesson, I demonstrate how to use both the =FV() and =PMT() Functions to calculate Future Values. I also show you how to perform “What-If” Analysis using a One-Input Data Table.

Here are the steps to follow in this Excel Video Lesson:

  1. Input values for Interest Rate (RATE), Number of Periods (NPER) and your Investment (PMT). These are the three required arguments for the =FV() Function. Use the Function Argument Dialog Box to ensure that you use the correct syntax for this function.
  2. One of the “optional” arguments is “Type.” If you omit this, Excel defaults to a “0” which means that you will make your investment contribution at the end of each period. A “1” means that the contribution is made at the beginning of each period. Over a number of years, this can make a significant difference in the “Future Value” of your investment.
  3. FV is also an “argument” in the =PMT() Function. If you want to calculate how much you need to contribute each month to reach a Savings Goal, use the =PMT() Function. However, in this case, the FV – while showing as an “Optional Argument” will refer to the cell containing your Savings Goal.
  4. Create a One-Input Data Table to perform “What-if” Analysis – e.g. to substitute a series of different interest rates in your =PMT() – or =FV() Function.

NEW! Download the Practice File for this Excel Video Lesson:

Find the Excel Video Lesson you want – Index of all Excel Topics

News! My DVD, “The 50 Best Tips for Excel 2007” is now available to purchase. I invite you to visit my online bookstore for more details.