For this, you can use one of the merge & combine tools included with our Ultimate Suite for Excel. The built-in Excel Consolidate feature can summarize data from different sheets, but it cannot combine sheets by copying their data. How to combine Excel spreadsheets with Ultimate Suite Merge two Excel sheets into one by the key column(s).Overall, there are four ways to merge Excel worksheets into one without copying and pasting: In this case, you may want to employ one of the following techniques to automate the merge. But if you are to merge tens of sheets, errors with manual copying/pasting are inevitable.
To combine just a couple of sheets, you may not need anything else but the good old copy/paste. If you want to merge sheets in Excel by copying their data, the consolidation option is not the way to go. In particular, it works for numeric values only and it always summarizes those numbers in one way or another (sum, count, average, etc.) If you expand some group (by clicking the plus outline symbol), and then click on the cell with a certain value, a link to the source data will display in the formula bar.Īs you see, the Excel Consolidate feature is very helpful to pull together data from several worksheets. In this case, Excel will create links to your source worksheets as well as an outline like in the following screenshot. Select the Create links to source data box if you want the consolidated data to update automatically whenever the source data changes.Check the Top row and/or Left column boxes under Use labels if you want the row and/or column labels of the source ranges to be copied to the consolidation.In the same Consolidate dialog window, select any of the following options: If one or some of the sheets reside in another workbook, click the Browse bottom to locate the workbook.
Then click the Add button to have that range added to the All references Repeat this step for all the ranges you want to consolidate.