|
|
ABC ~ All
'Bout Computers
The Online Web-azine for Computer
Enthusiasts
-- brought to you by
contents page for this issue
A Functional Excel Setup
~~Ray Blake,
GR Business Process Solutions
Over the years, I must have set up Excel hundreds
of times on different computers, and I always find myself doing the same
things. It was only when Linda suggested I should write them down and
share them that I realised quite how many steps there were in my Excel
setup. Here it is then, my process for setting up Excel the way I like
it.
A complete install of the Excel software. I
say complete because these days the installer offers to install a
‘typical’ version in preference. The days of limited space on hard
drives are now gone, however, and the convenience of having
everything on the hard drive when I need it justifies what is still
a reasonable hard drive footprint.
Setting the default fonts. A serif font like
Times New Roman is a bad choice here. Although studies have
repeatedly shown that serif fonts are easier read on paper (the
little serifs – or lines – lead the eye along the text) Excel is a
screen-based tool primarily. Unlike Word files, which are created
mostly just to be printed, Excel files perform most of their useful
lives on screen, so I prefer sans serif fonts, like Tahoma or Arial,
which look much better on a monitor.
Loading the Analysis Toolpak. For some reason
best known to themselves, Microsoft have kept a large number of
incredibly useful statistical and financial functions locked up in a
separate place so that they are not available to Excel by default.
So, I go to Tools-Add-ins and make sure there’s a tick showing next
to ‘Analysis Toolpak’.
Loading the Conditional Sum Wizard. Whilst in
the Add-ins dialog, I tick the Conditional Sum Wizard as well, which
gives a quick and easy way to create array formulae without tearing
your hair out. Although not as powerful as writing these formulae by
hand, this wizard can generate something close to what you want
ready for you to then amend. It’s easier than starting from scratch.
Sorting out the Toolbars. On install, Excel
usually forces the Standard and Formatting Toolbars to share a row
at the top of the screen. Since this forces a number of useful tools
to be concealed, I think this is silly, and I drag the Formatting
bar down to the line below. I use VBA a lot, so I show the Visual
Basic Toolbar, which I dock on the top row to the right of the
Standard bar.
Getting quick access to formatting. There are
certain formatting operations – like word wrapping in a cell, for
instance, or vertical centering, that require far too many mouse
clicks in Excel. In the old days, I used to just record macros and
make toolbar icons to remedy matters, but recently I included my
collection of most-used shortcuts in an Excel Addin which is
available free to readers from my website here:
http://www.grbps.com/adddin.htm
Putting my reference library on the hard
drive. Over the years, I’ve learned and used all sorts of techniques
to get Excel to sing and dance. Every time I get something new
working, I save a sheet which just shows that technique in action.
So whenever I set up a new PC, I add my ‘Excel reference’ folder to
‘My documents’. It contains files with descriptive names, like ‘Auto
updating pivot table.xls’, ‘VBA array sorting’ and ‘DFunctions
demo’.
The nature of this article is, of course, that it
represents a personal selection. If I’ve missed a favourite step you
always take, please do share.
Ray Blake lives in England and spent 15 years training people in the
financial services industry there. He had always used PCs in his work, and
gradually realized he might make a career out of them. He and his business
partner set up GR Business Process Solutions (www.grbps.com)
which specializes in innovative IT to support knowledge testing and skills
assessment. Although he spends a lot of time these days developing in VB and
Access, Excel remains his favorite development tool, because, as he says,
'It can do everything; there's no computer application you can think of that
you couldn't develop in Excel.'
|
|
,
