Buzz
Today, Mytour will guide you through the process of combining data from two different sheets within a single Microsoft Excel workbook.
Steps
Double-click the workbook to open it in Excel. Ensure this workbook contains at least two sheets you intend to merge.
Click + to create a new spreadsheet. This button is located at the bottom of the workbook, to the right of the last sheet's name.
Click on cell A1. A single click is all it takes to select it.
Click on the Data tab at the top of the screen, located between “Formula” and “Review.”
Click on the Consolidate option in the “Data Tools” group on the top toolbar. The Consolidate dialog box will appear.
Select Sum from the “Function” dropdown menu. This is the first dropdown menu in the Consolidate dialog box.
Click the upward arrow next to the “Reference” field. The Consolidate dialog box will collapse and be relabeled as Consolidate – Reference.
- In some Excel versions, the arrow is gray or black. In others, you’ll see a small rectangle with a tiny red arrow inside.
Select the data in the first spreadsheet. Click the sheet name at the bottom of the screen, then click and drag over the data you want to merge. The data will now be surrounded by a dotted or dashed line.
Click the arrow in the Consolidate - Reference window. The larger Consolidate dialog box will reappear.
Click Add to the right of the “All references” box. This allows you to continue merging the selected data with data from another sheet.
Click the upward arrow in the “Reference” field. As before, the Consolidate dialog box will collapse and be relabeled as Consolidate – Reference.
Select the data in the second spreadsheet. Click the sheet name at the bottom of the workbook, then choose the data you want to merge.
Click the arrow in the Consolidate - Reference window.
Click the Add button. You’ll see the two selected data ranges appear in the “All references” box.
- If there are additional spreadsheets to merge, add their data in the same way you did with the first two sheets.
Check the boxes next to “Top row” and “Left column.” These options are located at the bottom-left corner of the Consolidate dialog box.
Click OK. The selected data will be merged and displayed in the new spreadsheet.
