Copy and Paste
This method sounds almost laughably simple, but don’t dismiss it too quickly. If you just need to move data between a few spreadsheets, this is the fastest and simplest way – though it is impractical for large-scale jobs. Excel’s copy-paste options don’t come in just one flavor, either! When you copy your data and go to paste it, right-click on where you want to paste and check out the “Paste Special” options. Here are a few of the most useful: Values/Formulas: If you have a numeric cell that is calculated using a formula, you can choose to either copy only the value, or you can keep the formula. The default copy-paste option in Excel keeps the formula.
Transpose: Changes the rows to columns and the columns to rows. This can be very helpful if, for example, you’ve received spreadsheets from people with different ideas about where the labels should go.
Paste Link: This is a handy feature when combining data from multiple sources. This will link the pasted cell to the source cell, whether the source is in the same workbook or somewhere else. Whenever the source is updated, the pasted cell is updated as well, so if you’re looking to combine data from spreadsheets into a continuously-updated master sheet, you can use this paste function to easily link the cells.
Use the “Move or Copy” feature to combine workbooks
If you’re working with more than a few workbooks/worksheets, copy-pasting will get old fast. Luckily, Excel has a built-in feature that will move worksheets between workbooks.
Open the workbook you want to move sheets into and the workbooks you want to move sheets from. Excel requires both the source and the destination workbook to be open to move worksheets. The example below shows them both on the same screen using the “View Side by Side” option.
Right-click on one of the sheets you want to move, and when the menu opens, click the “Move or Copy” button.
The menu should show you a dropdown list of all the open spreadsheets. Select one to be your destination. Choose if you want your sheet to be at the beginning, end, or somewhere in the middle of the destination workbook’s existing worksheets. Don’t ignore the “Create a copy” checkbox! If you don’t check it, your sheet will be deleted from your source workbook.
Check your destination workbook. The moved or copied worksheet should appear with the same name as in the source workbook, but may have a (2) or another number after it if there is a duplicate name in the destination.
Use the Consolidate feature to combine worksheets
The Consolidate feature is a very flexible way to push multiple worksheets into one. The best thing about it is that it automatically detects and organizes your row and column labels and merges identical cells from different sheets.
Create a new workbook or worksheet for your consolidated data, then open the source worksheets.
Open the new spreadsheet and go to the “Consolidate” button in the “Data” tab.
Notice that there are several functions listed here. Each function will combine cells with the same labels in different ways — sum, average, keep the minimum/maximum value, etc.
The boxes below the functions ask you where it should find your labels. If you have labels in your topmost row or leftmost column, check the appropriate boxes. Otherwise, your spreadsheets will just mash themselves together like a copy-paste.
The “Create links to source data” box will have the cells in your destination spreadsheet reference the ones in your source spreadsheets so that the data will automatically update.
Hitting the “Browse” button opens your file explorer. Select the spreadsheet you want to consolidate.
Click the “Reference” button and open the spreadsheet you just selected. Here you can highlight the data you want to merge.
Hit the Enter key and then the “Add” button. This should put the selected data into a merge list.
Repeat the above steps for as many worksheets/workbooks as you want to combine.
Click “OK” to combine the selected data into your new spreadsheet and check that it worked correctly.
Conclusion
These methods combine a user-friendly interface with a decent amount of power. There aren’t many jobs that these three tools, in some combination, won’t be able to tackle, and they don’t require any experimentation with VB code or macros. As with all things in Excel, though, it helps quite a bit if your data is well-organized before you start — name your workbooks and worksheets logically, check that your rows and columns are laid out the way you want, and make sure your references line up!
