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