When you import multiple tables, Excel attempts to detect and define relationships among those tables so that you don’t have to manually create the relationships or implement complex workarounds if you want to work with the data in a holistic manner.
Sometimes Excel fails to detect relationship between tables. In this case, you can use the information in this article to troubleshoot automatic relationship detection. To better understand the requirements and mechanics of relationship detection see Relationships between tables in a Data Model.
Message: No relationships were detected
The notification bar will always display a message indicating that a relationship is needed when you drag a field into the Values area of an existing PivotTable, and that field is not related to any of the fields already in the PivotTable. However, sometimes a relationship cannot be detected after this notification. There can be various reasons for this:
-
The relationship detection algorithm depends on the foreign key column having a name similar to the primary key column. If the column names are not sufficiently similar, we recommended that you open the Power Pivot window and manually create the required relationships between the tables.
-
The data types might not be supported. If any of the tables used in the PivotTable contain columns of non-supported data types only, no relationships can be detected. In this case, you should use the Create Relationships dialog box to manually create the required relationships been the active tables in the PivotTable. For more information, see Create a relationship between two tables.
You added unrelated fields to the PivotTable but no message appeared
Automatic relationship detection is triggered for measures only, not for calculated fields that you use in the row or column labels of a PivotTable. Therefore, as you begin to build your PivotTable, you might add unrelated tables but not see any relationships until you drop a field into the Values area.
No valid relationship between tables
Sometimes the tables that you add to the PivotTable simply cannot be joined to any other tables. For example, two tables might happen to have partially matching data, but actually have no logical relationships to the other tables already in use.
If you add a table to the PivotTable that cannot be joined to the other tables, usually automatic detection will not return any results. In other cases, you can generally tell from the results in the PivotTable that the fields do not produce meaningful calculations.
Automatic detection created the wrong relationships
When relationships are created by using automatic detection, an algorithm creates a list of all possible relationships, based on values in the tables, and ranks the possible relationships according to their probability. Excel then creates only the most likely relationship. Therefore, if your tables contain multiple columns that might be used as keys, some relationships might be ranked lower and will not be automatically created even if the relationship is valid.
If the automatic detection algorithm suggests a relationship that does not solve your business problem, you should delete the relationship and manually create the relationship using the correct key column. See
