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

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:

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

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:

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: