Consolidate data from Excel workbooks or worksheets

Consolidation Assistant in detail

The Consolidation Assistant is designed to help you combine / merge data from multiple Excel workbooks or worksheets.

With Consolidation Assistant for Excel you can

Excel Consolidation Assistant main dialog

• Create a list of workbooks and their worksheets for use in the next option;
• Consolidate workbooks and worksheets;
• Consolidate CSV or ASCII (text) Files;
• Extract a range from a group of workbooks;
• Freeze panes on selected worksheets;
• Set selected Excel worksheets to the same zoom and upper left cell;
• Extract data from selected sheets;
• Insert sum through sheet formulas.

Workbook consolidation can be done in several ways:

• Extract a range from selected worksheets (either a named range or a selected range on a specific worksheet)
• Combining worksheets from a list of workbooks
• Selecting a set of Excel workbooks and specifying a range name that identifies the data range to be extracted.

If you need to extract the same range from many workbooks, the Consolidation Assistant allows you to extract the same range from all selected Excel workbooks and put the output on a worksheet. You can specify either a range name or select a range on a specific sheet for extraction. The extraction can be transposed on the output worksheet, blank rows put between extractions, and the source workbook listed beside the extraction. If you do not want the data extracted to just one worksheet, then the data can be extracted to individual worksheets.

When consolidating workbooks, you can convert the sheets to values or copying the entire sheet. The first approach is the one to use if you have range names used in the formulas, and these range names can have different values. Excel by design will use the first range name that is copied into the consolidation workbook. This range name may not be what you want used for every sheet. And, sometimes it creates local names, so that you end up with the global name used by some sheets, and the local names on other sheets. If you do not use range names in the formulas, then copying the entire sheet allows you to retain formulas that may be of use to you.

Combining worksheets from a list of Excel workbooks is a two step operation. First you run the first option on the above menu and select the files containing the worksheets to be consolidated. This creates a list of workbooks and worksheets. For those sheets you wish to consolidate, type the name to use in the consolidated file to the right of the existing name. The set of names you specify must each be unique. Once you have done this, select the range containing the new sheet names and run option two on the above menu. Blank cells in the selection will be ignored. When the consolidation is finished, you will end up with a workbook containing each of the sheets that you specified.

The Consolidation Assistant has several other features that allow you to better view your data. One freezes panes on all selected sheets. Another sets the same cell on all selected sheets to the upper left corner. And at the same time sets the zoom to the same setting on all the worksheets.

Once you have the data consolidated into one workbook, you can use the search through features of the Consolidation Assistant to extract ranges from selected worksheets

Consolidation Assistant for Excel - extraction options.

For example, if row 50 is the total row for a group of sheets, you can extract this row from those sheets. If the total row is identified by the words "Total Row", but is not always the same row, one can use the search for matching text and extract the total row values.

If you are consolidation CSV or text files, options in the Consolidation Assistant allow you to:

• Consolidate by selecting files in a file open dialog, or
• Create a list of files in a directory and its sub directory and then select the ones you want to consolidate.

CSV and text file consolidation allow you to:

• Consolidate each file to its own worksheet
• Consolidate all files to one worksheet
• Consolidate all files to one worksheet and list the source file on each row

See also in "Excel - Data management - merge, convert, delete"

• Address Assistant - Split addresses from one cell into multiple cells.
• CSV File Creator - Create a CSV file from Excel workbooks / worksheets.
• Data Assistant for Excel - Make table form data applicable for Excel pivot tables.
• Data Cleaner for Excel - Remove spaces, Change text case, Replace / modify text, etc.
• Data Extraction Assistant for Excel - Extract data from one worksheet to many spreadsheets.
• Data Loader for Excel - Easy loading, processing, and transferring data .
• Filter Assistant - Filter multiple Excel spreadsheet columns at a time.
• Formula Manager - Manage all formulas in Excel worksheets easily.
• Formula Ref Changer and Sheet Index Creator - Change Excel formula reference types.
• List Assistant - Easy way to re-arrange rows in your Excel worksheet.
• Name Splitter - Split names into several Excel cells.
• Number Manager - Fill in the gaps in Excel workbooks.
• Report To Data Assistant - Convert text reports into Excel worksheet databases.
• Spreadsheet Assistant - Over 200 useful features for MS Excel.
• Text File Importer - Import text files with over a million lines into Excel.
• Text Manager - Change case in Excel, Join text to one cell, Remove spaces, etc..