Doorfox586

1. Excel Pivot Table Relationship Not Working
2. Excel For Mac Create Relationship Between Tables Without
3. Excel For Mac Create Relationship Between Tables With Rows
4. Excel For Mac Create Relationship Between Tables For A

May 04, 2018 - by Bill Jelen

A table relationship is represented by a relationship line drawn between tables in the Relationships window. A relationship that does not enforce referential integrity appears as a thin line between the common fields supporting the relationship. When you select the relationship by clicking its line, the line thickens to indicate it is selected.

1. Aug 23, 2012 Excel is telling me that a relationship between the two tables might be needed, so I go ahead and create it. A couple of things to notice when working with relationships: Both columns chosen need to contain the same type of data (Zip Code in this case). They only work when one of the table’s columns contains unique values.
2. This will set the relationship and your Sales table will be able to see the values in the Names table. For Excel 2010, you can do the same using the Create Relationship Dialog Box: STEP 10: With this, our setup is complete. Now it’s time to create a Pivot Table to do our analysis. Within the PowerPivot Window, go to Home PivotTable.

Rick from New Jersey asks about setting up a slicer to control two pivot tables that come from two different data sets. I've solved this in the past using some VBA. But today, I think there is an easier way using the Data Model.

Controlling multipled pivot tables is one of the main benefits of slicers. But both of those pivot tables have to come from the same data set. When you have data from two different data sets, using one slicer to control both data sets becomes more difficult.

In order to use the technique in this article, your pivot tables must be based on a data model. If you have existing pivot tables that are not based on the data model, you need to delete them and start over.

Notes

• If all of your pivot tables are based on the same data set, setting them up to use the same slicers is easier. Watch Episode 2011.

• If you are using a Mac and don't have the Data Model, you might be able to solve the problem using VBA. Watch Episode 2104.

The key step is to build a new SlicerSource table. If both of your data sets contain a field called Sector and you want the pivot table to be based on Sector, copy the Sectors from both tables to a new table. Use Data, Remove Duplicates to create a unique list of the sectors found in either table.

When you create a pivot table from each of the two data sets, be sure to check the box for Add This Data to the Data Model.

When you insert a slicer, there will be two tabs across the top. Use the second tab - called All. Find the Slicer Source table and build the slicer from there.

Initially, only one pivot table will respond to the slicer. Select the other pivot table and choose Filter Connections.

The result will be two pivot tables (from different data sets) that react to the slicer.

This method seems far easier than the VBA method described in video 2104.

Watch Video

Video Transcript

Learn Excel from MrExcel Podcast, Episode 2198: A Slicer for Two Data Sets.

Excel Pivot Table Relationship Not Working

Hey, welcome back to the MrExcel netcast, I'm Bill Jelen. I was in New Jersey doing a seminar there, and Rick asked a question, he said, 'Hey, look, I have pivot tables built on two different data sets and I'd like one slicer to be able to control those.' And now I did a video about this-- Episode 2104-- that used some VBA, but this video has really caused a lot of problems because people have slicers based on data that doesn't match. And so, you know, I wondered if there was an easier way to do this without the VBA.

And so, I have a table here on the left that has Sector, and I have a table on the right that has Sector. And if I have any existing pivot tables on these two data sets, I need to get rid of those pivot tables-- I need to just completely start over. And what we're going to do is we're going to build a third table that's going to live between the other two tables, and that table is just going to be really simple-- it's just going to be a list of all of the Sectors. So I take the Sectors from the left table, I take the Sector's from the right table, paste all of these together, and then choose the whole set, and under Data, choose Remove Duplicates-- right here-- and we end up with just the unique list of Sectors. Alright? Then we have to take each of these tables and make them into-- Format as Table using Ctrl+T, alright. So I take the left one, Ctrl+T; 'My table has headers', Yes; second one, Ctrl+T, 'My table has headers, Yes; third one, Ctrl+T, 'My table has headers'. Now Microsoft gives these really boring names, like 'Table 1', 'Table 2', and 'Table 3', and I'm going to rename those-- I'm going to call this left one Sales, I'm going to call the middle one my Slicer Source, and then this one over here I'll call Prospects. Alright.

So, I have the three tables and I need to somehow teach Excel that this table is related to both this table and this table over here. So we come to Relationships-- Data, Relationships, and I'm going to create a New Relationship from the Sales table. It has a field called Sector that's related to the Slicer Source-- Sector, click OK. Now create another Relationship from the right side, from the Prospect table-- it has a field called Sector, it's related to Slicer Source, the field called Sector, click OK.

So, now, I've taught Excel how what the relationship is, both from this one to the Slicer Source, and from this one to this Slicer Source. Now, at this point, I can build my two pivot tables. So I start here, Insert, PivotTable, make sure to check the box for 'Add this data to the Data Model, and we can build a nice report of Customer, and maybe Revenue-- like that. I want to see this high to low-- so Data, Z to A, and I want to narrow it down to just the top 5, or top 3, or something like that. Great, okay. Then, I want to build a second pivot table that's using the second data set. So, from here-- Insert, PivotTable, again make sure to 'Add this data to the Data Model', this time I'll put it on the same sheet, so we can kind of see how they are interacting with each other. Click OK. And we will get a unique count of prospects. It starts out with a count of prospects, but if I go into Field Settings, because I'm using the Data Model, I have an extra calculation here at the bottom called Count-- Distinct Count. Click OK, and we'll put the Sector here so we can see how many prospects there were in each of those Sectors. Okay, beautiful, that's all working great.

Now, what I want to do is insert a slicer, but the slicer's not going to be based on the Sales table, nor the Prospects table; that slicer is going to be based on the Slicer Source. Alright, so we choose a new slicer based on the Slicer Source, field is Sector, we get our slicer here, change the color if you'd like. Okay, so, just do a test here-- choose, for example, Consulting, and you see that this pivot table is updating but that pivot table is not updating. So from that pivot table, go to the PivotTable Tools-- Analyze, Filter Connections, and hook that pivot table up to the Sector Filter. And then as we choose, you see that this pivot table is updating, and that pivot table is updating as well. No VBA whatsoever.

Hey, be sure to check out my new book, MrExcel LIVe, The 54 Greatest Tips of All Time. Click that 'I' on the top right-hand corner for more information.

Today, Rick from New Jersey asked if one slicer can control pivot tables that come from multiple sources. And while I've done this in Episode 2104, with a VBA solution, we can do without VBA using the Data Model. That requires Windows, version of Excel-- Excel 2013 or newer-- and if you have any pivot tables that are not based on the Data Model, delete those, find the fields in common between your two data sets, copy each field to a new table, and use Remove Duplicates to get a unique list of that field. Now, you have three data sets-- the original data set, the other data set, and this new one. Make each one into a table using Ctrl+T; build a Relationship between the left data set and this new table; between the right data set and the new table; and then as you build your two pivot tables for each one, say 'Add this data to the Data Model'; when you build a slicer you have to click the All tab to see that third table; choose from the Slicer Source, that tiny little table; and then one of the two pivot tables is not going to be tied to the slicer; select a cell in that pivot table; use Filter Connections to connect the pivot table and the slicer.

To download the workbook from today's video, visit the URL in the YouTube description, and, you know, you can download the book.

Well, I want I thank you for stopping by, I'll see you next time for another netcast from MrExcel.

Download Excel File

To download the excel file: slicer-for-two-data-sets.xlsx

Excel Thought Of the Day

I've asked my Excel Master friends for their advice about Excel. Today's thought to ponder:

'Excel neither belongs to any specific discipline, nor any talented people. It is a general software which could be useful for any discipline and anybody.'

We have two tables of orders copied into one worksheet. You need to compare the data of the two tables in Excel and check which positions are in the first table but not in the second one. It makes no sense to manually compare the value of each cell.

Comparing two columns for finding matches in Excel

How can we compare values for two columns in Excel? To solve this task, we recommend using conditional formatting which quickly selects the color of positions that are only in one column. Worksheet with tables:

First, you need to name both tables. This makes it easier to understand which cell ranges are compared:

Excel For Mac Create Relationship Between Tables Without

1. Select the 'FORMULAS' tool - 'Defined Names' - 'Define Name'.
2. Enter the value - Table_1 in the appeared window in the field 'Name:'
3. With the left mouse button click on the input field 'Refers to:' and select the range: A2:A15. Then click OK.

For the second list follow the same steps and just use another name - Table_2. And choose the range C2:C15 respectively.

Helpful advice! Range names can be assigned more quickly by using the name field. It is located on the left of the formula row. Just select the ranges of cells, and in the name field enter the appropriate name for the range and press Enter.

Now let's use conditional formatting to compare two lists in Excel. We need to get the following result:

Positions that are in Table_1, but not in Table_2 will be displayed in green. At the same time, the items in Table 2 which don’t present in Table 1 will be highlighted in blue.

1. Select the range of the first table A2:A15 and select the tool: 'HOME'-'Conditional Formatting'-'New Rule'-'Use a formula to determine which cells to format:'.
2. Enter the formula in the input field:
3. Click on the 'Format' button and specify a blue color on the 'Fill' tab. Click OK in all windows.
4. Select the range of the first list C2:C15 and select the tool again – 'HOME'-'Conditional Formatting'-'New Rule'-'Use a formula to determine which cells to format:'.
5. Enter the formula in the input field:
6. Click on the 'Format' button and specify the green color on the 'Fill' tab. Click OK in all windows.

Excel For Mac Create Relationship Between Tables With Rows

Principle of comparing the data of two columns in Excel

Excel For Mac Create Relationship Between Tables For A

We used the COUNTIF function when defining conditions for formatting column cells. In this example, this function checks how many times the value of the second argument (for example, A2) occurs in the list of the first argument (for example, Table_2). If the number of times is 0, then the formula returns TRUE. In this case, the cell is assigned a custom format specified in the conditional formatting options. The reference in the second argument is relative, then all the cells of the selected range will be checked in turn (for example, A2: A15).

The second formula works similarly. The same principle can be applied to various similar tasks. For example, for comparing two prices in Excel and even on different worksheets.