This guide will explain how to fix data model relationships not working in Excel using four simple and efficient methods.
Excel is an excellent tool containing many incredible and useful features. And one of them is the ability to import tables into a new or existing worksheet. Furthermore, Excel also detects and defines the relationship of the tables imported.
Data model relationships in Excel refer to connections between two tables. And it can be a tiring task to create them all over again when importing. Since Excel lessens the burden by also detecting the relationship from imported tables, we do not have to spend time manually creating them again.
However, there are times wherein Excel fails to detect and create these data model relationships. And there can be several reasons why the data model relationships in Excel are not working.
Let’s take a scenario wherein you may encounter this issue.
Suppose you are working on a worksheet containing two tables. And you want to have more power and control over the tables by creating a data model relationship. But, you find out that the data model relationship is not working in Excel.
But, worry not! We will proceed to discuss four simple and efficient ways how to fix data model relationships not working in Excel.
Manually Create Data Model Relationship
The first method will be to create a data model relationship in Excel manually. When we try to check if a relationship exists between two tables, it will usually give us a message if a relationship between the tables is detected or not.
If the data model relationship is not working in Excel, we get a message that no relationship is detected. When this happens, we can just create one manually by going to Manage Relationships.
To manually create a data model relationship in Excel, follow the steps discussed below:
1. Firstly, we need to open the PivotTable Fields. So right-click on the table and select Show Field List.
2. Secondly, the PivotTable Fields will appear on the right side. From there, select the All tab. Then, select the columns of the other table. And a yellow message will appear saying Relationships between tables may be needed. Lastly, select Create.
3. Thirdly, the Create Relationship window will appear. In the window, select the tables and columns you want to create a relationship between. Next, select OK.
4. And tada! We have manually created a data model relationship in Excel.
Change Unsupported Data Type
Another reason the data model relationship is not working in Excel could be that the tables contain an unsupported data type. For instance, the tables contain two different data types. Then, no relationship between the two tables can be created.
To fix this, we simply need to change the data type of one table so that both tables will have the same data type. Afterward, a relationship can be created.
1. Firstly, let’s decide which table we will change the data type. In this case, let’s change the second table containing decimals to percentages to match the first table.
2. Secondly, select the data from the second table. Then, go to the Home tab and select the Number Format dropdown menu. Next, select Percentages.
3. And tada! We have successfully changed the unsupported data type to fix the data model relationship not working in Excel.
Adding Fields into Value Area of Pivot Table
Excel contains automatic relationship detection. But, it is only applicable for measure only. So Excel cannot detect the calculated fields we input in the row and column labels in the Pivot Table.
So we need to input fields into the Value area for a relationship to be created. Because we insert several tables when building a Pivot Table, we can unintentionally add some unrelated tables.
To fix the data model relationship not working in Excel by adding fields into the Value area of the Pivot Table, follow the steps below:
1. Firstly, we need to open the PivotTable Fields window. So right-click the table and select Show Field List.
2. Secondly, the PivotTable Fields window will appear. Then, go to the Active tab. Under Values, make sure there is a field added. If not, add a field.
3. Afterward, we can now create a data model relationship.
Using Automatic Detection to Detect Wrong Relationships
Lastly, we can utilize the automatic detection feature in Excel. So the automatic detection feature in Excel can create all the possible relationships based on the values we have on our tables. And they rank the relationships based on their probabilities.
Afterward, Excel will present the most likely relationship based on the automatic detection algorithm. But, sometimes, Excel can create the wrong relationship, especially if the tables contain too many columns.
To fix this issue, we can simply delete the wrong relationship and create the correct one manually.
1. Firstly, we need to go to the Manage Relationship window. So go to the PivotTable Analyze tab. Then, select Relationships.
2. Secondly, the Manage Relationship window will appear. Next, select the relationship. Then, click on Delete.
3. After deleting the wrong relationship, we can manually create a new and correct relationship.
Awesome! We have discussed the last method we can use to fix data model relationships not working in Excel.
You can make your own copy of the spreadsheet above using the link attached below.
And that’s pretty much it! You have learned four simple and efficient methods to fix data model relationships not working in Excel. Now you can try each of these methods and find the one that works best for your situation.
Are you interested in learning more about what Excel can do? You can now use the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.