I made a short video about how to create a data model in Excel. Typically you can do this with formulas however as the sizes of our data sets get larger and more complex using the Excel Data Model functionality presents some more intelligent ways of being able to manage data at scale. You can download the sample file I created here but in principle the steps are:

1. Select the data from your first table, navigate to the Data tab and select “From Table” in the Get & Transform section.
2. Upon creating your first Data Model table the Query Editor will appear, you can press “Close & Load”, as it’s mainly designed for data outside of Excel it will generate a new worksheet with your table in, you can just delete this.
3. Do the same for your second (third, fourth, n^th) table but on your last table leave the Query Editor open.
4. On the Home tab select the Merge Queries dropdown from the Combine section and select “Merge Queries as New”.
5. Select your first table and your second table then highlight your join criteria (you can CTRL or SHIFT and select more than one).
6. Select your Join Kind and press “Ok”, you should see a new table called “Merge1”. If you want to include additional tables you can do another merge based on your previous merge.
7. Go to your last column which should be new called “NewColumn” and select the double arrow icon where the filter icon would normally be. This should give you a pop up box to select what columns from your second table you want to include. There is also an option to “User original column name as prefix” which basically tells you the table that column is from but if your columns are uniquely named there’s not much point.
8. You can then click “Close & Load” and it will create a new worksheet with the output from your query.