Combining Multiple Excel Tables into One

Five methods you can use

A few months ago I encountered a scenario I hadn’t faced before. I was dealing with a large number of financial transactions from multiple bank accounts that were being housed in separate Excel worksheets. I needed to consolidate them so they would appear in one single table. This was part of an interactive profit and loss system I was building.

It may sound like a simple task, but I needed an automated solution that would update in real time.

Like with any Excel conundrum you’re faced with, the first port of call is trusty Google. Surprisingly though, after scouring the internet for hours, I found it was bereft of solutions.

What didn’t help was I had to think about compatibility because the version of Excel the company was using was Excel 2016 for macOS. This was hardly ideal — because as many of you may already know — it is a shadow of the Windows equivalent. I like to keep up to date with the very latest version myself, which is why Microsoft 365 (formerly Office 365) for Windows is the best one to have.

After digging and poking around, I uncovered several different techniques. I’m going to show you five methods you can use in Excel to combine multiple tables. They each have pros and cons, but hopefully they will help you in the…