I have two worksheets in two different excel files They both contain a list of names, id numbers, and associated data. One is a master list that includes general demographic fields, and the other is a list that only includes name and id, and an address. This list was written down from the master list by another office

I want to use the second list to filter the first In addition i want the results to include other fields from the master worksheet alongside the address fields of the second worksheet I know how i could do this very easily with a database inner join, but i'm less clear on how to do this efficiently in excel. How does one join two worksheets in excel? Bonus points for showing how to do outer joins as well, and i would greatly prefer knowing how to do this without needing a macro.

Best Answer


For 2007+ use Data > From Other Sources > From Microsoft Query .

  1. choose Excel File and select your 1st excel
  2. choose columns
    (if you don't see any list of columns, make sure to check Options > System Tables )
  3. go to Data > Connections > [choose the connection just created] > Properties > Definition > Command text

You can now edit this Command text as SQL. Not sure what syntax is suported, but i tried implicit joins, "inner join", "left join" and unions which all work. Here's a sample question

SELECT *
FROM `C:\Users\Peter\Documents\Excel-to-excel\Source_1.xlsx`.`Sheet1$` a
LEFT JOIN `C:\Users\Peter\Documents\Excel-to-excel\Source_2.xlsx`.`Sheet1$` b
ON a.col2 = b.col2