 Ever meet that really annoying person who says—usually
loudly—“Oh
I’ve been using Product X for so long, there’s nothing you can tell me about
it I don’t already know. I’ve used it for all sorts of jobs and I can make
it do anything I want!”? Well run this past those who say this about Excel
and see how many of these tips and tricks they actually know.
I’ve been using Excel for almost 6 years. When I was employed at a private school running the admissions department, we had a
UNIX database that also operated the accounting system, but my part
of it handled 15,000+ family entries, with multiple children, multiple
years of (potential) entry to the school, multiple feeder schools,
multiple dates of visit to the school, etc. I used Query to import the
data into Excel and then run mail merges or whatever and have loved
working with Excel ever since.
In the last 2-3 years I developed large-ish
(60+ worksheets) workbooks with multiple cross referencing, conditional
formatting, date-sensitive updating, for the management
of work commitments under licence. A mini Microsoft Project type application I guess.
So I thought that I was skilled enough to skip
the offer of a free, one-day Microsoft Certified Partner training
course on Excel Level 1 and go straight for Level 2. Until, that is, our
tutor decided that as only one of my group of 7 had been through
Level 1 he would *offer us some tips* that we might have missed along
the way. And
were we glad he did—each and every one of us found out something
that we had been doing much less effectively under our own self-taught
methods! Here
are just a few for you to ponder:
· If
you hold down Ctrl then hit the key with ¬ ` and ¦ you double
all your cell widths and simultaneously display all your formulae
in a worksheet. (By the way...in the absence of a better name I'm
calling it redundantkey because, up until now, it's been about as
useful as the AltGr to me.)
· Cell
column width too narrow? Hover your mouse over the cell header row (i.e.,
the gray A, B, C , etc., row) and double click the bar between
the cells. Your column will now autoformat to fit the width needed to take
all the text it contains.
· To
repeat a formula down a range of cells, double click the bottom right corner
of the first cell in the range and the formula will be automatically entered
for the rest of the column.
· Using
a formula based on a non-variable cell, even though the formula is repeated
at various locations in the worksheet? Then lock the value for
that non-variable cell by using the dollar symbol. On the formula bar click
the Equals Symbol,
then click the first cell reference then press F4 to
insert the dollar symbol automatically, then click the second cell reference
(or complete the rest of the formula entry). Oh, how I wish I had known this
when I was building my 60-page workbook!
· Obvious
though it seems, if you fill a series across a page
it is the cell numbers that increase, whereas if you fill down the page it
is the cell letters that grow.
· Charts—I
never did work out how to do these before (not being a natural number
crunchier). Play
around with them—try selecting a range of values and then click F11 for
an instant chart that you can reformat to your heart’s content!
· And
if all this makes you feel that you can’t wait till Christmas for that must
have guide
to Excel, then you can count down the days between now and then as follows:
- In Cell A1 type 25/12/03, in Cell B1 type =TODAY()
- Format Cells A1 and B1 as Date cells (if your version
of Excel hasn’t already done this for you)
- Format Cell C1 as General then type in the formula =A1-B1
- Presto! You now know how many days between now and Santa’s
arrival!
These are just a very few, fun examples of how to make Excel
sing and dance for you. The list could no doubt be expanded
by you. So why not send in your suggestions for EXCELlent Tips? You can
submit them by clicking the TechTrax Feedback link above. Be sure
to use EXCELlent Tips as the subject
and I'll publish your submissions in a future article.
 Anne has been working with computers since 1990 when she first encountered a Toshiba laptop with an orange on black display, working WordPerfect in DOS. Microsoft Works was a huge innovation in 1991, introducing the concept of a Windows emulation mode to a DOS environment. Since then she has developed skills across a wide range of application types, including desktop publishing, web design, music transcription, presentations, databases, spreadsheets and, of course, word processing. It is a matter of great pride to her that she can still type at more than 85 words per minute even if the fingers fly too fast sometimes to keep the accuracy at 95%!
Having spent many years working as a PA for senior British politicians, the
National Health Service, a private school in Scotland and latterly in a public
company in the English Midlands, she has now undertaken a midlife career change
and has trained to become a mortgage consultant, working for Mortgage
Expert, where the money is a great deal better.
Anne and her partner John live near Nottingham, England with their four teenage
children and two hunter-gatherer cats. She enjoys singing a wide range of music
and losing herself for hours on the PC—when the children can be persuaded
to let her get time on it!
Anne can be reached at: anne.robson@ukonline.co.uk.
|
