[0:00]Let's assume you asked Claude to make a sheet with the problem statement. And now once you are done with it, you go to your manager and present that. The manager ask you, okay, what is this formula you have used? How have you wired this? What is adding, what is subtracting?
[0:14]and you are blank. You're like, oh, I haven't put these formulas, Copilot has done that, Claude has done that. I'm telling you your seniors would be frustrated.
[0:29]Hi everyone. As a professional, be it in consulting or finance domain, or any job which requires analysis or handling data, there is one tool which you require is Excel.
[0:40]But there has been a lot of questions around should I learn Excel in the era of AI. The brief answer is yes. Why? See, I myself have uploaded videos on how to use AI in Excel.
[0:51]But like I have mentioned in those videos, it's AI in Excel. AI by itself cannot give you useful analysis. Excel is the way to think logically, to put inputs and get the outputs.
[1:04]See, if you know the Excel, you know the logics, you know the formulas, you would be able to ask AI better questions. You would be able to delegate better.
[1:12]And that's why in this video, I'm going to share five essential Excel skills that every professional should know. First is lookups. You should know the entire family of lookups.
[1:22]Second is data cleaning with Power Query. Third is dynamic errors. Fourth, Power Pivot. And fifth is Lambda function.
[1:30]I had a discussion with my friend Chinmay, who's an Excel expert. And we both discussed it. I asked particular set of questions on your behalf about how to use all of these in Excel.
[1:43]So let's get started. I'll show one case study where we can like, you know, discuss the lookup functions better. So, like let's say you have this data.
[1:54]So I am presenting here some employee names and the days spent by them in the organization. So like I've kept a very basic data, but usually what happens, like as a financial analyst or as a consultant, you need to group the data or, you know, you need to put data into various buckets and then analyze it.
[2:14]So we are just trying to do here is that this data per se is of no, like very significance to us because it's too scattered. So we just want to create some groupings and we just want to understand four parameters, like the age of the organization.
[2:28]How many employees are like less than six months, how are like, you know, six to one, six months to one year, how many of them have spent two years or more. So you are just trying to group your data into like various buckets and try to analyze it.
[2:45]So let's try to like let's try to do this in a very simple fashion. So I'll try to do this with the help of VLOOKUP.
[2:54]So now here what is my lookup value? My lookup value is the day spent in the organization, which is 179.
[3:02]And then this is the table array where I have to look the data. So, and then I have to choose column index number, which is going to be three, because lower range is first column, upper range is second column and grade is the third column. And then usually we prefer to have an exact match, so we will put a false as an option.
[3:25]Now if I do this, first of all, I cannot drag this formula down. If I drag this formula down, I'll get errors because I have not freeze the column references.
[3:34]So what I'll do is that I will select this data points and I'll just freeze it. So there is a keyboard shortcut F4, you can freeze the data sets. Now if I just copy and drag this formula down, So now you have to tell me that for Mayuri I'm getting an output for Gauri I'm getting an output and for Chinmay I am getting an output. So Chinmay is a special name, so it will always give you an output. But then why the discrepancy, right? Like why Gauri is giving us an answer and why Sakshi is not giving us an answer? Like what what do you think what
[4:07]Because the number in column B is exactly, it's not exactly matching with the number in column G and H. So for example, 366 is your G16, but 529 is not there in the table.
[4:21]Yeah, exactly. like a very, very good find. So what has happened here is that
[4:26]I have an important announcement to make. Chinmay and I are launching our third edition of Excel workshop on Mastering Excel with real case studies, where we cover real life case studies and use Excel to solve them, like building a stock dashboard, reverse lookup, multi-lookup, financial modeling, payback period, portfolio analysis, root revenue analysis, logical thinking in Excel, and a lot more.
[4:50]This workshop is going to be different from everything that we have done so far. We are going to teach you something which you can apply from day one at work. We would be looking forward to see you then on 11th and 12th of April.
[5:01]It's a two day workshop, three hours each. You can check out all the details with the link in the description. Let's go back to the video.
[5:08]we are trying to do an exact match, but in this particular case, we did not be cannot do an exact match because we have to do the bucketing of the data. So just instead of using false argument, what we can do is we can use a true argument in VLOOKUP, and then I can drag this formula down.
[5:22]And now you can see I'm getting the buckets done in a like a very simple manner.
[5:31]So what I've done, a lot of people how do they solve this data is that they use a very long nested IF, like if less than zero, greater than zero, less than 180 and and, you know, making a very complex formula. So this is just our own basic VLOOKUP function, but with just a true argument, like which will help you give, you know, buckets to your data, like help you do an approximate match. And approximate meaning here it only works on numbers, so it's not for text. So this is how VLOOKUP function works.
[5:59]Now, one of the problems with VLOOKUP function is that it cannot look to the left of the data set. So for example, if I had this particular grades in this column and I didn't have this, then there is no way VLOOKUP can give me the answer because now my lookup range is here and my data is to the left of it. So I'm not going to be able to get the answers through my VLOOKUP function. So in such situations, uh, so long back, uh, we didn't have XLOOKUP functions. So VLOOKUP was released in 1985 and XLOOKUP was released in almost 2019-20. So for those 15 plus 20 years, INDEX and MATCH, a combination of two functions, people were using that to kind of solve this problem of, you know, looking to the left side of the data. Right, but after 2020 we have XLOOKUP and you absolutely I love XLOOKUP a lot. I I I have stopped. So I started my career journey because I faced a bug in VLOOKUP and today I am at a stage where I don't use VLOOKUP at all.
[6:59]So I'll just walk you through the XLOOKUP function. It's very easy to learn and very like very practical and very handy and useful function.
[7:07]So what I can do is in the XLOOKUP is that I'll start with XLOOKUP function. My lookup value is days spent in the organization. And then this is my lookup array because I'm going to find my values in this range, which is like cell G14 to G17. I'll again freeze it with a dollar sign. And then what I want in return is I want a grade in return, which is my buckets.
[7:33]It asks like if there is an N/A error, then what output you should give. So like let's say we can say, you know, not available. Like I can just type it.
[7:44]And now it asks a match mode. Like do you want an exact match or you want an exact match or an X smaller item or the next larger item? So I can choose minus one in this particular scenario.
[7:55]And now I'll get the bucketing using XLOOKUP function. So I'm just copying this formula and dragging it down. What is something additional that XLOOKUP is doing or can do here because we were able to do this with VLOOKUP as well?
[8:10]Yeah, that's that's a very good question. And now just let's do the twisting, like let's say I send this file to my client and he just, you know, just feels that I'll just sort the data use. Kuch change toh hoga nahi. And he just sorts the data. And you can see, like moment you sort the data, the VLOOKUP function which we had used, it's throwing us an error. But XLOOKUP is still being able to give us the correct answer.
[8:34]So yeah, so VLOOKUP function requires the data to be an ascending order, when you have a number based match approximate match, XLOOKUP can work even if the data is not in ascending order. So this is one of the advantages of, you know, using an XLOOKUP function. And let's say sometimes you don't have a lower range, you have an upper range. So how will you solve this problem? So I'll again use XLOOKUP.
[8:56]This is again my lookup value. Now I'm using lookup array as my upper range. So I'm selecting this data. Freezing it with F4. Again, my return range is going to be my grades. F4. And now if not found, I'll just choose NA, and here now I'm not going to choose minus one. I'm going to choose plus one.
[9:17]Correct? Because I'm referring to my upper range, I'm referring to column H here. Correct? And now I can just copy this and I can paste it. You can see I'm getting the data. So if you don't have lower range, if you have upper range, you can use XLOOKUP in that scenario as well. So a lot of things, uh, like, you know, this small, small things which are there in XLOOKUP, which makes it very handy and it's A very easy to learn.
[9:40]Like I train people a lot on Excel. I have trained more than 12,000 people. VLOOKUP is slightly complex, but XLOOKUP is something which people pick up in like 10-15 minutes.
[9:50]And you are saying XLOOKUP has replaced INDEX, MATCH, etc. Or do we still need to use those functions?
[9:55]I feel like in nine out of 10 cases, XLOOKUP would do the job which either VLOOKUP or INDEXMATCH might be doing. So if you learn XLOOKUP, then you are sorted. Got it. Got it. Yeah. And again, coming to the questions where you mentioned, like, you know, Chinmay, what are those basic things where people miss out on Excel?
[10:18]So I'll just present another case study. So these are some account numbers and these are bank balances. Uh, and what here I've just noted down the same account number. So you can see this is the same account number which I've noted here.
[10:32]And I've used a VLOOKUP function, uh, and I have used an XLOOKUP function as well here. So, but still it's throwing me an N/A answer. So now if you see my VLOOKUP value is G26, which is this cell in this range and it is giving me second value as an output. So now you can see, like, this is 140052005. Yeah, exactly same number. So VLOOKUP is giving a wrong answer and even XLOOKUP is giving a wrong answer. So why is it so? Like what what do you think?
[11:07]I see there is some like green flag there and because of it, maybe there is some spacing or it's not in the right format due to which VLOOKUP is causing the error. And I have seen this error a lot of times and I needed to clean my data before applying VLOOKUP.
[11:24]Yeah, that's that's correct. So in this case those green flags are technically red flags. So Yeah. Yeah. So there is one very basic function in Excel, which I love using, which is an is number. So it's basically just, you know, asking uh each cell like are you a number? So if I go here and if I ask this cell ki hey, are you a number? It will give me false as an answer. Because it's it's not a number, correct?
[11:48]Now I'll ask the same question to the cell which is to my right. So hey, are you is number? So I'm typing is number. I'm referring it to this particular cell and I'm hitting enter. Now it is saying me true. So basically Excel is recognizing column G as a number, but when it comes to like this particular cell or the target cell, which is this, it is not being able to recognize it as a number. So that's why our VLOOKUP is also giving us a different error. And one more thing, like which you if you need to be really good at Excel, is that you should have like, you know, very minute observation skills. So do you, Nandini, do you notice any difference between the way this cell is and you know, the way this cell is? Like any small difference do you notice?
[12:33]Yes, very small difference is that A29 it's left aligned and G is right aligned.
[12:40]Yeah, that's that's a very amazing observation. So that's a very small hint, like, you know, it's giving that this is aligned as a text and this is aligned as a number because numbers are right aligned. So yeah, so what so I mean this is a way where you have to clean the data set. So either you can convert these numbers, so basically what you have to do is we have to convert these account numbers to like, you know, proper Excel numbers. So for that we have a number value function in Excel. So you just use number value and you refer it to any text which you have and you can close the bracket. It's it's as simple as that. You hit enter, you copy paste it and you drag the formulas down. So now we have converted all these numbers to, you know, values, which is numbers in Excel. All I will do is that I will copy this and I will value paste this.
[13:31]So control alt V and I will do a value paste. And you can see now And just to equal to, I think that also converts that cell value to number. Yeah, it it it can work. So many a times let's try it out. So I am doing equal to and let's just try it out. Nandini's trick versus Chinmay. In this case, maybe it's not working. Uh but I think number value function works. And I I think you're right. So see, what I would do is that I would use some basic uh mathematics function. So I would say this plus zero, right? Because anything plus zero is the same number. And then maybe then Excel might consider this to be like a number. Now let me just copy this and value paste this.
[14:15]I think now it's considering it as a number and it's giving us the right answer. Now you can see that this is throwing the correct answer, which is 10,000 is the account balance. So yeah, so I either you can do this, like, you know, you can just convert you can use some basic, you know, Desi Jugaad that you do equal to plus zero and you do or if you want to play safe, you can use a number value function as well.
[14:42]Many a times it would happen that like, you know, you get data from different countries. Like, you know, even the comma logic is different at times, like, you know, billions versus lakhs is very different. So in all those situations, number value function like, you know, does a very good job. Since we are on the topic of cleaning the data. So what are some of like two, three, four things that one can do to clean the data? Let's say that file has some comma, spaces, and some other kind of symbols and I want to like do some analysis on that data. So how should I go about cleaning that data?
[15:12]Yeah, sure. So I'll give you some basic example, uh where we can clean the data set. So I've plotted this simple income statement. So you can see gross income to net profit. Uh and as you can see here is that instead of writing zero, like many a times when you copy the data from PDF, you can have a dash here.
[15:35]Correct? So these two are numbers and you can see that instead of writing general expenses, this is like a dash. And here selling expenses is also a dash. So it's not good to like, you know, keep data in a dash format because like let's say if I decide to add something, it will give me value error because it's not a number. It's a dash as an input. So now let's say you want to quickly replace this dash with a like let's say a zero.
[16:00]Okay. So let's try it out. So everybody knows find and replace, quite a basic thing in Excel. So I do select the data and I do control H, which is a keyboard shortcut for find and replace. Uh now what I do is that I find dash and I replace it with zero. Sounds good. Like we can go ahead with this strategy.
[16:20]Yeah, I think so. Yeah. So now it's replace all. So the profit is 400. Let's see what happens when we do it.
[16:28]Profit has changed to 1600. Okay. So it's a good thing that we have increased the profit of the company. But I mean, it's not a good thing because we have like completely messed up the data. So what has happened, all those expenses, those who were negative, they have also become positive. So this was minus 500. I'll do control Z. I joke, you know, control Z has saved more life than vaccine.
[16:49]So I'll do a control Z. And now you can see that this is minus 500, which became like, you know, positive 500 because Excel went and it replaced this minus sign also with a zero. This minus sign with a zero. All these minus signs with zeros. Now and then we got 1600 as an output. So we don't want Excel to do that. So how we can clean this data, I'll do control H. Uh, find what, find dash, replace it with zero. But there is a very small setting, like, you know, you can see here you have something called as options. So you can just click on options. You can say match entire cell contents. So what I'm telling Excel is that find dash and replace it with zero when the entire cell content is dash.
[17:39]So match entire cell contents. So in this particular case, entire cell content is not dash. It is dash with 500. So Excel will ignore that particular cell. And in this particular cell, which is B14, I just have dash is my entire cell content. So now I'll do a replace all. And you can see, all done, we have made two replacements. So you can clean the data, uh, like, you know, with find and replace options. And these are very small, small nuance, which like, you know, you should be aware of. Like, you know, you if you want to clean the data set.
[18:09]Got it. This is very helpful.
[18:12]Yeah. And just one more small point like in terms of like, you know, understanding Excel better. So you can see that here I have written like some branches and their total profits. Correct? Now again, a very small thing in Excel, I'll do a control F and I will try to find 11. Do you think Excel will be able to find 11?
[18:34]No. Okay, you're saying no, but I think mere aankhon ke saamne mujhe 11 dikh raha hai and I am asking Excel, you know, just find 11. So I'm doing a find all.
[18:44]And say it says that, you know, we couldn't find what you're looking for. Exactly. Yeah. I mean, aankhon ke saamne 11 dikh raha hai. I am asking it to find 11, but it's not able to do that. So I think you should answer this question because I think you have you know this very well. So why is that?
[19:01]Because it looks for absolute value. So if 11 is there or 12 is there, then it can find, but actually 11 is not an absolute value here. It's a sum of three values. So it can't read those formula outcome values.
[19:14]Yeah, correct. So I mean for you like for you and my eyes, this is 11, but for Excel that is not 11. It is B4 plus B5 plus B6. So therefore Excel is not able to read this as an 11. Again, there is a very small setting. So instead of, you know, look in formula, what you should say that look in values. So what will Excel do? It will convert all your formulas to like basically, uh numbers and then it will try to search for those values. So now I'm telling Excel that look values, don't look in formulas. And if I do a find all, see now it is able to give me the correct answer. That Chinmay, Nandini, podcast is the file name. In the cell B7, you have 11 as a value. So these are some of the small nuances, like, you know, let's say you, you are just rushing for a meeting and your boss has asked you to check few numbers and aap control F karke aapko dikh nahi raha hai. So just, you know, try this tricks around.
[20:06]Got it. This is very helpful. Yeah. Another way is that you can also use Power Query to clean the data set. Like again, uh like, you know, continuing our theme on cleaning the data set. Uh now you can see here, like, you know, I have written this name, which is Swadeshi Steam Navigation Company. Cool. Now we want to split the data based on like, you know, the capital letters. So you can see I want Swadeshi, then I want Steam, then I want Navigation, then I want Company. Right? Now here is the first name, Rahul Chimman Bhai Mehta. So I want Rahul, then I want Chimman Bhai and then I want Mehta.
[20:37]Right? Now Excel cannot do this. Correct? So Excel does not have a lot of functions which will help you do it easily. But you can do it with the help of Power Query. So I'll just brush upon basics of like, you know, AI or machine algorithm based features. We will use Power Query to clean this data set and then we will uh like, you know, move maybe to the topic of AI because that's the hot topic. So yeah, so what I'm doing is that I want to clean this data set. So I'm going to get data from other sources. I'm saying from table and range.
[21:07]Right. So we get into Power Query. I will say add a column and I will say column from examples. And now I'll just give it an example, which is R A H U L Rahul Chimman Bhai and Mehta. And then I'll give it one more example.
[21:24]Ram Krishna Prasad Memory.
[21:27]Yeah. Uh, Correct? And now say it's able to pick up like what I want to do. And I'll and you can see the formula which it has written, like, you know, is equal to let split names, okay. I'll just click on okay. And maybe I can just click on close and load to. So I can click on close and load to. And you can see my data has been cleaned.
[21:47]Right? Now it would be very helpful if there would have been like 500 rows like this. Yeah, yeah, yeah, exactly, exactly. And you cannot do this in traditional Excel, uh like, you know, you are kind of forced to uh like, you know, do this into uh Power Query only. So that's that's another reason. And this is our Power Query editor. So I'll show you something. I go to view and I click on advanced editor.
[22:10]Right. So yeah, so now you can see this function, let in, you know, table.add column, table.transform column. So this is the language of Power Query. It's called as an M language. Okay. Like, you know, it has used a text.combine function. So these are also like a completely set of new formulas in Excel, which is called as Power Query formulas. So I'll just walk you through the basic concepts of dynamic arrays. I think that's like a very new topic which I'm passionate about. So let me just go to that sheet.
[22:31]What are Dynamic Arrays?
[22:42]So one thing jo hum log discuss kar rahe the, like, you know, which was the concept of dollar signs in Excel. And like, you know, a lot of people, uh they kind of don't like dollar signs. They feel like, you know, kaafi complex hai, uh kaise karna hai. So like, let me just throw this data to you. Correct? You have parts of computers, uh you have the USD, INR rate. And these are your currency conversion rates. Abhi aapko ek ek convert karna hai. So you do this into this. Okay. Then you do this into this. Ek toh manually 24 bar karo. Right? Correct. So that is one way. Otherwise you can use a concept of dollar signs is that where you freeze column C because you don't want your column C to change. Toh mai column C ke pehle ek dollar de deta hu. And I will just copy paste this data. Let's see if it's working or not. So it's working fine for the first row, but in the second row, it's giving me an error because it's again referring to the bottom cell. I realize ki mujhe 20 ke pehle bhi dollar sign daalna hai. Toh mai 20 ke pehle dollar sign dalta hu. And then I do this. So now you're doing this using dollar signs and people are like, oof, this is too complex. Yeh mujhe samajh me nahi aa raha hai.
[23:54]Yeah.
[23:57]So yeah, so now there is a concept called as dynamic arrays in Excel. I'll again go to my screen. And I will show some. Yeah. So do you remember like studying this in school days matrix multiplication? Yeah. Yeah. So you can use the same concept again. It's the very much like one of the most important topics used a lot in computing world, which is matrices.
[24:20]Unfortunately not taught best in the school. So you can just use a simple MMULT function, which is a matrix multiplication function with the help of dynamic arrays. You select array one and you select array two. And you can see you get the output, like aapka pura data ho gaya hai. And you did not even use MMULT. You can just simply say array one into array two, that's it. And you get the entire output. Like no concept of dollar signs, no concepts of nothing, just multiply one array into array. If you want to understand how it works, uh go to your a library 12th standard ki matrix ki book le lo and thode questions solve karo. You'll understand it better. Or maybe you can reach out to me, like if you want to learn Excel. Yeah, we'll do that.
[25:06]So dynamic array includes everything where you select like a like an array and you just play around with that. Like you just don't choose two cells stand alone. It should be an array. What you are multiplying, what you are dividing. Yeah, yeah, yeah. I mean that's that's a very good way you have put it.
[25:24]So see, traditionally hum log Excel mein kya karte the? I am doing C21 into this particular column. So cell C21 into cell I20. This is traditional Excel. But yaha pe hum log kya kar rahe hain? Array C21 to C28 into array I22 to K20. So now we are multiplying one array by another array, not a cell. We are multiplying a range of cells. So this is array. What is Power Pivot?
[25:51]So Power Pivot, again, like it's a so it's an uh like an add-in. So you can see I have a Power Pivot and it's something called as data modeling in Excel. So I'll give a very theoretical explanation to Power Pivot. So basically, as I told you in Power Query, it's based on M language. So Power Pivot is based on DAX language, you know, D A X, data analysis expressions. Okay. You know, that's that's DAX. So like if you learn Power BI, then that works on the basis of DAX. So what you can do is like you can within Excel, uh you can install Power Pivot and you can uh like basically uh work on uh like Power Pivot. So you'll have to learn DAX language. And what you can do is that you can basically uh work on very large data sets. So what happens is that Excel has a limitation that it cannot analyze rows beyond one million, like that is 10 lakh rows. But Power Pivot ka woh limitation nahi hai. You can analyze even like four million, five million rows of data sets. So that is one advantage where you can use the concept of Power Pivots and Power Pivots mein DAX ke functions hote hain. Again, like a completely new programming language. So you can use those predefined functions very easily and you can like, you know, kind of do your job. So Power like Power Pivot helps you do DAX based calculations within Excel. So like that's what is Power Pivot. And a lot of a lot of things are there, like, you know, you can connect few tables. So hum log see dekho hum log lookup lagate hai, right?
[27:28]So yaha pe mujhe lookup lagane ki zarurat nahi hai. I've just connected the tables. I've just said, this is my table. Yeh department, yeh department connect karo. Yahan se isko yeh connect karo. And I've just created, abhi sara data ek common consolidated table ban gaya, which I can directly analyze. No need of any lookups. So that's like, you know, that's the concept of Power Pivot. I understand like, maine kaafi brief mein aapko answer diya hai. But I think that's a very good answer because you have now a theoretical understanding. Maybe then if you want to learn it in detail, you'll have to spend a couple of hours on each topic, you know, to understand it in detail. Would you think would be the users of Power Pivot? Like I have never used it in my work.
[28:07]See, Power Pivot seekhna hai toh aapko do teen ghante lagane padenge basic seekhne mein. Power Query mein aapko do teen ghante lagane padenge. VBA macros seekhne mein do teen ghante lagane padenge. So the purpose of this podcast is not to scare you, but to enlighten you that this is the Excel universe. And every part of the universe, like you'll have to spend a considerable amount of time, maybe three, four hours, to understand the basics of it, so that you can start using it. And based on aapke job mein aapko kya lagta hai, you can use it. Like a lot of people I know, they work a lot on Power Query, unko Excel functions ka kaam hi nahi padta hai because they have that type of a role. Correct? So every role, like some people I know are VBA developers, unko basic Excel functions bhi nahi pata hote hain because their job is more on, you know, developing VBA based add-ins. So aapke job mein aapko kya lagta hai, like, you know, what you need, that is something which you should focus on. And you should know ki yeh universe hai and these are the things which are, like, you know, existence. And what is this Lambda function in Excel?
[29:03]So Lambda's function is my favorite function in Excel. So I'll just give you a very quick overview on like, you know, what is a Lambda's in Excel. So this has happened to Excel in like 2022, 2023. And I make a lot of money, like, you know, by auditing Lambda functions and writing Lambda functions. So I write Lambda functions like which are two, three page long and I audit them. But of course, like we'll cover the basics in this podcast. And I'll just give you an idea of, you know, what is a Lambda function. So like, let's say we have these dates and we just want to understand like what are the fiscal years. Like, you know, you being a CA coming from tax background, you mentioned it was one of your favorite subjects. So fiscal year is a different concept and calendar year is a different concept. So there is a function in Excel which is called as a year. So I just refer it to a date. And I just drag the formula down. So it will give me like the calendar year. So this is 2024, 2023, 2025. Correct? Simple calendar year which is being pulled up.
[30:28]Yeah. So but now if I tell you Nandini, you give me a fiscal year out of this data set. Like I'm not interested in the calendar year. I'm interested in the fiscal year because Indian fiscal year starts from like 1st April to 31st March. Yeah. So this is 20th Feb. So I think this will be 2024. Like, you know, tax year. This will also be 2024. But this is 2024. Yeah, this is again 2024. Uh and this would be 2025. Yeah, correct. It would end in March. So now how do I, like, you know, get this data set? Like there is no fiscal year function in Excel. And there would not be any fiscal year function in Excel. Don't blame Microsoft because they're catering to 100 plus countries. And every country has its own logic. Before this, I was working at EY. EY follows a 1st July to 30th June calendar. So like you can't expect, you know, so have so many fiscal years in Excel.
[31:25]So we will learn a very basic function, if function in Excel. So if we have to define a logic, see, again, while working on Excel, it's not just that you should know about the functions. You should have like a very good logical thinking. So in this particular case, can you tell me with the help of if function, like, you know, what's the logic and then we will build the if formula. So you have months in front of you. You have calendar year in front of you. Now you just need to define a logic which we can just drag it down across the years.
[31:53]So the logic would be if let's say uh that particular date is before March of that year, let's say we are looking at FI 25, maybe we would need to create one more column where we can apply that cell, but the logic would be if it is before, if it is between 1st April to March, uh 2024. Like 23 to 24, then it would be FI 24. Similarly, 1st April 24 to March 25. Yeah, but again, if you're working on a very long data set, then, you know, it becomes very complex to organize, like, you know, write such a long formula. So there is a very simple way to do that. If like my month is Jan, Feb and March, then my calendar year is same as my fiscal year, right? This is what we have observed. And if it's not Jan, Feb and March, like it's from April to December, then my fiscal year is basically my calendar year plus one. Right? Because in December 2024, it was 2025. So I'll just use this simple logic.
[32:53]So I'll just use if, I'll use a month function. So I'll extract month from this particular data set. If it's greater than three, right? If if my month is greater than three, then it's April to December. Then basically it would be calendar year plus one. Yeah, year plus one.
[33:10]Yeah.
[33:13]If not that, like, you know, what if the value is false. So see, if statement works on logical test. So I've given a logical test. If month is greater than three, then you do calendar year plus one which we have done here. And if it is false, then just give me a simple calendar year. Correct? So I'll just take calendar year as an example. And now you can see I can drag this formula down. But again, now you asked me the question about Lambda and yaha pe calendar years, fiscal years, you know, ye sab cheezon mein chala gaya. So like what's the use of Lambda? Now you see this is a very long function and writing, I mean, I love working on Excel and I love, you know, writing these big formulas again and again. But let's appreciate people don't always like it. And second thing is you want to do your work efficiently, like, you know, you want to fasten your work.
[34:01]And there is one more problem. Like I always used to hear this from my seniors that Nandini always keep the formula simple because the longer the formula is, more are the chances of making a mistake or inserting something by any chance and the entire flow gets disrupted. Yeah, absolutely. I mean, that's a very good point, because what happens is that, uh like, let's say some junior is trying to, you know, write this formula again and he makes a very small error here and there. And then, you know, we are difficult to trace. So what we can do is that we can create our own functions. So in Excel, usually we have formulas and functions. And like we have like 500 functions. Now what I will do is that I will use Lambda function to create my own function. So what I'll do is that I have written this formula. Now I will just put it inside a Lambda function. So I'll write the Lambda and then I will give it a variable. So like let's say I'm giving it a variable as month name. Fine. And then instead of cell A6, I will just replace everywhere, you know, with a month name. So I'm just typing it month name here.
[35:04]Okay. Yeah, and and then just see the magic like it's it's one of my favorite functions. I have done this, it will obviously give me a wrong answer. So I will now just copy this Lambda. I'm copying this and I'm going to Excel's name manager. So you go to formulas and you go to a name manager. And then I will type new and let's give it your name. So Nandini's fiscal year. I'm writing it as Nandini FY. And I'm just pasting this function here. And then I'm clicking on okay. So now my Excel will have a function which is called as a Nandini FY. And then I it's see now, moment I write it, it asks us month, like, you know, month name or date, whatever you want to just give it. So you'll just give it as a date. And then you can see I'm getting all my answers in one go. So I don't have to write this big formula again and again. I have written Nandini FY and I can just use it again and again. So that's the beauty of Lambda. You code a very complex logic, and then just you can use it again and again. It's it's like, you know, having those ready to eat packets where you have made all the efforts in one place and then you just have to boil it and you can use it. So that's the concept of, you know, Lambda. You can create your own functions. And it will happen that in a job role you will be doing same calculations again and again, like depreciation is going to be same in all models, payback period is going to be same in all models. Year to date values is going to be same in all, you know, fields. So why not create Lambdas and reuse them again and again to improve your efficiency. Got it. Got it. This is very interesting. So basically, Lambda is a function which you can use to create customized formulas for yourself and you don't need to type them again and again. Or even drag them again and again. Yeah, yeah, yeah. That's that's the beauty of Lambda. And it's so simple. I'll I'll just create a new file. So in this particular new file, again, the keyboard shortcut is control and N. So you can see here we don't have that Nandini Lambda, right? So when I type N, it's not giving. So what I'll do is just I will create a blank sheet in this workbook. The keyboard shortcut is shift and F11. I will do a right click. And I'll I'll just move a blank sheet to that new workbook. So this is the new workbook, which I started and I'm just moving a blank sheet there. Create a copy, click on okay. So see that blank sheet has moved. And now all my Lambdas have been here. So if I type Nandini FY, you can see Nandini FY is here. You just need to transform one blank sheet into the model and those Lambdas would be. So it would be there in sheet one as well. Yeah, yeah, it would be there throughout this workbook now. So you can it it will be here also. So you can see here is also it's Nandini. So basically your Lambda is sta is stored in this name manager. And once you transfer a sheet, all your names also get transferred. So that is how like your Lambdas also get transferred. So that's kind of the way it works.
[37:59]So guys, that's all for this video. Hope you liked it. And if you did, let me know in the comments so that I can come up with more such videos for you. And also, I'm looking for suggestions from you that what all should be covered in a detailed podcast on Excel versus AI in Excel. Looking forward to your suggestions. Thanks a lot for watching. See you in the next one. Bye bye.
