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:

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

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:

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: