REPLAY - Your Excel Questions on CUBE functions, Accounting skills, Forecasting, SUBTOTAL & More

hello everyone welcome to our monthly q a webinar this is the first time i'm doing a q a style web in a live stream instead of the usual topical ones and i moved this one week prior but first just tell me uh in the chat if you can hear me loud and clear [Music] so i hope you're able to hear the audio well um but uh it is so good to see you all uh in in our november live stream i am super excited for today because this is completely like an open-ended thing i have not prepared any specific topic instead i want you to ask some questions and answer those questions through through the this medium i do have an excel file so i'll open that and i'll get into that in a minute but for now uh let's get started with some hollows and uh and you know greet some of those regular viewers as well as the very people who are uh coming on to the stream for the first time so um i just will we got some of the regulars like uh bharatharam uh who is saying uh namaste hello bertram how are you and um this is erica who is uh um saying uh hello from amherstfort netherlands hey eric how are you uh must be late uh late in the afternoon i think uh evening for you so and david says hello hi david and uh philip is counting cup of coffee so i just went downstairs and made this cup of coffee so i didn't bring my uh flask uh style cup because yeah i'm not gonna drink a lot of coffee i've been having a little bit early start for the last five days now because i was running my first ever live power bi cohort training i finished that on yesterday so we were doing that whole week last week and it was like an awesome experience just that i had to get up super early to get ready for the sessions and by the time they would finish it's almost 11 o'clock so lunch time uh and i'm really looking forward to the weekend when i'll finish this and you know chill out with my kids i guess david comes back and says it's 11 a.m in portland maine uh that's not bad actually and we got uh mahmoud from egypt do you tell me in the chat uh what what you're drinking what is it that you you're doing right now as as you get onto the stream um this is funny uh atul says you forgot to comb i wish i could actually comb and control my hair it's now completely gone crazy so i i decided to just leave it like that it's about 5 a.m so i just got up like an hour ago to get get set for the session and make sure everything is ready but this is the usual morning hair so yeah if you want what i offer then you have to live with the hair that's the rule and this is roberto he says hi from brazil uh hi roberto how are you um and uh baseem says greetings from cairo it's 6 p.m now how is bar funny [Laughter] bar is doing super awesome he's he's the most hard imaginary employee of the awesome chocolates company so and ramesh says hi chandu how are you i am doing super good ramesh how are you uh how's your week been and uh i hope you're uh looking forward to the weekend uh we'll start taking the questions in a in a few minutes so hold on to your questions on excel uh you're of course you're more than welcome to post them there but uh and rabbi says hi do your hair is really super low uh thank you thank you ravesha my wife who uh some of you know this but most people may not we actually study together in college so we kind of know each other for 22 years now when when we were in college and soon around the final year of college we started dating and then we we we dated for several years before we got married so during that time in college she she would now tell me but she always thought that you know i would go bald uh this is the trajectory on which i am so she's like pleasantly surprised that i'm still not balding and you know the hair keeps growing like every time we have like a reunion phone call with the rest of the school college friends most people have receding hair lighted i still have hair which is crazy raihan asks when new zealand will re-open for international students i wish i know the answer brother but i have no idea really what the government policy is they might open next year after that who knows you see h o o b i think a tube uh he says hi chandra which place in india you belong to so i belong to uh andhra pradesh um and uh from in southern india but uh i kind of lived in different parts of india during my growing up and studies time so i lived for some time in tamil nadu and then i studied in madhya pradesh i worked briefly in mumbai and you know yeah all over the place really but just before we moved to new zealand we were living in vishakhapatnam on the base so dilip uh says please speak one word in telugu namaskaram i'll speak many words dilip in fact dilip is my brother's name so i'm gonna [Music] so yeah which simply means how are you brother are you good okay so what i will do is uh we're getting some um some nice messages in the chat i'm just kind of waiting until people who are just signing into the stream uh they they come um and this is shame ahmad chaudhary he says thank you for amaze your amazing contribution uh thank you thank you so much shaim for that nice words and yeah i i really enjoy um talking about technology sharing whatever i'm learning and building that community so that we you know it creates that side kind of like a positive environment where everybody is learning and sharing and you know it contributes to all of our growth and and well-being and happiness so that's that's really what i want i want all of us to be awesome so i enjoy doing what i do this is why i get up early this is why i do everything this is an interesting question arthur asks do you write a script before shooting so normally i'm not writing script i'm a little too lazy for that sort of thing but what i do is i'll show you my working file uh so i go i have a notepad on my desk in fact i'm kind of very notorious for keeping notepads with me all my life and every time i'm shooting something like a video i mean for the live stream there is literally no script i do kind of write some bullet points if i'm hazy about what i will say uh or if i want to for example have a a unusual or a humorous start to my video like some of my videos have a small intro skit kind of thing where i do something with my family or i introduce my clone chantu into the video so then i'll have to think about you know what's what's the sequence is like so that i'm not really wasting time but otherwise it's all freestyle abdul says okay you are not seeing any message of mine well i saw this one but you didn't ask me the question there okay let me print the official excel q a session message there welcome to the excel for masculinity session uh as i said this session is your session you decide what gets covered uh for that specific reason there is no example file right now but at the end of the stream if i end up creating two or three workbooks i will share them with you i do have a very very small presentation which will kind of take us through the the process for getting into the stream i was a little too ambitious with this stream because i thought you know we could actually do like a live zoom call thing as well for if someone wants to ask a question but it's very hard to explain in the chat you could call me on zoom and ask me that but i was kind of naive with that i thought it will be easy for me to integrate all of that into my streaming setup but in all likelihood we are not going to do the zoom call so we will use the comments to drive most of the discussion um and and then see that but you know i i did mention that but i i don't think i can go ahead with that because it was saying i need to install some additional software now which is super crazy i'm not going to do any installation now so current says hi chandra you're amazing in excel hoping to see many more successes to you buddy thank you karen thank you so much for that cheerful message and uh uh you're also amazing for taking the time to write that message this is s l akane says best contributor on your forum alan sidman hui veal at him and bosco yip oh these are like rock stars i mean if you think what i do in excel is good you got to see what these people do on my excel forum they are like at a different level altogether and they they share such amazing tricks and ideas some from time to time i would go there like when i created the forum i was quite regular there but very soon these people overtook me it's like a race and they just went way past beyond me and then at one point i stopped even bothering going there because i'm like okay these people are so good you know why why am i even bothering [Music] the deep shikha says please read my comments sir i'm deep shikha from nandigama and i speak telugu and it's 9 p.m here i'm eating dinner please call me chilli hello chilli uh uh chilly simply means sister uh hi deep shikha uh i hope you're doing good um i i have a very good school friend who used to live in nandikama so uh and i i i i'm born and brought up in machilipatnam and i studied in vijayawada so i'm kind of very familiar with where you are i hope you're doing good enjoy your dinner and danny says why do you shoot live early morning um i could do this at any point of the day and and i would still not be able to reach out to a part of my audience so i figured for live stream early morning would work best for me because i'm an early starter just that i don't wake up at 4 am but i do kind of wake up well before 6 on most days and the second thing is it's kind of there is no other disruptions when i'm doing the live stream like kids are asleep um nobody is there the streets are super quiet and most likely the internet connection will be smooth as well so there's no disruption so for all those reasons i like to do it in the morning i know it's some for some people this timing is not working like especially every time i do my live stream announcement i get like at least one or two emails from my australian friends saying mate it's almost midnight when you do this because it's five in new zealand most cities in australia are at least two hours behind so three a.m some people one a.m in the night and they're like nah we're not gonna attend this so that's that uh anyway we're getting like a lot of questions um i will um i'll address one or two these are generic questions so i'm assuming this is all q and a uh which company do you work for uh i'm definitely not working for secret lab that's just the chair company in which i sit but i don't work for anyone i work for myself i have been running chandu.org as a business since uh 2000 i want to say 2010 middle so yeah almost 11 years now i've been self-employed but from time to time i do work with companies and government ministries here in new zealand as a consultant or a contractor for some special projects and then i but it's not a full-time job i usually do this like for two three days a week and that's a very very good way for me to get into some industry or an area which i don't have any familiarity learn about the technology learn about people learn about the processes and then use that to enhance my my material on the website or courses or my youtube channel okay so should we get into the presentation and then when we when we do those three or four slides we'll then come back i'll start looking at some of these questions i'll take up uh a topic i'll talk a little bit and then we'll we'll see how this goes um i know you people are getting a real really anxious like ah i want to ask the questions and i can see that so many interesting questions are coming but we'll just do the slideshow first and then get into that meanwhile feel free to get a sip of whatever it is that you're drinking and enjoy the show let's just um share my screen not not that slight show excuse me while i figure this out because i think it's going to the other screen primary monitor all right here we are your excel questions with chandu first up i want to say a big thanks to everyone who showed up today as well as some of my live streams that i did this year this is something that new that i started this year and i was quite nervous to get into live streaming primarily because this adds a lot of unnecessary uncertainties and things could go wrong it's completely live there is no way for me to go back and edit things out or i wasn't even sure if people would show up for that so i'm really happy uh with the way these live streams turned out and more importantly i am really thankful and happy that you showed up uh to spend some time with me share your messages and love as well as learn and teach me so big thanks to everybody who who took some time out of their day to join me on the live stream uh i also want to say thank you mainly for the other reason that this is going to be my last live stream for this year the reason why i pulled it one week prior is because next weekend is actually thanksgiving holiday in u.s and and then the last week of december we i usually do the live stream on the last friday of the month so last week of december we got the christmas holidays so uh this is my last live stream for 2021 i will actually see live you i mean do the live stream again next year uh at the either end of jan or at the end of february depending on what happens during my holiday schedule but uh i want to take this opportunity to um like face to face thank you all for uh for attending the live streams being with me on this journey i was actually quite curious just beforehand to see uh you know what kind of reception these live streams had uh or at an overall level and it is completely overwhelming like i was just counting how many chat messages people left on the live stream videos that i've been doing so some of you might know but i've been doing live streams every month since february every month on the last friday so about 10 10 videos 10 live streams have done and over 5000 chat messages you know that is completely overwhelming many of you have written to me to say uh how how uh how you enjoy my content and all of that so it is amazing and it is awesome thank you so much um for for being with me and i wish you a very very happy end of the year because i'm not i will of course put some other messages on my channel and there will be regular videos anyway until end of the year only thing live stream this is the last one next year is the first one so i thought you know this is a good point for me to say that thanks personally what's gonna happen today you ask questions i'll try to answer them and then we will discuss my answers if you have some follow-up questions in the chat and comments and then we will take more questions as simple as that it's a very very simple process really ask questions i'll try to answer them i made up some random data of awesome chocolates in an excel file so i'll try to use that as a basis to help you but if i if i'm not able to do a good job or if if your question is something that requires me to create some other data set then i'm not then i will generally talk about it and maybe show you which button or which formula is is gonna give you the answer that you're gonna you want this is the slide that i was talking about i was hoping to do a live zoom call thing because certain questions you cannot type like even seven sentences and explain them it's a lot easier if you call me and tell so i thought i'll do like a link through which you can call me it will open up the zoom meeting and i could invite you into the zoom call and i could screen share and all the thing that i hadn't realized is you can hear my voice but it will all be useless if you can't hear what the other person is saying and for that to happen apparently there needs to be some other software installed which i found out too late in the process so i'm not going to do this thing i just left the slide out but so before we begin what's going to happen with the rest of the stuff how to ask questions what is download and what is super so i will post some completed excel workbooks whatever example i will show on the screen those i will combine into one or two files and then i will post that but after the stream i don't have any workbooks for you right now instead we will just you know you could use excel made up some random data or whatever and then watch it um but at the end of the stream if i have anything significant i will put that as a link in the video description from which you can download you are of course this whole point is you can ask questions so start your questions with q that way when i'm reading your chat messages i can see your question and please stay on the topic the topic for today is excel related data analysis through formulas that's the kind of narrowed down topic you are more than welcome to ask questions on power bi pivot tables dax and whatever else that you can think of but i may not be able to address those but if you stay on the topic which is data analysis through excel then i will try to help you out maximum again we have uh 200 people on the stream as of now so if even everybody asks a question and it takes five minutes for me to answer i won't be done so i'll try to answer as many as possible but you know i'm not really making any promises and if you ask that question and i haven't gone come to you please do not repeat uh it will just clog up the messages and it's not gonna help anyone and you can of course support the stream as well as uh this with a super chat or a super message it is really simple and it's a fun way for you to support me all you gotta do is just click on the dollar thingy there and you know select an amount or an icon and send it to me it's a very very simple process again some of you allowed to do it so i thought that's um that's what i'll show all right we'll start with the joke and then we'll go from there what do star gazers do they spread sheet and look up go now let's go into excel i'll look at your questions and then we will uh we'll get there all right eric asks eric is one of our regulars so let's uh let's see what he has what are cube functions where when do you use them cube functions are a set of functions that are available in excel that you can use to talk to the data model functionality of excel so some of you already know this excel has a data model functionality which is a kind of loosely power pivot essentially and you could use power paper to make pivot tables but sometimes you don't want to make a pivot table you want to get the data that is in the pivot table as a formula so for regular pivot tables we got get pivot data which you can use to talk to pivot table get the data but power pivot is a whole another beast so this is where the cube formula comes in cube formula is a set of formulas using which i can talk to pivot tables uh get some information out of the pivot table [Music] when the pivot table is constructed through power pivot and use it now the that is what a cube function when where do you use them i almost never use these functions again that simply because i find them that they're a bit too clunky to work with i wish they they are rather simple to work with but problem is they are somewhat tediously designed and probably put there for certain other purposes but they are in my opinion clunky to work with i don't almost use them i do use them from time to time to enhance a certain thing on my dashboard where there is literally no other alternative and i have to use them so unfortunately i haven't got any articles on my website on cube functions that go beyond the basics but i want to say chris webb [Music] has got some excellent articles on on these so i'll just say chris webb q functions yep oh this is probably an older one newer one yeah so um somewhere on one of these website i think his his website is what i remember has got a like a set of tutorial that go through how you you do this but here is another cheat way this is what i normally use to get the cube functions is you would make a pivot table so i got some made up data here our usual awesome chocolates data so i'll insert a pivot table making sure that i'm adding this to data model so we will add this this is how you trigger the power pivot side of things and then you make a pivot table now here i can make any pivot table i'm just going to make a very very simple pivot table which is we'll see sales by uh geography so we get some sort of a number this is a pivot table and if i use equal to and point to my pivot assuming i got my generate pivot get pivot data i'll get my usual get pivot data thingy this is not what we want we want the cube function so once we have this you can select any cell in the in in this data model pivot table go to analyze and from here fields items and sets no olap tools from there you can convert to formulas and this will pink the screen and then it will give you a formula based version of that pivot table which uses the cube functions it will write those cube formulas for you to tell you if you want to use the cube value function on on this you will get that the beauty of this is it's all linked so i can change from australia to usa here and this formula will give me the usa value so it's it's a cheat way of generating the cube functions for you and then testing them out learning a little bit more as i said they're very clunky sometimes it's not even sure exactly what's happening how the result is coming so yeah there you go um so that's the cube functions i want to say thanks to bhartram for for the super sticker thanks bartram and let's see the next question here jagoda record asks is there a simple way existing function without vba usage that can give us rgb code of a cell background now unfortunately at least as far as i know there is no such function they might eventually add something in future or i could be even totally wrong right now but as much as i know there is nothing in excel that you could use to get the color of the cell or the color of the text without using vba you could create a very simple user defined function in vba to do this i'm not even gonna show that but you could do this if you want okay let's see uh this one yam would like to learn something with date and quantity for periodical rental charges calculations this is required for third-party logistics company um do you want to elaborate that i'll watch out for your subsequent comment yeah but can you give me what you are looking if you are looking for general information on how to work with dates and all of that then my suggestion is you go to my website and you can even just search on google but if you want something that i the way i teach then probably here is another option and then search for date in fact i think that's the second article there some of these are older articles that i keep updating so here is a good starting point because excel dates are somewhat tricky to understand if you're coming from other softwares and you don't view dates as numbers but everything is a number so this one kind of gives you a good level of detail on how to get started with dates and more importantly it also has some additional pointers for you to take your journey so yes start there [Music] because you're putting longer messages i'm not even sure if i can answer your questions but i'll flash them on the screen that way it's very easy for me to read and everyone can you can also read luke asks what are the key skills for an accountant i've started learning power query would you recommend anything else similar um i am not an accountant i never worked as an accountant nor am i qualified to be an accountant so my answer is uh is is not going to be the final word on this but i do have many accountant and finance friends who happen to be working rigorously in excel fields as well so i do know what is it that either they learn or they struggle with so that's my perspective i feel like power query is like the lowest hanging fruit that you everybody should grab so if you have not uh of course luke is already learning but if anybody else out there even if you're not an accountant you're not learning power query you're only hurting yourself so just go and learn power query uh if this there's only one thing that you're taking away from this live stream that should be that that is learn power query end of the end of the thing so learn power query power query will give you quite a bit of freedom and and flexibility when it comes to dealing with crap data that we get day in and day out in business situations okay you're never almost never going to get anything that is clean cut from any systems that you export any any sources that provide you data there's always going to be some drama there and power query will reduce that and simplify your day this has gone really cold now apart from power query what else do you know so there is obviously a whole heap of date and finance related and number related formulas in excel which become essential for people who are working with money and and finance related data in excel so setting aside some time to at least give give all of them a go and see when you would use what how you would use them so that's a good skill the other thing that people kind of forget like you know they'll say oh i know vlookup i know subtotal i know some i know some ifs but another important skill when it comes to learning these things is you want to learn not using these words in isolation but how to combine them like how do i combine this word with that word to make a sentence because learning words is like reading a dictionary it has no purpose other than knowing the meaning whereas learning to speak is is a more valuable skill so that's what you want to learn so learn how to combine them and obviously learn how to use other tools in excel that simplify this and do the same job so that means learning pivot tables uh learning some of the screen features that will simplify your job so yeah that's the thing anil uh sends a super sticker as well thanks anil uh for for that and uh i'm gonna see what money she's asking here how do you use vlookup on merge itself when occurrence of each merged cell size is different okay you got me here right you asked about merged cells i was not even planning to tell this joke but you asked this i'm gonna tell this joke before we get into the actual answer for this so a couple of techno technology people they all do some scams and they all get arrested so they go to the jail um but two technology people let's just say they are testers okay i don't have any negative feelings towards testers i'm just making this up so two testers um they're they're buddies with the jail warden so then they go to the ward and then they request the warden hey can we stay in the same cell okay so the warden was like uh okay i will let you stay but if if your current roommate the cell mate has any objections then i won't let you do it so the warden then arranges a meeting with the roommate of the cell mate of this this testers and that cellmate happens to be um okay let's just call them an accountant again nothing against account accountants either so the accountant says no i i object to it but the warden is like what's your objection and then the accountant says no i just object to it but then the one is like no no i'm gonna do it so they'll they he takes these two and then they they now have their own cell okay fast forward three months these two testers dig a hole and escape from the jail and then the warden is like ah really pissed off he's angry he's mad he's like oh what's happening in my jail so then he comes back to the he comes back to this account and then says i should have listened to you man you know they escaped and then the accountant simply says i told you merged cells are bad that's the joke it's a bad joke but i have fun making up these jokes anyhow how do you use vlookup on merge itself that's the question it's a very very simple thing if you have a merged cell in your excel spreadsheet well let's make one this is cube demo so let's say got one here two there three there this is a single cell we will merge this we will merge that i'm gonna enable border so we can see how big these mergers are and if i say sum you asked about vlookup but it's it's the same logic in respect to how you do it it's going to be six irrespective of how the merges are six because that's the total value now let's see what happens with sum off and i pick these i want to sum up the d column but as a select because there's a merged cell they expand it expands so we'll say d2 to d5 your d4 that alone now you would think this would be two but it will be zero because even though this is merged the merge is at a visual level at the back end level the data is still in the very first cell so only b2 has the value so column d sum is 0 but if i write a sum of b2 to b4 that'll be 6. so if i just sum up these three cells i'll get six i don't need to sum up the whole thing so this is the basic concept uh sometimes we have to merge because that's what we need on the screen level so we end up merging but irrespective of what you do internally the value is always in the top left cell of the merged region so as long as you keep that you can do if you have merged cells and you need to do some processing on that my suggestion to you is figure out if you can unmerge which will save you a lot of time and help you write better formulas but if you cannot do that for whatever reason maybe use power query to transform this data and get you a cleaner cut easily jagoda sends in a super sticker as well thank you jakoda and luke asks a follow-up question what other low-hanging fruits would you say are great other than power query i would say once you are with good with power query immediately latch on to pivot tables many people underestimate how good pivot tables are and somehow get into formulas before understanding and appreciating what pivot tables can do and then um and then it's all a lot depends on um so for example i'll tell you i've never worked as an accountant so i can't really give you from that side um but i i always surprised like how peop how easy it is to impress people uh if you can if you can anybody can make a spreadsheet that calculates things but if you can spend a little bit of time polishing it and making sure that it looks good that will take you a lot further because what we are doing at the end of the day with excel is we are building things so that other people can understand information so as long as you keep that in mind and then think that whatever i'm doing is for someone else to read then you will always ask the question how do i make it better and that that kind of tidying up and making things look good is a step that all too often people forget but if you just do it it only takes 10 minutes and over time you will improve your efficiency in that that will be very very quick way to impress people i have had throughout my career when i was working as a business analyst and even when i do consulting every time i put together a nice looking spreadsheet of course it needs to be accurate it needs to give correct detail but a nice looking spreadsheet i always got appreciation and it helped me further my cause so i hope uh that's something that you will take it it's not a low hanging fruit but it's something that we forget to pick up when when it is there all right let's see what pratik is asking hi chandu i am from chennai cinema industry so we do a lot of forecasting of footfalls and sales but not sure how to do it with formula so how do we forecast data in excel 2010 and plot it on chart um you could do a lot of different modeling with this forecasting is a very very interesting area and and there is more to it than what i could say in the next five ten or even um you know 300 hours for that matter so whatever i'm saying take it with a pinch of salt what i would recommend to you is many industries and many many scenarios and models will have some sort of a cyclical nature so for example if i am running a cinema hall i would naturally expect that more people will show up to watch a cinema in the weekend or on fridays rather than on a wednesday afternoon so there is a cyclical pattern that is going on so that is something that you need to keep in mind when you are forecasting i'm just going to open up this this file it's going to take me a while to look it up because so i'll flash this on the screen if you go to my channel um and see the uploads one of the recent videos that i did is uh 10 things you thought excel couldn't do but it does them so well i'm not trying to promote my other videos this video literally starts off with this question like how to forecast in excel it doesn't use excel 2010 so my first suggestion to you even before i get into anything else is get an updated version of excel you're already 11 years behind don't let an old version saddle you because newer versions offer better functions so here i got my um coffee sales which kind of have a cyclical pattern and i'll show you quickly how to get this this is the tip in that video you can use 2016 or any other version from there on which has forecast dot ets functions which are a bit more uh sensitive towards that seasonal trends and all of that so if i go to insert sorry if i go to data and then forecast sheet excel will do most of the heavy lifting it sees that this is the pattern so here is how your forecast could look like i can use the options and then set my seasonality to seven days which immediately improves my performance of the graph and i can even predict it for next several weeks and notice that you know excel saw that you have this sawtooth pattern and it picked up that and it will show those things so an amazing function and definitely recommend checking this out this is the first starting point you could get more complicated into these modelings with using linear or multiple regression functions or building a more complicated model using different parts all using excel you don't need anything complicated unless you have like a jillion rows of data or something so definitely i would recommend that but please get into a newer version of excel 2010 is really old so that's uh that's a little bit about that i just want to give shout out to some of these super chats that are coming in malaysia sends a super chat pedro sends in a super chat thank you thank you everybody and melissa sends one more with excel vba bi crash course available less than six months i'll definitely address that in a minute let me just see uh yeah so do i have any crash courses on one of these things i don't i mean i do have full-time courses that that go on to these things and then of course there's youtube channel where i have lots of free content free doesn't mean easy or simple this quite in-depth content that is there and then monthly live streams are a very good way for you to learn a specific topic but if you are looking for something that is well designed and gives you everything that you need from a data analyst or a business person kind of a perspective on learning all of that then i highly recommend checking out the excel school program um again the intention of this live stream is not to sell you anything you can look at it consider it and if you think this is gonna help you uh purchase it because that way you are learning something new you're also supporting your creator and you know it's a win-win for all of us so excel school is that program that i recommend to you melissa check it out uh this is a course that i designed many many years ago and then constantly revised it keeping up with the newer versions of excel the whole purpose of this course is to teach you how to be very good with excel when it comes to doing data analysis and dashboard reporting style work so it's a fairly advanced course you will get power query you will learn how to use pivot tables data model how to make advanced interactive charts how to combine and match formulas how to use conditional formats slicers and you know all sorts of things you could read through this page you can see some of the demos of what i built uh what i teach my students to build some of these are like um really complicated complicated dashboards and reports that take a lot of working and all of that is explained in in a very beautiful manner so yeah check it out if you think this will help you sign up there's links for that you can go for a similar program called vba classes another course that i run you can go for that alone or you can combine this in that i do have another course on power bi and you could also sign up for that if you if you want that similar pattern but for power bi all right let's see what else is happening here um cheryl asks greetings i'm an accountant i need to become an expert in excel i just i know just the basics which version would you recommend would you recommend how what would you recommend how i start um we did talk a little bit about luki's situation so um i think same advice will apply for you learn the things that are easy to start off with so probably start off with power query and then pivot tables structuring and managing your data and working with data how to get around functions and all of that if you want a course again excel school is what i would recommend but as i said you know you don't have to go through any course you can learn also on your own or whatever works for you so yeah all right let's just see this one nagraj says is there a formula which is like sum product if or some product is similar to some if and some ifs um sum product is that formula my friend it is quite powerful it can do whatever you want that said i i have a a i used to have a very long love affair with some product i have got quite a few of articles on some product on my blog that go into quite a bit of detail and i used it quite quite a lot and uh and we have even guest articles written by other people on my website that go into greater detail on some product these days i don't use some product that much because there are other dynamic array functions available in excel such as filter which could also do these things so if you give me an example i'll show you what formula you you you could use i might actually miss that message but uh yeah if you have if you're still there nagaraj feel free to post that and i'll watch out for your name so shabari asks chandu you are starting any the power excel education with laila gharani i'm not sure what i what do you mean by starting something with delaila uh i'm i don't have any course that we together are doing doing we do kind of do stuff on our own but we appear on conferences and things like that so somehow maybe that was confusing but if you're asking if i have any courses then i have excel school which i just showed on the screen which is uh a comprehensive excel course that kind of goes into power query excel data model all sorts of stuff so feel free to check that out um yahya asks merging data in one sheet from multiple excel files okay this is a question that many people asked in various forms either here or in my other posts where i ask you know what do you want to learn i always get a feel that you know people have something like this like i got data that is there i want to merge how do i do this the easy answer you can do this with power query i'll do a quick demo so that you know we will all see how to combine data this will help you combine the data it will help you reconcile the data if the data is in different places it will save you heaps of time and the best part is it's all automatic the other thing that i want to remind is this combining data this is my youtube calendar by the way every every month i print this and i write which videos are going going live so i was just looking when this video is going out it's literally going out what's the date today yeah so next tuesday i'm actually posting a video on how to combine multiple worksheets using power query in an automated manner so that video is coming up next week so you could also kind of re-watch the whole thing but i'll do a quick demo now so over to excel um i'll open a new workbook just so we have something to use you asked the question one sheet from multiple excel files so the technique is exactly same so i'll show you a different method which is one sheet from multiple sheets we'll assume that there is multiple sheets and we just want to combine that so for the purpose of this uh we'll just be lazy you know we'll print some numbers here um you can type the numbers there's excel functions as well just because we are learning i'll show you this one you can generate a random array of 10 numbers um with minimum of let's just say 30 maximum is 90 and they are all integers so we'll get some 10 numbers there this is sheet1 i'll make a copy into three sheets because it's a random function each sheet will have different values okay we will replace this ctrl c right click values so that there is um no active formula running this way what happens is that we can easily verify the results so we got sheet 3 has 50 as starting oh this one is also 50. i think we copied this and then to make things interesting i'll change the sizes of these so one sheet has 10 values the next one has 9 then the last one has seven it doesn't matter really so we got three val three sheets you can name these so we will name this as jan feb and then march so three worksheets each named according to the month now assume this is how your accounting department sends you data every month they'll add a tab and then send it to you the budget file and your job as the finance controller is to combine all of this come up with one picture that gives all the data so we'll save this guy um live q a multi sheet no i didn't think i correctly named it but doesn't matter and then we go to a new workbook right here i would like to combine all the three work that workbook get all the three sheet data here in one go as a big table so it's kind of like that so you can go to data get data from file from workbook so this is the key you could use power query in office 365 2021 2019 2016 even in 2013 as an add-in so this is something that has been out there for a good part of time now and and it's very easy to use once you get started so we'll say from workbook i won't go into the inner details of everything here i'll just be like very quick but as i said there's a video coming up and i have many other power query videos on my channel so i'll put a link to them in the video description later on uh feel free to check them out so we'll go multiple sheet import now the fun part is power query finds that there are three tabs so it'll list them but you can only import one at a time or you can import multiple if you import one then you will get that one if you import multiple then you'll get three different queries we don't want either we would like to combine all of this into one table so i'm just going to pick any one we'll just pick jan for that matter we'll go to transform data and this is where i can tell excel how i want my data to be manipulated and combined so here i can just say i don't want just jan alone i want everything so so this is just the jan data we want we don't want jan alone we want all of them so we'll delete all of these steps go to source and here i can see all my tables we will keep name and data right click and remove other columns so only this is there and then expand this thing so that all my data along with month name comes up here the name is nothing but your spreadsheet name so whatever is the name of the tab that will come up here and then this is my all data we'll give it a name and then close and load and bingo our data is here combined nice and clean now you can save this as combined data the beauty of all of this is this is a dynamic connection so if your original file changes all you have to do is come back and refresh this so let's test that we'll open this guy here i'll go to jan notice that everything is a two digit number we'll make this four five six three and then we'll put chandu here and then we go we will add one more we'll call this as april and then one two two three three four hello so we'll put some other data here close this come here right click and then refresh and you'll get all the data including the april data at the bottom this is what power query does this is one of the many many things that power query does right now just literally in in two three minutes you built a program in excel that will automate this job once you build it that's it you can forget about it every time something changes it'll all come through uh but you can also uh add more complicated stuff so if this is your first time coming across this black magic then what i recommend to you is go to my channel um after the stream of course and then look for a power query video i got a very very good video this is a video that i posted a while ago but every day i get like hundreds of people going through that and telling me how good it is and they learn so i'm just looking for yep this is so this is the power query tutorial what is it how to use it it's a one hour 24 minutes video i recorded it so there is no live stream style of interruptions or anything it's properly edited and it goes into great detail and you can see all these comments and everything it's an amazing video just definitely check it out and learn from there from beginning to end all right so that is uh that i think i missed quite a few messages and chats but let's just see what else is happening this is an interesting one uh daniel says what's your way around sorting tables with array functions without copy paste values so let's take a look at that uh i'm gonna save these things so here i got some product data i prepared some sample file data set just in case you know we get some questions which can be answered and this is my product and their cost per per unit information and i want a copy of this table here but sorted by cost per unit in a dynamic way so if this changes that should change so this is where the new functions in excel can be useful you can use the sort function sort and then give the name of the table so the name of the table is product products and then specify the column so the column is second column and then i want it in the descending order so minus one and then it will sort that so that the product with the highest price goes on the top this will only sort the data it won't sort it won't have the header information but i can get the headers as well which is a products [Music] headers and then the headers will also show up there the best part is this is a formula so if something changes here so for example my milk bars are not 933 but 1933 notice that that is the highest number now they'll go to the top here automatically so this is the sort there is another sort function called sort by you could use that as well to do some sorting operations and you could get creative with these things in in combination with certain other things and that will give you a better option so ramon asks what are good resources that you like recommend for a uni student [Music] i think beginning in data analytics i have beginners excel experience obviously you are at the right resource i am your resource but jokes aside there's quite a few places where you could go in fact before the thing i wrote down the recommendations that i'm going to talk about these are some books that i would recommend excel for data analysis and business modeling by wayne winston and excel bible by it's no longer by john walkenbock but i think mike alexander is writing that so those books are very very good books i have got the links to them in the video description right now you can check them out and the other excel channels that i recommend that you subscribe or at least watch some videos to get a good hang is layla garani she puts out some very high quality amazing videos on all topics of excel mrexcel from bill jelen and excel is fun from mike girvin so these are the three places that i personally go to learn so yeah you should also go and learn and some websites my website and blog chandu.org wp has more than 000 articles on various topics of excel power bi power query and powerpivot mrexcel forum for some questions uh and stack overflow for other really excel related questions so these are the places all of this exact whole thing is in the video description right now so go and check it out and feel free to get anything i mean these are all my good friends and i support them and i learn from them and i want you to learn from them as well all right rishabh asks is there a way to retain formulas in csv file upon closing and reopening no csv file by definition is just storing your data in a comma separated format it cannot store your formulas how do you use sparkline is it good for presentation it is very good for presentations and reports it's very easy to create there's a tutorial on my website that i recommend checking out on sparklines subash asks how to convert 3.10 into 310 and make time calculation so let's do that time calc we'll call this as time number and then the next one has time value i'm not even sure why you would have 3.10 and want to mean 310 but let's just say you know this is the problem with most of the things that we do we end up having data that is not clean or good looking and we'll have to deal with that there are many ways to do it i would say if possible automate the whole thing with power query but let's just say you got this in excel we'll throw a few more things there 0.23 i don't know how you would convert that 4.65 now what would you do here and 3.01 so i'll put these things and then at this point you need to define a rule on what what needs to be happening so if you universally agree that anything integer is hours and anything decimal is minutes if that's the policy that you're going with then all you have to do is generate a time value where hours is the integer portion of the number and minutes is the decimal portion of the number multiplied by 100 or whatever so we can use the time function hour is my integer of this and then minute is the decimal portion so here there's a couple of different ways to calculate what is the point one zero uh you could for example use uh mod function to divide something with one so what this will do is it will give you the modulo or the reminder so three point one zero when it is divided by one the remainder will be 0.10 then times 10 100 and then seconds is zero so you'll get 310 and then you can just do it like this notice what happens here this is because we've gone past 60 minutes so it's 65 minutes which will then take it to 5.05 so this is uh that if that's not the correct way of representing this data and there needs to be something else happening like rounding it to 60 or something then you can add an extra condition in the formula to take it up to 60 or you know flag an error or whatever else so robert asks is excel a relational database um excel is not any sort of database excel is excel excel is a spreadsheet where you keep the data people use all sorts of things to do in excel and you might have seen people implement some sort of database functionalities with excel and maybe using vba or other stuff i am not either pro or against these things whatever works for you you should do it that's fine but if you do need a relational database for whatever purpose then my friend go and use a relational database not excel because that will offer you security data integrity and it will simplify your job but excel can deal with relational databases so if you have five tables they're all linked together and you want to use that tables and linkages to do some calculations on your end you could use powerpivot and from data ribbon relationships button this is the button to set up the table relationships within excel and then do whatever you want with with that okay i've got some videos on my channel that talk about how to use the relationships and how to set up powerpivot so check them out later on if you want uh alternatively you can give it a go and then see simon says hi chen do i'm late to session sorry but i have an issue when using power bi for a source um it comes into excel as a pivot table but i cannot add anything to values only rows any ideas please um it's okay you can be late to the session this is not like a class or anything where we kick start exactly um so i'm not taking any power bi questions but because i can't read them on the thing i usually flash them so i put that there but i have not personally tried too much of export to excel functionality within power bi so i'm i'm not able to give you a proper answer right now but yeah maybe search on the power bi forum i guess um all right let's see what key get get that go get at me 30 i have sales data i have sales data i import from and sort out various product sales using formulas but when sale prices change it becomes too cumbersome to manual i was thinking lambda and x lookup combo to automate [Music] so maybe i'm missing the question but and i'm not even sure why you would need lambda there uh yeah if you are already thinking of using something just uh use it but if you have a question feel free to post that there i'll watch out for that um all right let's just see stefan uh sends a super sticker as well thank you thank you for being a resource i'm learning through your videos thank you stephen for for your support as well and thanks for that message all right let's see this one i'm working with large data sets across multiple worksheets is it better to use vlookup or index match in extracting the info i ultimately need um i'm not gonna get into this stick fight of vlookup versus index match whatever works for you use it you know unless you're dealing with like literally one million row tables you will not find any serious speed limitations for a single formula that you're typing there are many other things that that can impact the performance there's quite a bit of discussion on my website and and i talked to some other excel experts on my channel a while ago i think we talked about which one works but here is the thing that i will say that that kind of changes the perspective because many times we we we think that we are in this room with only two doors we look up door and index match door and the only way to get out of the room is to open one of these two doors but what you need to be thinking is you need to think outside the cell okay this is a excel pun but essentially see if there is other things in excel that will solve this problem or make this problem irrelevant for you so one classic example is large data sets across multiple worksheets there your data is split in multiple places so why would you even why not consider using the power query approach that i just showed to reconcile the data and then send the data to pivot tables that's one option second option if the data cannot be like same data split but it's different data sets like sales in one place products in another place why not use the relationships to connect the data and then use pivot tables to drive your calculations so all of these are other options available to you at your disposal so don't think this versus that there's more more to this than what you could do but if you are using either of them and you are not really having any speed issues you are not having any other problems then there is no need to change just continue the course until you hit a wall and then figure out what to do okay um we're gonna take some serious political questions now raj and gary asks india new zealand cricket match which side you are i felt sorry when new zealand lost their first odi a couple of days ago but i was actually really surprised at that they just finished the t20 world cup and then they went to india i mean even india was playing there so and then they just go there and then they start playing again like and i was like ah what didn't they just finish playing like a world cup and how come there's another another set of cricket games starting already like where is some gap and rest and recuperation time for these people so it felt a bit strange to see that you know back to back games [Music] but yeah uh i don't know i i enjoy cricket i love i'm happy if india wins i'm happy if new zealand wins i can celebrate both ways so yeah but i felt really sorry when new zealand lost the t20 world cup to australia so yeah but i think they're very good players and they'll probably bounce back and i was i'm i was telling joe my wife that uh when next year the t20 world cup happens in australia and if travel is allowed and you know there's not so much of code drama happening all over the place we should probably go and watch some games so you might see me live streaming from mcg i'm just kidding i'm not going to do that all right enough cricket let's talk more xla um casper asks what is the best resource to understand operators can be used in formulas filters and array formulas that's a very very good question casper some of you don't know this but other people might know this i used to have a podcast okay i i ran 53 episodes or 54. i don't remember exactly how many and this used to be like a once every two weeks after a while ago after we moved to new zealand i stopped doing the podcast but before between 2012 and 2015 i think i did that many episodes so one of the episodes on my podcast is [Music] so it's it's a episode on exactly this the operators i think it's very funny all about excel operators so this is this is the podcast that i would recommend uh this goes into greater detail on all the operators it's a probably an hour long episode that goes into various operators explains them you can just set it up go for a long walk enjoy it all of these episodes are available on my website so you can still listen um i'm no longer doing the podcast i might actually revisit this and set up podcast but i'm enjoying the live stream so i'll do this for now um but yeah that the only limitation for this podcast episode is it was recorded when did i record this this is uh yeah december 2014 so a long time before the array formulas dynamic array formulas got introduced so there is no mention of the spill or the hash operator in that sense so that information is not there but everything else is there so please check that out casper you might enjoy it pedro asks i developed several dozen lambda functions how should i replicate them in a new worksheet so i am i have i'll be i have a confession to make here i haven't written a single lambda function so far in my life this is because i'm um as i said many times on my channel and my live stream as well i'm a little lazy on the second so i i i don't want to install the the beta version of excel just to test out the lambda i want to wait until the lambda is actually generally available and then play it out just like most of you do i am still on the insider program but not on the super insider program where the lambda is available so i haven't tried the lambda but i did watch many videos read many articles participated in discussions and even debated with people about whether lambda is a good idea or not so i'm well versed in the topic it's just that personally i have not written them i have written lambdas in python and other languages so i know what the concept is so with that note what i what i think i can tell you is that microsoft is still building functionality around both development and usage and and usability of the lambdas so there might even be a future feature in excel where you can kind of create a repository of lambdas and roll them out or kind of export and import them i don't know whether that's the case right now but otherwise you may have to just simply copy paste them into it notepad file and use that or i don't know maybe just uh put them somewhere else in another file and as a template and cop and make files from that yeah and i see that we have got excel exercise who is helping out people big thanks to you excel exercise for responding to some of the chat messages thank you brother and mimi asks uh from sunny san diego thanks for answering i'll try that power query index approach allowed that one thing outside the cell thank you mimi um and uh sridevi says how to copy paste within the same sheet once we use filtered option for the data so excel filters are somewhat weird and this is um so if i have filter here and then i just filter to let's just say new zealand data and we copy some things ctrl c if i paste here so you notice what's going to happen is this is going to look quite weird like the paste will will happen but because certain rows are hidden it will not look like that so you'll see that i copied so many rows how come only four are pasted this is because it pasted the whole thing but some of that pasting happened in the cells that are hidden due to the filter but it still pasted the data it's just that there so i guess the question that you are asking is how can i paste like that in the corresponding rows ignoring the hidden rows i don't think that's how the paste is built for so i don't ever have even that need but yeah if you copy paste the default behavior is like that there is also some other shortcuts that you could use to select the visible cells only i believe i have yeah so this is a select visible cells i think alt semicolon will select all the visible cells i don't really use it that much but i had to use it once or twice so that's why i have it on my quick access toolbar select visible cells alt semicolon so yeah you could try that i don't know whether that will help you are ranger ask how can we find summary of sales for a particular period by selecting date range only well i'll show you one so we will add a a pivot this is usually these kind of things you want to do it in a pivot table so i will insert a pivot table while using the data model again not necessary to use the data model you could do this without data model but it helps to kind of capitalize on some of these things if you want oh i forgot that we don't have any dates there well let's just put some dates in this data make a so you can generate a random date by let's just make up a date in this year this is not a formula that you will find used for in most real life situations but uh i thought this is funny um in so one jan 2021 then today so it will generate the date we'll just uh select this control c control v on your speed so we don't have that random formula anymore and then you can use the shortcut ctrl shift 3 to convert the cells to date format so ctrl shift 3 and we got some dates here now i can go to my pivot right click and refresh so we get that nice little date function date as well and i can right click on the date i can add a timeline which will give you that sort of a thing and then product and amount so this is my all amount and i can use the timeline and then i can pick a month or i can drag this this is defaulted to months but i can go into quarters i can go to year i can even go to individual days and then just select what happened in this particular week so timeline is like a slicer but just for dates so it's a very interesting powerful uh use using which i can select and then see the results immediately if you want you can write a sumifs or some other type of formula to do the same thing but i find timelines are more fun and easy to get the answer that you want so rajan asks please give any example for area function what is an area function oh i suppose you mean areas or maybe there is some new function that i don't have access to yet um i don't even use areas i don't remember the last time i used this function so i really wonder what this does and if it has any real-life uses there's many functions in excel that that are there for some some internal things or some very arcane purpose they're there but you know not everybody need to use it if you use the area function post a chat message and tell me to tell me what you use the area function for but let's try this area function areas returns the number of areas in a reference an area is a range of contiguous skulls or a single cell okay let's just put data here so that's one area i suppose so i think we could do something here let's see what this area will be areas i kind of have a feeling what this does so we need to probably name the cell so b7 i'm going to name as range one this as range two these two cells as range three so we'll select all of these so this cell has a name this cell has a name and these two cells also have a name so now if i say areas or that range no i was expecting three all right i give up i don't want to spend time on the areas i don't use this function i have never used it in at least i've never used it in all my time so far so someone says he might mean the array function oh well uh a function is a more interesting one i guess probably [Music] so if it's an array function then probably you're talking about dynamic array functions or something and there's quite a bit of videos on my channel so i'm not gonna go into that detail um i'll see these questions um so santosh says chanduana can we simplify this formula to count unique values sum minus minus ln unique filter call call equal to rho okay i don't know what this function is actually doing let alone to simplify it can you tell santosh what what it is meant to do uh in like what what is the intended result you tell me i'll come back and answer that meanwhile let's see what future dr shishir ask can you please explain subtotal yes i will subtotal is a beautiful function that i love and i use quite a lot in excel so i'm going to show that to you with this sample data i'll insert a row on the top so we got some space we'll move this logo there so what subtotal does is by default it will let you deal with the data that is currently visible on the screen so that means if you filter something out it's gone so if i have a sum function on my amount column this is my total amount and irrespective of what i filter so if i right click on usa filter by selected cell value even though i'm looking at usa i still see 1.24 million as my sum what subtotal on the other hand does is subtotal and then you will have to specify what sort of total you want so you could say nine for some or one for average some nine and then data table amount column this number will be 189 000. so this is what a subtotal does it will only pay attention to what is visible the beauty is i can change this so i can get to india and then i'll get the india total there right now so when you are sending someone asks you usually hey can you send me the data but i also want to know some quick details instead of making data and pivot table you could kind of make the data end subtotal and then send it to them so that they can see the data but if they filter something they'll then be able to see the sum or average or minimum or maximum like that for that filtered portion using the subtotal function so i love subtotal i use it quite often to improve the readability of data tables normally if i do i try to put that on the top so that it's easy for me to see and occasionally i even use a sneaky trick of selecting the row beneath my header and then from view freeze panes so that that is frozen so whatever the subtotal is always visible and here i can just then kind of select whatever i want to immediately see what that proportion is and even calculate some percentages and things like that so philip says thanks jen do happy holidays you're always unquestionably awesome see you in 22. thank you philip it was awesome to have you on the live streams quite regularly so i hope you enjoyed all this stream uh fun things and you know it's good to have you and i'll see you in 2022 in another live stream i will of course maybe see you in other videos that i publish from now to 2022. okay thiago has got some challenge i have a row the first on first one and i want to print this row on each page but i have a formula in one cell of this row that needs to update okay i have the suppose that's the same message you posted twice um why would the formula update you mean to say the foreigner needs to show the total values that are printed on that page if that's the kind of thing that you are trying to do unfortunately the the page layout print titles thing is not going to work for you because it will always print the rows that you select irrespective of what formula is there because that formula is not going to change based on what goes on the print so you would need to get a bit more creative here you will have to develop such a page and then repeat the calculations on each page so many people don't know these things but so i'll show you how you might be able to get what you want without using some sort of one formula fits everything kind of a thing which is if you go to the from the view if you change to page break view you'll see where the page breaks are happening so let's just say in page 1 i want to print some total so that will correspond to what is shown there in page 2 again i want to print the totals like that this is a table so i can't even do anything here but imagine you know this is what you want you can use this mouse pointer between on the dotted blue lines and you can adjust your print area and move them around so if you want you can page break there so then you can use this space here to repeat the calculation and then go like that so this is probably an easy one yeah i don't really know if you i don't think you can do the thing with print titles i could be wrong but at least as far as i know that's not something that we could do okay so [Music] jitesh asks can you help me with explaining about random number generator in data analysis tool pack i don't understand bernoulli poisson etc distribution drop down um i'm not a statistics maestro i know enough to get around but there are different type of number distributions in statistics so this bernoulli distribution poisson distribution normal distribution uniform distribution like that so when you use the random number generator from there so i guess you're referring to this go to data data analysis and then from here the random number generation so you can pick the distribution that you want and accordingly it will generate the numbers so if you're not sure what a bernoulli distribution is it's very easy simply go to google type what is bernoulli distribution and then we'll give you a wikipedia article that explains in greater detail what that is um i never had to use the bernoulli or even the poisson distribution that much once i finished with my college statistics course so yeah i don't know it has been more than 20 years since i learned about these things so i don't know what they do anymore but yeah if you need to if someone says hey jitesh give me some bernoulli distributed numbers then you could use this you can read more about them and then go and make them i do normally make both uniform and normal distributed random numbers so you can make uniform distribution which means each number in the random range that you specify has the same probability of coming and this is the random rand between functions so if i say rand between 1 and 100 i have the same probability of getting any number between 0 to 100 1 200 whereas if i want a normally distributed one that means the numbers in the middle have higher probability than the ones on the edges you could use uh something like norman and and then random and then let's just say the middle point is 50 and we'll put let's say 20 as standard deviation so i get 77 but if i keep running this using my f9 key the numbers will be all over the place but there is a higher probability that the numbers are closer to 50 then they are further away from it because 50 is the mean that we specify so these are the two ones that i use all the time the other ones i never use that much so i can't really comment okay um i might have missed many of your questions so we can make an exception if you posted a question long time ago and you haven't heard from me feel free to repost that but uh i'm gonna read these things chef 1707 do you think microsoft will develop more around preset dashboard templates and additional focus on easier reset templates for example on professional report data presentation for layperson um [Music] we might eventually get there because that's the that is uh one possibility the problem is there is so much variability between what is a preset for someone versus another person so for example i i if you go to file and then new you'll see that there are several templates that they offer so try some of these you know this is basically the direction they were going there's some card charts the class project plans and this and that and you can search they might be adding more of these um they usually have telemetry that tells them who is clicking not who is clicking but how often people are clicking on these and using them and that feeds into microsoft so they see if something is more popular then they'll try to build around that otherwise if nobody is using certain things then they're not gonna probably bother adding more stuff so yeah i have i have no crystal ball to predict what microsoft will do these things tushar asks can we extract data from outlook attachment like pdf if you have outlook attachments in a pdf format you can extract using power query i got a video on that on the channel somewhere so feel free to check that out deborah says when i use slicers including multiple slicers per graph i would like to see the slicer values above the graph is there a way to do this now this takes all the way back to the cube formulas that we talked about early on so this is one situation where i could use a cube formula to see what is being picked by the slicer alternately you could also use like a harvester pivot table technique this sounds like a clumsy weirdo thingy but it's basically a very very simple technique so i'll show this because this might be useful for many many people so we got our our pivot table here um for the time being i'll delete this timeline what deleted you why are you still there on the screen okay so we got this and if i add a uh i don't want product i want to put geography and amounts and if i add a slicer on my salesperson and if i pick somebody i'll get their data this is all fairly straightforward you understand this you might have used this like hundreds of times already so if i make a insert graph i'll get this graph and the slicer so we'll cut these two controls cut these two things and then we'll paste them here so we got a report that is basically running behind scenes from a pivot i can pick something i am seeing that person so instead of the word turtle i want to see canar sales or brain sale or whatever that is so this is how i would do it i'll copy my original pivot paste it delete this and put sales person delete everything else so i get a dummy pivot table just with the sales person column we can disable totals and subtotals and notice that this currently says brain but if i go here and pick carla this will say carlos whatever you are picking this will give you the name of the person so from here we can generate the title of the chart title is is equal to this and you can use an ampersand and then write something like uh sales report so it will become carla molina sales report if you don't want to for example print their last name just use the first name for if you want you could also add some polish like left off that find space in f4 and then minus one so you'll get just carla sales report and then you can add extra stuff here like single quote yes so that will be carlos sales report the only thing that you may want to keep in mind is what if i clear now i'm looking at everybody but this title says bars sales report so here i can add and if condition if this cell is not blank that means you picked more than one person then we say multiple people else that thing so it will become multiple people's sales report so you could kind of polish this whatever it is that you're doing you have the title uh we'll put some blue color here notice this is i4 and then we can come to this chart select the title title is equal to i4 so now i got multiple people sales report i'll make it nice and bold and then i can pick whatever i want that's the name that will show up there but if i pick multiple people or even just two people i'll get that one so that's uh one way in which you could do it alternatively you could also use cube formulas to extract the slicer value they get a little bit clunky so i don't normally use cube formulas unless i have to but i've been able to just use the this harvester pivot table technique to get the answer bharataram says please talk about legacy excel to dynamic arrays that's my answer taking a drink of water this is because as of now at least there is no support to legacy excel when it comes to dynamic array formulas or x lookup or any of the new functions um i i suppose there's nothing that microsoft will do at this point to somehow release an updated version of excel 2013 that will include support for these things because the whole lure of these functions is so that people can say oh newer version has excel of excel has this i might as well upgrade so there is no yeah i don't know if there is enough backlash from people or everybody going to go completely crazy about them then they might actually do something i have i don't even think that that's the path in which most technology moves there's newer versions precisely for this reason so that you can access newer features aj says i have 50 columns in first sheet 100 columns in second sheet and in the sheet i want to have only matched 50 columns which are in the first sheet this is the kind of problem that you should solve using power query if you do it with your hands then it will be they'll become very numb i'm actually reading some excel jokes the jokes i told so far are the ones that i made up but this is a joke that i read online and i thought oh this is a good one i should use it so i'll tell that joke now what do you get after a whole day of using excel and typing numbers your hands will go numb num so that's the one that i read i thought oh that's cool all right uh salim says how to create excel macro template for daily usage example when i create pivot amounts in desired format etc please advise you don't need a macro to make the template you can create anything as a template so here you can see that i got a file download template when i click on that this creates a ready-made file for me which has clear title my logo and just one worksheet so to save the template all you got to do is open a new file set it up the way you want so for example i want all my sheets to be called as report and i want my first row to be i don't know green color and then it should say report title there like that and then it should be nice and big in white color so this is my template so whatever you want you create and then you go file save as more options and from save as type from excel workbook change to excel template this will change your folder automatically to a place where microsoft keeps all the excel templates so if you now save the file here so i'll show dummy template this gets saved you can close this now if you go to file new uh oh no i was wondering where the template is probably it's gone somewhere yeah so it's in the personal and it'll be in the dummy template if you want to see it all the time you can just pin it and that will go and sit in the screen there i don't want this dummy templates i'm not going to do that but you can and you can put literally anything there you could load up your data and all of that that's fine elitas might sense a super sticker thank you elita thank you for your sticker and um vishwanath from hyderabad says from hyderabad love thank you vishwanath [Music] and this is johnson hi chendu question is there a formula to compare rose base in column with headers for both set first name last name dob columns and highlight duplicate okay so you mean to say you got like three columns with first name last name dob and then another three columns with first name last name dob and you want to match them you could do that with formula but this will get very complicated so this kind of thing again you could try and use power query to quickly do but if you just want to highlight um one simple cheat technique is create a fourth column which is just merged all the three values using concatenate or something and then use that to conditional format highlight duplicate values like that that might be very quick all right in microsoft excel which tab gets created when a new chart is inserted are you asking a question from your school quiz because that's what it looks like but if you select a chart you will see that there are some additional tabs added on the top depending on what they are what type of chart it is this is a pivot chart so it will have pivot table pivot chart analyze design and format but usually there's design and format tabs that will appear on the tab on the top shekar asks can we scrap data from net using excel yes you can so this is my pivot chart demo but scraping there are multiple ways to do it you can use data from web option to scrape the data through power query into excel but this is very very powerful it gives you quite a bit of flexibility and definitely try that but if you just want a simple formula to read an api and get a value you can use the web service function and then specify the url and then it will go to that url get the data from there so for the web service to work you need a url that is providing the data as a an xml so it's usually for that xml endpoints kind of thing and then that will come here and then you can use the filterxml function to use xpath and extract the data and there is also a encode url function to encode the url if the url has spaces and stuff like that so these three functions are available in excel right now as a formula based root and then you have web on top of all of this there is also additional functions that they keep adding so there is a special function to get stock market data called stock history from web and then you got your data types to get the data from wall from our alpha raf or whatever that is and some other standard data set so for example if i want to know what is the capital of india new zealand usa like that so as you see if i'm typing it says so convert these two countries so we convert that at this point excel will treat them as countries and i can use dot capital city uh and it will run a query and then get you some data so there's quite a few powerful functions and techniques that you could use in excel to do these kinds of things okay we've got microsoft excel you combining recalculate arrays with excel charts i know you would come up with some sick ass stuff i have been trying i will add more functions more technique on my channel as i as i experiment and succeed so far the dynamic arrays are not natively supported by chart so you'll have to go through like an extra loop so i only use them where there is they're needed but there's some very powerful things that i was able to do with that so i'll share them as as and when i get a chance mimi says chandu will consider comedy riddle master for his next youtube channel i'm sure all subs will welcome some excel humor well you know me i can't just sit in excel all the time all right i think we have had a very very good session there's quite a few questions i addressed i'll save a copy of this file and share i want to wrap up by jumping to my presentation quickly so i can share a few more slides with you that that talk about some additional things that you may want to know so people were asking you know what are some of the other places that you could learn excel i mean i've tried to answer some questions but there was like quite a few questions that i couldn't get to so if you have a question or if you're thinking ah chandra is not helpful i need more places to learn excel these are the places that you should go to the channels are uh that i recommend are lila's channel mike girvin's channel excel is fun and mrexcel channel so feel free to check them out and subscribe to them or learn from them they will they will certainly help you on your journey towards excel awesomeness and the websites and blogs my blog gender. blog mrexcel forum and stackoverflow for your excel related questions and stuff if you want to get a book or two to kind of learn excel sit and learn excel and get everything in one go then i recommend getting either of these books i have the older version of excel bible and even the older version of the data analysis and business modeling book with wayne winston the especially this book by wayne winston this one at the bottom this is a very very good book that goes into greater detail on how to take excel to business world for solving real life problems so very very good there's more advanced books as well but these are the starter books that i recommend if you are looking for a book to learn so get them there is a link to the books in the video description feel free to check that out and of course i got my own stuff as well which is i did show this briefly but if you want do consider and enroll into the excel school program this is a very all encompassing one course that will give you all the things that you want to learn in excel and of course this is my channel where there's tons of excel videos and i keep posting new videos every week so check that out as well you're already there so i don't need to tell you that and that's uh a little bit about how you can take the knowledge forward but before i wrap up i want to again take a minute to thank you all for joining this live stream but also coming in and out throughout the year this is the first time i have done my live streams and i've been doing them since february and i plan to do them on the last friday of the month for good part of next year as well but i won't be doing the live stream in december because it's holidays and i i don't want to sit in front of computer on the christmas day so i wish you all uh a very happy and safe and fun holidays and then i'll see you in january when i um do my new live stream either january or february i haven't really decided because we are going on a road trip in jan so fingers crossed it's gonna be fun um but i will i i thought i'll conclude by saying big thanks to all and thanks to everybody who showed up to the live stream posted a comment posted a message chat sent me the super chat or super sticker like shabari has here just now done so everybody who is who is cheering me supporting me loving me and uh and posting positive messages big big thank you for for all of that i will still be here for next 10 15 minutes but i thought because we are already at the close to two hour mark i'll first share my closing messages then you know if you still have some questions i will take them um sorry here uh paula says that was awesome as always going to purchase access cool water resource thank you paula thank you uh and sorry somebody was saying ankur says please take my one question so uncle go ahead and post it if you have not already done meanwhile i'll see what this is texas says how to combine x lookup and average formula with multiple complex criteria data not sorted first last use a boolean array two x lookups nested example please so this is a very um you have not told me exact specifics but i understand what you mean so i'm gonna use my own data here to make up something and then show you one way of doing it um now if you are already using x lookup this is something that most of us forget but if you are using x lookup you also have access to other powerful functions filter unique etc so you should be using them as well rather than kind of twisting x lookup in a weird way to get the answer so i'll show you what i mean by that so here i got my data i'm just gonna clean it up a little bit because it's too much junk here so this is our awesome chocolates sales data as you can see and and we got sales person geography product and amount so obviously this combinations each person appears multiple times so if i want to know for example gunnar and uk as a combination we'll copy these things paste them there so this is my person that's my country and i want to see their record i'll disable the [Music] freeze pane so we can just see that so with x lookup obviously you can only look up one value so you can look up either gnar or uk so if i'm looking up kunar in the data sales person column and then say data amount i'll get the very first amount for gunnar which would be there is this 3983 this is what i get and same for uk alone not what i want is i want a combination of these two so you might see some syntaxes on web for these kind of things so for example look up now what we want is gnar and uk so you could kind of do the boolean thing that [Music] texas is talking about so you would say look up true where the lookup array is data person sorry salesperson is equal to [Music] this star data con geography is equal to that so in fact we'll just say one so when you do it like this this will kind of generate a boolean array once and zeros it'll be one wherever both of them match zero otherwise and then we look up for the one and then data amount this is still useless because it's gonna say 21 which would be yeah so the first combination is 21 so that's why it is saying 21. now neither is useful i want to average all the matching values so this is where the filter comes in you don't need to use x lookup at all you don't need to go through any of this boolean circles you'll simply say i want to average my data amount where my data person is equal to because it's an end condition you want to say star data um geography is equal to [Music] uk so what filter will do is it will give you all the amounts these are all the combinations and you want to average them so you will just put average around it and then so that's the average amount for this combination and i can change everything it's all parameters so we could for example put india here i'll see that and i can put chess [Music] bornell i could put chandu here which is not there in the data so i'll get an error so it works beautifully the filter function if you have access to x lookup by definition you also have access to filter so use the filter one if possible because that will save you time all right um oh there's more questions now so i'm gonna spend next 10 minutes and then i'll i didn't even show you these things but uh do you see this excel is right next to me that's our dog by the way so she's been super quiet but i'm pretty sure she will not enjoy me talking for more than that much time so okay so where are we we are i think i wanted to come back to someone's question but somehow i missed that yes ankur says desperate to know your indian journey in short at least please i'm really a big fan of you since last eight years my excel journey is purely inspired by you thank you so much ankur might as well just talk about it then [Music] so [Music] the i mean i don't want to go all the way back to 2003 or something when i started my website um but yeah i i started writing about excel because i was using quite a bit of it in my work and i thought oh what i'm doing seems very unique uh maybe i should mention it on my website so other people will also kind of benefit from it but at least i i have a sounding boat for sharing my ideas and and the fun things that i'm doing so that's how it began and one thing led to another and eventually i ended up leaving my job so that i could focus this as a full-time thing of talking about excel in data and now power bi sharing the information on youtube and my website and reaching out to people connecting in all of that so that's at a very very high level what my journey is uh but maybe i will give you a few more details on what is happening in the recent past um [Music] for example uh there were quite a few big big moments in my life but uh the i think the recent moment would have to be after living in india for about uh many many years of my life um and running the business in 2016 we all thought it would be fun to live in other other parts of the world because we're getting old the kids are getting oh you know they were about six years old then so we wanted to explore and live in other parts of the world before they're too old and they're set in their ways and we are we're also too old to move so that's when we moved to new zealand in 2016 [Music] and uh we've been living in wellington new zealand since then i really love it here i am very i feel very fortunate that you know we are able to come and live in a very beautiful part of the world and experience a different culture and meet different people make friends my kids enjoy it here we have bought a dog into our family and you know it's all fun of course it still hurts a little bit for me especially given the fact that uh my mom and my brother they live in india joe's family they all live in india so um we came under the assumption that we could always go back and visit them spend time and be there when they when the family needs us or when there is a occasion to celebrate unfortunately due to covet we are all locked up wherever we are so i'm looking forward to the time when we could go back to india and uh you know hug the family members and spend some time with them but that's a little bit about what's happening in the recent past after moving to new zealand i um i kind of diversified a little bit into doing more consulting contract work as well uh just to make connections and friends here as well as you know um i i came to a place where um i i i started having more hobbies and other things so i was kind of like away from my website for some time but i kind of rekindled that and two years ago i decided okay youtube is where i will be spending most of my energy in terms of contributing and helping people awesome become awesome in their work so that's how the re-kindled energy into my youtube channel came up and uh now i'm really happy i took that decision because i'm i'm able to connect with audience like you share my stories share my information learn from you talk to you and give the info you know yeah it makes everybody's life better i guess you know i have good time doing it i hope you're having a good time enjoying this so that's a little bit about the journey um i want to thank texas for the super sticker as well thank you texas um let's see what else is uh angel asks how do i protect my dashboard from user scrolling this is a very very good question i will show you a sneaky trick that i use to stop people from scrolling away when i have got an important thing uh on on my page so let's say we got this thing here and i've made this [Music] for a moment i'll turn off my face so you can actually see this so i got this but i don't want my people to go like that so this is what i do i select a row that is good enough so for example this row number 25 or 23 or something like that and then i go to view freeze panes freeze pane so now what happens is all the scrolling can only happen bottom the top one always stays there so this is number one technique but if you don't want to do it like this another option is you can select the area that is outside the dashboard so from row 24 onwards there is nothing ctrl shift down arrow you select all the rows right click hide them so the spreadsheet is literally up to row 23. likewise column k onwards everything hide them so even if they scroll there is nothing for them to see so they will eventually come back here so these are some techniques that you could use to kind of just keep them on the part of the screen that they should be on there's more messages uh i would love to stay and help you as much as possible but it's i'm also mindful about the time but i'll stick around for some time evolve says some says you learn excel or other things better in job because of practicality which is not possible merely doing courses on excel or power bi what is your call on this so it's a very tricky one at least when i was learning excel is the only platform so i didn't have to be like should i spend my time and energy and even money on excel or power bi or python or something else but nowadays there is more choice so which means there is also things that are pulling you in different directions which can create a lot of unnecessary anxiety and confusion what i do feel is if you if you go out and and get into that mindset that i have to learn everything then that can confuse you so instead pick a direction and start walking okay so pick anything it doesn't really matter unless you got an interview on sql tomorrow then the direction you want to go is learn sql but otherwise just pick anything in excel power bi python or whatever you want just pick one thing and then just focus on that turn off all the distractions cut away anything that is negative or pulling you down and and and pick one or two resources again there is a lot of mindless scrolling and and too much information on any place you go youtube or blogs or books or courses there is just literally too much so take take one or two things pick a direction and then stick with them even if it is hard even if it is not understandable like you know [Music] it has kind of become our tendency that we put a youtube video and then we'll pause it in 10 seconds we'll go somewhere else we'll do something like open a web page not even read the whole thing go out and do that this is because we have access to all of that but this works completely against you like you you give whatever direction you want to go give it enough time so that you you you feel like you are taking that journey so pick something and do it don't let the rest of the world disturb you instead you you close down and you just focus on that if you have to pick one of them and especially you are just out of college or freshly looking to learn and you have the time then i would say maybe start off with something that is a bit more popular like power bi or a bit of programming or even excel for that matter it doesn't really affect but you know if you know which direction you want to go because that's that's what the interviewers are asking for or whatever then pick that close up all the distractions and give it time that will be enough you know don't uh don't constantly second guess your choice and go back and and browse for more pick something stick with it ashley says my husband works a set schedule certain days during month how do you formulate something to show what does he will be in the months to come to show what he will be on in the months to come um this is a very good question normally i won't use excel for these kind of things i'll try to use a calendar software such as outlook or google calendar or something else if there is a recurring pattern that you can configure but i'll show you a technique that that can also be useful um if if you have something that is very specific or very um like very critic not critical but very very unique that you cannot use the calendar or maybe don't want to use them for whatever reason so um let's just make up your husband's schedule for the next year here so 2022 all the dates i want in a table if you are using modern excel you can do this in many different ways i'll show you dates i want all my 2022 dates one rope one date per row so we can just say uh make a date where year is 2022 month is one and a day is a sequence of numbers from 365. yeah next year is not a leap year so this will give you all the dates um and then so you'll get all the dates in 2022 like this [Music] and then once the dates are there you don't have to use a form you can just type the dates and drag them down and excel will fill them down and now you can then set up the scheduling as you see fit so if it is hazard then you will go to a date and then type a carpenting job and then one more carpenting job like that but if there is a set pattern like every four days he'll go to client abc and does the work for two days and that repeats then you could kind of generate that sort of a thing through some sort of a formula the formulas that you use will will get a little more crazy and this is where i find that there is not enough value with this you might want to use a calendar option if possible and then export to excel through some other means for analysis if you want um but if i want to for example every third day or every every thursday uh he will go to client abc so we'll put like that you know this is just so and then we could use something like a weekday sorry if weekday um of that and then thursday would be one is sunday so thursday would be five so if the weekdays happen to be five then visit abc else blank and this b3 is a array formula so i can just use it like that so this will automatically populate that uh and you don't even have to hard code this i can just link it to a cell so i can make this as five and change this to that so i can then change the schedule to tuesdays or sunday or friday or saturday whatever i want i can change and that will be that and you can complicate this formula to add extra conditions on what happens based on various things and keep generating but as i said i wouldn't almost use this kind of thing for anything but yeah this is one option that you have happy life asks please explain dollar position in index match while fetching data from many columns thank you i want to but i'm not gonna do that because i have a video that exactly does this so i'm gonna leave the link to that it's called how to use excel reference styles properly so i will copy link and when we finish with the stream i'm gonna put a link into the video description but you can check that this video basically talks about all the dollar styles and how they how to use in all of that siddharth asks my doubt isn't strictly excel but still is it possible to have dynamic data type for switch true measures in power bi this is a very very specific question i haven't tried this in a in a few like almost two years now but uh you know there's a couple of different things that power bi has added so yeah i would recommend probably searching for this uh if you want to know a bit more this they have added data types for measures i believe or maybe i'm wrong or maybe i've watched some other thing but yeah search for that you will be able to find something interesting ching's channel says chandu please answer this question i'm not able to get filter function in 2019 any alternative instead of filter function i have filter xml um unfortunately filter is only available in excel 365 at the moment i believe they might add that to 2021 but i might be wrong so yeah nothing we could do to get filter into excel 2019 or 2016 or any of the other versions um this is not something that i can like i can pull a lever and somehow you get it it's what microsoft decides so we will just all have to sit back and take their policy so if tomorrow they decide we are no longer gonna have a home ribbon in excel anymore that's pretty much what it is you will not have it so unfortunately now duty says suppose we want to analyze the cost amount to achieve targeted profit for a particular sales report is goal seek formula helpful or what other functions can we use can we please suggest i think goal seek is a good starting point if you have more complicated things you might want to consider using solver given the fact that you already used the word goal seek in your question i assume you know what it is and how it can be set up so yeah start off with that for more complicated things try the solver one there is some tutorials on both my channel as well as on my website uh on goal seek and solver so give them a read and and then see how you can implement shibu says um sorry where's this please get me best path for me to learn power bi from zero to hero um i'm not really sure if this is a zero to hero path but i do have a course that you can consider if you go to my website classes power bi played it check that out and if you want to join email me that course will the aim of that course is to take you from beginner to advanced level there is nothing like completely learn any software not just power bi not just excel there is no such course anywhere in the world so whatever course you are learning it will take you from point a to point b but the journey continues uh but have that learning mindset so jaipal asks how can we set financial year in the pivot charts and tables not really with a direct option but you could use like a link table to set that up i might have a video tutorial on my channel later on next year because we are almost at the end of the year and most my videos for the rest of the year are already done but i will add a video on this this is a very good topic so i'll make a note and i will add the video okay i think there's more questions but unfortunately um uh you know if i stay here you will keep asking and it's already 7 30 in the morning and i've been here since two and a half hours now so i'll take a leave now thank you so much for joining on the live stream i hope you all had fun and i was able to help you out if not all of you but at least some of you um we'll take this last question because this sounds pretty ominous is vba dead microsoft excel recalculates this i wish i know the answer to this but unfortunately i don't microsoft seems to be ignoring vba if that's the right word to use because they're not really telling us if they will pull out the plug on it but they're not adding any new features either like if you go to visual basic editor and the screen looks exactly same since 2007 of excel or even maybe earlier so it is it looks like it's not getting any love but it is still used by millions of people all over the world uh in any time i ask people what do you want to learn vba is one of the things that people want to learn so there is still interest and i love vba because it's a very good language yeah i think it lives so yeah that's uh that there's other questions but unfortunately i think i'll stop now uh i will i will record more videos and upload but this is my last live stream for the year as i said we will see you all again in 2022 so this is my happy new year and happy holidays to you from a live stream perspective but i'll have videos on the channel and community post that will tell you uh what's happening in the holidays so thank you all for joining and enjoy your rest of the evening or weekend i have uh i'm gonna go downstairs relax for a bit and then take my daughter for a cricket game and spend rest of the week evening enjoying thank you bye

Channel: Chandoo

Views: 123,276

Keywords:

Id: wD64ph3ZB9M

Length: 144min 48sec (8688 seconds)

Published: Fri Nov 19 2021