There are a plethora of excel add-Ins out there, many of which aim to improve or extend a feature which excel already performs. I am not much interested in these but what I am interested in is add-ins which enable you to do something really useful which you cannot currently do in excel. In this article I introduce seven excel add-ins which meet this criterion – NodeXL, Solver, Rainbow, Spreadsheet Converter, Crystal Ball, Fractal Maps and XCell Compiler.
Image: A fractal map of retail sales data
First here are my top 3 free excel add-ins
NodeXL: How do I analyse network data quickly and visually?
NodeXL, a free third party add-in, turns excel into a powerful Social Network Analysis (SNA) tool which allows you to do a comprehensive analysis of social network data in the form of network maps. NodeXL also calculates key network metrics such as Degrees Centrality, In-Betweeness and Closeness. With NodeXL you simply import your network data in various forms (known as sociographs) and then manipulate it in excel.
Solver: What is the optimum solution to my model?
Many spreadsheets take the form of a set of input decisions which then interact to produce a set of outcomes over time. For example, the percentage we could invest in different sales activities for different products or channels all of which add up to a total annual sales or profits figure. But what is the best combination? Solver is a free add-in which ships with every copy of Excel but is not automatically installed – you need to enable it from the options menu. Solver can be massively useful in supporting planning or strategy as it can automatically vary your input decision cells thousands of times subject to whatever constraints you specify until it finds the optimum value of a specified cell (e.g. Total Sales or Total Profits). If Solver is not enough for you there are a whole host of paid upgrades available from Frontline Systems.
Rainbow: How can I make this spreadsheet less of a maintenance nightmare?
Rainbow is a clever piece of spreadsheet auditing software which offers a free personal version as well as paid versions. Rainbow allows you to check a spreadsheet for unnoticed errors or bad practice which will cause problems somewhere down the line. As well as error detection Rainbow also helps you to restructure and colour code a spreadsheet to make it easier to maintain.
Next my top 4 paid excel add-ins
Spreadsheet Converter: How do I turn this spreadsheet into a full web application?
Spreadsheet Converter allows you to deploy spreadsheets as full-blown web-based applications or web apps solutions without having to recode them. Spreadsheet Converter is designed for applications which gather input values in forms which can then be processed in situ or sent over the web to dedicated back-end applications (e.g. survey and questionnaire type applications).
Crystal Ball: What are the range of outcomes in my data?
Crystal Ball is a tool from the massive Oracle Corporation which is broadly similar to Solver but provides additional functionality to allow you to fire different distributions of input numbers at a spreadsheet to test different solutions for risk and probability. Crystal Ball also allows you to perform standard complex analyses such as Monte Carlo simulations.
Fractal Maps for Excel: Can I see my data in a more compelling way than excel charts?
Fractal Maps for Excel is a powerful add-in which allows you to produce fractal map representations from your data. Fractal maps are a new data visualisation technique which are particularly useful for showing instantly how hierarchical and matrix data is structured. Think 3-D Pie Charts on steroids!
XCell Compiler: How can I distribute my spreadsheet securely as a standalone executable program?
It’s a well known fact that excel password protection stinks. If you do an internet search you will quickly find a free excel password cracker which will decode the passwords in a spreadsheet in a matter of seconds. It’s not really a problem if you are sharing material with your colleagues but say you are selling a commercial spreadsheet with valuable intellectual property embedded in it which would be compromised if your users could get at your formulae. Or you might have built a spreadsheet for a customer where you have used existing routines which you do not want to be copied.
Enter Stage left XCell Compiler which allows you to compile any Excel spreadsheet into an EXE application with securely hidden formulas in binary format and protected VBA code. It also allows you to add your own splash, icon and end user license agreement (EULA). XCell Compiler allows you to provide royalty free distribution of your compiled EXE, which doesn’t require any pre-installed Run-Time libraries. It also allows you to restrict the time period of usage for your compiled Excel workbook which is useful if say you wish to offer a 30-day trial. If you need professional protection of your excel IP then XCell Compiler really is a no-brainer – none of the other alternatives come close – I have checked them out.
So these are my seven most useful free and paid excel add-ins as at August 2012 – I would love to get readers views on other products to add to my list!