When you work in Microsoft Excel, you may find that your data has become more difficult to keep track of as the datasets are spread across separate sheets, pivot tables, etc. However, you don’t always need to use multiple sheets or Excel files to work with the data. especially if you work in a team.
To organize your data, you can combine data in Excel. You can combine sheets from separate files, combine separate Excel files into one, or use the consolidation feature instead to combine your data.
Here’s how to combine Excel files and data using these methods.
How to move or copy single worksheets in Excel
A typical Microsoft Excel file is split into different sheets (or worksheets), which are listed as tabs at the bottom of the Excel window. They act like pages, allowing data to be spread across multiple sheets in a single file.
You can move or copy sheets between different Excel files (or one file if you want to duplicate datasets).
- First, open the Excel file (s). With the Excel file window open that you want to copy from, click the worksheet you want to select at the bottom of the Excel window. You can select multiple sheets by holding Shift and clicking each sheet’s tab.
- To start copying or moving sheets, click the Home tab in the ribbon bar at the top.
- From here click “Format”> “Move or Copy Sheet”.
- In the Move or Copy field, select the Excel file you want to copy or move from the Field drop-down menu. Select where you want to place the sheets in the Before Sheet section. Excel moves sheets by default – check the Create a Copy checkbox if you would prefer to copy them.
- Click OK to move or copy sheets when ready.
The sheets you selected will then be moved or copied as desired, although this will not completely merge them.
– /
How to Merge Multiple Excel Files Together
You can use the Move or Copy Sheet tool in Excel to combine data from multiple Excel files. You can do this by selecting all active sheets in the file, then merging them into a single target file, repeating this process for multiple files.
- To do this, open the Excel files. With an open Excel file window that you want to move or copy to another file, select all the sheet tabs at the bottom of the window by holding down the Shift key and clicking on each sheet tab.
- Then click Home> Format> Move or copy sheet in the ribbon panel.
- In the Move or Copy dialog box that appears, select the target Excel file to be merged from the Field drop-down menu. Select where you want to place the merged sheets in the Before Sheet section. If you want to leave the original file intact, click Make Copy to copy the sheets rather than move them, then click OK to begin the move or copy process.
If you have multiple Excel files, you will need to repeat these steps to combine them into one file.
Use the consolidation tool to merge data together in Excel
Using the above methods, you can move and copy sheets between different Excel spreadsheet files. This moves the data, but doesn’t integrate it very well – the data is still stored in separate sheets.
To work around this issue, you can use the Consolidate tool in Excel to combine numeric data from multiple sheets into a new single sheet. Unfortunately, this process does not work with cells that contain text – you have to cut and paste that data manually, or create a VBA script in Excel to do it for you.
For this to work, your data must be presented the same across all sheets with the appropriate header labels. You also need to remove any blank data (e.g. blank cells) from your data.
- To combine data in Excel using this method, open the Excel files and in the target Excel file to combine the data, create a new worksheet by clicking the + (plus) button next to the sheet tabs at the bottom window.
- On the new sheet, click Data> Consolidate.
- Make sure Sum is selected from the Function drop-down menu in the Consolidate window. Click the button next to the Link input box to start selecting the first dataset – this is the data you want to combine. You can also enter a reference to a range of cells from yourself if you like.
- To select data with the mouse, click the sheet containing your worksheet, when the Consolidate – Link box appears, select the data, then click the Insert button.
- The range of cells will appear in the Link input field – click the Add button to add it to the All Links list.
- Repeat the steps above to select additional datasets by clicking Add to add each dataset to the sheet. If you have column or row labels in your data, make sure they are processed correctly by checking the Top row or Left column check boxes under Use labels.
- If you want to continue editing the data in the original single sheets, select the Create links to original data check box. This ensures that any changes to your original data are later reflected in the merged worksheet.
- When you are ready to combine the data into one sheet, click OK.
- The merged data will appear with a menu of options next to the row labels to be able to switch between different views of the data – click these options (for example button 1) to hide or view the data that you have merged partially or completely.
If you choose to create links to the original data, your new worksheet will act as a mirror version of your individual sheets and files. Otherwise, your data will be copied to this sheet and you can edit it directly.
Use embedded Excel data
Whether you decide to combine data in Excel into a single sheet or file, or if you prefer to spread your work across multiple files, these tips will help you keep order. When you’re ready, you can start sharing your Excel file with others to collaborate and analyze data effectively as a team.
If you’re worried about not being able to track changes in the merged data, don’t worry — you can easily track changes in Excel using the built-in tracking feature.
Let us know your tips for analyzing Excel data in the comments section below.
–