Hey mike, I didn't know anything I excel when I started your beginner series but at this point of time I feel like I know more than beginners and thankyou very much for your work. This videos are so awesome and now it feels like Excel will not be part of my professional work anymore It will be a permanent hobby and be a part of my hobbies just because of you thankyou very very much. I will always grateful of you.
You have a great knowledge, your channel is wonderful. I was applying for a job that needs EXCEL and i passed the job exam because of your videos. Thank you very very much words wouldn't express how thankful am I. I wish you all the very best in your life and in all what you are doing. God bless you, your family and all your beloved ones. again thank you very much
You probably dont care but if you are stoned like me atm you can stream pretty much all of the latest series on InstaFlixxer. Have been binge watching with my girlfriend recently xD
Honestly, this is the first time I'm seeing it in excel. This resembles powerbi and tableau. No wonder they say make sure you have a deep knowledge about excel before jumping to powerbi and tableau. Thanks so much for this, Mike. This goes a long way.
I love how you make each of your videos so easy to understand. I also love how you incorporate repetitiveness in your videos this really helps it to sink in. You teach Excel better than anyone else I've watched. Thank you for service.
You are welcome! The repeat is intentional - it comes from the fact that it is hard for me to learn new things - so when I learn I have to work hard and repeat, repeat, repeat!!! : )
I love the energy in your voice, so much good vibes every time you talk. Awesome! thank to you I've learned a lot about excel in just few lessons. Keep it up.
Thank you so much for this video. I recently began to learn PowerBi and even after watching multiple videos I never understood the relationship, primary, foreign concepts. It was surprising to see these concepts in Excel and now I have finally been able to understand it.
Having watched & studied many of your videos, I want to say that your presentation of a complicated subject is very well organized & makes a complicated subject much easier to understand.
Sir, Your every video is more than excellent. I am so confident that there is no other advanced tutorial and trainer like you. I am not interested to follow any other trainer's tutorial if you have tutorial in which I am interested to learn. If it is possible, I will give a lot of Thumbs Up in every of your videos. We need trainer like you to make the world more better. I usually suggest to download and follow your videos whoever want to learn excel meeting with me. You are the best. Thanks a lot to keep love and patience in your teaching field.
I'm going through each of your videos in this current Excel Basics Playlist. I start my new job on Monday and want to be super prepared with my excel. I'm hoping this playlist and the Excel Advanced playlist can prepare me. They just wanted basic excel skills but you never know.
@@excelisfun I hope so. I have been unemployed since last Feb. Breaking into procurement/supply chain is tough, especially to get an entry level role despite having some relative experience. Sometimes I don't feel like I'm good enough and need to keep learning more excel skills but it may be overkill.
Very interesting and clear explanation for the topic, specially the 1->Many relation. Thanks a lot ^_^ . The way I understand it is we use "Relationships" when we need to create a report from a table that does not exist, so first we need to create that table, then we create our pivot table.
It is also used when a lookup table (also known as dimensions table) does exist. If you have a sales table and a lookup table from product price that already exists, and there is a 1-many relationship, you can use the relationship.
Your channel is great and so useful. In my working life, I worked with OLAP database and lots of other database environments. I wish I had Excel at this level back then. We used Excel to prep data but this is amazing. I'm retired now and just enjoy learning this. One thing I have noticed is that MS must have decided to give this part (relational data model) to the world that used Office 365. I just have Office 365 Home and I have the Power Pivot engaged. I am really looking forward to the advanced Excel videos that you have and then on to Access. Thanks a lot, Mike. Originally from Oregon and Washington...now in Florida... Irv
First watch the videos in this Basics Playlist, then go on to the Advanced Excel (Highline Excel Class) playlist. If you need help fining the videos or playlists, just let me know.
Small note about the last HW instruction, it says "Counts How Many Sales" but I think you meant Sum of Sales/Revenue. Thankfully it's a quick fix to change Pivot Table Values from Count to Sum!
You're really really reeeeaaaaally good at explaining! I have basic pivot table knowledge and you just helped me tremendously - thank you soooo much!!!
When it is fun, the the explaining is reeeeaaaaally easy, and good for you!!!! Thanks for your support, Little Luuluu, with your cool comment, Thumbs Up and Sub : )
Thank you Mike, I've done your homework and it opens my mind and gives me motivation to learn more about data relation. Undeniably very good tutorial and exercise
I have 100s of videos about those : ) : ) Data Analysis Basics Playlist: ruclips.net/p/PLrRPvpgDmw0lPPRiJO5dCUratRGpGx3aT Advanced Data Analysis playlist: ruclips.net/p/PLrRPvpgDmw0ks5W7U5NmDCU2ydSnNZA_1&app=desktop
Mike, you are a master in Excel data integration..!!!!! I was just trying to do a similar job on my file and thought I would see an answer in your tutorials. What I’m thinking now is, I would have a transaction table every month and I need to adopt similar approach by loading the data located in a folder into data model and doing lookups. In SAP context, you have material number to track sales / production / inventory quantities and you wish to report by product / segment. How can we go about this?
Hi Mike, Huge thanks for putting these vides together, I appreciate this resource so much. Question: in this video and the previous one, when I download the files to follow along and then click "enable editing," I get the message "Microsoft has blocked macros from running because the source of this file is untrusted." Is macros a thing I need for these worksheets, and should unblock so it comes through? I haven't had this message pop up on any other previous files I've downloaded as I've been going through this class series.
It does not have to end!!! There are many, many more videos to come : ) If keep watching, clicking thumbs up and commenting, the videos will keep on coming : )
Dear Mike, its a great feature in power pivot but then it comes with a rule and which is that you cannot put many side table lookup in pivot row. It has to be always coming from a lookup table. If you put a lookup from a table which has repetitions in pivots rows you would not see the result. However, i know then you can use related function but it should have been in Relationships too. I mean why we have to be selective in dropping the items always from unique table in pivot row field. Correct me if i am wrong...
I do not understand your question, Ajay. I have a whole comprehensive video about Power Pivot, and one of the thing the videos shows is what happens when you use the Many Side or the One Side: ruclips.net/video/Rbkbr89cuHo/видео.html
That is great that it is useful for you and you like it too much, Enes! We are a Team: I post the videos and viewers like you can learn and support with cool comments and thumbs ups : ) : ) : )
@@excelisfun You guys so nice and kind. People like you deserve much more! Appreciate again because you are teaching really good and explaining everything that you know.
@@enesnature5486 Remember: it is not "you guys". It is just me. I create, post and manage everything here at the excelisfun channel at RUclips. I wish that I had a group helping me with the management ; )
@@excelisfun When you said "We are a team" I thought you are working as group :) But now I got what you mean :) I appreciate about this great channel i will try to deserve whatever you did for us! Thank you so much.
@@enesnature5486 Yes : ) : ) That is what I meant. I guess i am the one that misunderstood you : ( We are 100% a Team!!!!!! I misinterpreted the mangement side of the channel. Yes we are a Team and I would NEVER have the amazing content that I have without all the viewers like you supporting and giving ideas for new videos and showing me better ways to accomplish our excel Goals : ) : ) : ) : )
Great video! I'm not sure if this was mentioned in any of the comments, but when I was following along in the workbook and dragged "Sum of Rev" to the 'value' field, it didn't give me the subtotal for the country categories. I clicked in the "Country" area of the table and then went into PivotTableAnlyze Ribbon. Here, I selected the 'Field Settings' from the 'active field section'. Then I clicked 'automatic' for the subtotals and it popped up! I'm not sure why it was set to "none" or if I did something on my side mistakingly that set it to that, but I was able to figure it out lol.
Hi Mike! Now I know what the Relationships button mean because of you! thanks a lot! By the way, the PDF notes uploaded for EB21 is the same as the PDF notes for EB20?
Yes, Office 365 is THE only version to have now. Remember, buy Office 365, not Excel 2016 or 2019, becasue the Office 365 is the only one with all the new features : )
I have over the years become a proficeint user of Excel, thanks to your channel. I have a question about this video. It seems that the pivot table will only return a calculated value. Suppose you have two tables one called "names" and one called "addresses". Each has a KeyNumber column. Is there a way to create a one to one relationship between the two tables such that when a name is selected the corresponding address will be returned based on the connected KeyNumbers? There is a "no calucation" option on the values button on the pivot table, but that does not seem to work. I'm appreciative of any suggestions. And, I'm appreciative of you making your knowledge so accessible.
Also for others, Mac Office 365 doesn't support relationships. At least I couldn't find it and Google (the first site I went to said it didn't). I had to switch over to my PC for this lesson.
You should not be using a Mac. As you will see many of the amazing tools in the next few videos and all throughout my advanced classes are not in Mac. Since 2009, Microsoft stopped adding many of the important data analysis tools to the mac. Funny thing is, i started with my beloved Mac Excel back in 1990s, but even back then, when i got my first Accounting job, they said loose the Mac because almost all business use PC Windows Excel. It is the same today.
Hello Mike. Thank you so much for your excellent videos. I want to use relationships between tables and I'm familiar with data model. I wonder how to access the resulting table in excel in the same way we'd use the foreign table to print in a report as an example, but not accessing the data via a pivot table. Thanks in advance for your help!
Thank you so much for sharing your knowledge. Your channel has helped me so much in building my excel skills for my new job. I am sure we all are very grateful to share all of this for free. I had a quick question. When I tried to add a slicer the option for years and quarters didn't appear, only the date. I am not sure if relationships allow slicers for years and quarters. Thank you very much for all that you are doing for this community.
@@excelisfun Thank you so much for taking the time to respond. I actually figured it out this morning. The trick is to place the date in the column section. For some reason, it will group the date to year, quarter, and month. Remove the date from the column section and the year, quarter, and month is applied to the pivot table field and now the slicer provides you the year, quarter, and month.
hey sir~ I am so grateful for this series and it's soooo helpful. But in this video I can't find Data-relationships button on excel 2016 for Mac. Any other method to build the table relationships? T T Thank you soooo much for all informative guidance!! Have a nice day!
Thank you so much for the video! First time commenting but I just wanted to say my thanks. I feel that I am late to the game since I only started watching your videos about a month ago but better late than never. I had one question though that I was wondering. How would you explain to a manager that having mulitple tables and connecting them using "relationships" is better and easier than just taking all that data and having it on the same table? I'm just thinking if a manager says, "Hey, that's cool and all, but why have 3 tables in the first place? Why not just put them all into one table? Change the Country codes in the sales table to the full country names. Take all the columns from the product table and just add it the main sales table? Then just use a pivot table then on the one, bigger table. why not?" Thank you again I look forward to learning more from these videos/classes and taking this knowledge to the work force =)
A couple ideas: 1) For small data sets, there is nothing wrong with having one table. Even though, if you use VLOOKUP and have many formulas, as long as everything works quickly, it is not a problem. And, you could use Power Query to merge multiple tables into one and do it that way. 2) Multiple tables solves a few problems that have plagued Excel people for years: if you have large data sets 500,000 to 100 million, then A) Too many VLOOKUPS would slow spreadsheet down, B) Too Much Data would not fit in Excel 3) If you have many items to lookup and have to build many formulas, it can be faster to use Relationships and multiple tables. Unless you really have big data, you can still do it with one table. One final note, as a teacher of this stuff for decades, I have noticed that in the last few years students say they prefer multiple tables and relationships, rather than VLOOUP, becasue they say it is easier.
Excellent! Thank you so much for the response, Mike! I am about to finish the last video of your excel basics series and then plan on starting either your finance class series or advanced excel series. I appreciate the time you've taken to make these videos. And I'm happy to have found a create series of excel related videos that can help me be more confident when searching and applying for jobs in the future. Thank you again!
Query: I am not getting the fourth negative format (negative number in parenthesis) in the number formatting window. I only get -222 (in red color)... Is there anything I can do? I use the Office 2019 student edition. Thank you so much for this video. ❤️❤️❤️
What if you're not trying to make a pivot? What if you simply want a flat table which pulls one field from a related table, depending on what user enters into another field? Thx
When i have 2 tabels , one of them about pay , other about sale , i want make them all in one table to do many calculaters and graphs , is the best realtionship ( Date ) , right ? because i want make weekly and monthly report. And thank you so much
Is there a channel on youtube where you teach MS Access database like you do in excel? Also, I am so glad I invested in Office 365 with the optimal office features so I can use the data analytics features. Also, I have four more videos to complete!! I am getting excited to begin my first data mining/analytics project.
Great video! Wow! The power of relationships is amazing! My question is: Why does excel have to link tables through columns, instead of just linking them by Table Name? You could just match the names of the tables to whichever ones you wanted matched! Perhaps it has to do with how the whole Excel program was written -- for whatever reason the only way for a relationship to happen between tables is for a common column name to be identified. Hmm, interesting. I ask because this caused some confusion on my part and I was afraid I was doing something wrong. I thought I was only connecting one specific column of information to another, and that I would have to manually create a relationship for whichever columns I wanted connected (somewhat a tedious process: column-to-column, create relationship; column-to-column, create relationship; ad nauseam). I was proved wrong though when the field list displayed ALL the different fields (column names) for each table. Goodness, hope this makes sense. Great videos anyway! Thanks!
Just like all relational databases in the world, for a one-to-many relationship (allowing accurate conditional calculation in PivotTable and doing vlookup if you want) you must have a one side and a many side so they can communicate correctly.
Hello Sir, thank you for having the best content, really taught me so so much. Just wondering, what is the workaround if you have multiple dates you want to link to a single calendar table? I have a table of 'Cases' which have open and closed dates for each case and need to try and show these on a single line graph, so I'd need to link both the Open and Close dates to the calendar table but Excel 16.0 allows only one filtering path between tables in a Data Model. An inactive relationship doesn't give me the true figures. With your wisdom do you know a way around this? Thanks again for the content, really great stuff and well explained!
I had a different outcome with my table than that shown in the video. I did not have subtotals for each country. I was able to add the subtotal by right-clicking the Country column and selecting Subtotal "Country". The option then appeared with a check next to it. I am using Office Professional Plus 2016. I don't know if the subtotal option is default in some versions and not in others. Now the Country subtotal row contains an asterisk that does not appear in the formula bar when I select the label's cell. I know this is a minor detail, but I am wondering what is going on.
For some reason at 17:49 my screen isn't providing the second two options. I only have an option to select a table, then it skips to choose where you want the PivotTable to be placed. I followed along and made sure the relationships were activated, yet it's not providing my any radio buttons for the last two options. I have Microsoft 365 Apps for business and am using Excel Version 2206. Any help would be appreciated. Thanks
Even Time when I use the Pivot Table the total of "Algeria total" and "Armenia total" and so forth in pivot table is no showing. How Can I make that necessary change? Thankyou
Hi Nice explanation. I have a table with team leads against their team members. I have created a unique id with employee id. Another table contains team members along with team lead names and empl id. So can you please let me know how to arrange them in order to set data model.
Great talent for teaching, but I can't get the Worksheet Table or Data Model Table options to show when creating a new relationship. Would love some help for anyone willing! Thanks!
Thanks for such a great video. But when I drag standard cost, it require me to create relationship again even I already created Sales table with product. Thanks for the response
Thank You Teacher. I want to ask you a question every time i download and open excel file it says" SECURITY RISK Microsoft has blocked macros from running because the source of this file is untrusted." How can i remove this warning.
Hello Mike- I am using excel 2016 standard version, which doesn't have a power pivot, how can I create a pivot table from two tables having duplicate values in each table, I cannot create a relationship as that would require unique values in at least one table.
When you created the pivot table and chose your style, you had an extra row that showed the total for the country. What is the setting I need to change to show that?
Good refresher video :) Can you add/delete tables/relationships in the behind the scenes Data Model and will it affect the original relationships you built via the Relationships button?
100% yes! Just like it say in this video, as soon as you use Relationships the tables and Relationships are put into Data Model. Then if you go to the Data Model and delete or change that relationship is changed too.
That is what is so cool about the Relationships button, you don't even need to open Data Model, just build relationship and build your PivotTables - no need to have Power Pivot if you don't need to make a complicated model : )
Hey mike, I didn't know anything I excel when I started your beginner series but at this point of time I feel like I know more than beginners and thankyou very much for your work. This videos are so awesome and now it feels like Excel will not be part of my professional work anymore It will be a permanent hobby and be a part of my hobbies just because of you thankyou very very much. I will always grateful of you.
You are welcome!! Yes, my basics class is more than just basics : )
This video opened my third eye
You have a great knowledge, your channel is wonderful. I was applying for a job that needs EXCEL and i passed the job exam because of your videos. Thank you very very much words wouldn't express how thankful am I. I wish you all the very best in your life and in all what you are doing. God bless you, your family and all your beloved ones. again thank you very much
I am super happy to hear that the videos helped you pass your job interview!!! Thank you for the support with your comment, Thumbs Up and Sub : )
Of course. now I'm more interested to learn about excel
Awesome!!!
You probably dont care but if you are stoned like me atm you can stream pretty much all of the latest series on InstaFlixxer. Have been binge watching with my girlfriend recently xD
@Karter Stanley yup, been watching on InstaFlixxer for since december myself :D
Honestly, this is the first time I'm seeing it in excel. This resembles powerbi and tableau. No wonder they say make sure you have a deep knowledge about excel before jumping to powerbi and tableau. Thanks so much for this, Mike. This goes a long way.
You are welcome, Abdulsamad!!! It is 100% true that for the working world you must have a deep knowledge of Excel : )
I love how you make each of your videos so easy to understand. I also love how you incorporate repetitiveness in your videos this really helps it to sink in. You teach Excel better than anyone else I've watched. Thank you for service.
You are welcome! The repeat is intentional - it comes from the fact that it is hard for me to learn new things - so when I learn I have to work hard and repeat, repeat, repeat!!! : )
Thanks you for the support with your comment, Thumbs Up and Sub : )
I love the energy in your voice, so much good vibes every time you talk. Awesome! thank to you I've learned a lot about excel in just few lessons. Keep it up.
You are welcome, Lord : )
I watched and practiced 15 video of in a row, I just wonder how you made it so simple and organised. Love from India:3
I did it by good story telling and 20 years as a teaching : ) Thanks for the love : )
Thank you so much for this video. I recently began to learn PowerBi and even after watching multiple videos I never understood the relationship, primary, foreign concepts. It was surprising to see these concepts in Excel and now I have finally been able to understand it.
Glad to help!!!!
@@excelisfun I wish you would teach power bi too someday! I really enjoy your teaching method
Everytime I study one of your classes I found something interesting, new and ready to apply. Thank you Mike.
Yes, Relationships are such a great tool in the "New Excel"!!!
I totally love this way of drill-down-repeat-drill-down-repeat explanation
Yes, sir! Good stories make it easier to learn and makes it more fun, at least I hope : )
Having watched & studied many of your videos, I want to say that your presentation of a complicated subject is very well organized & makes a complicated subject much easier to understand.
That has been my goal for my 20 years of teaching. Thanks for noticing : ) : )
Sir,
Your every video is more than excellent. I am so confident that there is no other advanced tutorial and trainer like you. I am not interested to follow any other trainer's tutorial if you have tutorial in which I am interested to learn. If it is possible, I will give a lot of Thumbs Up in every of your videos. We need trainer like you to make the world more better. I usually suggest to download and follow your videos whoever want to learn excel meeting with me. You are the best. Thanks a lot to keep love and patience in your teaching field.
You are welcome! Thanks for the kind words : ) Please do tell all your friends : )
I'm going through each of your videos in this current Excel Basics Playlist. I start my new job on Monday and want to be super prepared with my excel. I'm hoping this playlist and the Excel Advanced playlist can prepare me. They just wanted basic excel skills but you never know.
You should be overprepared with all these videos. I bet you will do great : )
@@excelisfun I hope so. I have been unemployed since last Feb. Breaking into procurement/supply chain is tough, especially to get an entry level role despite having some relative experience. Sometimes I don't feel like I'm good enough and need to keep learning more excel skills but it may be overkill.
@@bigjuicefolife It is not overkill. The better you are at Excel the better chances of getting a job and keeping it : )
Very interesting and clear explanation for the topic, specially the 1->Many relation. Thanks a lot ^_^ . The way I understand it is we use "Relationships" when we need to create a report from a table that does not exist, so first we need to create that table, then we create our pivot table.
It is also used when a lookup table (also known as dimensions table) does exist. If you have a sales table and a lookup table from product price that already exists, and there is a 1-many relationship, you can use the relationship.
@@excelisfun Thank you ^_^
STUNNINGLY CLEAR!!! FINALLY!!! You are the best teacher ever!!! A MASSIVE THANK YOU!!!!!!
I'm really having fun listening to you, you're a great teacher 🙌🙌
I am so happy to hear that. This means we are both having fun with excel : )
The best I have seen thus far. Terrific and great job!
O Yes, the new and amazing Power Pivot and Relationships!!!!
Your channel is great and so useful. In my working life, I worked with OLAP database and lots of other database environments. I wish I had Excel at this level back then. We used Excel to prep data but this is amazing. I'm retired now and just enjoy learning this. One thing I have noticed is that MS must have decided to give this part (relational data model) to the world that used Office 365. I just have Office 365 Home and I have the Power Pivot engaged. I am really looking forward to the advanced Excel videos that you have and then on to Access. Thanks a lot, Mike. Originally from Oregon and Washington...now in Florida... Irv
First watch the videos in this Basics Playlist, then go on to the Advanced Excel (Highline Excel Class) playlist. If you need help fining the videos or playlists, just let me know.
Great explanation! Thank you for going behind the scenes to show the Data Model!
The most useful channel on RUclips. Thank you.
Small note about the last HW instruction, it says "Counts How Many Sales" but I think you meant Sum of Sales/Revenue. Thankfully it's a quick fix to change Pivot Table Values from Count to Sum!
Thank you for that correction : )
You're really really reeeeaaaaally good at explaining! I have basic pivot table knowledge and you just helped me tremendously - thank you soooo much!!!
When it is fun, the the explaining is reeeeaaaaally easy, and good for you!!!! Thanks for your support, Little Luuluu, with your cool comment, Thumbs Up and Sub : )
Again, this is the BEST you tube channel I found! Thank you!!
Glad you enjoy it!
Your channel is fantastic, I've learnt a lot, I have become an addict of your videos, thanks a lot for sharing your knowledge.
You are welcome, Wilson A!!!
thank you very much Sir, God Bless. All your videos are the best very comprehensive.
You are welcome very much! I am glad that the videos help so much! Please do tell all your friends : )
Thank you Mike, I've done your homework and it opens my mind and gives me motivation to learn more about data relation. Undeniably very good tutorial and exercise
Indeed Masterpiece 🙏
Glad you like it!!!
Another fantastic video!
Glad you like it!!!!!
I like how you call this all Excel Basics, but in all my classes at school, this is considered "advanced" haha
Yes, that is 100% true. My Basics classes are advanced, and my advanced classes are all about stuff that is not in any school text books : )
Totally agree!
This's my 1st time get to know this 'relationship' feature in excel, amazing! U would be a very good teacher on PowerQuery & PowerBI as well
I have 100s of videos about those : ) : )
Data Analysis Basics Playlist: ruclips.net/p/PLrRPvpgDmw0lPPRiJO5dCUratRGpGx3aT
Advanced Data Analysis playlist: ruclips.net/p/PLrRPvpgDmw0ks5W7U5NmDCU2ydSnNZA_1&app=desktop
wow...I really enjoyed your tutorial sir, thank you very much.
I can't to wait to start you advanced series - and go beyond that.
Thank you for your videos. It's very helpful.
You are welcome for the videos, Law!!!! Thanks for your support with your comment, Thumbs Up and Sub : )
Mike, you are a master in Excel data integration..!!!!!
I was just trying to do a similar job on my file and thought I would see an answer in your tutorials.
What I’m thinking now is, I would have a transaction table every month and I need to adopt similar approach by loading the data located in a folder into data model and doing lookups.
In SAP context, you have material number to track sales / production / inventory quantities and you wish to report by product / segment.
How can we go about this?
Thanks for the video! Great stuff as always!
Thanks, Teammate!
Wow Mr.Girvin more then great
Glad it is great for you, Hazem!!!
Hi Mike, Huge thanks for putting these vides together, I appreciate this resource so much. Question: in this video and the previous one, when I download the files to follow along and then click "enable editing," I get the message "Microsoft has blocked macros from running because the source of this file is untrusted." Is macros a thing I need for these worksheets, and should unblock so it comes through? I haven't had this message pop up on any other previous files I've downloaded as I've been going through this class series.
For me it was an advance level ❤️
I would love to explore your advance level excel 😍
What another great video, Many thanks for your efforts.
You are welcome!
it's really something i don't want it to end
It does not have to end!!! There are many, many more videos to come : ) If keep watching, clicking thumbs up and commenting, the videos will keep on coming : )
ExcelIsFun i always do that
Dear Mike, its a great feature in power pivot but then it comes with a rule and which is that you cannot put many side table lookup in pivot row. It has to be always coming from a lookup table. If you put a lookup from a table which has repetitions in pivots rows you would not see the result. However, i know then you can use related function but it should have been in Relationships too. I mean why we have to be selective in dropping the items always from unique table in pivot row field. Correct me if i am wrong...
I do not understand your question, Ajay.
I have a whole comprehensive video about Power Pivot, and one of the thing the videos shows is what happens when you use the Many Side or the One Side: ruclips.net/video/Rbkbr89cuHo/видео.html
The best video i have ever seen. You are so kind about everything i liked ur channel too much!
That is great that it is useful for you and you like it too much, Enes! We are a Team: I post the videos and viewers like you can learn and support with cool comments and thumbs ups : ) : ) : )
@@excelisfun You guys so nice and kind. People like you deserve much more! Appreciate again because you are teaching really good and explaining everything that you know.
@@enesnature5486 Remember: it is not "you guys". It is just me. I create, post and manage everything here at the excelisfun channel at RUclips. I wish that I had a group helping me with the management ; )
@@excelisfun When you said "We are a team" I thought you are working as group :) But now I got what you mean :) I appreciate about this great channel i will try to deserve whatever you did for us! Thank you so much.
@@enesnature5486 Yes : ) : ) That is what I meant. I guess i am the one that misunderstood you : ( We are 100% a Team!!!!!! I misinterpreted the mangement side of the channel. Yes we are a Team and I would NEVER have the amazing content that I have without all the viewers like you supporting and giving ideas for new videos and showing me better ways to accomplish our excel Goals : ) : ) : ) : )
This is really an amazing feature! Thank you for teaching us.
You are so welcome, Nyuklehead!!!!
Thank you for a good excel video.
Thumbs up absolutely! This is so clear and useful. Thank you my instructor!
You are welcome for clarity and usefulness, Donghua!!!! Thank you for your support : )
Thanks for sharing ❤
You are welcome!!!
Great video! I'm not sure if this was mentioned in any of the comments, but when I was following along in the workbook and dragged "Sum of Rev" to the 'value' field, it didn't give me the subtotal for the country categories. I clicked in the "Country" area of the table and then went into PivotTableAnlyze Ribbon. Here, I selected the 'Field Settings' from the 'active field section'. Then I clicked 'automatic' for the subtotals and it popped up! I'm not sure why it was set to "none" or if I did something on my side mistakingly that set it to that, but I was able to figure it out lol.
I am not sure, but thanks for being smart and figuring out to fix it : )
Thank you so much for making and sharing this video. It is so great.
You are welcome, Nga!!!! Thank you for your support with your comment, thumbs up and your Sub : )
I am going to write it for one by one of the videos.
you are god!
Thanks for your support, Nima : ) : )
SO HELPFUL
this was fun.Keep up the good job
Glad it was fun for you!!! Thanks for your support!
You are great sir.
Glad you like the videos, Dipak! Thanks for your support with your comments, thumbs ups and your Sub : )
Hi Mike! Now I know what the Relationships button mean because of you! thanks a lot!
By the way, the PDF notes uploaded for EB21 is the same as the PDF notes for EB20?
Thank you very much for helping me to find and fix the errors in the class web site. I have fix it now.
Now I know what I want Santa to bring me - an updated version of Excell!!
Yes, Office 365 is THE only version to have now. Remember, buy Office 365, not Excel 2016 or 2019, becasue the Office 365 is the only one with all the new features : )
@@excelisfun Thank you for that info. I will pass it along to Santa!
@@stephanieclancy254 : ) : ) : ) : ) Here is an Excel Santa video, just for fun: ruclips.net/video/3kn4Nm27shI/видео.html
Thanks for another great lesson! Keep up the good work!
You are welcome, Elmar! Thanks for your support of my efforts to bring free education to the world with your comment, thumbs up and Sub : )
Tanq Sir for your Quality of Information. :)
You are welcome, Bharat!!!
I have over the years become a proficeint user of Excel, thanks to your channel. I have a question about this video. It seems that the pivot table will only return a calculated value. Suppose you have two tables one called "names" and one called "addresses". Each has a KeyNumber column. Is there a way to create a one to one relationship between the two tables such that when a name is selected the corresponding address will be returned based on the connected KeyNumbers? There is a "no calucation" option on the values button on the pivot table, but that does not seem to work. I'm appreciative of any suggestions. And, I'm appreciative of you making your knowledge so accessible.
great job sir, appreciate it
Relationships are fun : ) : )
Also for others, Mac Office 365 doesn't support relationships. At least I couldn't find it and Google (the first site I went to said it didn't). I had to switch over to my PC for this lesson.
You should not be using a Mac. As you will see many of the amazing tools in the next few videos and all throughout my advanced classes are not in Mac. Since 2009, Microsoft stopped adding many of the important data analysis tools to the mac. Funny thing is, i started with my beloved Mac Excel back in 1990s, but even back then, when i got my first Accounting job, they said loose the Mac because almost all business use PC Windows Excel. It is the same today.
Hello Mike. Thank you so much for your excellent videos. I want to use relationships between tables and I'm familiar with data model. I wonder how to access the resulting table in excel in the same way we'd use the foreign table to print in a report as an example, but not accessing the data via a pivot table. Thanks in advance for your help!
Thank you so much!
Thank you!
Thank you so much for sharing your knowledge. Your channel has helped me so much in building my excel skills for my new job. I am sure we all are very grateful to share all of this for free. I had a quick question. When I tried to add a slicer the option for years and quarters didn't appear, only the date. I am not sure if relationships allow slicers for years and quarters. Thank you very much for all that you are doing for this community.
If the dates are grouped, it should appear in Slicer. You can also try the Timeline option.
@@excelisfun Thank you so much for taking the time to respond. I actually figured it out this morning. The trick is to place the date in the column section. For some reason, it will group the date to year, quarter, and month. Remove the date from the column section and the year, quarter, and month is applied to the pivot table field and now the slicer provides you the year, quarter, and month.
@@emmanuelavila8035 : )
Thanks a lot mike!
Relationships are awesome! You are welcome as always, Nika : ) : )
Another gem!!
Fun and enjoying
Glad it is fun and enjoyable for you, Rodolifo!!! Thanks for your support with your comment, Thumbs Up and Sub : )
Thanku sir so much
hey sir~ I am so grateful for this series and it's soooo helpful. But in this video I can't find Data-relationships button on excel 2016 for Mac. Any other method to build the table relationships? T T Thank you soooo much for all informative guidance!! Have a nice day!
Mac didn't have support for Relationships
Thank you so much for the video! First time commenting but I just wanted to say my thanks. I feel that I am late to the game since I only started watching your videos about a month ago but better late than never.
I had one question though that I was wondering.
How would you explain to a manager that having mulitple tables and connecting them using "relationships" is better and easier than just taking all that data and having it on the same table?
I'm just thinking if a manager says, "Hey, that's cool and all, but why have 3 tables in the first place? Why not just put them all into one table? Change the Country codes in the sales table to the full country names. Take all the columns from the product table and just add it the main sales table? Then just use a pivot table then on the one, bigger table. why not?"
Thank you again I look forward to learning more from these videos/classes and taking this knowledge to the work force =)
A couple ideas:
1) For small data sets, there is nothing wrong with having one table. Even though, if you use VLOOKUP and have many formulas, as long as everything works quickly, it is not a problem. And, you could use Power Query to merge multiple tables into one and do it that way.
2) Multiple tables solves a few problems that have plagued Excel people for years: if you have large data sets 500,000 to 100 million, then A) Too many VLOOKUPS would slow spreadsheet down, B) Too Much Data would not fit in Excel
3) If you have many items to lookup and have to build many formulas, it can be faster to use Relationships and multiple tables.
Unless you really have big data, you can still do it with one table.
One final note, as a teacher of this stuff for decades, I have noticed that in the last few years students say they prefer multiple tables and relationships, rather than VLOOUP, becasue they say it is easier.
Excellent! Thank you so much for the response, Mike! I am about to finish the last video of your excel basics series and then plan on starting either your finance class series or advanced excel series.
I appreciate the time you've taken to make these videos. And I'm happy to have found a create series of excel related videos that can help me be more confident when searching and applying for jobs in the future. Thank you again!
You are welcome! Please help support this free resource that I post with those Thumbs Up and comments on each video that you watch!
Great video
Hi Mike! Just a heads up the finished Excel file only has a red HW tab (no blue one to try on)
Query: I am not getting the fourth negative format (negative number in parenthesis) in the number formatting window. I only get -222 (in red color)... Is there anything I can do? I use the Office 2019 student edition.
Thank you so much for this video. ❤️❤️❤️
Thank you so much. For the homework, shouldn't it be count of revenue instead of sum of revenue
What if you're not trying to make a pivot? What if you simply want a flat table which pulls one field from a related table, depending on what user enters into another field? Thx
When i have 2 tabels , one of them about pay , other about sale , i want make them all in one table to do many calculaters and graphs , is the best realtionship ( Date ) , right ? because i want make weekly and monthly report. And thank you so much
Is there a channel on youtube where you teach MS Access database like you do in excel? Also, I am so glad I invested in Office 365 with the optimal office features so I can use the data analytics features.
Also, I have four more videos to complete!! I am getting excited to begin my first data mining/analytics project.
I do not of a full Access RUclips Channel... Yes, Office 365 is THE only version to have - so much fun!
thanks, this is great.
how do you create a calculated field while using data model in pivot table?
Great video! Wow! The power of relationships is amazing!
My question is: Why does excel have to link tables through columns, instead of just linking them by Table Name? You could just match the names of the tables to whichever ones you wanted matched! Perhaps it has to do with how the whole Excel program was written -- for whatever reason the only way for a relationship to happen between tables is for a common column name to be identified. Hmm, interesting.
I ask because this caused some confusion on my part and I was afraid I was doing something wrong. I thought I was only connecting one specific column of information to another, and that I would have to manually create a relationship for whichever columns I wanted connected (somewhat a tedious process: column-to-column, create relationship; column-to-column, create relationship; ad nauseam). I was proved wrong though when the field list displayed ALL the different fields (column names) for each table.
Goodness, hope this makes sense.
Great videos anyway! Thanks!
Just like all relational databases in the world, for a one-to-many relationship (allowing accurate conditional calculation in PivotTable and doing vlookup if you want) you must have a one side and a many side so they can communicate correctly.
Thx 👍
Glad you are learning Excel form these videos, Lalit!!!!
4:42 Whoaaa!!!! Fun Treasure Hunt Game ..
Yes indeed, treasure hunts are fun : ) : )
Hello Sir, thank you for having the best content, really taught me so so much.
Just wondering, what is the workaround if you have multiple dates you want to link to a single calendar table?
I have a table of 'Cases' which have open and closed dates for each case and need to try and show these on a single line graph, so I'd need to link both the Open and Close dates to the calendar table but Excel 16.0 allows only one filtering path between tables in a Data Model. An inactive relationship doesn't give me the true figures.
With your wisdom do you know a way around this?
Thanks again for the content, really great stuff and well explained!
I have some videos on two dates. Here is one:
ruclips.net/video/HJ9NMJpqJjE/видео.html
I had a different outcome with my table than that shown in the video. I did not have subtotals for each country. I was able to add the subtotal by right-clicking the Country column and selecting Subtotal "Country". The option then appeared with a check next to it. I am using Office Professional Plus 2016. I don't know if the subtotal option is default in some versions and not in others. Now the Country subtotal row contains an asterisk that does not appear in the formula bar when I select the label's cell. I know this is a minor detail, but I am wondering what is going on.
Click a cell on table, go to Designs> Subtotals> Show all subtotals.
@@pierregiancarlo omg thank you so much, it works now. This was driving me crazy for like 30 minutes trying to figure out how to get those subtotals.
For some reason at 17:49 my screen isn't providing the second two options. I only have an option to select a table, then it skips to choose where you want the PivotTable to be placed. I followed along and made sure the relationships were activated, yet it's not providing my any radio buttons for the last two options. I have Microsoft 365 Apps for business and am using Excel Version 2206. Any help would be appreciated. Thanks
Even Time when I use the Pivot Table the total of "Algeria total" and "Armenia total" and so forth in pivot table is no showing. How Can I make that necessary change? Thankyou
Hi
Nice explanation.
I have a table with team leads against their team members. I have created a unique id with employee id.
Another table contains team members along with team lead names and empl id.
So can you please let me know how to arrange them in order to set data model.
Great talent for teaching, but I can't get the Worksheet Table or Data Model Table options to show when creating a new relationship. Would love some help for anyone willing! Thanks!
Thanks for such a great video. But when I drag standard cost, it require me to create relationship again even I already created Sales table with product. Thanks for the response
Thank You Teacher. I want to ask you a question every time i download and open excel file it says" SECURITY RISK Microsoft has blocked macros from running because the source of this file is untrusted." How can i remove this warning.
Hello Mike- I am using excel 2016 standard version, which doesn't have a power pivot, how can I create a pivot table from two tables having duplicate values in each table, I cannot create a relationship as that would require unique values in at least one table.
Good one ... Thanks Mike
You are welcome, Syed Muzammi Mahasan Shahi!!!!
What if for Algeria, the Revenue of Advanced Product Category is 0 or empty. How can you hide it?
So u are slowly roping in daxx... great 😁
Not yet, not for many months until the big DAX series : (
Thank you So much!
Great video as always! One thing I noticed, which I found strange was that you were still using Windows 7! May I know why?
Hi i am stuck while i am using your file or my file during making relationships Datamodel not coming
Also stuck here.
Magnificent!
When you created the pivot table and chose your style, you had an extra row that showed the total for the country. What is the setting I need to change to show that?
Click a cell on table, go to Designs> Subtotals> Show all subtotals.
Excellent Job
Thanks you : )
Good stuff Mike
Glad you like it!
Good refresher video :) Can you add/delete tables/relationships in the behind the scenes Data Model and will it affect the original relationships you built via the Relationships button?
100% yes! Just like it say in this video, as soon as you use Relationships the tables and Relationships are put into Data Model. Then if you go to the Data Model and delete or change that relationship is changed too.
That is what is so cool about the Relationships button, you don't even need to open Data Model, just build relationship and build your PivotTables - no need to have Power Pivot if you don't need to make a complicated model : )
Wonderful ..
Glad the video helps, saeed : )