One of the courses/workshops I offer, Spreadsheet Models for Managers, includes
two add-ins that — if you use Microsoft ® Excel — will transform your life. The course is
available as a download, or on line or on CD, but if you just want the add-ins…
veryday users of Microsoft Excel face a tedious, complicated, and annoyingly detailed working environment.
Life can be better if you take my course, Spreadsheet Models for Managers,
either on line, in person, or by downloading the hyperbook. But I know that some of you might not be able to do that
right now, and I want to help anyway. Your life can be very different if you'll just install these two add-ins.
There are two add-ins — one has commands, and one has worksheet functions.
Commands Add-in
Skip to the Details: How To OrderOne add-in installs a menu with five commands. Just five, but
they're very powerful, if your use of Excel is even a little bit higher than the elementary.
Define Up, Down, Left and Right
This command defines the names Up, Dn, Lt and Rt to be references relative to
the cell in which they're used — one cell up, down, left and right, respectively. It also defines the name Me to be the
cell in which it is used. Each name is local
to the worksheet on which it's defined, and they're defined on all worksheets in the active workbook.
Using these names instead of explicit cell references makes formulas far more readable
and much easier to understand.
Define Local Name…
This command defines a local name, which you provide, to be the selection on the active
worksheet. You can choose between defining that name on all worksheets, on the selected worksheets, or on
the active worksheet.
Local names are especially useful when you want to use identical names on several worksheets.
This might happen, for example, if you want one sheet per sales region, and you want to name a range TotalRevenue on
each worksheet.
A local name also enables you to enter formulas on several sheets at once, or to copy from one worksheet to another.
Resize Current Array
This command examines the active cell to determine whether
or not it contains an array formula, and then applies that formula as an array formula to the selected range.
The purpose of this command is to make it easy to change
the shapes of ranges that contain array formulas that use names.
Tile Top Two Windows
This command arranges the top two Excel windows to cover the screen. One is on the top half, the other on the
bottom. This is most convenient when you need to look at two windows at the same time, possibly to "A/B" them.
If there are fewer than two windows, nothing happens.
Apply Names to Chart Objects
This command examines all the chart objects on all selected sheets, and modifies the SERIES formulas in each
chart so that they use names when possible.
Worksheet Functions Add-in
The second add-in provides two very useful worksheet functions:
MMMult
This function performs matrix multiplication. It's just like Excel's MMULT, except that it can operate on
up to ten arguments, which makes formulas that contain matrix products much easier to read and maintain,
because you can avoid the nested function calls that would be necessary with MMULT. MMULT can operate on only two
matrices.
Convolve
This function takes two arguments, each of which is a rectangular range. It produces the convolution product of
the two ranges. The convolution product is very useful in modeling time behavior. For instance, modeling the effects
of hiring employees, and taking into account a learning curve or training period, can be very mwessy unless you
use convolution. Another example: leasing a stream of equipment, such as desktop computers, under uniform lease
terms at varying times.
Projects never go quite as planned. We expect that, but we don't expect disaster. How can we get better at spotting disaster when there's still time to prevent it? How to Spot a Troubled Project Before the Trouble Starts is filled with tips for executives, senior managers, managers of project managers, and sponsors of projects in project-oriented organizations. Check it out!
The key to managing virtual or global teams is creating a sense of team despite the obstacles of separation. Read my tips booklet, 303 Tips for Virtual and Global Teams, to learn how to make your virtual global team sing. Newly revised and updated for 2008! Check it out!
Are you doing work you love? Are you less in love with the job? Bad boss, long commute, troubling ethical questions, hateful colleague? Read Go For It! Sometimes It's Easier If You Run to learn what we can do when we love the work but not the job. It helps you get moving again!
A Tip a Day arrives by email each business day. It's 20 to 30 words at most, and gives you a new perspective on the hassles and rewards of work life. Most tips also contain links to related articles. Free!
Point Lookout is my free, weekly email newsletter for people who work in problem-solving organizations. The Friends — both organizations and individuals — support this publication financially through voluntary contributions. To help make our workplaces more fully human, join the Friends today!
Exchange your "personal trade secrets" — the tips, tricks and techniques that make you an ace — with other aces, anonymously. Visit the Library of Personal Trade Secrets.