Want to aggregate multiple Excel sheets into one? Let’s see together which tricks to use to speed up the procedure and avoid errors
For those who work in the accounting office and in administration, they know how tedious it can be to have to merge multiple Excel spreadsheets in one to be able to aggregate the data in the summary sheet to be delivered at the end of the month. This operation can be made easier by using simple functions integrated in all versions of Excel, so as to increase productivity.
In the following guide we will show you how to combine multiple Excel sheets into one, starting from the scenario where we have multiple XLS or XLSX files to combine together (the most common) but also the scenario where we have only one file but multiple spreadsheets to combine .
For those who want to combine Excel sheets from home we have also created a chapter dedicated to LibreOffice Calcthe free and open source version of Excel for everyone.
READ ALSO -> How to import data from a PDF into Excel
1) Merge multiple Excel files into one
If we have multiple Excel files to combine into one, we can proceed in an orderly and clean manner by opening Microsoft Excel on our PC, opening a new Empty workbookby pressing at the top of the menu Databy clicking on Retrieve data and finally clicking on From file -> From excel workbook. Now all that remains is to select the sheet to import and press down on Load to add it to the new summary sheet.
The system allows you to add one sheet at a time: if we have other Excel documents to join, all we have to do is press the + icon at the bottom to add a new sheet and repeat the procedure seen above, so as to have all the files in one single workbook.
2) Merge Excel spreadsheets into one
What if instead we wanted to merge multiple Excel sheets already present in the same workbook? In this case we don’t have to do a simple copy and paste (which could ruin the formatting of the data) but we can use the functions STACK.VERT e STACK.ORIZto be chosen according to your needs (the first combines the data vertically, the second horizontally).
To combine multiple Excel sheets into one vertically, press down on the + icon to open a new spreadsheet, press on cell A1 and type the following formula inside:
=STACK.VERT(
Now we select the function suggested by Excel (in the window below the entry field) to activate it, press on the first sheet to be aggregated, select all the data to be merged, hold down shift on the keyboard, press on the second sheet and confirm the present selection (or change it as needed); now all that remains is to press on Enter o Enter on the keyboard to merge all data into a summary sheet.
To stack the data horizontally we will have to use the following command:
=STACK.ORIZ(
The steps to follow are the same as for vertical aggregation, remembering to keep the key pressed shift when we switch from one sheet to another.
This function is very effective if the data in the various sheets occupy the same cells and columns, since in the following sheets we won’t have to change the selection; if the cells or columns are different we will first have to move them to the right position (same cells and columns of the first sheet) and only then stack them with the commands seen above.
3) Combine multiple Excel files in LibreOffice Calc
At home you can combine multiple excel sheets into one using LibreOffice Calcpart of the free LibreOffice suite and able to provide support for Excel sheets without having to spend a euro.
To merge several files into one we open LibreOffice Calc on our PC, we press up on insertwe press at the top right of the menu insert and click on Insert sheet from file.
A window will now open where you can choose which Excel file to import into the first sheet; we can repeat the operation for all the other files to be merged, so as to have a single workbook with all the merged sheets.
At the time of writing you cannot use the STACK functions in LibreOffice: for now they are exclusive to Excel. However, nothing prevents us from proceeding the “old way” by copying the cells and stacking them in a new spreadsheet.
Conclusions
Once again, Microsoft Excel proves to be a must-have program in the workplace: the new STACK functions allow you to combine multiple data from many different sheets, lining them up or lining them up as needed.
If instead we just need to merge multiple Excel files into one we can use both the Excel data import system and LibreOffice, since both allow the addition of files within individual sheets in the workbook.
Still on the subject of Excel, we invite you to read our guides on how to insert images in an excel sheet e how to make a table in excel.