Nick's Excel Tip Of The Day - Part 1

Nick's picture

Special - how do you use it?

ctrl+g to 'goto' a place in the document is a very useful tip.

'Special and then say 'comments' seems to take me to the first cell with comments. How do I go to the next?

How else do you find 'Special' useful?

BTW, embedded screen shots make the tips very easy to follow.

Nick's picture

GO TO Special

you can tab through the cells selected...
Shift Tab from the first one to go to the last.

Special can be useful in lots of ways when you know that tab takes you to the next one..

Tab - doh!

Thanks. I obviously thought I tried 'everything' :)

Nick's picture

31. Create Range names quickly

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"
Create Range names

Step1:
Select the range (B2:C20 in this case)
and go: ALT then i then n then c in sequence
That brings up this:
Create Range names
- 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:
Create Range names

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

Nick's picture

30. View and Edit range names - well done XL 2007

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:
Delete range names

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

Talk about old habits dying hard...

Despite seeing this user friendly list, I was deleting them one at a time. Thx for the tip!

"ALT then i then n then d (in sequence)" - Nick, get real :)

Nick's picture

29. Calculating proportions with COUNTIF

Suppose you have the following data, and you want to calculate the percentage of people who are Male, and the percentage of Females

COUNTIF

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:

Nick's picture

28. Easily change the print area

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
change the print area

Step1:
Select the data you want to print:
change the print area

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.
change the print area

Step2:
Now suppose you want to change the print area to include Data7
Go View => Page Break Preview
change the print area
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.
change the print area

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

Nick's picture

27. Find and Replace across the whole workbook.

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
Excel-Tips-Find-and-Replace-across-the-whole-workbook

Sheet2
Excel-Tips-Find-and-Replace-across-the-whole-workbook

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:
Excel-Tips-Find-and-Replace-across-the-whole-workbook

Step2:
Click “Replace All”

Result:
Excel-Tips-Find-and-Replace-across-the-whole-workbook
Excel-Tips-Find-and-Replace-across-the-whole-workbook

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:

Nick's picture

26. Data Validation - part 1

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

Step1
Select the cell, In Excel 2003, go: Data=> Validation... In Excel 2007, Data => Data Validation and configure like so:
Data Validation

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.
Data Validation

Sheet to practise with

Video Training on Data Validation: