Buzz
Microsoft Office Excel offers a wide range of features for customizing tables and charts containing important data. It also provides effective methods to combine and summarize information from multiple files and worksheets. Common ways to merge in Excel include merging by position, category, using formulas, or utilizing the program's Pivot Table feature. Let's explore how to merge in Excel to have your data displayed in the master sheet and be available for reference whenever you need to create reports.
Steps
Merge by Position in an Excel Worksheet
The data in each worksheet must be presented in list form. Ensure that you remove any blank rows and columns with identical information labels.
- Insert and organize each column range to divide the worksheet. Note: avoid adding ranges to the master sheet you intend to merge.
- Highlight and name each range by selecting the Formulas tab, clicking the downward arrow next to Define Name, and choosing Define Name (the steps may vary depending on your Excel version). Then, input a name for the range in the Name box.
Prepare to merge data in Excel. Click on the top-left cell where you want the merged data to appear in the master worksheet.
- Go to the Data tab on the master worksheet, then select the Data Tools group. Choose Consolidate.
- Access the summary function in the Function box to set up the data merging process.
Enter the range name in the Summary Function feature. Click Add to begin the merging process.
Update merged data. Select Create Links for the Source Data cell if you want to automatically update the data source. Leave this cell blank if you prefer to manually update the data after merging.
Identify the categories to merge Excel data
Repeat the steps from the previous section to organize data in list format. On the master worksheet, click the top-left cell where you want the merged data to be placed.
Navigate to the Data Tools Group. Locate the Data tab, then click on Consolidate. Use the summary function in the Function box to set up the data merging process. Name each range and click Add to complete the merge. Repeat the process to update the merged data as described earlier.
Use formulas to merge Excel data
Start with the main Excel worksheet. Enter or copy the labels for the rows and columns you want to use to merge your Excel data.
Select the cell where you want the merged results to appear. In each worksheet, enter the cell reference formula needed for merging. In the first cell where you want to include the information, enter a formula like: =SUM (Department A!B2, Department B!D4, Department C!F8). To merge all Excel data from all cells, use this formula: =SUM (Department A:Department C!F8).
Access the PivotTable feature
Create a PivotTable report. This feature allows you to merge Excel data from multiple ranges while providing the ability to rearrange categories as needed.
- Press the keyboard shortcut Alt+D+P to open the PivotTable and PivotChart Wizard. Select Multiple Consolidation Ranges and click Next.
- Select the option “I Will Create the Page Fields” and click Next.
- Go to the Collapse Dialog box to hide the dialog box on the worksheet. On the worksheet, select the range of cells > Expand Dialog > Add. Below the page field options, enter 0 and click Next.
- Select a location on the worksheet to create the PivotTable report, then click Finish.
Tips
- With the PivotTable option, you can also use the wizard to merge data for Excel worksheets with one sheet, multiple sheets, or no data fields.
