How to Merge Two or More Excel Spreadsheets 

If you're a business analyst, you probably merge Excel files every month: regional reports, team spreadsheets, exports from different systems.

Merging means one of two things. You either append rows from several files into one dataset (concatenation), or you match rows across files using a common column (a join).

Excel formulas handle small, one-off merges well. When the same merge comes round every week or month, a repeatable process saves time and removes the manual steps where errors start.

This guide covers both: how to merge in Excel, and how to build the same merge once in Knime and reuse it.

Manual: you repeat the same formulas and copy-paste steps every time new files arrive.

Repeatable: you build the merge once in a visual tool like Knime and run it again whenever new data arrives.

Either way, choosing the right kind of merge up front saves you cleanup afterward.

If you merge Excel files regularly, the biggest gain comes from removing the manual work altogether.

In Excel, you work directly in the file. Every formula you apply changes your data, and after several steps, there may be no easy way to undo a mistake or see where it started.

Knime works differently. You build the merge as a series of connected steps, and your data passes through them to produce the result. Your original files stay intact. Every step is visible, so you can see exactly how the result was built and find an error where it happened. When next month's files arrive, you run the same process again instead of rebuilding it.

Here's how to merge files in Excel and in Knime, using both concatenation and joins.

• Concatenation appends data. You add rows (or columns) to the ones already there. Example: you have one Excel file per region and want a single dataset for all regions.
• A join matches data. You combine two or more tables that share a common column. Example: one file holds customer transactions, another holds customer details, and you join them on Customer ID.

Joins come in several types, depending on which rows you want to keep.

In Excel: 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) and drag the formula down to concatenate data for each row.

In Knime: 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. 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.

In Excel: 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:

In KNIME: You can turn this task into a repeatable and error-proof process 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. 

Excel is a powerful, flexible tool, and for many tasks it's all you need. It's the right choice when:

• You merge one or two files occasionally
• The steps are quick to repeat by hand
• Your datasets are small
• You're doing a one-off analysis

It's time to switch when:

• You merge files every week or month
• You combine many files at once, such as one per region or team
• Files are growing and Excel is slowing down
• You spend time fixing formulas or checking for errors
• Others need to use or trust the process you've built

Many teams start with Knime by automating one task, such as merging spreadsheets or preparing data for a monthly report. Once that runs on its own, it's a short step to combining data from more systems, working with larger datasets, and moving on to machine learning, geospatial analysis, and AI agents.

Download Knime for free and build your first merge.