Excel Magic Trick 1450: Replace VLOOKUP When Making PivotTables with Excel 2016 Relationships

Download File:

Learn how to use Relationships in Excel 2016 (or later versions) rather than the VLOOKUP Function when you are making PivotTable Reports from multiple tables. See how to create a Product / Region Yearly Report with a Slicer for Months. Relationship feature allows you to see Multiple Tables in PivotTable Field List and drag and drop fields to make your report. Also lean about the automatic Grouping Feature for PivotTables to group transactional dates into months and years.
Excel PivotTable Reports made easy with Relationships rather than VLOOKUP.

[LeilaGharani]

Thank you for bringing attention to such a powerful feature!

[obits]

Thanks. This looks so much more elegant than adding vlookup columns to source tables.

[programSense]

This pretty significant for me. I do a lot of either lookups or simple access databases to do these type of tasks. Thanks for uploading.

[OzduSoleilDATA]

YEAH! Relationships! An underused feature.
Great video.

[JediPhantom]

That's really cool. it's as though Excel is turning into Access!

[abdiali]

Amazing videos and tutorial. You are absolutely great at teaching

[jdi]

Awesome tip/trick that came along exactly when I needed it for a huge project. Thank you so much for all your super helpful posts!

[roderickmose]

Another excellent video! BIG thanks as always....from sunny beautiful Barbados ;)

[maricelrose]

Very helpful presentation. Thank you for sharing!

[MrBahaw]

Thanks so much for this video. Very helpful

[joshblackman]

Great video, I love it.

Follow on question: is this another way of getting info into the data model or is it a separate construct? Could you write a DAX expression for these 3 related tables? Is there any performance difference between this method and importing those tables into the data model?

[charlesotstot]

I don't know if it's a best practice or not, but I've started naming all of my tables with "_" as the first character (and then with the f and d conventions that you use). Doing so let's me bring up a list of all my tables when building formulas and table references. It saves me a ton of time when I have multiple tables (I've got some sheets with 10-12 queries against external spreadsheets) that I need to manipulate. I thought I'd pass this along in case anyone finds it useful...

[vog]

Don't have Office 2016/365 yet, but could follow along using Power Query and Power Pivot. :-)

[nishantkumar]

Can I make many to many relationships using "Relationships"?
Thank you.

[alandouglas]

Hey Mike, here's a fun little challenge for you... The kids game FizzBuzz is played by counting up from 0 and when you reach a number that is a multiple of 3 you say "Fizz", multiple of 5 is "Buzz", multiple of both like the number 15 is "FizzBuzz"...

1, 2, Fizz, 4, Buzz, Fizz, 7, 8, Fizz, Buzz, 11... etc

Making a formula for that is easy. See bottom

What is hard though is having it adaptable so I can easily add things like multiple of 7 is "Was", so 14 would be "Was" or instead of the number 21 it says "FizzWas" or being able to change it from the number 3 to 4 without having to change the formula.

Is this even possible?



=IF(MOD(ROW(), 3)=0, IF(MOD(ROW(), 5)=0, "FizzBuzz", Fizz"), IF(MOD(ROW(), 5)=0, "Buzz", ROW()))

[rockguitarist]

Mike, how is your Data tab different looking from mine? I have Office 365 and my Data tab is a little more scrunched together than yours. Ex: You can see Relationships spelled out and see the full icon, but my Relationships icon is tiny and below Consolidation. Maybe it's just Excel converting to the size of my laptop screen?

[guymoody]

Hi and apologies for an off topic question about Pivot Tables, but really need help from power users like you. I cannot find anywhere (so far) how to stop the Pivot Table Data Source from making itself an absolute reference to a table that includes the file name. The issue is as soon as I change the name of the file, the Pivot Table Data Source does NOT change and so the pivot table cannot find the source table anymore. Do you know the setting where this is controlled?  Example: Table name that is Pivot Table source:Consolidated_Data  File name:Employee data.xlsx  Once I save the file and exit, when I come back in the Pivot Table Data Source becomes:'Employee Data'!Consolidated_Data  If I rename the file Employee Data V2.xlsx and move it to an archive folder, then the pivot table no longer refreshes.

[jaybaptiste]

I have to pull the same type of report everyday and I use pivot table to format it. Is there a way I can save the file and replace daily it with the most current information? I only need to convert the information to a pivot table and print. I don't have to save the info permanently. Most days after I print I delete the file and do the same thing the next day.

[missjesserz]

In order to use the relationship feature do the tables all need to be on the same worksheet?

[rockguitarist]

Does the d in dTable or the f in fSales mean anything to you?