This example will show you how to calculate the number of days between now and christmas.

Step1:
In a cell A1 in a new workbook, press: CTRL + ;
That will put today's date in the cell

Step2:
In cell A2 type: 25/dec
Excel will correctly guess the year

Step3:
In cell A3 type: =A2-A1

Step4:
Press CTRL + SHIFT + # to format the cell as a number

So from today, there are 6 days until Christmas

Example File

Festive greetings !

Nick

You can easily create a list of numbers by:

Step1:
In cell A1, type: 1

Step2:
In cell A2, type: 2

Step3:
Select the range A1 to A2

Step4:
Click and drag down to A10 the square in the bottom right of your selection

.. you can drag this as far as you want, and it will keep on incrementing the number

Video Training on creating numbers 1 to 10:

A neat trick that most people don't know (even some professed experts) is a quick way of pasting values.

Step1:
Type anything in cell A1

Step2:
Right click on the right hand side of selection, and drag slightly to the right

A Hidden menu will appear:

Training Video on easily Pasting Values:

Autofitting your selection is an extremely useful piece of functionality.

Lets say that you have a large piece of text in the first row, but then very small text in subsequent rows.

You can save valuable screen real estate by autofitting the selection.

Here's how much screen space you use when you autofit the column:

Now, select everything apart from the first cell... then press in sequence:
"ALT" then "O" then "C" then "A"
Now look how much less screen space you use when you autofit the selection:

Training Video on how to Autofit your Selection:

One of the most useful tips I have is on how to easily display the arguments of a function.

To demonstrate this:

Step1
In cell A1, type: =vlookup (don't press ENTER)

Step2
Press CTRL + SHIFT + A

- hey presto, the function arguments appear.

Training Video on how to display the function arguments:

So you've inherited a really large spreadsheet from some plonker who never liked, but has now left the company without leaving any documentation.

The spreadsheet is now broken and you have been tasked to fix it. There are massive functions:

Where do you start?!

Well fortunately help is at hand.

Step1:
Go: Insert=>function (click on the offending cell, and go: ALT then I then F)

Step2:
Well, there's nothing particularly new there, but here's the cunning part.
You can now drill into the function call to debug it by going into the formula bar and clicking on one of the imbedded functions, like so:

Aha.. now you can see more.

Step3:
It gets better.. You can drill down even further to the most imbed function, and work backwards.

There are more ways to debug large function calls, and I will go over these in later "Tip of the day" posts.

Nick

Training Video on Debugging a large function call:

This tip is a very quick and easy one

Step1:
Type: "Monday" in cell A1

Step2:
Select cell A1

Step3:
Click on the square in the bottom right of the selection
- Drag it down until A7

You will now have all the days of the week.

Training Video on how to create days of the week in Excel:

This is a quirky one.

You can use the excel function call CELL("filename") to retrieve something that should be the file name, but for some odd reason isn't.

Fortunately, the function call does have a couple of uses -
You can use it to retrieve the file path by using a few text functions around it like so:

=LEFT(CELL("filename"),FIND("[",CELL("filename"),1)-1)
- returns file path (if you have saved the file).

Try it and see !

Training Video on getting the file path:

Not a lot of people know this trick, but it can be a massive time saver if you have the same information on lots of sheets, and you want to change it.

It's simple when you know how !

Step1:
Select all the sheets that you want to enter a value into or change a value on. You can do this by holding down the CTRL key

Step2:
Simply type the value in the cell you want on the active sheet, and as if by magic, it appears on all the other sheets.

Nick

Training Video on how to Change Values on Multiple sheets:

Did you know that you can get access to a whole load of new functions in Excel 2003 by adding the analysis toolpak ?

Go: Tools=>Addins and select Analysis toolpak

Now you can use functions like EDATE that allow you to add/subtract a specified amount of months to a start date.

..or let Excel do it for you with the RANDBETWEEN function.

=RANDBETWEEN(1,10)

... useful for example if you're writing a card game, you can use =RANDBETWEEN(1,52) to pick a random card.

... or for a random walk analysis on a share price.

Training Video on Random Number Generation:

If you need help, the last thing you want to do is to use Microsoft's help (pressing F1)... It's really rubbish.. Sorry MS !

The best place to look (IMHO) is google groups:

Google Groups

- this is where you're most likely to find an answer to your question as it's structured around a Problem => Solution framework, and someone else is sure to have encountered the same problem as you before... and solved it !

VLOOKUP is an extremely useful function and you should know it like the back of your hand.
In a few words, it allows you to look up a value in a table, and return another value on the same row.

In my example, you've bought yourself a massive Buy To Let portfolio, and are wondering when you can retire. On typing in the new prices, you realise this might not be any time soon.

Now, you'd like to be able to type in the purchase number, and return the new value.. so here's how to do it:

VLOOKUP example sheet

If you want to know for example the 5th property you bought, you type 5 in cell C3.

Here's a financial example use case for foreign currencies.
You have done a transaction in USD, and you want to convert the value into GBP.

VLOOKUP Currencies example sheet

Nick

Training Video on VLOOKUP:

I am adding this as I looked at the search results, and noticed that people had searched for IRR, and sadly they didn't find anything useful.

Here's a noddy's guide to IRR.

IRR is a function that takes a series of cash flows and works out the effective rate of return if they were to be annually invested.

Let's take a look.
Here's an example of an IRR function call:

I think IRR is easiest explained with a bond example
In this example, there are 4 cash flows
- at time 0 (today), I pay 100 for a bond (a negative number means you pay out money... positive means receive)
- at years 1 and 2, the bond pays me 10
- at maturity, it pays back the 100, and also 10.
... The technical name for this is: a 3 year 10% coupon bond trading at par, but don't worry too much abt that.

If you insert an IRR function call and input the cash flows, it returns 10%. This is the internal rate of return on the bond cash flows.

Now, let's look into this a bit deeper.
We'll extract the cash flows, invest them at each point, and grow them at 10% each year. If we sum them at the end, they sum to 0.

SO, another way to look at IRR is that it is the rate at which these cash flows invested sum to 0 at the end.

IRR Spreadsheet

As a side note, the last thing is that you will notice that the IRR function call has 2 arguments. The 2nd argument is a guess. The reason for this is that there's no mathematical way of solving this equation, so a numerical way is used. The only difference this makes for a sensible set of cash flows is in the speed of the calculation. The closer you are with your guess, the faster the calc. A normal user is unlikely to notice the difference.
To demonstrate this, in my example sheet, on the "Explained" tab.. select cell g5. Then go: Tools => Goal Seek
You want to set g5 = 0, by changing g2
That's: "Sum of invested cash flows" = 0, by changing "Investment Rate"

... the difference is small, and due to Excel giving up before it finds the right solution.. it thinks that 6 decimal places is good enough.

Hope that helps !

Nick

IRR training Video:

Screen real estate is a valuable commodity, and with this tip, you can save using it unnecessarily.

If you autofit the columns with large headings, it will look like this:

However, to get it to this is only a few steps:

Step1:
Select the row, right click, format cells, alignment... and click "Wrap Text"

Step2:
Still with the row selected, right click, row height, and change it to something huge like 200

Step3:
For each column reduce the column size manually until you can see each word on one line.

Step4:
Then select the column and type in sequence: ALT then O then C then A

Step5:
Select the row again, and type in sequence: ALT then O then R then A

..if you don't like the look of having one word on each line, the same process works for having 2 words per line.

Training Video on Wrapping Text for large headings:

There are 2 ways to hide a sheet.

Most people know the first way:
Format => Sheet => hide

Not many know the second way:

Press ALT + F11 to bring up the VBA editor.

.. and change the visible option as shown:

To the untrained eye, this Very Hidden sheet is difficult to find.

If you don't see the "properties" box, single click on "Sheet2" and press F4

Training Video on how to hide sheets:

Today's tip is a quick one as I am feeling lazy, but it's a useful one none the less.

- By formatting dates in a certain way, you can display the day of the week, and not using much space.

Step1:
Select A1, and press CTRL + ;
(this gives you today's date)

Step2:
Right click => format cells => custom
type: ddd dd-mmm-yy

For today, you get:
Sat 27-Dec-08

- this is my favourite date format for the following reasons:
1. the "08" is all you need in this century
2. Sat = day of week
3. 27-Dec => it's completely unambiguous and much better than infuriating American date format of: 12/27/2008 (mm/dd/yyyy), as you will find out when it's the 2nd Jan (01/02/2009)... or is it the 1st of Feb ?!
[End of rant]

; - P

Video training on Date formatting:

I come from a financial background, and the most important date in finance is Today's date. It can mean many things, but the most significant is that it is the date at which the discount factor = 1, but I'll save that discussion for another day.

If you're writing a bond or derivative pricing function, you will need to enter today's date. The Excel function for that is: =TODAY()

- However, this function comes with a toxic hazard warning as it's the most common cause of sluggish spreadsheets in investment banks.

- The problem is that it's a volatile function, and will calculate whenever any cell changes on the sheet, AND for common usage like inserting a row or column.

- SO, the advice is: If you have a spreadsheet, that does a lot of calculations, it requires today's date, and calculation speed is a problem for you... DO NOT USE =TODAY()

Training Video on sluggish spreadsheets caused by the TODAY function:

There isn't a single function to return the sheet name, but you can use a combination of functions to get at it.

=RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"),1))

- Here's a breakdown of what it's doing:
Sheet Name example

Training Video on getting Worksheet Name:

VLOOKUP is all very well when the data is arranged in the right way for the function - with the thing you're looking for to the left of the thing you want to return, but what happens if you want to do the reverse of VLOOKUP ?

Well, fortunately help is at hand with INDEX + MATCH.

MATCH returns the position of a value in a range
INDEX returns the value of a cell that is offset from another.

Combined, you can use INDEX + MATCH to simulate VLOOKUP, and to do the reverse lookup as well as the VLOOKUP:

Index Match Spreadsheet

Training Video on VLOOKUP INDEX MATCH

There are lots of shortcuts in Excel, but In my 13 years of using Excel every day, these are the ones that I think are most useful and a good Excel user should know all of these:

Video Training on Useful Excel Shortcuts:

So, you have data in column 1, and you want to create a formula in column 2, and copy it down.
Normally, this is a very easy task:
- enter the formlula, select the small square in the bottom right corner of your cell, and double click on it to automatically populate your 2nd column with the formula.

However, what happens when the data looks like this?

The autofill will stop on line 3.

One way to complete the task is to drag down manually, but there is a much better way.

Step1: (for this example)
Select cell b2, and copy it

Step2:
Press CTRL + END
This will take you to the bottom right

Step3:
Press CTRL + LEFT ARROW
This will take you to the bottom left

Step4:
Press CTRL + UP ARROW
This will take you to the bottom left of populated data

Step5:
Press right arrow once, then CTRL + SHIFT UP ARROW
This will select the correct area to paste into

Step6:
ENTER

Once you get quick at shortcuts, you can do this entire action in a few seconds.

Download sheet to practise on

Video Training on finding the last cell:

When using Excel for your daily work, you will often need to insert rows and columns.

Remember, when you insert rows and columns, Excel changes the references so that your formulae do not appear to change materially, however, there are some things to watch out for.

Example with 2 columns, and an addition formula:
=B3+A3

When you insert a column in between A and B, the formula adjusts like so:

Note: nothing is materially affected - you are still adding 10 + 2

Now, suppose unstead that you use the SUM formula:

... When you insert a column, the formula adjusts as you'd expect, but if you look carefully there is a difference. Now, cell B3 is included in the summation:

SO... if you want the addition of new columns to be included in your summation formula, use the SUM function... If you don't, you have to use simple addition.

Practise sheet

.. same works for rows.

Video training on Inserting Rows and Columns:

Why is there no easy way to paste values in Excel ?.. huh, huh.

If I got a pound for every time I had to paste values in Excel, it wld be worth at least 7 EUROs, and I'd be a Zillionaire in Zimbabwean dollars.

The best way of performing this operation known to man is the following:

Step1:
Select range to copy, and press CTRL + C

Step2:
Select the place you want want to paste to, and press the following keys in order:
ALT then e then s then v then ENTER

..there are other ways using the mouse but ExcelExperts prefer to use the keyboard for everything. (Take note Mr Ribbon Designer)

Another way

Pasting values - Video Training

This tip is one of my favourites, and I have used it a lot for trading systems.

Suppose there's something sooo fundamental on your sheet that if you get it wrong, the consequences can be very costly indeed. Well, as a system designer, you should try your hardest to protect users from making thes mistakes.

Conditional formatting helps enormously here.

In my trading system, I have formatted the headings so that if you change the BUY to a SELL, the headings change colour from blue to red automatically.

BUY:

SELL:

The way to do this is the following:

Select the cells you want to format like this:

In Excel 2007, from the Home ribbon, select conditional formatting => new rule, then set the formula as below:

...choose the format you want in the event that the formula is true, and there you go !

Similar in Excel 2003..
Format => conditional formatting... then same principles apply.

Video Training on conditional formatting:

Data Validation is extremely useful functionality and I use it in every system I write.

The primary aim is to guide people into entering correct data. If you allow people to type whatever they like into a field, you end up with all kind of gunk.

There are a few ways to use Data Validation... we'll discuss the way based on other cells.

Suppose you want to restrict the number entered in the following example to even numbers between 2 and 10

Step1
Select the cell, In Excel 2003, go: Data=> Validation... In Excel 2007, Data => Data Validation and configure like so:

Step2
Click on the cell next to “Number” and press ALT + DOWN ARROW... You will now see the list appear and you can use the UP and DOWN arrows to select the one you want.

Sheet to practise with

Video Training on Data Validation:

This tip shows you how to find and replace across the entire workbook.
You might spend ages looking for things when a simple trick can save lots of time.
Suppose you have a Workbook that looks like this:

Sheet1

Sheet2

You have the name “Mike” on 2 worksheets.
Now suppose you want to replace “Mike” with “Nick” in the entire workbook.

Step1:
Press CTRL + H... then Options... set it up like this:

Step2:
Click “Replace All”

Result:

If you only want to replace on certain worksheets, you can select them first by clicking on “Names1”, holding CTRL, then select “Names2” tab etc... and go to Step1

Training Video on Find and Replace:

Here’s a tip on printing and how to change the print area.

Suppose you have this data, and you want to print up to Data6

Step1:
Select the data you want to print:

Then go:
Xl 2003 File => Print Area => Set Print Area
Xl 2007 Page Layout => Print Area => Set Print Area
You’ll notice the dotted lines around the area you want to print. They indicate the end of the page.

Step2:
Now suppose you want to change the print area to include Data7
Go View => Page Break Preview

This shows Excel in page break view, and from here, you can change the print area by clicking on the edge of the highlighted area, and dragging it.

The print area is actually just a range name, so if you bring up the range name dialog box, you can change it from there too.

Excel annoyingly doesn't allow you to get rid of the small dotted lines once you have set the print area, but there are 2 ways round this:
1. Select a cell a long way away from your work area, and set that as the print area
2. Close and reopen the Excel

Training Video - Change the print area

Suppose you have the following data, and you want to calculate the percentage of people who are Male, and the percentage of Females

Use the COUNTIF function to count the number of each in the range, then divide by the total to get the percentages.

Practise sheet

Training Video on calculating Proportions:

Here's something that has improved massively in Excel 2007:
- the handling of Range names

If you go: ALT then i then n then d (in sequence) it brings up the range name defenition panel:

In Excel 2003, in order to delete range names, you need to select each one individually and press delete. The can be a real pain if you have loads to delete.

Excel 2007 improves on this by allowing you to sort the range names, select multiple, and delete them.

Well done Excel Dev.

; - >

Training Video: View, Edit and Delete range names

This is a MASSIVE time saver for a developer, and avoids typos.

Suppose you have the following data on your sheet, and you want to add range names to the input fields. i.e. you want the cell you enter 100,000 into to be named "House_Price"

Step1:
Select the range (B2:C20 in this case)
and go: ALT then i then n then c in sequence
That brings up this:

- by selecting "Left Column", it means that range names will be created using the data in the left column.
- Click Ok

Step2:
Now look at the range names that have been created by clicking on the dropdown shown:

NOTE: Excel does not allow spaces or other special characters in range names, so spaces are replaced by "_" and % is ignored. Would be nice to get better treatment of these characters in future versions.

Training Video - Create Range Names

This is a follow-on from Tip 31

So, you've entered all your range names and now you would like to go to one of them and you can't remember where it is.

Step1:
Press CTRL + g
This brings up the Go To menu:

- note all the range names are listed and you can select any of them to go to that range.
- you can either click on a name or type in a range reference like B2:C30

Step2:
There is also another very useful menu available if you choose "Special"

Video Training on how to use GO TO:

This example shows you how to calculate moving averages.
They can be useful when you have a share price for example that changes every day, and you want to calculate the average of the last X days.

Here's the data we'll work with:

In cell F3, we enter the number of days we want to calculate the average for.

Cell F4 contains the formula:
=AVERAGE(OFFSET(C4,COUNT(C:C)-F3,0,F3))

Let's break it down to see what it's doing.

First of all, the OFFSET function returns a range.
- How do we make sure this range is the right one ?
- We want this range to be the last 3 populated cells in the table.

OFFSET takes the following arguments:
reference,rows,cols,height,width

So, we tell the OFFSET function to create a new range with the starting cell being 10 cells below C4 (the first share price), and continuing for 3 cells down.

How does it know to start 10 cells down ?
- we enter COUNT(C:C)-F3 as the reference
COUNT(C:C) returns the amount of populated cells in the column C.. in this case 13. Subtract 3 cos we want the last 3.

... then we wrap this with the AVERAGE function.

Download Example Sheet to play with

Training Video on Moving Averages:

Pivot tables are a truly amazing invention. They allow you to summarise, slice and dice data in many different ways without the need to write fiddly formulae.

Let's take the simplest of examples:

In this data, you will see that you have several entries for the various items that you spend your hard earned money on.

Lets say you want a summary of that.

Step1:
Select the data (that's Range B2 to C9) and go:
Insert => Pivot Table (XL 2007), then click OK
You'll now get a new worksheet:

Step2:
On the right, click on both "Expense" and "Cost", and there you have it, a summary of the data !

Training Video - Introduction to Pivot tables

There is a way to open Excel safely which prevents macros from running. - Excel sheet calculations will still run.

Here's how to do it:

Step1: If downloading a file from the internet, click on a link to download the file - save it... if not, skip to step 2.

Step2: Hold down the CTRL key, then open Excel. You will see the following messagebox:

Click YES You'll know if you have done it right as Excel will have the words: "Safe Mode" at the top.

Now open the file and use it safely. Training Video on How to Open Excel Safely:

Today's tip is about saving you precious time.

Suppose you have the following data, and you're trying to fill all cells with the correct formula:

In this example we have numbers 1 to 13, and we want the other 2 columns to be populated with the squares and cubes of the numbers. We have entered formulae in the first row, and want a quick way to populate the rest of the formulae without typing them in one by one.

Step1:
With the setup as above, double click on the square at the bottom right of the cells selected
HERE:

This will populate all relevant cells with the formulae!

It's as easy as that.

Download Practise sheet

Video Training - Copy Down

For XL 2003, there are many ways to find and remove duplicates from a sorted column of data, but this is my favourite as it doesn't involve leaving formulae on the sheet.

With a bit of practice you can become very quick at it.

Suppose you have the following data:

and you want to end up with the duplicates removed:

Step1:
Enter the following formula in the cell adjacent to the 1st data cell:

This is basically asking if the cell is the same as the one below. If it is, then it's a duplicate.

Step2:
Copy the formula down

Step3:
Select the 1st heading (B2), and press in sequence:
ALT then d then f then f.. this turns on the data filter.
Now filter for TRUE (click on the arrow at cell C2, and select TRUE)- this selects only the duplicates.

Step4:
Select the entire row for data that has been filtered (excluding the heading).. and right click=> Delete.. to delete.

Step5:
Turn off the data filter by using the shortcut: ALT then d then f then f, and clear the formulae that you added... There you have it:

Video Training on how to Remove Duplicates:

This is a follow-on from yesterday's tip and relates to XL 2003

Suppose you have the following data and you want to remove the dupes (i.e. where both columns are the same):

Add a another column containing the concatenation of the previous 2 columns like so:

Copy it down, and proceed as with yesterday's tip