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

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

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

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:

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: