Share


Ultimate Guide To Merging Spreadsheets

by Clarissa Ng on 2020-06-26

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!

  1. Moving spreadsheets from one workbook to another.
  2. Merging all the spreadsheets in one workbook together.
  3. Merging one spreadsheet in multiple workbooks together.
  4. Merging multiple spreadsheets in multiple workbooks together.
  5. Joining spreadsheets with different structures together.
  6. Troubleshooting.
  7. 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.

Merge Excel Files

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”.

Excel Spreadsheet

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:”.

Excel Spreadsheet

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!

Excel Spreadsheet

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.

Excel Spreadsheet

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.

Microsoft Excel Spreadsheet

IV. After you’ve completed, click “Transform data”.

Microsoft Excel Spreadsheet 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!

Microsoft Excel Spreadsheet

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”.

Microsoft Excel Spreadsheet Append Queries

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!

Microsoft Excel Spreadsheet Query Settings

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

I. Go to the Merge Spreadsheets website and upload all your workbooks.

Merge Spreadsheets

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.

Merge Spreadsheets File Options

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!

Merge Spreadsheets

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.

Excel Move or Copy

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”.

Excel Spreadsheets

V.Then, select your workbook, check the “Select Multiple Items” box, and check off all the spreadsheets you want to merge together.

Excel Spreadsheets

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".

Excel Spreadsheets Transform Data
Excel Spreadsheets Append Queries

VII. Finally, click “Close & Load” and your merged spreadsheet should be in your new workbook!

Excel Spreadsheets Query

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.

Merge Spreadsheets

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.

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”.

Excel Spreadsheets

II. Next, under the “Get & Transform Data” section, click “From Table/Range”.

Excel Spreadsheets Table Range

III. After arriving at the Power Query Editor, click for more options under “Close & Load” and select “Close & Load To”.

Excel Spreadsheets Close and Load

IV. After that, check “Only Create Connection”and click "Ok". Now, repeat this for every single sheet except for the last one.

Excel Spreadsheets Import Data

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.

Excel Spreadsheets Append Queries

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”.

Excel Spreadsheets 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”.

ExcelSpreadsheetsPowerQuery

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.

PivotExcel Spreadsheets

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)


Frustrated Man

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:

  1. AbleBits Merge Table Wizard- a program that you can download as an Excel add-in with additional features along with merging spreadsheets.
  2. Kutools- another Excel add-in with multiple features including merging spreadsheets.
  3. Easy Data Transform- a program that allows you to merge and reformat spreadsheets and tables.
  4. Synkronizer- an Excel add-in for merging spreadsheets.
  5. Excel Tool- a program for merging spreadsheets, along with some other features.
  6. 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?

Code

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!

Let us know what you think