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:
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:
- Check Use the same range on all selected sheets to get the same range selected automatically across all the chosen sheets.
- 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.
- 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:
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:
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:
- Or choose Use left column label to look at the records in the leftmost column instead:
- Feel free to check both options to summarize by both, rows and columns:
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:
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:
- 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.
Please contact us here