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.

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.

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)

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:

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.

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:

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

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.

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.

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:

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

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?

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:

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.

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:

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()

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

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.

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:

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:

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.

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 !

..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:

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.

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:

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.

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:

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)

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:

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: