Author: Oscar Cronquist Article last updated on November 19, 2019

Merge two related tables before creating a pivot table

This article demonstrates how to merge two relational data sets before creating a Pivot table. A Pivot Table is limited to one table (data source) when you are working with relational data and I want to calculate the sales figures for each salesperson.

The image above shows one Excel table to the right that contains the product, price, and the company. The Excel Table to the left shows the salespersons and the corresponding company, a salesperson may work with multiple companies.

Create Excel Tables

I highly recommend Excel Tables, they save you a lot of time if you need to add more data to your data set and they have other useful features as well. You don't need to update the cell references each time you add data, however, you still need to refresh the Pivot Table.

  1.  Select all cells you want to convert to an Excel Table.
  2. Press CTRL + T to create an Excel Table, a dialog box is displayed. Image: Excel Table - dialog box
  3. Enable the checkbox if the Table has headers.
  4. Click OK button.

Excel applies automatically cell formatting to a new Excel Table, you can change the Table style if you don't like it.

Select any cell in the Excel Table, go to tab "Table Design" on the ribbon and select a new Tables Style if you prefer something else.

Add a column to an Excel Table

Merge two related tables before creating a pivot table1

Excel expands the Excel defined Table automatically if you enter a value in an adjacent cell. Let's look for data in the first table [Table1].

  1. Select cell G2 which is the adjacent cell in this case, see image above.
  2. Type or copy/paste the following formula:
    =INDEX(Table1[Salesperson], MATCH([@Company], Table1[Company], 0))
  3. Press Enter.

Another great feature is that the Excel defined Table automatically copies formulas to all cells in a column which you will see after you pressed Enter in the third step above.

The formula you entered in cell G2 is actually better than a VLOOKUP formula, it allows you to search any column and the column you return values from is not hardcoded into the formula which may be a problem if you insert more columns to a data table.

The MATCH function has three arguments, the first argument is a cell reference to a cell on the same row in column Company as the cell you are currently adding the formula to. [@Company] It is called a structured reference and is special to Excel Tables.

[@Company] does not contain a reference to a Table name, the formula is located in the same Table so the Table name is not needed.

The second argument is a structured reference to all values in column Company in Table1 Table1[Company] and the third argument 0 (zero) tells Excel to perform an exact match.

MATCH(lookup_valuelookup_array[match_type])

becomes

MATCH([@Company], Table1[Company], 0)

becomes

MATCH("MARS Industries", {"MARS Industries"; "The Lanford Lunch Box"; "Minuteman Cafe"; "Springfield Beans"; "Smith and Co."; "Sixty Second Avenue"; "Cyberdyne Systems"; "Osato Chemicals"; "Klimpys"; "Corellian"}, 0)

and returns 1. "MARS Industries" is in the first position in the array.

The INDEX function returns a value from a given cell range or array based on a row and column number. The column number is optional.

INDEX(array[row_num][column_num])

becomes

INDEX(Table1[Salesperson], MATCH([@Company], Table1[Company], 0))

becomes

INDEX(Table1[Salesperson], 1)

and returns the first value from column Salesperson in Table1 which is "Kirti Whitt".

The Excel Table has now the corresponding salesperson next to the company name.

Merge two related tables before creating a pivot table2

If you are looking for more examples on merging two data lists, check this post: Merge lists with criteria

Create Pivot Table

If you are new to Pivot Tables don't freak out, they are so useful that I recommend you take time to get to know them better.

A Pivot Table allows you to quickly create totals based on conditions you specify, there is no need to build complicated formulas.

The speed the Pivot Table executes tasks is incredible. It is one of the greatest built-in features in Excel, in my opinion.

  1. Select any cell in Table2, the table to the right.
  2. Go to tab "Insert" on the ribbon.
  3. Click "Pivot Table" button and a dialog box is diplayed, see image below.
    Merge two related tables before creating a pivot table3
  4. Click OK button. This will create an empty Pivot Table in a new worksheet.

Set up Pivot Table

Merge two related tables before creating a pivot table4

The image above shows a Pivot Table to the left and the corresponding task pane to the right. The task pane contains a list of fields based on headers in your Excel Table, you can drag these fields to different areas below which are:

  • Report Filter
  • Column Labels (horizontally)
  • Row Labels (vertically)
  • Values

Follow the simple instructions below see how much each salesperson has sold and to what company.

  1. Drag Price to Values area.
  2. Drag Company to Row Labels area.
  3. Drag Salesperson to Column Labels area.

The Report Filter lets you examine the data even deeper, simply drag a field to the Report Filter and a drop down list shows up above the Pivot Table. The drop down list contains items you may want to use as a filter condition.

Download Excel file


Merge-two-related-tables.xlsx