All You Need to Know About

Other Formulas in Excel

The Excel Other Formulas are not very well-known, but you will be surprised on the usefulness of these. We have in store for you Financial, Array Excel Formulas, and a lot of hidden nuggets in the Excel Formula world!

Here are the top things on what you can do with Other Formulas in Excel:

Jump To A Cell Reference Within An Excel Formula

When writing, editing or auditing Excel formulas you will come across a scenario where you want to view and access the referenced cells within a formula argument.

This is helpful if you want to check how the formula works or to make any changes to the formula.

There is a cool tip where you can jump to the referenced cell or range within the formula and make your changes.

DOWNLOAD EXCEL WORKBOOK

STEP 1: Double click inside your Excel formula

Excel Cell Reference

STEP 2: Select the formula argument that you want to edit with your mouse

Excel Cell Reference

STEP 3: Press F5 which will bring up the Go To dialogue box and press OK

Excel Cell Reference

STEP 4: This will take you to the referenced cell/range

Excel Cell Reference

STEP 5: You can select the new range with your mouse and also make any changes to the formula bar

Excel Cell Reference

STEP 6: Press Enter and your formula is updated

Excel Cell Reference

Calculate your Monthly Investment with Excel’s FV Formula

What does it do?

Calculates the compound interest

Formula breakdown:

=FV(rate, nper, pmt, [pv])

What it means:

=FV(interest rate, number of periods, periodic payment, initial amount)


Computing the compound interest of an initial investment is easy for a fixed number of years.  But let’s add an additional challenge.

What if you are also putting in monthly contributions to your investment? Now that’s a lot more challenging to compute now!

How much would be available for you at the end of your investment?

Thankfully there is an easy way to calculate this with Excel’s FV formula! FV stands for Future Value.

In our example below, we have the table of values that we need to get the compound interest or Future Value from:

Calculate your Monthly Investment with FV Formula

There are two important concepts we need to use since we are using monthly contributions:

  • Since our interest rate is the annual rate, we will have to divide it by 12 to make it monthly
  • We will need to convert our number of years into number of months by multiplying it by 12

I explain how you can do this below:

DOWNLOAD EXCEL WORKBOOK

STEP 1: We need to enter the FV function in a blank cell:

=FV(

Calculate your Monthly Investment with FV Formula

STEP 2: The FV arguments:

rate

What is the rate of the interest?

Select the cell containing the interest rate and divide it by 12 to get the monthly interest rate (make sure that this is in a percentage):

=FV(B9/12,

Calculate your Monthly Investment with FV Formula

nper

How many periods?

Select the cell containing the number of years and multiply it by 12 to get the number of months:

=FV(B9/12, C9*12,

Calculate your Monthly Investment with FV Formula

pmt

What is the periodic payment?

Select the cell that contains your monthly contribution (this is your periodic payment):

=FV(B9/12, C9*12, D9,

Calculate your Monthly Investment with FV Formula

pv

What is the initial amount?

PV stands for present value, the initial amount. Multiply the entire result by -1.

=FV(B9/12, C9*12, D9, A9) * -1

Calculate your Monthly Investment with FV Formula

Apply the same formula to the rest of the cells by dragging the lower right corner downwards.

You now have all of the compound interest results!

Calculate your Monthly Investment with FV Formula

How to Remove Formulas in Excel

There are times when I have an Excel worksheet full of formulas and I want to hard code the results and remove the formulas completely.

This is very easy to do in Excel!

Here is our sample worksheet which has the following formulas in Column E:

I explain how you can remove formulas in Excel below:

DOWNLOAD EXCEL WORKBOOK

STEP 1: Select all the cells that have formulas:

STEP 2: Right click and select Copy:

STEP 3: Right click again and select Paste Values:

Now you will see that the values are only retained and the formulas are now gone!

Latest Tutorials

Want to get better at Excel?

Join Our Excel Community

Get our Free Weekly Excel lesson plans that cover the must know Excel features and tips to make you better at Excel!
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]