Consolidate numeric data in Excel

Use the Consolidate Sheets wizard to summarize data from multiple Excel worksheets into one. For example, if you have a table with the number of sold items from each of your regional stores, you can use data consolidation to quickly get these figures in a single report.

Video: How to consolidate several files in Excel

How to use the Consolidate Sheets wizard

Start Consolidate Sheets

To run the add-in, click the Consolidate Sheets icon on the Ablebits Data tab in the Merge group:
Run Consolidate Sheets by clicking on its icon on Excel's ribbon.

Step 1: Select the worksheets to merge and calculate

You will see a list of all open workbooks in the Consolidate Worksheets window:
See the list of all open workbooks in the Consolidate Worksheets Wizard window.

There are 3 buttons at the bottom of the window to help you pick out the needed Excel files:

  • To add a file for consolidation, click Add files... and browse for the necessary Excel workbook in the Select files window.
  • To delete a workbook from the list, click on its name and press the Exclude button.
  • Feel free to use the Collapse all/Expand all option to see all sheets in your files.
Tip. If you have a long list of sheets, take advantage of the checkbox next to the Worksheets column. It lets you instantly select and deselect all tables.

By default, the add-in pulls all data ranges from the chosen worksheets. To alter the highlighted area, select the sheet name in the list and click on the Select range icon next to it. You will see the table and a dialog box allowing you to pick the correct cells:
A dialogue window where you can pick the needed data range.

When the worksheets are added and the ranges are selected, click Next to proceed to consolidation options.

Step 2: Decide how to consolidate records

A couple of consolidation options will help you achieve better results:
Set the consolidation options.

Choose the function

Select the function to consolidate your numbers with. A drop-down list offers 11 summary functions you would normally find in Excel: sum, count, average, max, min, product, count numbers, StdDev, StdDevp, Var, and Varp.

Summarize by rows and columns

If your tables have the same headers but the columns are in a different order, choose to Consolidate by label.

  • You can tick off Use header label to consider the top row:
    Consolidate files by column headers.
  • Or choose Use left column label to look at the records in the leftmost column instead:
    Summarize values by rows.
  • Feel free to check both option boxes to summarize by both, rows and columns:
    Combine and calculate data by row and column labels.

Merge and calculate by position

If all your sheets have the same structure, choose the Consolidate by position radio button and disregard the labels in the source ranges:
Summarize data by position in the worksheets.

Click Next to choose the destination for the consolidated data in Excel.

Tip. You can return to a previous step and pick out other worksheets if you click the Back button.

Step 3: Choose a place for the result

Select one of the following locations for the resulting sheet:
Specify where you want to place the results.

  • Pick New workbook to create a new file with the summary.
  • Select New worksheet to add a resulting sheet to the current open workbook.
  • Or choose the destination for the summarized data manually by picking Custom location. Just click the Select range icon or type the top left cell address for your resulting table in the Custom location.

Click Consolidate to summarize data from multiple Excel files into one.

Post a comment

If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at support@ablebits.com.
Privacy policy Terms of use Contact us

Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.