The Ultimate Guide to Merging Spreadsheets
What do you even mean by merging spreadsheets?
The definition of merging spreadsheets is right in its name — we want to combine two or more spreadsheets into a single spreadsheet. This is a really essential skill to learn for any novice Excel users out there.
Interesting… what should I already know?
If you know how to make simple tables and organize data in Excel, you’re ready to go! As a general rule of thumb, the first row of data is usually composed of headers and each file (a workbook) can be composed of several pages (spreadsheets).
So, what exactly is in this article?
I’m going to start with the easiest types of spreadsheet merging and then move onto the harder types of merging. After that, I’ll include some common problems I ran into and some other methods of merging spreadsheets that you could use!
- Moving spreadsheets from one workbook to another.
- Merging all the spreadsheets in one workbook together.
- Merging one spreadsheet in multiple workbooks together.
- Merging multiple spreadsheets in multiple workbooks together.
- Joining spreadsheets with different structures together.
- Troubleshooting.
- Other methods.
Cool, so how do I move a spreadsheet from one workbook into another?
Moving a spreadsheet from one workbook to another is really simple. It can also make combining spreadsheets a lot easier in the future if you move everything into one workbook. There are several ways to do this, but I’ll show you what I believe to be the best methods.
I. First, begin by opening all the workbooks you want to edit.
II. Then, select the spreadsheet you want to move by clicking the tab with its name at the bottom (it should become highlighted). If you’d like to select more than one spreadsheet, you can do so by pressing down “Ctrl” while you click more tabs.
III. Now, right-click on your selection and click “Move or Copy”.
IV. From there, you can select which workbook you’d like to move your sheet(s) to under “To book:” and where exactly you’d like to insert them under “Before sheet:”.
V. After you click “Ok”, your sheet(s) will be transported!
Okay… now, how do I merge all the spreadsheets in one workbook together?
If all your headers are the same, it’s really easy to merge spreadsheets together. Excel allows you to add data from one spreadsheet after the data in another spreadsheet (appending) in several ways. I’ll go through two main methods so that you can decide which one you’d prefer!
Method One: Using Power Query
Power Query is a great included tool in Excel that allows users to edit and sort spreadsheets. You may also know it as the “Get & Transform Data” tool in the Data tab. It’s included in most newer versions of Excel, but you can download it through Microsoft if you don’t see it.
I. First, open a new Excel workbook and make sure you don’t have your workbook of interest opened. This is the easiest method I found for Power Query, but feel free to play around and figure out what works best for you.
II. Then, under the “Get & Transform Data” section in the Data tab, click “Get Data” → “From File” → “From Workbook” and select your workbook.
III. Once you’ve opened your file of interest, you’ll be prompted with a Navigator that shows you all the sheets in your workbook. Check the “Select Multiple Items” box and then check off all the spreadsheets you want to merge together.
IV. After you’ve completed, click “Transform data”.
V. Now you’ll have the Power Query Editor opened and we’re almost finished! On the left, you’ll see a list of your sheets under “Queries”, click on the sheet that you’d like to begin with (I typically leave it at whatever it automatically selects). You can also click on each sheet to see what data it includes!
VI. Next, click “Append Queries” under the “Combine” section. If you’re combining two sheets, simply select the sheet that you want to add to the sheet you just selected under “Table to append”. If you’re combining three or more sheets, select the “Three or more tables” option and use the “Add>>” button to choose the specific order you want your final spreadsheet to be in. Once you selected everything, click “Ok”.
VII. Now you should be back at the Power Query Editor again. The table you see on your screen is a preview of your merged spreadsheet! If it looks fine, click on the “Close & Load” button on the left to save your new spreadsheet into your workbook. You’re done!
After you’ve done this a couple of times, the steps seem less confusing. I like this method because it’s generally straightforward and included in Excel. However, it can seem a bit intimidating at first. If this isn’t something you’re comfortable learning, try the next method.
Method Two: Merge Spreadsheets
This method uses a website called Merge Spreadsheets.
I. First, you want to visit the website and upload the workbooks that you want to merge using the “Upload or Drop Files” button. We are going to leave the website on the default “Append” setting, which will allow us to add spreadsheets after one another. The “Join” setting will be used for differently structured data and we’ll be using it later in this article.
II. Then, go to the File Options of your workbook on the website and click “Import Other Sheets”. Here, you can select all of the sheets or specific sheets that you want to merge. Then, click the “Import Sheets” button when you’re done!
III. After you’ve selected everything, you can move around the columns using the arrows or rename the columns by clicking on the name.
IV. When you’re satisfied with the preview, you can select a file name and download the file at the top of the screen, next to the “Upload More Files” option.
The final product will look just like it did when we were using Power Query!
After reading these instructions, this second method may seem a lot easier. However, I do think both options are equally approachable and able to achieve the same end result.
Hmm, so how do I merge spreadsheets from different workbooks together?
If you have a lot of workbooks, but you’d like to merge one specific spreadsheet in each of them together, you can follow the steps below. Just like before, there are also two main methods for approaching this issue.
Method One: Power Query
Before we start using Power Query, we need to make some adjustments to our workbooks.
I. First, make sure that the sheet you wish to merge has the same name in each workbook (such as “Sheet 1”).
II. Then, we want to move all of our workbooks into one folder. From my experience, this seems to be the easiest way to merge multiple workbooks using Power Query.
III. Now, open a new Excel workbook and go to the Data tab like before. Once you’re there, click “Get Data” → “From File” → “From Folder” and select the folder that you just created.
IV. Then, select “Combine & Transform Data” under the “Combine” button.
V. A new Combine Files window should appear. Here, you should select the sheet name that you want to be merged in each workbook on the left. Typically, I only have one sheet per workbook when doing this type of merging so I just click the only sheet that shows up. However, if that isn’t the case, just make sure you have the correct name selected! When you select a name, a preview of the table from the first workbook in your folder should appear. After you’re done, click “Ok”.
VI. Now, you’ve arrived at the Power Query Editor. A preview should be displayed on your screen (this will include each row of data and which workbook it came from). To save this table to your new workbook, click “Close & Load” on the top left corner.
Using Power Query for this can be tedious if your data isn’t formatted properly. For that reason, Merge Spreadsheets (method two) may be a better option if you have data that can’t be easily renamed or moved.
Method Two: Merge Spreadsheets
I. Go to the Merge Spreadsheets website and upload all your workbooks.
II. Then, check and see if all of your sheets of interest are opened. If not, go to “File Options” and click “Import Other Sheets” to choose the proper sheet for each workbook. I found this method to be great if I had more than one spreadsheet in some workbooks that I wanted to merge. There’s also a “Delete File” button if you’d like to exclude a sheet from being merged.
III. Again, you can move around and edit the columns if you’d like. Once you’re satisfied, you can download your spreadsheet at the top right of the page.
For me, this method seemed a lot simpler but, they are both approachable and easy to learn.
Okay… but could I also merge multiple spreadsheets from multiple workbooks together?
Yep! We can combine multiple spreadsheets from different workbooks together using Power Query or Merge Spreadsheets, too!
Method One: Power Query
I. Let’s begin by opening all the workbooks you want to merge together.
II. Then, using the “Move or Copy” tool, move all the spreadsheets of interest into one workbook. As a reminder, you can find this by selecting all the sheets you’d like and right-clicking on them.
III. Now that we have all of our spreadsheets in the same workbook, we can combine them together as we did before. Just to go over it again, save your current workbook and exit the window. Then, open a new workbook.
IV. Now, go to the Data tab and click “Get Data” → “From File” → “From Workbook”.
V. Then, select your workbook, check the “Select Multiple Items” box, and check off all the spreadsheets you want to merge together.
VI. Afterward, click “Transform data”, then click “Append Queries” under the “Combine” section. Select and order the sheets like you would’ve before, then click “Ok”.
VII. Finally, click “Close & Load” and your merged spreadsheet should be in your new workbook!
If you have a lot of different workbooks you need to combine, only using Excel’s features can be tedious. This method may be fine with a couple of spreadsheets but I would definitely recommend using Merge Spreadsheets when dealing with larger amounts of data to avoid wasting time.
Method Two: Merge Spreadsheets
I. Like before, go to the Merge Spreadsheets website and upload all of the workbooks that you’d like to merge.
II. Then, under “File Options” and “Import Other Sheets”, select all the sheets in each workbook that you’d like to merge. I found the “Select All” switch to be extremely helpful for this.
III. Finally, edit the columns if you’d like and download the final file on the top right of the screen.
But, what if my data is structured differently in different spreadsheets? (How can I join spreadsheets?)
If you have data that may have one common column header (or more) but all other columns are different, you can still join them together! For instance, if you have food names and ID numbers in one sheet but food names and prices in another spreadsheet, they can still be easily merged so that you can match up the ID numbers and prices to each food item! Just like before, you can do this either in Excel or through Merge Spreadsheets.
Method One: Power Query
Using Power Query for this is still really easy, however, it is a lot more tedious than when we used Power Query for other types of merging (appending).
I. First, we’ll begin by turning all of our sheets into tables and loading them as a connection. To do this, start by selecting a cell in your first sheet and pressing Ctrl and T at the same time. Then, verify that the range for your table is correct and click “Ok”.
II. Next, under the “Get & Transform Data” section, click “From Table/Range”.
III. After arriving at the Power Query Editor, click for more options under “Close & Load” and select “Close & Load To”.
IV. After that, check “Only Create Connection” and click “Ok”. Now, repeat this for every single sheet except for the last one.
V. Once you’ve arrived at the last sheet, turn it into a table and open it in the Power Query Editor as you did before. Now, instead of closing and loading it, click “Append Queries” and append all the other tables like you normally would for identical headers.
VI. After all the sheets are appended and you’ve returned to the Power Query Editor, click all the column headers that are the same throughout the sheets (press Ctrl while clicking if there’s more than one). Then, right-click and select “Unpivot Other Columns”.
VII. Now, select on the column named “Attribute” and go to the Transform tab, where you’ll click “Pivot Column” in the “Any Column section”.
VIII. Within the new window, select “Values” under the “Values Column” and select “Don’t Aggregate” under “Aggregate Value Function” in the advanced settings. Finally, click “Ok” to see a preview of your joined spreadsheet.
IX. Once, you’re satisfied with the result, click “Close & Load” to save it to your workbook!
Method Two: Merge Spreadsheets
Using Merge Spreadsheets to join spreadsheets gives us many more options in much fewer steps.
I. First, open the website like usual and click on “Join” rather than leaving it at the default “Append” setting. Joining will allow you to combine spreadsheets that may have one column of the same data (or more) while all other columns are different. Unlike using Power Query, all that’s different here compared to appending is a single button.
II. Then, upload your spreadsheets and choose your join type. I typically choose Full Join (which Power Query will automatically choose for you), but you can play around and look at which preview fits your needs best.
III. Finally, click “Join Files” and then “Download File” to save your new spreadsheet!
I found this method to be much easier and I ran into far fewer problems than using Power Query but, if you’re willing to put in the effort, both methods work fine in the end.
Help, I ran into some problems! (Troubleshooting)
If you run into problems, let me just say that you aren’t alone. Here are the ones that I ran into during my own merging experience:
Wait, I just made a mistake in the Power Query Editor, how can I go back??
The editor lists all of the steps you made on the right of the screen, under “Applied Steps”. To undo your most recent step, simply click on the “X” next to the last step in that list.
How can I move or copy spreadsheets with a table? Why isn’t Excel letting me copy them?
For some reason, Excel won’t allow us to use the “Move or Copy” function on sheets that include tables. The easiest fix for this is to click on any cell in your table, then click “Convert to Range” in the Tools section of the Table Design tab. This will turn the table into a normal range and allow you to move it without any problems.
If you’d like to change it back into a table after moving it, simply select any cell in the range and press Ctrl and T at the same time. Then, check to make sure the range is correct and select “Ok”.
What if I have duplicates? Is there an easy way to remove them?
Power Query: Within the Power Query Editor, there is a “Remove Rows” tool under the “Reduce Rows” section. Within that is an option to “Remove Duplicates”, which will look at your first column and only keep the first instance of any identical cell values.
Merge Spreadsheets: After you’ve uploaded your spreadsheet, you’ll be able to see a “Remove Duplicates” checkbox next to the download options. This will look for identical rows and keep only the first instance.
How can I move around my rows or columns?
Power Query: To move columns, simply click and drag the column header within the Power Query Editor. To move rows, I found it easiest to click and drag the cells after you’ve closed and loaded the table into your workbook.
Merge Spreadsheets: To move columns, use the arrow keys under the column names. And, like for Power Query, I found it easiest to move rows in the normal Excel workbook after you’ve downloaded the spreadsheet.
Wait, you talked so much about Merge Spreadsheets, but are there other online tools?
Of course! There are many tools out there, here are the ones I’ve seen being used the most:
- AbleBits Merge Table Wizard - a program that you can download as an Excel add-in with additional features along with merging spreadsheets.
- Kutools - another Excel add-in with multiple features including merging spreadsheets.
- Easy Data Transform - a program that allows you to merge and reformat spreadsheets and tables.
- Synkronizer - an Excel add-in for merging spreadsheets.
- Excel Tool - a program for merging spreadsheets, along with some other features.
- ASAP Utilities - a program for merging different types of files, including spreadsheets.
The first two choices on this list are what I’ve seen most commonly recommended other than Merge Spreadsheets. However, there are might be many more resources out there, so feel free to do more research on your own!
What if I want more customizable options? Is there a more technical option?
If you’re into coding, working with Excel in your programs can be really fun! The most common way to connect with Excel is using Visual Basic for Applications (VBA), which is what most Microsoft Office programs are programmed with. There are many resources available online but here’s a great starter.
Another option is using an API. As a starting place, here’s an article about how to read spreadsheets using an API in Python. With an API, we’re able to use other languages like Java or R as well!
There are also some options that’ll focus primarily on coding. You can use Python again without an API or try using the new coding favorite, R. If you’re into using the Command Line, you can try that as well! Some other languages, such as Java, will require extra libraries in order to merge spreadsheets.
If those options don’t seem to fit you, try researching for other options! The possibilities for coding are endless, so you’ll definitely find something that’s perfect for you!
Wrapping Everything Up
Congratulations, you’ve made it to the end! Now you should know how to merge different types of spreadsheets in the best way for you!
If you have any further questions don’t hesitate to reach out at info@lovespreadsheets.com!
