BOOT CAMP ARCHIVE 2003

  

 

BOOT CAMP 269 (01/04/03)

 

EXCEL HINTS AND TIPS

 

Originally this short series of hints and tips was only going to cover word processing, Internet browsing and email but judging by the number of queries and responses we receive relating to spreadsheets we thought we’d put together a few bonus tips for Excel fans. Incidentally, they are mostly intended for Excel 2000 and 2002 but many of them should also work with Excel 97.

 

If you haven’t already got a ‘wheel’ mouse get one, it’s the Excel user’s friend. Apart from making it a lot easier to move around a worksheet it can do other things too, like quickly changing the magnification. Simply highlight a cell hold down the Ctrl key and roll the wheel, it’s especially useful for viewing very large worksheets. It also comes in handy for copying and pasting. Highlight the block or cells you want to copy then depress the wheel then scroll to the place where you want it to go, you’ll find a wheel mouse will give you much greater control over speed than a conventional type.

 

When something goes wrong it’s useful to be able to quickly identify cells that contain errors. There are a couple of ways to flag up problems, in colour if you prefer, making them easier to identify. Method one: highlight a cell then press Ctrl + A to select the whole sheet then on the Format menu click Conditional Formatting. On the Condition 1 drop-down menu select ‘Formula Is’, in the formula field enter ‘=IsError(A1)’ (without the quotes), then click Format. Choose a colour or style and click OK. Method two: select Go To on the Edit menu (or press F5) and click the Special button then check ‘Formulas’ and ‘Errors’ and press OK.

 

Worksheets can be extremely dull so here’s a way to brighten them up or make them stand out in presentations. Excel is seamlessly integrated with the Office graphics utility WordArt. To use it position the cursor on the worksheet and got to Insert > Picture > WordArt. Select a style or layout and click OK then enter the text you want to appear and click OK again. You can position and resize the graphic (hold down the Ctrl key to maintain proportion). WordArt can also be used to create vertical text, just use the Free Rotate icon on the WordArt toolbar, or click the ‘abb’ icon, which flips the graphic through 90 degrees.

 

You can also change the orientation of text within one or more cells, highlight the cell(s) then right-click and select Format Cells. The Orientation control lets you twist the text through 90 degrees vertically or horizontally, or by clicking the word ‘Text’ the characters will be arranged vertically.

 

Another simple way to draw attention to something important on a worksheet is to put a circle around a cell or group of cells. To do that call up the Drawing toolbar (right-click on an empty area of the Toolbar), select the circle or oval drawing tool (remember to hold down Ctrl if you want a perfect circle) and draw your circle. It will be solid and obscure the cells below so right-click the circle select Format Auto Shape and under Fill select No Fill. You can also change the line colour, thickness and style from this menu.

 

As you know you can enter fractions directly into cells but Excel has a problem discriminating between fractions and dates, so if you enter 1/2, say, it will almost certainly convert it into 01-Feb. It only happens when there’s no whole number, so to stop it happening put a leading zero in front of the fraction, i.e. 0 1/2. Don’t forget that the decimal value of a fraction will be displayed on the Formula bar when the cell has been selected. Excel can also convert factions in columns of cells into decimal values. Select the cells then go to Format > Cells, select the Number tab then Number in the Category window and click OK.

 

It can be difficult to keep tabs on what’s going on in large worksheets so here’s a quick and simple shortcut to display all of the formulas it contains. Hold down Ctrl and then press the key immediately to the right of the 1 on the main keyboard (the one with the ‘¬’ symbol; to toggle back to the normal view press Ctrl + ¬ again.

 

We’ll round off with the ‘Easter Eggs’ hidden away in Word 2000. There’s also an Easter Egg in Excel 97 (see Tip of the Week) but apparently some spoilsports complained about wasted resources so there aren’t any in Excel 2002.

 

There are two Easter Eggs in Excel 2000. The first is a list of everyone who worked on the program, it’s not very interesting but it’s good practice for accessing the second one. Start by opening a new workbook then press F5 (Go To), enter X2000:L2000, click OK and row 2000 will be displayed, press Tab once to shift to column M then hold down the Ctrl and Shift keys and click the Chart Wizard icon (if it doesn’t work try right-clicking the icon). All being well you’ll see a list of developers.

 

The second Easter Egg is much more interesting, it’s a secret racing car game but it only works if you’ve installed the whole Excel package, including Web Components and have DirectX installed on your PC. Begin with a new workbook then go to the File menu select SaveAs and then select Web Page in the Save As Type drop down menu. A new dialogue window opens, click the Publish button then under Viewing Options check the ‘Add Interactivity’ box.  Give it a name under File Name, click Publish and it will be saved on your hard drive as an ‘*.htm’ file. Launch Internet Explorer and open your newly created file. Work your way down and along to cell WC2000, which should be positioned in the bottom left hand corner of the worksheet and highlight the whole row. To start the game press Shift + Ctrl + Alt and click the Office logo in the top left corner of the worksheet. Use the cursor keys to steer, press the O key for an oil slick, H to turn on your headlights and the Spacebar to shoot at other cars. To exit the game press Esc.

 

Next week – Safer Surfing

 

JARGON FILTER

 

DIRECTX

A software component in Windows (included as standard in Win 9x and XP) used to improve the graphics and sound performance of programs, especially games

 

EASTER EGG

Unofficial, undocumented and usually frivolous hidden feature put into a program by the developers

 

WHEEL MOUSE

Type of mouse with a rotary control wheel for faster and more accurate page scrolling

 

 

TIP OF THE WEEK

Hidden away inside Excel 97 there’s rather nifty flight simulator game. To find it open a new worksheet, press F5 (Go To), type in X97:L97. Press Enter then Tab, hold down Ctrl + Shift and click the Chart Wizard icon. Use the mouse to control your craft, the object of the game is to find the pyramid, fly around it and you’ll see a cast list of the developers.

Search PCTopTips 


Web

PCTopTips

Boot Camp Index

2010

2009

2008

2007

2006

2005

2004

2003

2002

2001

2000

1999

1998

 

Top Tips Index

Windows XP

Windows Vista

Internet & Email

Microsoft Word

Folders & Files

Desktop Mouse & Keyboard

Crash Bang Wallop!

Privacy & Security

Imaging Scanning & Printing

Power, Safety & Comfort

Tools & Utilities

Sound Advice

Display & screen

Fun & Games

Windows 95/98/SE/ME

 

 

 

 

 

 Copyright 2006-2009 PCTOPTIPS UK.

All information on this web site is provided as-is without warranty of any kind. Neither PCTOPTIPS nor its employees nor contributors are responsible for any loss, injury, or damage, direct or consequential, resulting from your choosing to use any of the information contained herein.