How to merge two or more Excel spreadsheets 

There are two ways to merge Excel spreadsheets: The long way and the short way. This article covers both.

The long way is manual. It involves downloading and importing .xls files and performing a series of VLOOKUPs. It is typically followed by a few data cleaning steps. The short way is using Excel in combination with a free no-code tool like KNIME Analytics Platform. 

Either way, you can save time by being smart about exactly how you merge your tables to minimize data cleaning steps afterward.

The easiest option is to simply add the complete data set from one spreadsheet to another (i.e. to “concatenate”). Another option is to combine the data based on specific criteria, like joining two tables based on, say, matching rows. An example would be if one dataset had countries and populations and a second dataset had countries and size. If you wanted countries, population and size based on countries that appear on both lists, you could merge the two based on that criteria. If you want to do a more sophisticated merge, it’s best to decide beforehand so there’s less work later.

This article runs through how to merge any amount of data from any spreadsheet using either just Excel or the no-code KNIME Analytics Platform. The former lets you remain in the tool, while the latter lets you create an automated process that you can re-run at any point.

The open source and free KNIME Analytics Platform lets you build visual workflows to automate and augment spreadsheet work. This lets you more efficiently do things like combine data from numerous spreadsheets, as well as other tasks – like aggregating data from other tools, transforming the data into a consistent format, cleaning data, etc. – in a way that’s repeatable. That way, you can spend less time on data aggregation in the long term.

The tool also comes with a repository of community-built workflows, so in many cases, you can find a similar workflow to your use case. The below workflow, for example, is available to download from the community hub here. You can drag and drop it onto your canvas and adjust for your own needs.

People who regularly work with larger datasets (and don’t have the luxury of copy and paste) use joins and concatenation to combine the data exactly the way they’d like it. Concatenation is essentially appending data—as in adding a bunch of rows, or a bunch of columns in addition to all the rows or all the columns that are already there.

Joins, however, are used when you have two or more tables with a common column. These can further be broken down based on how exactly you want them combined:

The easiest way to concatenate data without leaving Excel is to use a VLOOKUP. Suppose you have data in Sheet1 and Sheet2 within the same workbook, and you want to concatenate data from columns A and B in Sheet1 with columns C and D in Sheet2. In the new sheet, you can use a formula like this in the first cell (assuming your data starts in row two):

Drag the formula down to concatenate data for each row.

If you want to consistently merge spreadsheets that are in this format (as well as add other data cleaning steps) then it’s more error-proof to do so in KNIME Analytics Platform. KNIME is also a better alternative for merging spreadsheets that house large amounts of data.

For that, you can use two Excel Reader nodes to pick the two spreadsheets you want to combine, and follow it with  a Concatenate node. Then, you can decide how you want to export it—say, as an Excel file in the same folder on your Desktop.

To combine two spreadsheets using an inner join in purely Excel, you can use a VLOOKUP. First, let’s assume we have two sheets named Sheet1 and Sheet2 and you’d like to combine them based on a column called CommonColumn. In the new sheet, you can use a formula like this (assuming data starts in row two):

This formula assumes that the common column in "Sheet1" is in column A, and the data you want to retrieve from "Sheet2" is in columns A and B (although, of course, you can select many more columns). Drag this formula down to apply it to each row in your new sheet.

However, you need slightly different formulas for different joins. See the full chart below:

This can be made repeatable and error-proof by using a KNIME workflow instead. Again, you need two Excel Reader nodes, followed by a Joiner node, and, finally an Excel Writer node. 

You can set whichever kind of join you’d like by hitting the gear icon above the node.

When you configure the Joiner node, you’ll get a handy visual to make sure you’re combining based on the criteria that you’d like.

You’ll also need to choose the column that includes the criteria you’d like for matching. In our example, if both Sheet1 and Sheet2 have CustomerIDs, that’s what would be selected. 

KNIME Analytics Platform is used by people who work with data across all disciplines and experience levels. When you automate some of your data work (like merging spreadsheets), you get time back and increase the transparency and repeatability of that work. That opens up time to start doing even cooler and more interesting things with data, like text mining, geospatial analysis, or harnessing the power of machine learning.

KNIME allows anyone to do data work at any level of sophistication without code, if they don’t want to. Download it today and use it free forever.