How to merge two sheets in Excel
By
SpreadCheaters
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
