How to Create a Full Outer Join in Excel – 2 Examples

 

Types of Outer Join in Excel

Outer Join merges all rows in the source tables. The process can be done in 3 ways:

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

 

Example 1 – Create a Full Outer Join for Matched Values in Excel

There are 2 sample tables. They showcase a Sales Report and Product Data.

Create Full Outer Join for Matched Values in Excel

To create a new table with all data:

  • Click any cell in the first dataset.
  • Go to the Data tab and select From Table/Range.

  • In the Create Table window, the cell range to create a table is automatically selected.
  • Click OK and the Power Query Editor will be displayed.

  • Select Close & Load To in the Home tab.

  • Select Only Create Connection in the Import Data dialog box and click OK.

Create Full Outer Join for Matched Values in Excel

  • Follow the same procedure for the Product Data table.
  • You will see 2 connected tables in the Queries & Connections panel.

  • Right-click Table2 and choose Reference.

  • Select Home > Combine > Merge Queries in the Power Query Editor window.

Create Full Outer Join for Matched Values in Excel

  • In the Merge window, select Product ID and Product Name columns by pressing Ctrl.

  • Choose Table3 and select the columns that are in Table2.

  • Choose Full Outer (all rows from both) as Join Kind.

Create Full Outer Join for Matched Values in Excel

  • A new column is added: you can the row values by left-clicking them.

  • Expand this column by pressing the both-sided arrow shown below.

  • You will see the list of columns in Table3.
  • Select the columns you need to show after merging. Here, all columns are selected. You can but deselect them by checking Use original column name as prefix.

Create Full Outer Join for Matched Values in Excel

  • Click OK.
  • In the Import Data window, select Table and New worksheet.

  • Click OK and you will get a Full Outer Join table:

Matched values are aligned together and the rows with unique values have blank cells.

Read More: How to Perform Outer Join in Excel


Example 2 – Perform a  Full Outer Join for Unique Values in Excel

These are the sample datasets containing unique values.

Insert Full Outer Join for Unique Values in Excel

  • Connect both tables using the Power Query Editor as described in the first example.
  • Go to the Data tab and click Get Data.

  • Choose Merge in Combine Queries.

  • In the Merge window, choose Table7 and Table8.

Insert Full Outer Join for Unique Values in Excel

  • Select Product ID and Product Name from each table.

  • Select Full Outer (all rows from both) in Join Kind.

Insert Full Outer Join for Unique Values in Excel

  • Click OK.
  • In the Power Query Editor, expand the last column with the selections shown below.
  • Uncheck Use original column name as prefix.

  • Click OK and choose Table and New Worksheet in the Import Data dialog box.

  • Click OK to see the output.

Read More: How to Inner Join in Excel


Download Practice Workbook

Download the practice file.


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
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... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo