This tip is all about that annoying time when someone gives you some dates for your spreadsheet and they come in all wrong. What has happened ?

You will be painfully aware of it if you work in a multi-regional company, and you are in Europe.

Well... most likely, along the way, someone has been using an American date format:

mm/dd/yyyy

April 2009 Stats update from ExcelExperts.com

This tip is about doing what the Excel CONCATENATE function should be doing, but isn't !

Suppose we want to create a comma-separated list from a range or 2 ranges... How can we do that ?

• Write a customised function in VBA !

Here's out source Data:

This tip shows you how to center text across cells. Suppose you have the following arrangement, and you want the heading to span the whole table:

One option is to merge the cells.. right click => format cells => Alignment => merge cells.

This tip is on how to select the Current Region using a keyboard shortcut:

CTRL + A

Suppose you have the following data:

Pressing CTRL + A will select a square around all the cells that are connected to the current cell.

Hi all I have been extremely busy over the past week doing Excel training videos for the Excel tips and calculators... all recorded with the best free software that money can buy !!

; - >

There are 64 new Excel Training Videos with some gr8 tips and tricks..

Feedback welcome

Nick

Excel Training Videos

This tip teaches you how to paste as a hyperlink.

• This means that you create a hyperlink from an existing cell.

Step1: Create a new workbook.

Step2: Save it

Step3: Copy the cell you want to hyperlink to.

Step4: Select another cell, and click on the Paste ribbon... choose "Paste as hyperlink" Step by step training Video on how to Paste As Hyperlink:

Announcing some new training videos from ExcelExperts.com

Facebook Texas Holdem Poker Training Video:

Buy Or Rent Calculator - Worked Example:

Small numbers look odd in Excel.

A Very small number will look something like this: 3.38E-09

- This is scientific notation meaning 3.48*10^-9 (3.48 times 10 to the power of -9, or 3.48*0.000000001) The reason Excel does this (I think) is to be able to display a small number in a small space.

What is RISK ?

It’s a word we hear a lot these days...

CEO, Investment bank:

UP !

I had a look at interest rates available on the market, and found the 1y rate to be 1.50% and the 5y was at 3.20%

Taking just these 2 rates, and assuming that there's no arbitrage, I calculated the 4y rate in 1 year's time to be 3.63%.

Here's a tip for those of you who need to prepare end of month reports.

There's a way to calculate the last Friday of the month in Excel.

Here's how it looks in Excel:

So first, you enter a date.

Interpolation is a methodology of guessing what the value would be in between 2 points.

Linear interpolation assumes that between the 2 points is a straight line.

Lets have a look at an example:

Suppose we know the Zero Rate for 1 year: 3% We also know the 4y Zero Rate: 8% ... how do we calculate the 2 year Zero Rate ?

ExcelExperts.com has teamed up with YouTube to provide Training videos.
A lot of people loved the Buy or Rent calculator, but they thought it was too complicated.
Well, hopefully these training videos will make it all clear.

There are 2:
1. An introduction to using the calculator
2. A worked example building up the calculation step by step.

Hyperlinks can be like mosquitoes

- Sometimes you can't see them and they get you when you're least expecting.

The reason is that even if you clear the contents of a cell, the hyperlink can remain. Time to blast them with a bit of VBA. It's easily done... Hyperlinks are stored on a sheet by sheet basis, so all you have to do is to loop through the hyperlinks collection of the active sheet and delete them.

Today’s tip is an introduction to Pivot tables and Pivot table summaries.

Pivot tables are an amazing way to summarise data. They are extremely versatile, and can provide many types of summary seamlessly. They can save hours of trying to write formulae to create summaries.

What is Quantitative Easing ?

Simply put:

It's printing money !

It's a sneaky way of stealing money from cautious savers who carefully tucked money away for a rainy day, and giving it to reckless borrowers.

What is Leverage ?

What is Yield ?

Remember IRR ?... well, simply put, it's another name for that.

It's the return you get from a series of cash flows, but applies mostly to Bonds. Lets prove that using Excel functions.

What is a Basis Point ?

A basis point is quite simply 0.01 of 1% So if an interest rate is 5.00% and it increases to 5.01%, that corresponds to 1 basis point increase. What is it used for ? Profit margins for fixed income products are measured in basis points.

Lets look at an example in Excel:

What is IRR ?

IRR stands for Internal Rate of Return but don't let that confuse you.

It is simply the percentage return over a specific period of an investment. It is widely used for investment appraisal.

What happens is this:

In our previous tip on Future Value, we learnt that you could take an amount today, and grow it by an interest rate, and that it would be larger in the future... we called that amount in the future "Future Value", and we proved that the FV excel function was doing just that.

Finance is all about money, and working out today's value for money arriving in the future.

So we looked at interest compounding and we found that even though the quarterly compounded rate was lower, the fact that it was compounded 4 times a year meant that the annual rate ended up higher.

What is £1 worth today ?

... well, clearly it's £1 !

The very first tip is also the basis of finance

What can you do to make VBA code run faster? Well, when you run VBA code in Excel, you will notice that the screen flickerseach time an action is performed. Lets take a simple example: