Excel Tip: The Greatest Excel Tip of All Time?

Happy New Year everyone!

Welcome to the first post of 2015! I hope you've had a great break and are ready to explore more of what Excel has to offer in the New Year.

Many of you will know Bill Jelen from his many Excel books and/or his excellent (no pun intended) website, www.mrexcel.com. Well Bill is compiling a new book, MrExcel XL - 40 Greatest Excel Tricks and he sent me the following tweet over the Christmas break:

This was a tough challenge and the only way I could really think to address it was to look at the popularity (website traffic) of each of the tips that I post on here. This simplified the task hugely as there is one post that is consistently in the top three posts each month, even though it was written over three years ago. It is also something that I use in almost every formula I write!

My Greatest Excel Tip of All Time would be....

FANFARE....

"Learn how to use absolute references using $ and F4"

I have copied the information from my original post below, but if you like, you can still see the original, The dollar sign ($) in a formula - Fixing cell references, here.

Relative cell references
When you copy and paste an Excel formula from one cell to another, the cell references change, relative to the new position:

EXAMPLE:

If we have the very simple formula "=A1" in cell B1 it will change as follows when copied and pasted:

Pasted to B2, it becomes "=A2"

Pasted to C2, it becomes "=B2"

Pasted to A2, it returns an error!

In each case it is changing the reference to refer to the cell one to the left on the same row as the cell that the formula is in, i.e. the same relative position that A1 was to the original formula.

The reason an error is returned when it is pasted into column A, is because there are no columns to the left of column A.

This behaviour is very useful and is what allows a sum to be copied across or down the page and automatically refer to the new column or row that it finds itself in.

But in some situations, you want some or all of the references to remain fixed when they are copied elsewhere.

The dollar sign ($)
This is where the dollar sign is used.

EXAMPLE:

Take an example where you have a column of Sales values in Pounds Sterling in column A and a formula to convert these into US Dollars in column B. You could enter the actual exchange rate into the formula but it would be more sensible to refer to a cell where the exchange rate is held, so that it can be updated whenever it is needed.

The simple formula for cell B2, would be "=A2*E1", however if you copy this down, then the formula in cell B3, would read "=A3*E2" as both references would move down a row as described above.

This is where the dollar sign ($) is used. The dollar sign allows you to fix either the row, the column or both on any cell reference, by preceding the column or row with the dollar sign. In our example if we replace the formula in cell B2 with "=A2*$E$1", then both the "E" and the "1" will remain fixed when the formula is copied. i.e. in cell B3, the formula will read "=A3*$E$1", still referring to the cell with the exchange rate in it.

In this example we have fixed both the row and the column, but in other situations, you may just want to fix one or the other, for example:

Above we have a spreadsheet calculating the times tables where we want to every cell in the white area to be the product of its row and column heading. This is easy using the dollar symbol. In cell B2, the formula without dollars would be "=A2*B1", but for this formula to work when copied to each column, we need it to always look at column A for the first reference and to work for each row, we need to always look at row 1 for the second. Using the dollar sign to do this, it becomes "=$A2*B$1". This can then be copied to every cell in the white area.

Quick Tip
You can speed up entering the dollar signs by using the function key F4 when editing the formula, if the cursor is on a cell reference in the formula, repeatedly hitting the F4 key, toggles between no dollar signs, both dollar signs, just the row and just the column.



If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Excel Tip: Add a Christmas background to your spreadsheet

Well, this is my last post before Christmas, as I am taking some time off with the family over the Christmas break, so my next post will be in the New Year.

I thought I'd sign off with a simple, but seasonal post.

But, in case you don't read on, I would first of all like to wish all of my readers a very Merry Christmas and I look forward to working with many of you in the New Year.

If you want to add a bit of seasonal spirit to your spreadsheets, why not add a Christmas background?

You can easily add something like the Christmas Tree picture on this post as a background to any worksheet.

All you need to do is go to the Page Layout ribbon and click "Background". You can then browse to image you want to use and click OK. That worksheet will then use that picture as it's background, visible in cells that have "No Color" as their background colour. Note that a fill colour of White is therefore different to "No Color". A cell with a white background will hide the picture.

The picture will "tile", i.e. repeat itself indefinitely, and any cell contents will appear on top of it.

When the Christmas period is over and you want to go back to your boring old spreadsheet, just go back to the Page Layout ribbon and click "Delete Background".

Have a great break and see you in 2015!



If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Excel Tip: Calculating Net Present Value (NPV)

Last week we covered the IRR function, which enabled us to calculate the Internal Rate of Return of a project.

This week I am going to introduce the NPV function which allows us to calculate Net Present Value - a function closely related to IRR.

The Net Present Value of a series of cash flows, gives a value of those cash flows today discounted by a required rate of return.
The required rate of return represents represents the investor's time value of money. This is often the rate of return achievable in alternative investments.

So, for example, if we have the following series of cash flows (the same example as used for the IRR post):

Initial outlay  £20,000
Year 1 positive cash flow  £500
Year 2 positive cash flow  £5,000
Year 3 positive cash flow  £8.200
Year 4 positive cash flow  £9,000
Year 5 positive cash flow  £9,000

and we require a 10% annual rate of return.

The syntax of the NPV function is:

=NPV(rate,value1,[value2],.....)

where rate is the required rate of return and value1, value2, etc. are a series of cash flows at the same regular interval as the rate, so if the rate is an annual rate, then the cash flows should be a year apart.

value1 is required, whereas [value2] onwards are optional. These can be entered as values or refer to a range. Note that these cash flows are assumed to be at the end of each period.

So we could write the function for our example as follows:

=NPV(0.1, -20000,500,5000,8200,9000,9000)

which returns £2,257.25.

More likely though, we will have the values held in a range of cells as we did in the IRR example:

B2  -20000
B3   500
B4   5000
B5   8200
B6   9000
B7   9000

In this case our function would be:

=NPV(0.1,B2:B7)

Obviously, we would normally also refer to a cell to provide the rate as well, rather than enter it directly into the formula.

NPV relates to IRR  because IRR is the rate that produces zero NPV, so:

=NPV(IRR(B2:B7),B2:B7)

will always return zero.




If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Excel Tip: Calculating the internal rate of return for a project

Last week I introduced the PMT function for calculating payments on a loan.

This is one of the many functions under the Financial heading in Excel. This week I want to introduce you to another. One that allows you to calculate the internal rate of return (IRR) for a series of cash flows.

The IRR is a method often used to compare alternative projects or investments. and is stated as a percentage return, within a specified period.

As with the PMT function, it is worth starting by stating the assumptions used in the calculation. The IRR function requires the cash flows to be at regular intervals and the return percentage it calculates is  for the length of that interval. So, for example, if the cash flows fed to the function are annual cash flows, then the IRR returned will be a percentage per annum.

Also, the cash flows involved must include both positive and negative cash flows. If you think about this is self-evident, as any return percentage would be infinite if there was no outlay and all cash flows were positive!

Typically this will involve an initial outflow (or investment) followed by a series of projected inflows.

For example, a business investment opportunity might be presented as follows:

Initial outlay  £20,000
Year 1 positive cash flow  £500
Year 2 positive cash flow  £5,000
Year 3 positive cash flow  £8.200
Year 4 positive cash flow  £9,000
Year 5 positive cash flow  £9,000

We might want to compare it to another project and a useful piece of informationin doing this would be to calculate the IRR for each project.

So, how do we do it.

The IRR function syntax is as follows:

=IRR(values,[guess])

Most of the time you won't need the optional [guess] argument, but I will come to that in a minute.

values is the range of cells that show the cash flows in chronological order.

So, for our example we could enter the cash flows in cells B2 to B7 as follows:

B2  -20000
B3   500
B4   5000
B5   8200
B6   9000
B7   9000

Our function would then be:

=IRR(B2:B7)

which returns an IRR of 14% (rounded to zero decimal places). This means that over this 5 year period, the project has an internal rate of return of 14% per annum.

The [guess] argument is there because Excel uses an iterative process to calculate IRR and will give up if it has not arrived at it after 20 iterations. To do this it must start with an estimate. If the [guess] argument is omitted, an estimate of 10% (or 0.1) is used. For most projects this will work with no problems, however with cash flows that fluctuate wildly and/or have an unusually high or low return, it may not get there in the first 20 iterations, if it starts at 10%.

If this happens it will return a #NUM! error. You can then add a guess to the function so that it starts closer to the true answer and therefore can get there within the 20 iterations.

Next week we will look at calculating Net Present Value, which is the flipside of IRR.




If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".