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:

How do you time how long it takes for VBA code to run ?

Simple..

Step1: At the start of your code, create a variable called "Start" (or whatever)

Set it = Timer (that takes a snapshot of the time)

Step2: At the End of your code, you can use a messagebox to display how long it took.

MsgBox Timer - Start & " Seconds"

Don't use VLOOKUP, INDEX, or MATCH from VBA when finding things on a worksheet with VBA.

• The problem with these is that when the function doesn't find something, it will return a VBA error which you then have to trap and handle successfully.
• This is a very messy way of coding, and prone to errors.

The best way to look for things on a sheet is to use .Find

An annoying feature of Excel is not resetting the the used range. What is the used range ? - well, it's meant to be a square around all populated cells.

- How can you select the Used Range for this example ?

Loops are fundamental to programming, and in this tip, we'll look at FOR loops.

If you have never programmed before then this step by step guide will show you how to write your first program.

Q. What is a loop ?

A. I think this is best explained using an example.

Using VBA, there is the functionality for Excel to track most of what a user is doing to a workbook.

Armed with this information, a system designer can make things happen that help make the user's life easier.

Today, we're going to program a simple example of tracking selection change events. i.e. when you select something different from what is currently selected, an event is triggered.

How do you reverse a name in Excel ?

This tip is very useful if you have a list of names in "Surname FirstName" format and you want to reverse that quickly.

Well, in Excel, there are a set of useful string manipulation functions you can use for this: LEN, FIND, MID, LEFT, RIGHT

How do you find out whether a cell contains a certain string ?

- In VBA, it's easy.. use the INSTR function, and test whether this is greater than 0.

In Excel, you need to use both the LEN and the SUBSTITUTE functions.

=LEN(SUBSTITUTE(StringCell,StringToLookForCell,"",1))<>LEN(StringToLookForCell)

Here’s an Excel tip on the LEN function.

Generally speaking, LEN returns the amount of characters that a cell displays.

However, lets look at a few examples and exceptions:

1. Normal text:

This is one of the most useful Excel tips that you can use in emails or presentations– How to Capture a Screen Shot of your Excel.

There is a quick and easy way of capturing an Excel screen shot without buying commercial software.

Step1: Launch PAINT by going: Start => All Programs => Accessories => Paint

This is an Excel tip for those of you who don't like using the mouse, (like me) and prefer to use only the keyboard. This can be a big time saver if you use Excel every day.

Step1: Press the WINDOWS key on your keyboard. This is the key that has the same symbol as your START button:

This tip is a followon from the previous Excel tip on Calculation

Today, we're going to look at different ways to calculate

There are a few different ways to calculate something in Excel, and you can use each differently.

This tip shows you how to do a random sort of a list in Excel.

Suppose you have a list of people and you need a way to find out who goes first. Maybe you're the HR department of an investment bank and you need to decide quickly who to make redundant.

Here's a screen shot:

Calculation in Excel is a big topic, but I am going to try to give you the essentials about calculating formulae.

Here are a couple of useful Time-related tips for Excel.

The first one is the NOW Excel function
Lets see how it works..

Step1:
Enter the following formula in any cell:
=NOW()
For me right now, this returns: 03/02/2009 09:22

NOTE - if the column width is too small, you will see something like this:

Today's tip is a short one, but one of the most used in finance - Toggling the formats from date to number and back again.

The default date format in Excel is very rubbish, so whenever I enter a date, I always reformat it immediately into something sensible.

Lets show you how..

Step1:
Enter a date in a single cell.
For example - enter doday's date by using the Excel date shortcut for TODAY: CTRL + ;

Adding 2 strings together is very easy in Excel.

There are a couple of ways.

Method1: use: &" "& NOTE - this adds a space between the 2 strings Here's an example:

Method2: use CONCATENATE

This is a follow-on from tip on how to calculate the 1st day of the month in Excel

Suppose we want to calculate the last day of the month... can we use the same function ?

YES, but we need to be a bit more cunning this time.

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1

This tip explains how to Calculate the 1st Day of the month in Excel.

We know how to get today's date... use: =TODAY()

For me right now, this returns 30-Jan-2009 ... so is there a way to get the 1st Day of the month from this ?

YES - use the DATE function.