How to Create Full Outer Join in Excel (2 Ideal Examples)

Get FREE Advanced Excel Exercises with Solutions!

Merging or joining columns is a very common requirement among Excel users. But things get really difficult when the dataset is large and has repetitive values. If we want to join two or more tables where all the rows will be visible, only copypaste cannot help us. In this case, we need to apply Full Outer Join in Excel to merge columns faster. In this article, we will describe 2 ideal examples of how to create Full Outer Join in Excel.


Download Practice Workbook

Download this practice file to try by yourself.


Types of Outer Join in Excel

The motto of Outer Join is to merge all the rows of the source tables. The process can be done in 3 ways. Each one provides different outcomes. They are as follows.

  • Left Outer Join
  • Right Outer Join
  • Full Outer Join

2 Ideal Examples to Create Full Outer Join in Excel

Let us now learn the process of creating a Full Outer Join for 2 columns in excel. Following are 2 examples that will show the process for both unique and matched values.

1. Create Full Outer Join for Matched Values in Excel

In this example, we will perform the Full Outer Join where both tables have some matched values. For this, here we created 2 sample tables. They show the information of Sales Report and Product Data.

Create Full Outer Join for Matched Values in Excel

Now, we want a new table where we can get all the data in a single one. For this, we will create a Full Outer Join table following the steps below.

  • First, click on any cell of the first dataset.
  • Then, go to the Data tab and select From Table/Range.

  • Following this, you will get the Create Table window which automatically selects the cell range to create a table.
  • Here, click on OK and it will be connected to the Power Query Editor.

  • Afterward, select Close & Load To under the Home tab.

  • Then, select Only Create Connection in the Import Data dialogue box and press OK to close it.

Create Full Outer Join for Matched Values in Excel

  • Next, follow the same procedure for the Product Data table as well.
  • As a result, you will get the list of 2 connected tables in the Queries & Connections panel.

  • Now, rightclick on Table2 and choose Reference.

  • Then, select Home > Combine > Merge Queries in the Power Query Editor window.

Create Full Outer Join for Matched Values in Excel

  • Accordingly, you will be directed to the Merge window.
  • Here, select the Product ID and Product Name columns together by pressing Ctrl on your keyboard.

  • After this, choose Table3 from the drop-down list and select similar columns as Table2.

  • Lastly, choose Full Outer (all rows from both) as the Join Kind.

Create Full Outer Join for Matched Values in Excel

  • Now, we will notice a new column where we can see each row’s values when we leftclick on them.

  • Henceforth, expand this column by pressing this bothsided arrow as shown in the image below.

  • Accordingly, you will get the list of columns of Table3 where you can select which columns you need to show after merging.
  • For ease of work, we kept all of them selected but deselected Use original column name as prefix option.

Create Full Outer Join for Matched Values in Excel

  • In the end, press OK and you will get the Import Data window.
  • In this window, select Table and New worksheet as we want the output as a table in a new worksheet.

  • Finally, hit OK and you will get a Full Outer Join table like this.

  • From this table, you can see that matched values are aligned together without replacing each other. Even, the rows with unique values have blank cells to make it easier to understand.

Read More: How to Perform Outer Join in Excel (2 Easy Ways)


2. Insert Full Outer Join for Unique Values in Excel

In this example, we will create a Full Outer Join table from 2 tables where each of which has unique values. Here are 2 sample datasets that have unique values titled Sales Report and Product Data.

Insert Full Outer Join for Unique Values in Excel

Let’s follow the steps below to merge them as a Full Outer Join table.

  • In the beginning, connect both tables individually to Power Query Editor as we described in the first example.
  • Then, go to the Data tab and click on Get Data.

  • Here, choose Merge from the list of Combine Queries.

  • As a result, you will get the Merge window.
  • Here, choose Table7 and Table8 in each of the drop-down lists as shown below.

Insert Full Outer Join for Unique Values in Excel

  • Afterward, select similar columns named Product ID and Product Name from each table.

  • Along with it, select Full Outer (all rows from both) as the Join Kind.

Insert Full Outer Join for Unique Values in Excel

  • Next, press OK and you will be directed to the Power Query Editor.
  • Here, expand the last column with the following selections.
  • Also, mark unchecked the Use original column name as prefix option.

  • Lastly, hit OK and choose the Table and New Worksheet options in the Import Data dialogue box.

  • Finally, by pressing OK you will get the required output like this.

Read More: How to Inner Join in Excel (2 Easy Methods)


Conclusion

Finally, we are at the conclusion of our article. Here we tried to illustrate with 2 ideal examples of how to create Full Outer Join in Excel among two columns. Let us know your insightful suggestions in the comment box. Keep an eye on ExcelDemy for more tutorials.


Related Articles

Sanjida Mehrun Guria

Sanjida Mehrun Guria

Hello! Welcome to my blog. I have completed my Bachelor in Architecture from Ashanullah University of Science & Technology, Dhaka. I am a passionate, goal-oriented person with an immense thirst for knowledge and an attitude to grow continuously. Besides Architectural work, I find it very enthusiastic to work in Excel blogging. Exceldemy is a platform where I have got the opportunity to flourish my skill in Microsoft Excel and therefore, here I will be posting blogs related to it.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo