Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

Excel Connections vs Queries: Main Dissimilarity with Example

In this article, we will show the main dissimilarity between connections vs queries in excel. Data Table is a very useful tool in Excel and is mainly used for forming data from different tables with connections and queries. Therefore, it is necessary to know the difference between them. So, without the delay, let’s start the discussion.


Download Practice Workbook

To practice by yourself, download the following workbook.


What Are Connections and Queries in Excel?

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

Queries are tools used for importing data from different workbooks and then adding, deleting, or changing data as per requirements. We can gain a better understanding of these terms by following the example below.


Step-by-Step Procedures to Demonstrate Dissimilarity Between Connections and Queries in Excel

For forming a data table, we will use connections and queries. We will merge two tables using the Excel Power Query feature. In Table 1, there will be information about Student ID, Student Name and Math Scores. The name of the first table is Math_Scores.

excel connections vs queries

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

excel connections vs queries

Let’s pay attention to the step-by-step procedures to understand the dissimilarity between connections and queries in Excel.


STEP 1: Create Table in Excel

  • Firstly, we need to convert the dataset into table format.
  • To do so, select any cell of the dataset and press Ctrl + T.
  • As a result, the Create Table dialog box will appear.
  • Click OK.

Create Table to Show Dissimilarity Between Connections vs Queries in Excel

  • So, the dataset is converted into a table.

  • Similarly, convert the 2nd dataset into Table 2.

Read More: How to Find External Data Connections in Excel (5 Quick Tricks)


STEP 2: Load Data in New Worksheet

  • In the following step, select any cell from Table 1.
  • After that, go to the Data tab and select From Table/Range.

Load Data in New Worksheet

  • As a result, the Power Query Editor window will pop out.
  • On the leftmost side of the window, you can see the Math_Scores table.

  • After that, 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.

  • Instantly, the Import Data dialog box will appear.
  • Select Only Create Connection and press OK to proceed.

Load Data in New Worksheet

  • Repeat the above steps for the second table.
  • After completing the steps, you will see the tables in the Queries & Connections pan.
  • Here, Math_Scores is the first table and Physics_Chem_scores is the second table.

excel connections vs queries

Read More: How to Close Queries and Connections Pane in Excel


STEP 3: Merge Table in Power Query Editor

  • In the following step, navigate to the Data tab and select Get Data. A drop-down menu will appear.
  • After that, select Combine Queries >> Merge from there. This will open the Merge window.

Merge Table in Power Query Editor

  • In the Merge window, we have to select the tables and matching columns to create a merged table.
  • Therefore, select Math_Scores from the first drop-down menu.
  • Then, select Physics_Chem_scores from the second drop-down menu.
  • Also, select Left Outer (all from first, matching from second) in the Join Kind box.
  • Now, most importantly 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.
  • So, click on the Expand icon to insert values in this column.

Merge Table in Power Query Editor

  • A message box will appear.
  • Now, unselect the Name column from there.
  • Also, check ‘Use original column name as prefix’.
  • Click OK to proceed.

  • As a result, we can see the merged table in the window.

Read More: [Fixed!] External Data Connections Have Been Disabled in Excel


STEP 4: Insert Merged Table in Worksheet

  • In the following step, go to the Home tab of the Power Query Editor window and click on the Close & Load icon.
  • After that, select Close & Load To from the drop-down menu.

Insert Merged Table in Worksheet

  • In the following step, select Table and New worksheet in the Import Data box.
  • Then, give a tick on Add this data to the Data Model.
  • After that, click Load to move forward.

  • Finally, you will see the merged table in a new sheet named Merge 1.

Read More: How to Create Excel Data Connection to Another Excel File


STEP 5: Show Connections vs Queries Dissimilarity

  • After finishing, you will be able to see the Queries & Connections pane.
  • On the Queries tab, you can see 3 queries. The tables are basically termed Queries.

Show Dissimilarity Between Connections vs Queries

  • And in the Connections tab, you can see 1 connection and that is the workbook data model.

Show Dissimilarity Between Connections vs Queries


Conclusion

In this article, we have demonstrated the way to differentiate between excel connections vs queries. There is a practice workbook at the beginning of the article. Go ahead and give it a try. To read similar articles, check out the ExcelDemy website. Last but not least, please use the comment section below to post any questions or make any suggestions you might have.


Sudipta Chandra Sarker

Sudipta Chandra Sarker

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel. Here I will be posting articles related to this. My educational degree is BSc in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology, Bangladesh. I have a great interest in research and development. I always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo