How to consolidate data in Excel
Summarize data from several worksheets
With Consolidate Worksheets Wizard you can quickly summarize data from multiple worksheets into one. This smart tool for Excel 2016 - 2007 provides a number of consolidation options to choose from. You can merge data from one or several workbooks and csv files without opening them, combine sheets by label or by position, create summary reports, and more.
Consolidate data from multiple worksheets into a single sheet
Use the Consolidate Worksheets tool to summarize data from separate sheets 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 roll up these figures into a single report.
- Start Consolidate Worksheets for Excel by clicking on its icon.
- You will see all open workbooks in the list on the Consolidate Worksheets window. To add a file for consolidation, click the Add files… You will get the Select files window where you can browse for the necessary workbook.
- Select the workbooks and worksheets for consolidation by ticking the checkboxes next to their names.
Tip. If you have a long list of sheets, take advantage of the check-box next to the Worksheets column. It lets you instantly select and deselect all tables.
- You can delete a workbook from the list on the add-in window by selecting it and pressing the Exclude button. Benefit from the Expand all option to see all sheets a workbook includes.
- By default, the add-in automatically pulls all data ranges from your selected worksheets. However, you can change them, if necessary. To alter the highlighted area, select the sheet name in the list and click on the Select range icon next to it. It will open the selected sheet and you will see a dialog box allowing you to pick the correct cells.
- Select the function to consolidate your data. Just click on the small down arrow and pick the correct option from the drop-down list. This is the standard list you would normally find in Excel: sum, count, average, max, min, product, count numbers, StdDev, StdDevp, Var or Varp. The summary data will be created based on this function.
- On the Select consolidation options step you can choose one of the two alternatives:
- Press the Consolidate by label radio button to summarize data depending on the location that contains headers. You can tick off Use header label, Use left column label, or select both
- Select the Consolidate by position radio button to disregard the labels in the source ranges.
- Click Next to choose the destination for the consolidated data in Excel. You can select one of the following radio buttons:
- Pick New workbook to create a new file with the results.
- 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.
- Click Consolidate to summarize data from multiple Excel worksheets into one.