How to Merge in Excel

Microsoft Office Excel offers a variety of features for customizing tables and charts that contain important information. The program also provides efficient ways to combine and summarize data from multiple files and worksheets. Common methods for merging in Excel include merging by location, by category, using formulas, or the program’s Pivot Table feature. Learn how to consolidate in Excel so that your information shows up in the master sheet and can be referenced whenever you need to report.

Table of Contents

Merge by position on Excel sheet

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/d/d9/Conspidate-in-Excel-Step-1-Version-3.jpg/v4-728px-Conspidate-in-Excel- Step-1-Version-3.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/d/d9/Conspidate-in-Excel-Step-1-Version-3.jpg/ v4-728px-Conspidate-in-Excel-Step-1-Version-3.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:” <div class=”mw-parser-output”></div>”}

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 sheet. Note: the range 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.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/4/45/Conspidate-in-Excel-Step-2-Version-3.jpg/v4-728px-Conspidate-in-Excel- Step-2-Version-3.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/4/45/Conspite-in-Excel-Step-2-Version-3.jpg/ v4-728px-Conspidate-in-Excel-Step-2-Version-3.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:” <div class=”mw-parser-output”></div>”}

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 Tops tool group. Select Conspidate.
Access the summary function feature in the Function pane to set up data consolidation.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/e/ea/Conspidate-in-Excel-Step-3-Version-3.jpg/v4-728px-Conspidate-in-Excel- Step-3-Version-3.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/e/ea/Conspite-in-Excel-Step-3-Version-3.jpg/ v4-728px-Conspidate-in-Excel-Step-3-Version-3.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:” <div class=”mw-parser-output”></div>”}

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

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/c/c3/Conspite-in-Excel-Step-4-Version-3.jpg/v4-728px-Conspite-in-Excel- Step-4-Version-3.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/c/c3/Conspite-in-Excel-Step-4-Version-3.jpg/ v4-728px-Conspidate-in-Excel-Step-4-Version-3.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:” <div class=”mw-parser-output”></div>”}

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

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/a/a8/Conspidate-in-Excel-Step-5-Version-3.jpg/v4-728px-Conspidate-in-Excel- Step-5-Version-3.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/a/a8/Conspidate-in-Excel-Step-5-Version-3.jpg/ v4-728px-Conspidate-in-Excel-Step-5-Version-3.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:” <div class=”mw-parser-output”></div>”}

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.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/5/5b/Conspidate-in-Excel-Step-6-Version-2.jpg/v4-728px-Conspidate-in-Excel- Step-6-Version-2.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/5/5b/Conspite-in-Excel-Step-6-Version-2.jpg/ v4-728px-Conspidate-in-Excel-Step-6-Version-2.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:” <div class=”mw-parser-output”></div>”}

Go to Data Tops Group. Find the Data tab, then click Conspite. 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

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/8/87/Conspidate-in-Excel-Step-7-Version-3.jpg/v4-728px-Conspidate-in-Excel- Step-7-Version-3.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/8/87/Conspite-in-Excel-Step-7-Version-3.jpg/ v4-728px-Conspidate-in-Excel-Step-7-Version-3.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:” <div class=”mw-parser-output”></div>”}

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.

Access PivotTable Feature

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/c/c5/Conspidate-in-Excel-Step-9-Version-3.jpg/v4-728px-Conspidate-in-Excel- Step-9-Version-3.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/c/c5/Conspite-in-Excel-Step-9-Version-3.jpg/ v4-728px-Conspidate-in-Excel-Step-9-Version-3.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:” <div class=”mw-parser-output”></div>”}

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 Conspidation Ranges and then click Next.
Select the command “I Will Create the Page Fields” and click Next.
Go to the Cplapse 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.

Advice

With the PivotTable option, you can also use the wizard to merge data for Excel worksheets with only one page, multiple pages, or no data fields.

Steps

Merge by position on Excel sheet

Define categories to merge Excel data

Use formulas to merge Excel data

Access PivotTable Feature

Advice

Related Posts