Consolidate numeric data in Excel

Use the Consolidate Sheets tool 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 Consolidate Sheets

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. Select the sheets you want to consolidate. Besides, you can use the additional options:

See the list of all open workbooks in the Consolidate Worksheets Wizard window.

  1. Check Use the same range on all selected sheets to get the same range selected automatically across all the chosen sheets.
  2. If you tick the Use the same sheet names in other workbooks option and then start selecting sheets for consolidation, the tool will automatically tick sheets with the same name in other workbooks.
  3. 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.

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 then click the Exclude button.
  • 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.

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

Step 2: Decide how to consolidate records

The following consolidation options will help you achieve better results:
Set the consolidation options.

Select the function

Select the function to consolidate your numbers with. A drop-down list offers 11 aggregate 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 options 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 the previous step and pick out other worksheets by clicking 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.