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.