How to Merge in Excel

Merge by position on Excel sheet

The data in each sheet needs to be displayed as a list. Make sure you delete all blank columns and rows with the same info labels.

Add and layout each column range to split the worksheet. Note: ranges should not be added to the master sheet that you plan to use for merging.

Highlight and name each range by selecting the Formulas tab, clicking the down arrow next to the Define Name option, and choosing Define Name (operation may vary depending on the Excel version). Then, enter a name for the range in the Name box.

Prepare to merge Excel data. Click in the upper left cell where you want to place the merged data on the master sheet.

Go to the Data tab on the main sheet, then select the Data Tools tool group. Select Consolidate.

Access the summary function feature in the Function pane to set up data consolidation.

Enter the scope name in the Summary Function feature. Click Add to start the merge process.

Update consolidated data. Select Create Links for the Source Data box if you want to update the data source automatically. Leave this box blank if you want to update the data after a manual merge.

Define categories to merge Excel data

Repeat the steps at the beginning to set up the data in a list format. In the main sheet, you click in the upper left cell where you want to put the data after merging.

Go to Data Tools Group. Find the Data tab, then click Consolidate. Use the summary function feature in the Function pane to set up the data consolidation process. Name each range and then click Add to complete the merge. Then repeat the process to update the merged data as described above.

Use formulas to merge Excel data

Start with the main Excel sheet. Type or copy the labels of the rows and columns that you want to use to merge the Excel data.

Select the cell where you want to merge the results. On each worksheet, enter the formula that references the cells to be merged. In the first cell where you want to include the information, enter a formula similar to this: =SUM (Department A!B2, Department B!D4, Department C!F8). To merge all Excel data from all cells, enter the formula: =SUM (Department A:Department C!F8)

Access PivotTable Feature

Create a PivotTable report. This feature allows you to merge Excel data from multiple ranges with the ability to reorder items as needed.

Press Alt+D+P to open the PivotTable and PivotChart wizard. Select Multiple Consolidation Ranges and then click Next.

Select the command 'I Will Create the Page Fields' and click Next.

Go to the Collapse Dialog to hide the dialog box on the sheet. On the worksheet, you select the range of cells > Expand Dialog > Add. Under the page field option, enter 0 and click Next.

Select a location on the worksheet to create the PivotTable report, then click Finish.

Kareem Winters

Update 24 February 2022