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

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 copy-paste 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.

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

Create Full Outer Join in Excel: 2 Ideal Examples

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.

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.

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, right-click on Table2 and choose Reference.

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

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.

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

Henceforth, expand this column by pressing this both-sided 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.

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. 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.

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.

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.

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

Download Practice Workbook

Download this practice file to try by yourself.

Full Outer Join.xlsx

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.

<< Go Back to Power Query Joins | Power Query Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!