This is a follow-on from tip on entering an array formula in Excel

Once you have entered and array formula in Excel, you will notice that Excel won't let you modify it one cell at a time.

This Excel tip explains step by step how to enter an array formula. Why do I need array formulae ? I hear you ask. Well, one of the reasons is that you need them for functions that do not return their results in a single cell. Lets take MINVERSE for example. The following screen shot shows the data we're using:

This is a follow-on from the first tip on debugging a large Excel function call

This Excel tip is really one of the most useful ones I can think of. I use it all the time, so make sure you understand it

Suppose you have a large function call, and you want to understand what is going on.

Easy tip today... it's about how to delete sheets.

To delete a worksheet:

If you have selected the worksheet, press in sequence: ALT the e then l

This is a follow-on from tip 1 on Data Validation

When adding data validation to cells containing percentages, there is a little trick you need to know. If you're calculating a proportion: 100% being everything, 0% being nothing, you need to set the data validation to DECIMAL with range 0 to 1.

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

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:

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:

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

Suppose you have a big string like: "XXX0000sdsds000000000" and you want to know how many times "0" appears.

Here's our data:

The formula we use is:
=LEN(B3)-LEN(SUBSTITUTE(B3,"0",""))

Suppose you have a list of Names, and you only want the initials of the people.

There's an easy way to do this in Excel

Here's our data:

The formula we use is:
=LEFT(B3)&MID(B3,FIND(" ",B3)+1,1)

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:

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.