Excel is one of the pillars of Microsoft Office. Despite being feature-rich, there are some tasks that are either cumbersome to perform or are buried in the program. That shouldn't stop you from using Excel, as there are some outstanding third party Excel Ad-ins that nicely complement the program. One example is the free add-in from Bastien Mensink called ASAP Utilities that provides easy access to over 300 utilities.
The problem with reviewing an add-in such as ASAP utilities is time. If you look at the website, you see the product has steadily added utilities since the product's inception in 1999. The total is now over 300. Just so we're clear, I didn't test all 300 of these. I didn't need to test all these utilities to make my decision. I knew after using just three. Whether you use Excel daily or infrequently, there are features in this package you can benefit from As Soon As Possible.
It's easy to appreciate the breadth and depth of this utility, but what appealed to me was the way items were labeled. The labels are descriptive and action oriented. Here are some examples that illustrate this point:
- Delete all page breaks in active sheet
- Empty duplicates in selection
- Convert formulas to their values
- Delete leading and trailing spaces
- Find/Replace in all sheets
The author has nicely grouped the utilities by various categories that make the 300 utilities easier to find. In addition, he allows you to customize the tool using Favorites. This is where you can add or order items from other categories. I found this feature beneficial since it often saved me from remembering which category contained the item I needed.
While I could write a long article outlining my favorites, I'll instead show one case that I think many of us encounter. One example is when you have a spreadsheet where multiple people have entered data. Depending on who enters the data, you can get a mish mash of entries. For instance, one firm I work with gets a lead file from their trade association. While they're appreciative of the leads, the entries vary based on who entered the lead. Rather than seeing standard addressing for names such as John, you're just as likely to see JOHN, John, or john.
If you're doing analysis, the case isn't as important. However, it makes a big difference if you're using an Excel spreadsheet as a data source for a mail merge in Microsoft Word. I think we can say that few people would like receiving a Dear john letter instead of Dear John.
The highlighting in the screen below shows the Excel cells I would change before doing a mailing. My preference is to have the first letter of each word capitalized.
Using ASAP Utilities, I can choose from a series of Text utilities. For my purposes, I wanted to start each word with an upper case letter except for the state code. To do this task, I highlight my range and select Start Each Word With Uppercase from the Text menu.
As you can see in the screen below, the addresses now display as mixed case except for the STATE column. I did a similar procedure for the STATE column except I chose Convert to UPPERcase. The ASAP utility nicely converted those entries that were all lower or upper case and those that used a combination.
There were a couple cells where I needed to edit the data such as street directions (SW) and PO Box. Even with these tweaks, the time it took to convert the addresses with ASAP Utilities was far less than if I edited the entries or used one of Excel's text functions.
Even though the utilities are categorized, you may at first want to use help. Under the ASAP submenu, there is a link to a tool that helps you find your utility. As example, I typed the word convert and the help tool provided a list of items that applied with a short description.
In addition to the built-in help, the author offers some nice resource pages on the product website such as a printable list of Excel shortcuts and Excel Tips and Tricks. Both of these pages are accessible from links on the left frame. Bastien also maintains a blog for ASAP Utilities that offers useful tutorials and links to other Excel resources. Collectively, these pages offer a wealth of information for people wishing to learn more about Microsoft Excel.
Regardless of your Excel experience or needs, I would encourage you to try this tool. While you may not come even close to using all the features, I have no doubt that you will find many essential.
Update: Bastien released a comprehensive 115 page PDF guide to the software. You'll also see he's been hard at work on 4.0 which looks to have many more features including support for Outlook 2007 and Microsoft Vista.
Last Updated (Wednesday, 29 August 2012 15:03)