Astha Sharma on 2021-08-10
Spreadsheets are the most common database majority of people use.
Often times you may find yourself with hundreds of spreadsheets of data pertaining to your main topic.
In this case, combining or merging your spreadsheets together can help you to organize your data.
This is a beginners' guide where we will show you three easy ways you can combine your spreadsheets.
Also check out our Ultimate Guides!
This seems like a no-brainier, but it only works if the columns or sheets you want to combine have the same order.
You want to make sure the files aren't too big either.
On your spreadsheet, right click on the column you want to copy and click on "Move or Copy..."
Click on the sheet you want to move it to and copy.
You can also take the column or sheet you need and select it all to copy.
You can combine sheets using Excel Formulas such as Vlookup and Index Match
To use Vlookup, click on Formulas and Lookup & Reference
Next scroll to where it says VLOOKUP
Click on the cell you want to add the column to
In the Lookup_value we are going to enter the common value in both of our sheets. I highlighted the "Name" column which is A2:A47
Go to the other sheet and highlight the entire table. This will be the value of Table_array which is Email!A2:B44
For Col_Index_num enter the column you we are merging, in this case is 2
Enter "FALSE" for Range_lookup and press enter
All the emails will show up in this column! As you may notice, the order of both the sheets were different so it matched where it needed to.
Some values will say "#N/A" this means there is no match found between the sheets.
For Index Match you can do the same thing as before and access it through Formulas and then Lookup & Reference, or write it in the column we want our merging to go.
So we write =INDEX(Email!$B$2:B$44,MATCH(A2,Email!$A$2:$A$44,0))
The final result will look the same as before.
You first have to pick how you want to combine the files. You can either Append or Join files.
You can always upload unlimited files under a total size of 1 MB. If you want to upload files that have a total size of over 1 MB, you can upgrade to a pro plan for just $4 per month. With the pro plan, you can upload however big files you want.
The tool will automatically align columns with the same name. The name must be completely identical. Completely.
The sample of the how the final file will look will appear. It only shows the first row from each file so it might happen that the sample row appears blank.
You can pick two options and click Download to download the .xlsx file. You need to give the file a name before downloading.
Picking Remove Duplicates will keep the first row of identical duplicates from the merged file. This will be done automatically when you download. The example below is done manually just for example purposes.
Picking Add Source File Names will add a new column with the source file name and sheet name that a row comes from.
You can always upload unlimited files under a total size of 1 MB. If you want to upload files that have a total size of over 1 MB, you can upgrade to a pro plan for just $4. With the pro plan, you can upload however big files you want.
You need to select three things in order for the files to join
There are 4 Join Types: Inner, Left, Right and Full. They all merge the file differently
Read all about joins in our A Brief (yet Comprehensive) Introduction to SQL Joins
Finally, you need to select the column(s) whose values you want to join on. You need to pick the column name for both the left and right files. You can pick as many columns as you want. The joining will be done on the values of all the columns selected.
Give the file a name and download your merged and joined file.
For additional features, check out our guide!
Here were three simple ways to combine your excel files!
Make sure to check out our articles for in-depth ways to merge through our other articles
If you have any questions, feel free to email us at admin@lovespreadsheets.com
