How to Boost Microsoft Excel with Add-Ins
We all know Excel. Since its first introduction by Microsoft in the early 1980s, it has become one of the most popular software in the world. Millions use it every day at work or at home. Yet, who can say they know everything about Excel? Be honest, you don’t! Almost nobody does. There are certainly many situations where you faced an Excel problem you didn’t know how to solve. Maybe you even discussed it with colleagues and nobody managed to make it work as wanted. This is where you can want to take out one of the secret weapons of Excel: add-ins software programs.
Add-ins (or add-ons) are software usually created by third-parties and that plugs into Excel to enable new features in spreadsheets. Such modularity has spawned a variety of software editors providing tools, utilities, charts, diagrams, training or even games directly into Excel. Given the richness and flexibility of Excel, the possibilities are virtually endless. In this post, we highlight 3 main types of add-ins that provide great benefits: data visualization, statistical analysis, and time-saving utilities.
Data visualization add-ins are highly relevant if you are creating Excel spreadsheet in order to build a company dashboard. They focus on features that will let you display your data in a visually elegant way that wouldn’t be possible (or more precisely: that wouldn’t be easy) with Excel. You can use these add-ins to impress with beautiful graphics that make your data speak for itself. These features usually let you create charts, diagrams or maps to beautify your dashboard with appealing visuals. Several add-ins specialize in building for you these types of visualization elements, such as Power-user, Think-cell or Mekkographic.
For instance, creating waterfall charts is a must-have for many people working in consulting or finance. Waterfall charts allowing to visually break down a value into multiple components, something you cannot do on Excel with regular bar charts. These charts are widely used in finance to visualize financial aggregates for instance.
Illustration: A waterfall chart created with the Think-cell add-in
Other popular charts are Marimekko (or Mekko) charts. These charts are a variation of the Excel stacked column chart, allowing to have a variable column width. Mekko charts are often used to show in a single chart the respective market shares of several companies in several countries for instance. Without it, you would have to create one chart comparing markets with one another, and one chart to compare companies with one another… and one chart for any combination of a company and a market.
Illustration: A Mekko chart created with the Power-user add-in
Data visualization add-ins also enable mind-mapping diagrams. You can use these to show how ideas and concepts are related, for instance after animating a brainstorming session. Similarly, the GIGRAPH add-in will display network diagrams, letting people visualize relationships between organization entities or people.
Illustration: A mind-mapping diagram created with Mind-O-Mapper
Statistical analysis and scenario simulations
Excel is a powerful software to run calculations, but it does not offer the full package statisticians and researchers need for their work. That’s why several Excel add-ins focus on providing additional statistical and scenario simulations tools.
First things first: Microsoft itself has developed an add-in to provide simulation tools. Called the Solver, it enables you to run what-if analysis, and find out the optimal value a formula can take by changing multiple variables.
Another good example is XLSTAT, an add-in with 100 features to perform regressions, k-means, and other statistical analysis.
Illustration: XLSTAT provides statistical analysis tools
We could also mention Montecarlito or Simtools, which are add-ins performing Monte Carlo simulations, risk analysis and other probability analysis in Excel.
Time-saving Excel utilities
Most Excel add-ins are used for time-saving purposes. These utilities enable you to perform complex and time-consuming tasks with your data that are not available natively in Excel. Such utilities can cover a wide range of Excel situations to solve issues. Typically, utilities add-ins will help you automating a task that would require multiple manual iterations. Some of the popular add-ins in this area are Kutools, ASAP Utilities and Power-user.
Utilities add-ins can be seen as a toolkit to take you out of various painful situations. Take for instance ASAP utilities. One of their features enables us to insert cells before or after each cell in your current selection. If you want to do this manually, you can scratch your head for a while before finding a solution for it. But with the add-in, you can do this in just a few seconds.
Illustration: ASAP utilities features
Another example of a painful situation that requires an Excel add-in are merged cells. Merged cells can prevent you from working on a database, disabling PivotTables or luring your formulas. A tool like the merged cells manager from Power-user can take you out of this situation by letting you track and unmerge them, something that would take a lot of time if you were to do it manually.
Illustration: Getting rid of merged cells in a worksheet with Power-user
New Excel features enabled by add-ins can also take the form of additional functions that you can use in Excel, just like you would with any built-in function. For instance, Power-user gives the possibility to count or sum cells based on their colors, to count distinct values in a range or to get the number of the last row or column in a sheet.
Powerful and flexible, add-ins are a great addition to Excel and let you customize it to your own needs. They can take you out of painful situations and contribute to making you an advanced Excel users with very little effort. To learn more about add-ins, you can make your choice from this list containing dozens of great Excel add-ins. There is certainly one that will meet your specific needs.
“Olivier de Saint Louvent is the CEO of Power-user, a leading PowerPoint and Excel add-in with an impressive number of features. His experience with Office optimization comes from the years he worked in strategy consulting and finance.”
You’ll find loads more tips and tricks like these ones in our Microsoft Excel course, which will help you become an expert user in no time.