In 2013 I published a technique to create the union of two tables in Excel. This post is still very popular and so I am revisiting this topic.
Excel 2016 (and possibly Excel 2013 but I have not tested) has this capability built-in and the new method should always be preferred to the one that I published. Excel 2016 has the concept of a Workbook Query which is analogous to the output of a SQL query. These Workbook queries are normally used to import data from a database for processing in Excel but they can just as easily work on existing Excel tables.
To create the union of two or more tables follow these steps,
- Create your two tables and name them nicely. In this picture I have named my table “Table_usa_sales”
- Create a Workbook Query by clicking Data > Get and Transform > From Table
- Click in your table
- Click From Table
- Click “Close and Load” . The system will create a new sheet containing the data that is dynamically linked to your original table. Rename this sheet “Q-USA”. You will never touch this sheet so you can hide it if you wish. I prefer simply to colour the tab grey.
- Now do the same for your second table and you should have something like this,
- Now we can create a new Workbook Query that is the union of our two queries,
- Rename the resulting query and sheet “Union”
- Test your result – update a row in one of your tables and then refresh your Union
Using this technique is a good first step to learning more about Excel’s powerful ETL (Extract Transform and Load) capability and how to generate reports from its internal data model. I may post about these in a subsequent article.
Pingback: Create the Union of two tables in Excel | Dr James Bayley
Pingback: Using Excel 2016 to unpivot data | Dr James Bayley
Marc Buzina said:
Great article, Thanks a lot. I used this to create a union in Excel 2016 – but for people using Excel 2013 this did not work. Do you have an idea of how to migrate this to Excel 2013?
Dr James Bayley said:
I don’t have Excel 2013 anymore but I seem to remember that there was an Microsoft add-in called PowerQuery. This might or might not address this issue.
I followed all your steps (a few times) but the last query generates merged data and not append date. In other words, the columns from both tables appear side by side in the final result.
Any ideas why I am getting a merged result vs append?
Dr James Bayley said:
I don’t know but I can guess. SQL is very sensitive to data types. Please make sure that both your source table are identical. For simplicity I would create two new _identical_ tables and then paste your data into them “values only”. Then when you create the queries inspect the data type of each column and ensure that they are the same for both tables.
Maria Lourdes Macay Situico said: