Chapter 38. A Tour of the Best Add-Ins for Excel

In this chapter

Customizing the Ribbon by Using CustomizeRibbon 1020

Loading PDF Data to Excel by Using Able2Extract 1021

Doing Access with Excel by Using DigDB 1021

Creating Dashboards by Using Speedometer Chart Creator 1022

Accessing More Functions by Using MoreFunc.dll 1022

Using Add-ins from AddIns.com 1023

Although Excel can do amazing things, it cannot do everything. There are plenty of niche industries that need more functions or more specialized operations than what is available in Excel.

Sometimes, someone recognizes a common need. After seeing dozens of questions in Excel newsgroups about converting uppercase to lowercase, for example, a developer might offer an add-in to make that task easier than the current five-step process.

Literally thousands of add-ins are available for Excel. Many are being rewritten to be compliant with Excel 2007. This chapter is by no means a comprehensive list of add-ins available, but it contains some of my favorites.

Customizing the Ribbon by Using CustomizeRibbon

Whereas it was extremely easy to customize the menus in Excel 2003, it is nearly impossible to do so in Excel 2007. You have to be a developer who can work with Ribbon XML in order to customize the ribbons.

Patrick Schmid wrote the must-have solution for any normal person who wants to customize the ribbons. His COM add-in adds a CustomizeRibbon icon to the View tab. You can then use CustomizeRibbon to add new groups to any ribbon tab or even add a new tab to the ribbon (see Figure 38.1).

Figure 38.1. You can customize Excel’s ribbons by using Patrick Schmid’s CustomizeRibbon add-in.

Patrick plans to offer a limited freeware version and a more powerful version for sale. You can download or purchase Patrick’s add-in from http://pschmid.net.

Loading PDF Data to Excel by Using Able2Extract

People love sending data in PDF files. Now that Microsoft is providing a PDF creation utility, more and more documents will be emailed via PDF.

The authors of PDF documents often use PDF format to ensure that the data cannot be altered. Other times, the authors want to make sure that someone who does not have Excel can still view the data. Although these are fine ideas, getting data back out of PDF and into Excel is very difficult.

Data copied and pasted from PDF to Excel usually loses its columnar format. Able2Extract solves this problem. You simply open a PDF file in Able2Extract, and you can convert the contained data to Excel with one click. Or, if you need absolute control, you can specify regions so that, for example, titles and headers beyond page 1 are not imported.

There are some PDF files out there where the entire document is a scanned image of the original file. The Professional version of Able2Extract can even deal with these.

You can download a free trial version of Able2Extract from www.investintech.com/able2extract.html.

Doing Access with Excel by Using DigDB

The most amazing add-in for Excel is DigDB from Data Instrument Group. Data Instrument has taken all the difficult database operations you might ever want to do and made them possible in Excel. Now that you might be dealing with 1.1 million rows in Excel 2007, these functions will be especially important.

DigDB offers more than 40 new functions in Excel. The following is a partial list:

• Use Access-like queries, joins, and aggregates in Excel
• Perform roll-ups, which are better than pivot tables, including the Median function
• Match tables, including a fuzzy match
• Count/extract unique values in a range
• Combine columns
• Insert, delete, and copy select by row intervals
• Fill blank cells in a range by interpolating
• Extract valid emails from a range
• Transpose a cross table to a list

You can download a free trial of DigDB from www.digdb.com.

Creating Dashboards by Using Speedometer Chart Creator

When executive dashboards became the rage, many people wanted to take the term literally and offer a series of speedometer charts on their dashboards. The circular chart shown in Figure 38.2, created using Speedometer Chart Creator from Mala Singh, has various colors around the perimeter of the speedometer. A pointer indicates today’s value, and an alternate pointer shows yesterday’s value, so you can see whether there has been improvement.

Figure 38.2. You can create dashboards by using the Speedometer Chart Creator from Mala Singh.

To purchase the speedometer chart creator, visit www.mrexcel.com/speedometer.html.

Accessing More Functions by Using MoreFunc.dll

MVP Laurent Longre offers a free Excel add-in that has 66 new functions. Although some of the functions are specialized, there are great functions of use to many people using Excel. The following are just some of the new functions:

• LastRow—This function finds the last filled row in any column.
• PageNum—This function finds the page number of any cell.
• SheetName—This function finds the name of the current sheet.
• WordCount—This function finds the number of words in text.
• NBText—This function spells out a number as text in any of 13 languages.
• ISO.Weeknum—This function finds the ISO-compliant week number of a day.
• CountIf.3D—This function is the same as Countif but for 3-D references.

You can download the MoreFunc.dll for free from http://xcell05.free.fr/.

Using Add-ins from AddIns.com

Bob Flanagan of Macro Systems has dozens of add-ins available at Add-Ins.com. The most famous of these add-in is Spreadsheet Assistant. This single add-in provides the ability to simplify dozens of tasks, including the following:

• Perform any math action on a range of cells.
• Adjust text with the Paragraph Fixer feature.
• Toggle AutoComplete on or off.
• Fill blanks with the entry above.
• Copy a sum to the Clipboard.
• Insert a sticky note.
• Change letter case.
• Freeze panes on multiple sheets.
• Use many new functions, such as GrowthRate, LookForText, NumberToCurrency, DateInMonth, RemoveAllSpaces, and TrimLeft.

In addition to Spreadsheet Assistant, Bob offers numerous other time-saving and specialty functions. Visit www.add-ins.com for more information.