Excel is a mighty powerful application, and yet, there are any number of utilities available to extend its capabilities. I’m sure everyone has their favorites, and here I’m going to talk about mine. My reliance on these utilities is as a developer of Excel applications, that is, solutions that combine add-ins, templates, and regular workbook to accomplish specific objectives within specific operating parameters. My favorite utilities lean toward developer tools, rather than worksheet productivity tools. This is an incomplete list: there must be hundreds of worthy utilities written by dozens of developers, but these are the ones I have tried and kept. If you think I’ve left out an important utility, tell me about it in the comments.
Two of my favorite tools were developed by fellow Microsoft Excel MVP Jan Karel Pieterse. He has a variety of utilities on his web site, JKP Application Development Services, as well as a diverse collection of articles and tutorials. I use Jan Karel’s Name Manager and AutoSafe. Jan Karel has offered me a percentage of sales on these two utilities, but that won’t amount to much, because they are free.
Name Manager
The Name Manager is a high-powered tool for working with all of the Names in your workbooks. You can view all names, or filter by a number of conditions. You can make Names hidden or visible, local to the worksheet or global to the workbook. You can add and delete Names, and see what ranges they refer to. Especially powerful is the ability to work on many Names at once. This utility can save a developer many hours.
AutoSafe
AutoSafe is what Microsoft was thinking when they implemented Autosave. AutoSafe saves a copy of your open workbooks in a directory you specify, without overwriting the workbooks. If Excel shuts down improperly, AutoSafe offers to restore the files in use prior to the crash. When a new copy is saved, the existing copy is removed to the recycle bin. In this way, you can maintain an informal version history in the recycle bin, as well as track your activities. This utility has saved me many times.
Microsoft Excel MVP and author Rob Bovey, of Application Professionals, offers a handful of free utilities. I use Rob’s Chart Labeler and VBA Code Cleaner utilities.
Chart Labeler
Rob calls this the XY Chart Labeler, but it really works for any chart that accommodates data labels on its plotted series. Excel allows you to label points with the series name, category labels (or X values), or Y values, and once you’ve added labels, it allows you to customize those labels. But for some reason Excel lacks the capability to apply a set of labels from the worksheet to a series on the chart. The Chart Labeler addresses this shortcoming, allowing you to apply custom labels one point at a time, or one series at a time. The labels on the points remain linked to the cells containing the labels, so the chart labels change when the labels in the cells are changed. The Chart Labeler also allows fine positioning of one or a series of labels. I’ve heard that this is the most downloaded Excel utility, and I’m not surprised. It’s been around since VBA was introduced to Excel, and it’s useful for anyone from casual users to hard core developers.
VBA Code Cleaner
Rob says it best:
“During the process of creating VBA programs a lot of junk code builds up in your files. If you don’t clean your files periodically you will begin to experience strange problems caused by this extra baggage. Cleaning a project involves exporting the contents of all its VBComponents to text files, deleting the components and then importing the components back from the text files.”
Microsoft Excel MVP and author Stephen Bullen offers many example workbooks and free utilities on his Office Automation web site (Stephen and Rob have collaborated on several books). I use Stephen’s Smart Indenter and VBE Tools utilities when I’m developing in VBA.
The Smart Indenter restores an orderly appearance of your code modules by indenting each line. The indentation style is highly customizable. I use this very frequently while writing code, and often the first thing I do when I receive a workbook from someone else is run the Smart Indenter on its code. The Smart Indenter has versions for VBA and for VB6.
VBE Tools provides a set of enhancements to the Visual Basic Environment. It adds a toolbar that shows the size of the active code module and that adds “Nudge” buttons that provide fine positioning control of objects on a user form. It also wraps the location of files in the References dialog, so you can actually see the entire path and file name of the reference. This feature materialized after Stephen heard me whining about the truncated reference file name problem.
John Walkenbach, Excel MVP and author of dozens of spreadsheet books, has an extensive website with tips for developers and for regular users. “Mr Spreadsheet” also hosts the very popular non-Excel J-Walk Blog, and he has a number of utilities on his site.
J-Walk Chart Tools includes a labeling feature similar to Rob’s (above). It also includes features that export charts as image files, convert charts to embedded pictures, and create a report detailing one or all charts on a sheet.
Power Utility Pak is an extensive set of general-purpose tools that make working in Excel much easier. PUP includes tools for Formatting, Formulas, Charts, Ranges, Worksheets, Workbooks, and much more. This is the only Excel utility listed here with a registration fee, but the fee is nominal, and includes free upgrades for life. For an even smaller nominal fee, the user gains access to the VBA code behind the utilities, for educational purposes only (of course).
ASAP Utilities (“As Soon As Possible”) by Bastien Mensink offers a broad set of tools that enhance your productivity in Excel. It is similar in scope to the Power Utility Pak, but remarkably there is not a lot of overlap between them. When my work was primarily as a regular Excel user, I had both utilities installed, and used them about evenly. I find them less useful now as a developer, but both are worth mentioning here.
A utility I use a great deal as a developer is MZ Tools, written by Microsoft Visual Developer MVP Carlos Quintero. There are free VBA and VB6 versions as well as commercial .Net versions. MZ Tools provides many features that facilitate writing, documenting, and debugging code, and managing VB projects. I don’t even know all the features of MZ Tools; I discover new ones all the time.
For packaging my projects into standard installation executable files, I use Inno Setup by Jordan Russell. This standalone utility uses a versatile script to compile your files into a single EXE file, for simple and flawless installation. Inno places your files into a predetermined or user-defined directory, sets any registry keys, and adds shortcuts during installation, and cleans up after itself when uninstalling programs.
There are some utilities that I don’t use frequently, but I need them on occasion and recommend them to users with specific problems.
FindLinks by Excel MVP Bill Manville helps to find and fix those stubborn links in your workbook.
FlexFind is another handy utility from Jan Karel Pieterse (see above). This utility helps find and replace text throughout the entire workbook, in headers, footers, chart titles, and other places normally out of reach to the Edit menu.
Excel MVP Andy Pope hosts a number of utilities, as well as lots of neat Excel tricks. I use his Button Editor frequently when designing user interfaces.
I may as well mention a few non-Excel utilities I also use frequently.
IrfanView is an outstanding and free image editing utility.
SnagIt by TechSmith is a nicely made program, which can capture images or text from windows or screeen regions. TechSmith also makes Camtasia, which I’ve never used, but which is popular for creating and editing video files. SnagIt and Camtasia are commercial products.
For converting workbooks and other documents to PDF files, there must be dozens of free utilities. I use PDFCreator, which is unique in that it can be controlled using VBA (see Ken Puls’ ExcelGuru site for programming hints), and PrimoPDF, which has a very nice save-as interface.
For FTP file transfers, open source program FileZilla is the best free alternative I’ve come across.
Yawar Amin says
Jon, you mentioned converting to PDF so I thought I’d let you know about a free code library for PDF output I discovered recently: PDFJet (http://pdfjet.com/). I know you’re no stranger to coding, and the library is programmable with Java or .Net, so it might be another useful tool for you.
Andrew says
How about JMT Excel Utilities? They’re free too :-)
http://jmt.puremis.net/jmtutils
Jon Peltier says
Andrew –
As I mentioned, I don’t use many utilities any more which are focused on end users. I wrote about the tools I have installed on my system, but I didn’t have JMT Utilities installed. I have been watching over the past couple years while you’ve added to it, and the latest feature set looks pretty impressive.
Andrew says
Thanks Jon. Yep, they are getting better, some of the old code I come across makes me wince at times…we all have to learn, right?
Missed seeing you guys last week, maybe next year :-)
julia Dailey says
Jon, I am looking for a way to retain the original text label of a data point when I filter a scatter chart. Please help!
Julia
Jon Peltier says
Hi Julia –
I alluded to this problem when I mentioned cell linkages in the comments to Changes to Charting in Excel 2007. What I often do if the labels are critical is use a separate range next to my data table, and use some kind of lookups to put the records I want, including X and Y values and label text, into this range. The chart is made from this range, so that if I remove an item from the chartable data, everything, including labels, just moves up a row in the plot range.
It’s too much for a post comment, but I’ll add it to my burgeoning list of future topics.
zlatan24 says
Where is fine tool-how can recover an Excel sheet, it supports almost all existing versions of XLS and XLSX formats: Microsoft Excel 97, 2000, XP, 2002, 2003 and 2007 (xlsx repair), recover style of table, recover number formats (except colors, used in a number format), recover fonts, recover worksheets, recover columns width and rows height, recover cell data of workbook, recover all types of formula including functions, internal, external and name references, recover cells format (font, number format, line style, fill pattern, text orientation an alignment) recover colors of cells,recover colors of cell’s borders.
ckz says
Working in excel. I am using several worksheets with a drop down menu that define the users process, and a separate worksheet that helps them to calculate the cost of their processes.
I now want to tie all this information together, but the users may have varying processes.
So, I created a new worksheet where the user can define up to ten different processes. 5 processes and 5 hot processes.
From this newly created worksheet, I want the other worksheets to use this list as their dropdown menu. The data validation list function only lets you create a list already in the workbook. I want the drop-down lists not to show any blank cells, too.
In some of the worksheets where I do use data validation lists, the worksheets are dynamically created – meaning I have dialog boxes that are telling the program to insert or delete rows with text/formulas.
Ultimately, I want my summary table to summarize the costs of each line item by water type.
Any suggestions on how I approach this issue?
Thanks,
CKZ
Jon Peltier says
ckz –
I’m not sure if you’re asking a simple question or if you need help coding your project. I’ll assume it’s the former.
You can use a dynamic range for your data validation list source.
If you’re using VBA for dynamically creating worksheets, then you can use a few more lines to control population of thesee or other lists.
DaleW says
Jon – great list of must have Excel add-ins. In recent weeks I’ve been working my way through it and keeping almost every one. A couple including PUP6 were already favorites of mine.
BTW, does no one offer a free color palette manager add-in for classic Excel that is, well, more powerful than your PaletteChooser.xls file? (Yours is great — and free — but doesn’t automatically save and name the current palette.)
Jon Peltier says
Dale –
I don’t know of another palette tool. I don’t think it would be too hard to incorporate the ability to save custom palettes from the utility’s interface, rather than by manually editing the worksheet. I’ll put it onto my to-do list, which is only, oh, ten thousand items long.
Naresh says
Hi Jon,
Very good post! I real time experience I had faced a problem with one of my friend who is unable to change the date in excel sheet. When I tried to modify, its shows the same date not the modified one….Actually that excel sheet in version 2003 and he upgraded to 2007….From the day of up-gradation onwards he is facing the problem…..Can you please help me out in this issue..
Thanks & Regards
Naresh. K
Bill Rockenbach says
Jon,
I have used VBE Tools, Smart Indenter and Code Cleaner for many years. I recently bought a new lap top with Windows 10 installed. I installed Excel 2003 successfully but can not get the three utilities to appear in the VBE add in manager or any of the features to work. Any ideas? Excel security is set to accept programs to operate on the VBE. PUP Power Pack installed normally in the Excel workbooks.
Appreciate any help
Bill
Jon Peltier says
On this laptop, I have Windows 10, plus Excel 2003 through 2016. In 2003, Smart Indenter, Code Cleaner, and MZ Tools are all active in the VBE; I don’t know if I even have VBE Tools at all on this computer. These tools are present with toolbars and context menu items, and in the Add-In Manager. I set this computer up long ago, and I don’t recall any issues getting them to work.
Lisa Green says
Hi Jon,
I think you missed an addin… one that I use a lot. It’s not “readilly” availiable any more since it’s quite an old one… and so sort of the same as MZTools three… is free. It’s prettycode.print. It’s discontinued but still downloadable from https://community.submain.com/files/folders/retired/default.aspx.
BTW… Stephens excellent indenter has been rewritten ( As I understand it ) with permission, by the VBA add in team for the free VBA add in RubberDuck. It now works very well with 64 bit and the indent column number is easy to change!!!
Hope you find this interesting…
Regards
Lisa
Jon Peltier says
Hi Lisa –
Thanks for your message. I remember trying numerous prettyprint routines for formatting my VBA code, and I never really latched onto one. My approach now is to paste the code into Notepad++ (which is awesome, but you probably already know that), then copy the code in Rich Text Format, and paste it into a Word template I’ve designed for the task. Sounds like extra work, but it isn’t really too bad. And I’m using MZTools 8, now that Carlos finally extended it to 64-bit.
The MXTools indenter is okay, but it’s not as flexible as Stephen Bullen’s was. I’ve heard that Bullen’s Indenter has been incorporated into RubberDuck, which is one more reason I have been meaning to try out RubberDuck. I met Mathieu Guindon this spring, and he’s a very interesting guy, way smarter than I am.
Lisa Green says
Hi Jon,
Yes… I use the indenter in Rubberduck now. I think it’s worth cjecking Rubberduck out actually.
To go on a bit more about prettycode.print, It’s possible to export the output to a PDF file. Very useful for documentation and code reviews. I like the way the printed “blocks” are made so distinctive by the indentation lines! For me it really makes the code clearer to read, And it’s free!! And because it’s just for printing… you can put a snippet in a proc and print it without the need for Dims and so on.
And yes… I use Notepad ++ as well and just love it!
Now I have to get myself together with GIT for rubber duck ! Been meaning to for a while anyway. This is my foot in the derriere!
I’ve also been using MZTools 3 for a long time too but am having problems installing it on this new laptop, which is 64 bit.
Anyway, those, till now, have always been my big three for the VBE. Smartindenter, MZTools 3, and Prettycode.print.
And thank you for your reply!
Hugs
Lisa
Jon Peltier says
As an MVP, Carlos gives me a license to Mario is 8, and it’s awesome. I’ve also helped him fix a bug or two.
I’ll try out the Pretty Print thing, but my current routine is really all I need.
Lisa Green says
LOL!!!
Was emailing with Mathieu. I told him… the way some women do… that you said he was very clever.
His reply… “Jon is awesome!”
Lisa