Excel 2016 has been designed to support the management of large amounts of data in a complete, complete and fully automated way..
When we have to manage multiple data in different sheets, Excel 2016 gives us the possibility of combining one or more sheets in order to centralize the information stored there in a single sheet or in a single file, in this way we will avoid having data dispersed in multiple sheets .
Today in TechnoWikis how to combine multiple sheets in Excel in a simple way..
1. How to combine two or more sheets in Excel 2016 with copy and paste
Although it is a method that we use almost daily, this method is the most practical when it comes to managing little data thanks to the practicality of copying and pasting, although it is not practical for jobs where many data are involved.
The important thing about this method is that it is not enough just to copy and paste since Excel 2016 offers multiple types of paste such as:
- Only values
- Formulas
- Formulas of formulas and numbers
- Maintain source format and many more options
For example, the option to paste Values ​​and Formulas is useful when we have a numerical cell that is calculated with a formula, there we can copy only the value or we can keep the formula. The default copy and paste option in Excel keeps the formula.
Step 1
To define the type of payment, we will copy the source data and on the destination sheet, click on the Paste button located in the Clipboard group and there the various options will be displayed:
Step 2
Within these special paste options we have a very practical one that is to transpose, that is, to exchange the rows by columns, this will be practical if the source data must be edited in a different way, to achieve this, we copy the data from origin and, from the Paste option, select Transpose:
Another useful paste option is Paste Link, this is a useful feature when you need to combine data from multiple sources. Paste link is responsible for linking the cell attached to the source cell, regardless of whether the source is in the same workbook or elsewhere, so, whenever the source is updated, the pasted cell will also be updated, this It is useful if we need both sources (source and destination) to be updated in real time..
2. How to combine two or more sheets in Excel 2016 using the Move and Copy option
This is an option integrated in Excel that will allow us to move the worksheets between the current workbooks in a simple way.
Step 1
For this case, we must have both books open, the one of origin of the data and the book where these data will be pasted, and if we want to have a view of both workbooks, we can go to the View menu and there select the option View in parallel :
Step 2
Now, we go to the book where the sheet to be copied is located, right click on it and select the Move or copy option:
Step 3
The displayed menu will show us a list of all open spreadsheets, there we must select the desired book and additionally we have options such as:
- Set the sheet at the beginning, at the end or somewhere in the middle of the existing worksheets of the target book.
- Activate the "Create a copy" box because, if we do not activate that box, the sheet will be removed from the original workbook and was pasted into the new book.
Step 4
When defining the destination book and activating the Create a copy box, click on OK and we will see that the selected sheet will be copied to the mentioned book:
3. How to make use of the consolidation feature to combine sheets in Excel 2016
The Consolidate function is a dynamic way to insert multiple worksheets into one. One of its advantages is the ability to automatically detect and organize row and column labels and combine identical cells from different sheets.
Step 1
For this, we will create a new book or worksheet where the consolidated data will be, and then we will open the original worksheets. We go to the new Excel 2016 workbook, we go to the Data menu and in the Data Tools group we will see the Consolidate option:
Clicking on this button will display the following:
Step 2
There we can see that each function will combine cells with the same tags in different ways: sum, average, keep the minimum / maximum value, count and many more options:
The boxes located at the bottom of the functions refer to where the labels should be found. If we have labels in the top row or in the leftmost column, we must check the corresponding boxes, if not, the spreadsheets will simply be mixed together as when they are copied and pasted.
Step 3
The "Create links with the source data" box will have the cells of the destination spreadsheet as a reference in the source spreadsheets so that the data is automatically updated without our intervention.
Now, we will click on the Browse button and we will locate the Excel workbook that we want to consolidate:
Step 4
We can click on the "Reference" button to open the selected spreadsheet and there we can highlight the data we want to merge:
Step 5
Once the data range is defined, click on the Add button and we will see the following:
Step 6
We can repeat this process for each Excel sheet that we want to merge. Finally, click on the Accept button and in this way the selected data will be consolidated:
With any of these methods, it will be possible to combine several Excel 2016 sheets with the aim of a much more precise control over the data to be worked without losing any of them and always using the application's integrated tools.