, ,

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,

  1. Create your two tables and name them nicely. In this picture I have named my table “Table_usa_sales”
    • usa table
  2. Create a Workbook Query by clicking Data > Get and Transform > From Table
    1. Click in your table
    2. Click From Table
    3. 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.create Workbook Query
  3. Now do the same for your second table and you should have something like this,the queries
  4. Now we can create a new Workbook Query that is the union of our two queries,unionappendunion done
  5. Rename the resulting query and sheet “Union”union table
  6. 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.