How to Merge Excel Sheets?

You can merge Excel sheets by consolidating data from multiple sheets into one, or by copying and pasting data from multiple sheets into one.

Consolidating Data from Multiple Excel Sheets

This method combines data from different sheets into a summary sheet, using a selected function.

1. Navigate to the Data Tab: Open your Excel workbook and go to the Data tab in the ribbon.
2. Select Consolidate: Look for the Data Tools group and click the Consolidate option.
3. Choose a Function: A dialog box will appear. In the Function box, select the type of calculation you want to perform on the data. Examples include:
   • Sum: Adds up all values.
   • Average: Calculates the average of all values.
   • Count: Counts the number of values.
   • Max: Finds the maximum value.
   • Min: Finds the minimum value.
4. Select Data Ranges: In each source sheet, select the data you wish to include in the consolidation. Excel will automatically enter the file paths for you into the 'All References' area of the Consolidate dialogue box.
5. Add all references To add all source ranges click on the 'Add' button after selecting each range.
6. Complete Consolidation: Click OK to create a new sheet with the consolidated results.

Example

Suppose you have three sheets named "Sheet1", "Sheet2", and "Sheet3" with sales figures for different months, using the same structure ( same headers in the same order across the sheets). To consolidate their sales figures by adding them together, you would:

• Navigate to Data > Consolidate
• Select 'Sum' as the function
• For each sheet, select the sales data range, and click the add button to add it to the all references box.
• Click Ok.

Excel will then create a new sheet with the consolidated totals from all three sheets, using the sum function.

Copying Data from Multiple Excel Sheets

If you prefer a simple copy and paste method, follow these steps:

1. Open the sheets: Open all the Excel sheets you want to merge.
2. Select and copy: In the first sheet, select the data you wish to merge and press Ctrl + C (or Cmd + C on Mac).
3. Paste into the destination: Go to your destination sheet, click where you want the copied data and then paste using Ctrl + V (or Cmd + V on Mac).
4. Repeat for all sheets: Repeat steps 2 and 3 for each sheet you need to copy, pasting below the previously pasted data.

Example

You could use this method to create a master list by copying employee names and IDs from separate department sheets into a single sheet.