Add-Ins for
Spreadsheet Modeling

---

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.