Demonstrating the Main Differences Between Connections vs. Queries in Excel (Step-by-Step)

What Are Connections and Queries in Excel?

Connections are the dynamic links used for building internal relationships in your workbook and external relationships between your and other workbooks.

Queries are tools used for importing data from different workbooks and then adding, deleting, or changing data as needed. The example below can help us better understand these terms.

We will use connections and queries to form a data table. We will merge two tables using the Excel Power Query feature. Table 1 contains information about student ID, student name, and math scores. The name of the first table is Math_Scores.

excel connections vs queries

Table 2 will contain the marks for Physics and Chemistry. The name of the second table is Physics_Chem_scores. Here, the Name column is the common column between the two tables.

STEP 1: Create a Table in Excel

Convert the dataset into table format.
To do so, select any dataset cell and press Ctrl + T.
The Create Table dialog box will appear.
Click OK.

The dataset is converted into a table.

Convert the 2nd dataset into Table 2.

STEP 2: Load Data in the New Worksheet

Select any cell from Table 1.
Go to the Data tab and select From Table/Range.

The Power Query Editor window will pop out.
On the leftmost side of the window, you can see the Math_Scorestable.

In the Power Query window, go to the Home tab and click on the Close & Load icon to open the drop-down menu.
Select Close & Load To from there.

The Import Data dialog box will appear.
Select Only Create Connection and press OK to proceed.

Repeat the above steps for the second table.
You will see the tables in the Queries & Connections pan.
Math_Scores is the first table, and Physics_Chem_scores is the second table.

STEP 3: Merge the Table in the Power Query Editor

Go to the Data tab and select Get Data. A drop-down menu will appear.
Select Combine Queries >> Merge from there. This will open the Merge window.

In the Merge window, select the tables and matching columns to create a merged table.
Select Math_Scores from the first drop-down menu.
Select Physics_Chem_scores from the second drop-down menu.
Select Left Outer (all from first, matching from second) in the Join Kind box.
Select the Name column in both tables.
Click OK to proceed.

As a result, you can see a merged table in the Power Query Editor. But the Physics_Chem_scores column doesn’t contain any value.
Click on the Expand icon to insert values in this column.