Pane Relief: Excel Utilities

If you use Excel for more than just simple P&L spreadsheets, you've probably done some experimenting with its built-in functionality. But what if Excel doesn't do everything you need? Rick Cook discusses the pluses and minuses of using third-party resources to power up your spreadsheets.

Like this article? We recommend

Like this article? We recommend

Third-Party Excel Utilities

Excel has a powerful set of built-in commands and functions, including
graphing, forecasting, and the ability to produce PowerPoint slides as output.
But Excel can’t include everything. That’s where third-party
resources come in.

Resources is a generic term for the various Excel-based tools
ranging from utilities through forms to complete applications written to run in
Excel. There’s an enormous variety to choose from, available from an
equally wide variety of sources. Although some Excel resources can cost hundreds
of dollars, most of them cost less than $100, and a lot of them are free.

The Uses of Excel Resources

Non-native Excel resources (that is, things not built into Excel) are used
for a bewildering range of jobs, from the highly specialized and downright
bizarre to everyday applications. In fact, Excel is one of the most popular
platforms for writing calculation-oriented projects, ranging from tutorials on
machine design to highly specialized financial calculators.

Excel resources are also becoming the Windows equivalent of business forms.
Everyone from organizations like the Future Farmers of America (FFA) to
government agencies like the Occupational Safety and Health Administration
(OSHA) are using Excel resources as electronic forms that can be filled in on a
computer and submitted much more easily and accurately than paper forms. For
example,
Find A Grave
distributes an Excel template for recording gravesites, so that amateur
genealogists and other users can help Find A Grave to build their database of
burial sites.

Excel also provides convenient methods of presenting or manipulating data
imported from other applications. For example, Consumerism Commentary, a blog by
Flexo, has an
income and expense report template
that takes data from Quicken and outputs it in a standardized format in an Excel
spreadsheet.

A more complex use of the same feature is found in Microsoft’s Excel
2002/2003
add-in for SQL Server Analysis Services SP1.
This add-in lets users examine data from SQL Server Analysis Services cubes on
their desktops in Excel format. (Cubes are essentially multidimensional views of
data contained in a SQL Server database, usually a data warehouse.) This makes
it easy to use Excel to create customized reports, as well as using
Excel’s functions to create what-if scenarios.

Why Not Do It Yourself?

Of course, if you need an Excel resource, you can write it yourself—and
many of us do, at least some of the time. After all, Excel is designed to make
it easy to create these sorts of things. However, there are also some very good
reasons to use third-party resources. The main reasons are time and skill. You
can save time—sometimes a lot of time—by using a third-party
resource instead. Skill is also particularly important because, as discussed
next, there are several different kinds of resources. Some of them, such as
custom functions and add-ins, allow you to do things you can’t do in
regular worksheets or templates. Unless you’re an Excel expert,
you’re probably not going to be equally comfortable working in all areas,
and it quite likely doesn’t make sense to spend the time to really learn
them all.

Types of Resources

Although the range of available tools is enormous, underneath the covers
almost all of these resources consist of one or more basic Excel objects:

Add-ins. An Excel add-in is a generalized custom function
that can be used with any worksheet. An add-in is saved as its own worksheet,
usually with an .xla extension, and can be used with any worksheet or workbook,
just like a built-in Excel function.

Custom functions. In addition to a variety of built-in
functions, Excel provides an easy way to create your own functions. One limit on
custom functions is that they’re attached to a particular worksheet. If
you want to use a custom function with another worksheet, you either have to
import the custom function or reference it via the worksheet where it resides.
Custom functions can be written in Excel native commands, Visual Basic for
Applications (VBA), C#, or C.

Templates. In essence, a template is a more general version
of a prewritten spreadsheet. It includes formatting and perhaps some (but not
all) of the formulas and such you need to complete the job. Templates are
usually saved with the extension .xlt.

Worksheets. Prewritten worksheets can include labels,
formulas, and the rest of the data and programming you need already in
place.

Any of these objects, or any combination of them, can be used to write
utilities and applications for Excel.

Tools for Excel Programming

Plenty of third-party tools are written in Excel already, and available to
help you create your own Excel resources. This section discusses a few
categories of such tools. These aren’t necessarily the "main"
types of resources, but they’re certainly useful, and they give an
indication of the range of jobs that Excel resources can do.

Worksheet Auditors

One of the most useful programming applications is an auditor to check your
worksheets for errors.

Most worksheet auditors will check for consistency, circular references, and
other common problems. Many of these tools go beyond the basics, though.
CheckXL
from Critical Path Technology Services includes a range of comparison features
to let you check worksheets against other worksheets, compare ranges on
different worksheets, and perform similar tasks.

Add-In Decompilers

Before you start decompiling third-party add-ins, make sure that you can do
so legally under the terms of the add-in’s license. Because of the
potential for misuse, the author of the Excel add-in decompiler has chosen to
sell the latest versions of the software, rather than continuing to make it
available for free.

Other Examples

Navigator Utilities
is a package of tools designed to let you get around worksheets and
workbooks—finding references, locating links, named ranges, tracking down
objects such as graphs, and following cell dependencies.

Datapig Technologies’
Excel Explosion
splits worksheets to organize the data by one or more column categories, such as
address or date. It’s freeware, and the site features an excellent Flash
tutorial demonstrating the product.