If you are working with two or more MS Excel worksheets or even source data from different workbooks, it can be possible to consolidate and merge them into one single master worksheet. They can be combined by position if the worksheets have been created from a template and are identically structured; whereas they can be joined by category if they differ in structure but still use the same data labels. These operations can be performed by using the Consolidation Tool in the application, by following a few simple steps.
Instructions to join two worksheets together in Excel
Step 1: The first step involves opening all the worksheets that need to be consolidated. After you open Excel, click on the ‘File’ menu and select ‘Open’. A dialog box will appear from which you need to browse through and open the necessary worksheet that contains your required data. When the worksheet is found, click on it and select ‘Open’. This step needs to be repeated unless all the required worksheets are opened.
Step 2: You now need to create a new worksheet as a destination for the new or consolidated data. You can either open a new workbook by clicking on ‘File’ and selecting ‘New’ or add a worksheet to your current workbook by clicking on the ‘Insert’ tab on the toolbar and then selecting the ‘Worksheet’ option. In the new worksheet, you need to click inside the cell where you want to transfer the consolidated data. It needs to be ensured that enough room is left below and to the right of the destination cell for the transferred data.
Step 3: The data that you wish to consolidate needs to be selected. Click on ‘Data’ on the tool bar and select ‘Consolidate’ from the choices. When the Consolidation Tool dialog box opens, the first option you have is to choose the function. You may also need to perform a ‘Sum’ function which is already selected, as you are consolidating data. Type the range of the first source area in the reference field. Include only cell and sheet references if you are selecting data from different worksheets in the same workbook. However, you can additionally include a book reference if the data is from different workbooks. Once you enter the source area range in the ‘Reference’ field, click on the ‘Add’ button. Then you can notice that the range has been added to the ‘All References’ box. You can continue to repeat the step unless all the data sources have been added.
Step 4: Click on ‘Create links to source data’ that would enable the master worksheet to automatically update whenever any changes are made to the original sources. By checking the appropriate boxes under ‘Use labels in’, you can select the options accordingly if your source data has labels in either the left column or the top row.
Once you are finished, remember to click on ‘OK’ and the master worksheet would be created.