How to Perform Union Query in Excel (with Detailed Steps)

Get FREE Advanced Excel Exercises with Solutions!

When working with Excel, sometimes we get multiple tables with one common column or row. As a result, it is needed sometimes to merge the tables for better data representation. Now, this can be achieved through the Union query in Excel. In this article, I will show you all steps to perform a Union query in Excel.


How to Perform Union Query in Excel: Step by Step

Say, you have 3 individual datasets Sales Data, Product ID, and Region List. Now, these datasets are related to each other. So, you want to perform a union query between them to merge them into one table. Follow the steps below to achieve this.

Sample Dataset to Perform Union Query in Excel


1st Step: Collect Data and Create New Query Connection

To perform a union query, you must prepare the datasets into tables and create connections.

  • To do this, first, click on any cell inside your required dataset.
  • Following, go to the Data tab >> From Table/Range tool.

Create Tables from Data Range to Perform Union Query in Excel

  • As a result, the Create Table window will appear. Here, click on the OK button.

Create Table Window

  • Afterward, the Power Query Editor window will open automatically.
  • Following, at the Query Settings pane, under the Properties group, write your table’s name (SalesData here) in the Name text box.

Query Settings Pane

  • Afterward, under the File tab, click on the Close & Load arrow and select the option Close & Load To…

Choose Close & Load To... Option

  • As a result, the Import Data window will appear.
  • Following, choose the Only Create Connection option and click on the OK button.

Import Data WIndow to Perform Union Query in Excel

  • As a result, the SalesData table will be prepared to merge.
  • Now, follow similar procedures for all the other datasets to prepare them to perform a union query.

Finally, you can see this on the right side of your Excel file in the Queries & Connections window.

All Tables to Perform Union Query in Excel

Read More: How to Create Union of Two Tables in Excel


2nd Step: Proceed Next to Combine the Queries

Now, you can perform a union query between these tables.

  • First and foremost, go to the Data tab >> Get Data tool >> Combine Queries option >> Merge option.

Choose Merge Option to Perform Union Query in Excel


3rd Step: Make Necessary Selections in the Merge Window

  • As a result, the Merge window will appear.
  • Now, we want to perform a union query between the SalesData table and the ProductID table first.
  • To do this, at the first Preview window, choose the SalesData table, and at the second Preview window, choose the ProductID table.
  • Afterward, click on the common column (Product column here) from both tables.
  • Following, choose the Join Kind as Left Outer option and click on the OK button.

Merge Window to Perform Union Query in Excel

Read More: How to Do Union of Two Columns in Excel


4th Step: Place the Values Properly

  • As a result, the merge will take place, but the values haven’t been placed properly.
  • Now, click on the double arrow button inside the ProductID header.

Click on Double Arrow Button

  • As a result, a dropdown will appear.
  • Subsequently, tick on the Product ID option only >> untick the Use original column name as prefix >> click on the OK button.

Product ID Dropdown

  • Finally, you will see the two tables are merged properly now.

Performed Union Query Between Two Tables


5th Step: Proceed to Create Another Connection

  • Now, as you need to merge another table, go to the File tab here >> Close & Load arrow >> Close & Load To… option.

Choose Close & Load To... Option

  • Consequently, the Import Data window will appear.
  • Now, click on the Only Create Connection option and click on the OK button.

Import Data Window


6th Step: Make Necessary Selections in the Merge Window (Same as the 3rd Step)

  • As a result, you will be able to import the merged table as the Merge1 table.
  • Now, repeat the merging procedures above to merge this Merge1 table and the RegionList table.
  • So, at the first Preview window, choose the Merge1 table, and at the second Preview window, choose the RegionList table.
  • Following, click on the Country column on both tables >> choose the Left Outer as Join Kind option and click on the OK button.

Merge Window to Perform Union Query in Excel

  • As a result, the RegionList column will also be merged into the previously merged table.
  • Now, for proper values, click on the double arrow button on the RegionList header.

Click on Double Arrow Button

  • Consequently, a dropdown will appear.
  • Following, tick on the Region option only and untick the option Use original column name as prefix.
  • Subsequently, click on the OK button.

Region Header Dropdown

  • As a result, you will get the proper values in the Region column.

Performed Union Query Between Three Sheets

Read More: How to Make Union of Two Sheets in Excel


Last Step: Close and Load Data

  • Now, under the File tab, click on the Close & Load button.

Click on Close & Load Button

Thus, you will be able to merge all three tables with a union query in Excel.

Performed Union Query Between All Data Ranges


Download Practice Workbook

You can download our practice workbook from here for free!


Conclusion

So, in this article, I have shown you all the steps to perform a union query in Excel. I suggest you read the full article carefully and practice accordingly. I hope you find this article helpful and informative. You are welcome to comment here if you have any further questions or recommendations.


Related Article


<< Go Back to Excel Union | Excel Operators | Excel Formulas | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Tanjim Reza
Tanjim Reza

Md. Tanjim Reza Tanim, a BUET graduate in Naval Architecture & Marine Engineering, contributed over one and a half years to the ExcelDemy project. As an Excel & VBA Content Developer, he authored 100+ articles and, as Team Leader, reviewed 150+ articles. Tanim, leading research, ensures top-notch content on MS Excel features, formulas, solutions, tips, and tricks. His expertise spans Microsoft Office Suites, Automating Finance Templates, VBA, Python, and Developing Excel Applications, showcasing a multifaceted commitment to the... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo