Pane Relief: Excel Utilities
- Third-Party Excel Utilities
- Pitfalls of Using Excel Tools
- Where to Find Excel Resources
- Customizing Third-Party Resources
- Make or Buy?
Like this article? We recommend
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.
The Spreadsheet Detective performs inter-worksheet data flow to help analyze complex models and highlight inaccurate sensitivity ranges.
Add-In Decompilers
The Excel add-in decompiler converts Excel add-ins back into regular Excel workbooks. This tool is valuable if you’re customizing Excel add-ins.
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.