Jul
13

Top 4 add-ins to consolidate data in Excel spreadsheets

In my today’s review I would like to dwell some more on the topic that I started in my previous post: working with Excel documents. It’s generally known that one gets used to good things pretty quickly, and the stuff that seemed amazing yesterday looks quite ordinary today.

The problem of removing duplicates from Excel sheets touched in my previous review was just the top of an iceberg, but as you know, every big walk starts from the first step. From information flows to the summary report. And how nice would it be if you can concentrate on your main work and leave all trifles aside.

The major purpose of the add-ins reviewed in this post is to take care of data types and formats upon themselves and let us tackle the main task – to merge and consolidate Microsoft Excel data coming from multiple spreadsheets.

So, here is a list of consolidating plug-ins for Microsoft Excel that I suggest you looking at:

Name of the add-in Vendor Price, USD Trial
Advanced Consolidation Manager Mapilab 39 Yes
Excel Spreadsheet Consolidator Jabsoft 69.95 Yes
ConsoXL Jepib 130 Yes, with limitations
DigDB Excel add-in 7 DigDB 39 per 1/2 year license Yes

Advanced Consolidation Manager from Mapilab

On the vendor’s web-site (www.mapilab.com) you will find a clear and comprehensible description of the add-in and its basic functionality. Also, they have a screenshot that graphically demonstrates the main function of the add-in: consolidating data coming from multiple Excel worksheets.

Advanced Consolidation Manager - functions

I would like to make a little digression before we proceed further. As you probably know, making a summary report is too general a task that will definitely require the user to gain the understanding of some abstract model of actions suggested to us by the developer if you want to solve your tasks quickly and successfully using a given add-in.

All of us work with various specific documents, data types and ways of their presentation in Excel worksheets. Because of this it seems impossible to make up an ideal recipe of solving the general task of data consolidation in Excel. Ready-made step-by-step solutions are applicable, but only in narrow niches, when the developer precisely knows what type of data you will work with and what actions you will have to perform.

A payment for the universality of the add-in will be the time you will have to spend on learning the main notions, data types, way of their presentation and applicable functions. But it is worthy of it. Besides saving money (you buy one add-in instead of many), you will get a true Swiss Army knife among Excel plug-ins.

Below there is a description of the Advanced Consolidation Manager main functions:

Function Purpose
Sum Summation of cell values
Count Calculation of the number of cells in a certain range
Average Calculation of average value of cells
Max Maximum value
Min Minimal value
Product The product of the values
Count Nums The number of data values that are numbers
StDev An estimate of the standard deviation of a population, where the sample is a subset of the entire population
StDevP The standard deviation of a population, where the population is all of the data to be summarized
Var An estimate of the variance of a population, where the sample is a subset of the entire population
VarP The variance of a population, where the population is all of the data to be summarized

The vendor provides a fully functional 20-day trial version.

My installation of this consolidation add-in went easily and smoothly. At the first step, you select the installation language (in the current version English and Russian are available).

Then you choose the installation type: for you only, or for all users of the PC.

Advanced Consolidation Manager - Registration wizard

After restarting Excel, I saw the Consolidation Manager’s toolbar. And the add-in was ready for the immediate use.

When I say the immediate use, I mean you can start consolidating data from different Excel files right away. The developers separated basic functionality (Simple button) and advanced (Advanced button, accordingly). The Help button, as usually opens the help system. Help is detailed enough, but unfortunately with no screenshots. Screenshots are available on the add-in page only.

Now, how it works… The Simple button opens the following menu:

Advanced Consolidation Manager - Copy data from several files into one file

Yes, it is a familiar step-by-step wizard that has a short clear description of each step and a graphical scheme for each of its three functions.

Advanced Consolidation Manager - Copy data from several files into one sheet
Consolidation Manager - Copy data from several files into files using page title order

At the next step you select the files you want to consolidate:
Consolidation Manager - Select files for consolidation

And choose the way for naming new pages:
Consolidation Manager - Naming new pages

Finally, you choose where to save the result of consolidation:
Consolidation Manager - Saving result

Simple, fast and easy.

If you need advanced options (see above), make use of the Advanced button:
Consolidation Manager - Consolidation functions list

You can select the range by using a keyboard or a mouse.

You can always return back from any stage by clicking on the Back button and make any corrections you whish.

Thus, we have a nice Consolidation add-in for Excel with a very user-friendly interface. The work with the tool is easy enough for an inexperienced user, but not to the extent that would cut down the functionality in any way.

The add-in’s interface based on two functionality levels increases the comfort of work and makes the process of familiarization with all abilities and functions of the add-in easy and natural.

All you need to do after installing the add-in is to run its wizard to consolidate your Excel data from different sources, and later on, after familiarizing yourself with advanced functions, use the add-in’s abilities to their full extent.

Excel Spreadsheet Consolidator from Jabsoft

On the Excel Spreadsheet Consolidator home page you will find a short description which gives just a general impression of what the add-in can do. So, at your first visit, you will not be confused by too many details. Each function is presented on a separate page with a detailed description, screenshots, and sample Excel sheets. Also, there is a section that describes the add-in toolbar and how to work with it step-by-step. No doubts, it’s one of the best guides I’ve ever seen.

A fully functional evaluation version is also available.

On my PC, the add-in installed with no problems or stumbles. The first thing the user sees is the wizard windows and the add-in toolbar.

Yes, we can make some parallels with Advanced Consolidation Manager from Mapilab: they both provide a “quick start” option, though use different approaches. The Beginner and Advanced tabs in Mapilab’s add-in, and the “Tip of the day” window opening at the start of the Excel Spreadsheet Consolidator with three main consolidation options. You can start working right away from that window and learn all minute details later on.

After you gain some experience with Excel Spreadsheet Consolidator, you can uncheck the “Show always” checkbox and this quick start window will stop showing up. Well, probably some will find it pleasant to make that symbolic passage from beginners to experts.

Excel Spreadsheet Consolidator - Quick start window

If you hover over any icon on the toolbar, you will see a tooltip with its function. Also, at any moment you can access that help with screenshots that pleased me so much on the vendor’s web-site.

The Excel Spreadsheet Consolidator panel gives you access to the following functions:

Consolidator – has three basic options:

  1. Creating models – It is useful, when you often create & execute the same model.
  2. Exiting models – After you have saved model, you can execute it.
  3. Copy models from – It enables you to copy consolidation models from one workbook to another.

Excel Spreadsheet Consolidator - Consolidator window

Multi-sheets Consolidator – This tool copies the selected sheets from each chosen Excel workbook to an existent or to a new workbook. It consolidates the same range from each worksheet in a new spreadsheet (in case of a new workbook) or in the selected sheet (in case of an open workbook)
Excel Spreadsheet Consolidator - Selecting workbooks & sheets to consolidate

Smart Consolidation – This option allows you to consolidate disordered records that come from many spreadsheets and are contained in more than one workbook. All you need is to select and name the ranges you want to consolidate. You can do it even if the selected ranges are on different sheets.
Excel Spreadsheet Consolidator - Smart consolidation
Excel Spreadsheet Consolidator - ranges to be consolidated

Detailed Consolidation – Choose this type of consolidation if you want to see details along with the totals.

Conditional Text – is suitable to format cells in a range / database based on a criterion previously specified in the decision table.
Export Sheets as… – As follows from its name, this function allows you to export any sheets of your workbook keeping the formulas or changing to values.

Toggle Settings window
Excel Spreadsheet Consolidator - Toggle settings

It will not be an exaggeration to say that the Excel Spreadsheet Consolidator from JabSoft is a true pearl of our today’s review. A dream help, a wide variety of functions and a big number of possible settings seem almost unreal. The options of creating, saving and repeated usage of models open great opportunities in automating typical tasks of data consolidation in Microsoft Excel.

ConsoXL from Jepib

And here we have the first representative of consolidating add-ins focused of one small niche.
The vendor informs that their ConsoXL has been designed to consolidate budget like Excel spreadsheets. Also, on their web-site there is a flash demo, and step-by-step user manual.
The vendor provides a 30-day trial which has a limitation in the number of Excel files that can be processed.

After completing the installation, the add-in opened its window but didn’t run Excel.
ConsoXL - Main window

From its Help, I found out that it is not recommended using Excel while ConsoXL is making a consolidation because opening an Excel file from the explorer could interfere with the Excel copy used by ConsoXL. In my opinion it’s quite a strange approach, taking into account that all other consolidation plug-ins have no problems in coping with this.

In the ConsoXL’s Help you can also find some basic terms that could help you understand the principle of work with ConsoXL. One more thing that looks quite weird is that if you open Help from the add-in menu, you cannot switch between the add-in’s window and the Help window. In case you call Help from the Start menu, all works as it should be.

To tell you the truth, I was unable to understand the logic that authors were guided by when creating this add-in. If to add the price of USD 130 to its lame functionality and usability, then it’s hard to imagine who would choose this add-in with there being so many nice competitive plug-ins on the market.

DigDB Excel add-in

And here it is that very Swiss Army knife of today’s review.

When you start the installation, the DigDB add-in asks you to close Excel and set the trusted mode for macros. When the installation is completed, you will see the DigDB menu item in the Excel main menu: and I strongly recommend that you refer to Help. Clicking on the Help button will take you to the detailed description of all functions on the vendor’s web-site (unfortunately, the authors didn’t foresee the case when you don’t have the Internet connection at hand). On their web-site you will see another boon: besides step-by-step instructions and screenshots illustrating each function, they published source files from those samples. Considering the wealth of features of this add-in, this approach looks more than justified.

By the highest standards, this software is more than one add-in, it’s an add-in collection giving odds to many of this type. However, all this wealth is assembled in one menu:
DigDB Excel add-in - Menu

Main merging & consolidation functions and abilities of the DigDB Excel add-in:

  1. RollUp – an alternative to pivot tables and subtotal, working in a step-by-step mode.
  2. Sorting – sorting by more than 3 levels, by text value, text length, color, style, random sorting.
  3. Extract – extracting valid emails, visible rows in filter, subtotal result.
  4. Do Access in Excel – access-like query, joining, aggregating in Excel.
  5. Table / List – joining (merging) tables, matching tables (with fuzzy match option), combining (appending) tables, sheets, splitting table into subtables, transposing cross table to a list.
  6. Convert values – wide range of converting options: converting dates, data types, converting to proper case, variety of rounding options, trimming spaces, replacing all formulas by values.

Plus, a wealth of other functions for brining different data scattered over various Excel files to one standard. Let’s take, for example the Complex Table Filter option. It is a powerful filter with many capabilities: filter by wildcart, by multiple criteria, nested conditions, blank cells, duplicates, inverse of the filtering results, maximum and minimum filter.

DigDB Excel add-in - Complex filter window

Beyond question, other add-ins reviewed in this post cannot vie with the set of functions of this add-in. The DigDB add-in has everything, or almost everything you may ever need when working with Excel.

On one hand you get a marvelous universal tool and, which is not least important, for an affordable price, on the other hand, you have to learn how to make use of all this functionality wealth.

Taking into consideration all this, it would be nice to have a minimal entering threshold. With relation to its user interface, all seems to be logical enough: a list of the main functions and drop-down actions. But is all that obvious? No doubts, you will have to spend some time in the Help section (unfortunately, no context help is provided). The reward will be a universal application of the add-in with practically no limits in types and formats of your source documents.

The main shortcoming of this universal Excel plug-in is the lack of that user-friendliness and a quick start option that the Advanced Consolidation Manager from Mapilab and the Excel Spreadsheet Consolidator from Jabsoft offer.

So, what is my vision of the ideal Consolidation Add-in for Excel?

The versatility and specificity of consolidation tasks make a quality comprehensible Help section an indispensable part. Either screenshots, or sample files would not be out of place. The possibility to find directly from its window what this or that function is intended for will be of great help as well.

Most typical consolidation tasks are perfectly managed by wizards. Besides being exceptionally user-friendly, they also allow you to easily solve practical tasks without reflecting on trifling things.

A good trend among the add-in developers has become the aspiration for making their add-ins easy to start and use. In my view, the main problem facing the developers is to keep the balance between the universality of usage and easiness-to-use.

Advanced Consolidation Manager Excel Spreadsheet Consolidator ConsoXL DigDB Excel add-in
Installation and first start 5 of 5 4 of 5 4 of 5 4 of 5
Usability 5 of 5 5 of 5 3 of 5 4 of 5
Functionality 4 of 5 5 of 5 3 of 5 5 of 5
Help 4 of 5 5 of 5 3 of 5 5 of 5
Total 4.5 4.75 3.25 4.5

Thus, each add-in has its unique features: the Advanced Consolidation Manager from Mapilab can be installed for all users of the machine and has a nice user-friendly interface, the Excel Spreadsheet Consolidator from Jabsoft has the best ratio between functionality and user-friendliness. While the DigDB Excel add-in is indeed all-in-one solution for which there is no unsolvable tasks, but it would require some time to learn to use all its power. So, it’s up to you!

Related reviews:

Excel Lookup add-ins – how to merge data in Excel tables
How to compare and find differences in Excel worksheets?

Comments are closed.