How to merge two sheets in Excel

You can watch a video tutorial here.

Excel is a spreadsheet application in which each file or workbook has several sheets. This format lends itself easily to creating lists, tables, and databases. Excel file formats are frequently used for exporting data from applications as these can be easily consolidated and analyzed. When working with worksheets from multiple sources, you may need to merge two sheets. In this example, there are 2  sheets containing expenditures from 2020 and 2021. While these can be merged by checking each value independently, an easier way is to use the Consolidate feature of Excel. 

Note: To use this feature, the data being combined must be in the same format i.e. the same number of columns and the same column headers.

Step 1 – Select the destination

– Select the cell on the sheet on which the merged data is to be placed

Step 2 – Open the Consolidate box

– Go to Data > Data Tools
– Click on the Consolidate button

Step 3 – Select the first sheet

– Choose the Sum  function
– Place the cursor in the Reference box
– Select the data on the ‘2021’ sheet
– Click Add

Step 4 – Select the second sheet

– Place the cursor in the Reference box again
– Select the data on the ‘2020’ sheet
– Click Add 

Step 5 – Define the parameters

– Set the Function  as ‘Sum’
– Tick Use labels in:
>Top row
>Left column
– Click OK

Step 6 – Check the result

– The data in both sheets are merged into a single table
– The expenditure for the rows that have the same ‘Unit’ is added