 Okay. All right. Good morning, everyone. Welcome to 30-minute training session with Adiwali Isuf. Today, I'll be giving you some tips on some Excel functions that you really need to change the way you work. So I call them top 10 Excel formulas that will change the way you work. So I'll not spend much time adjusting Excel functions. We just look at different scenarios of how you can use all these functions that we've mentioned. So these are the functions I listed on my screen. The first one for me is text before and after, then the text split, the sequence, choose calls, vstack, each tag, to calls plus to row, take, drop, and filter. So for me, these functions are amazing, and I feel it's something I should share with you all, since I find them amazing while using them. So I'll start with text before or test after. So let's say, for example, in Excel, you have this kind of DETI test right here. This is not really DETI, just that they squash things together. This is names, star ID, and department, and you want them to just extract some kind of test from this thing. What most of us use in Excel, then is whether you use a text to column or use a function in Excel called left. Even when you use left, one of the problem of left is when I use left here, I want to use left to extract. I need to specify number of characters. Now, you need to now combine another formula. Maybe you combine a find formula, or you combine a search formula to be able to get the names that you need. If I combine a search formula now, I can see I want to search for where there is comma, and then that is when this test should stop, because if I don't specify that I want to drag down, it's not really going to be easy for this formula to identify where I want to stop. So if I do this, I'll be able to extract the name, but with the help of text before or test after, it makes things easier for you. So instead of doing that, all the way of doing things, I can just see equals to text before. On test before, you just ask him for some few arguments. I want to extract this text. What is the delimiter? Text before which delimiter? So the delimiter I want to extract is before comma. Before comma, and I can select an instances. So which instance do I want to select it? I can put a match mode. I can even put even offense. So what should I do? But I can stop here. So what this is doing is I'm extracting a text before comma. So you can see this is very fast compared to you doing left, or you even doing text to column. Same thing for department. If I want to extract this department from here right now, I'm going to combine three or four formulas to be able to extract L and G. If I want to use formula, I may probably use right, learn and also find or search. Those three formulas to be able to extract L and G alone. Or I should just use FlashFuel or test to column. But sometimes people really want to use formulas. They don't want to use FlashFuel because it's just the two and it will not change when the data set updates. So you can use test after here as well in this scenario. So you just come to your test after, you select whatever you want to do. I'll say I want this, right? And then after which the limiter is comma. So if you look at this test where you see I have name comma, I have star ID comma, then I have L and D. So this L and D is like the second instance of my comma because I have one comma here, I have another comma here. So what I'm going to specify my instance number right now is going to be two. Because I'm saying that I want the test after the second comma. So when I close my bracket and I do enter, then you're going to see L and D. Consider that kind of fast. I'm using only one formula. If I want to achieve this in the old way, using formula, I'm going to combine three. Formula to be able to do the same thing, all right? So that is the first formulas I want to share with you which is kind of interesting. So another one is called test splits, right? Which I find very, very interesting. So imagine you want to split the data and just split it right there with a formula. I don't need to use a test to column or anything. I don't need to use formula one by one. If I want to achieve these three things, that means I have to use left here, I have to use mid here and I have to use right here, right? But even all these formulas I put here, I see I have to combine some things with them so that they can work effectively. Like left now I have to combine find, mid I have to combine learn, I have to combine search, right I have to combine learn and search. So it's kind of very tasky. But with this one single formula, I can just split everything right there by using a function called text split in Excel, right? This is it here, text split. And what are the argument of text split? What text do you want to split this text? And what is the delimiter? I'm going to pull my delimiter is comma, right? And you have a root delimiter, I don't have that ignore empty, match modern, whatever, do I want to pad it with something? So that's another thing that I can kind of think of. So if I do this, I can just hit enter. And you see immediately I kind of split the entire text into three using the dynamic array, which kind of makes something this very, very interesting. And when I drag it down, just look at this. When I drag this down, you're going to split it for everything as well, right? So text split is kind of interesting. When you're working with text in Excel, it's something you should try. Another interesting formula that I see that I like using a lot is sequence, right? A lot of people don't use the sequence, right? I think you should start using it because I have so many things you can achieve with this. Just see, let's say for example, we want to create a random serial number, right? I can use a sequence and say equals to sequence, just very basic. And I want you to create a random rule, right? Now I'm using rule. How many rows do you want to create row, right? I want you to create serial number for 10 rows, right? Now it's going to give me serial number for 10. It kind of makes sense. Another instance of how you can use sequences, you can say, okay, maybe I want you to create a serial number based on whatever I type in this, under this name, whatever I type here, that is when you should create a serial number. So I can do equals to sequence of this sequence again. And I will say canter of, I'll just combine canter with this and say canter whatever is inside here, right? And anytime I type, it kind of create a serial number for me automatically. So you see this is showing crack right now because there's nothing here. So if I put Wally here, so I have serial number one, that is one. I put Pellume here. So this is creating the serial number automatically. I put Musin, I put Philip here. And as I'm typing, it's generating a serial number automatically, which kind of makes sense, right? Another instance where you can use a serial number is you can use it to generate a random number or you can use it to generate a random date. So if I say equals to sequence right now and I want to generate a number of rows, I can see, okay, I want to generate 10 serial number in a row. Then I want to create it for a column. No, I don't want to do for column, but you can as well do for column. Let me even show you example of a column. If I do two columns now, right? I want you to generate a 10 serial number within two columns. Look at how it's going to generate it. This is one, two, three, four. So just generating those numbers are cutting to the 10 rows in two columns, right? But that is not what I want. So I'm going to step the row. So I want to generate a random number and I want the random number to start from 50, right? Start from 50 and I want to step that 50 by five, right? Which means you'll be adding an additional five, I can even say 10, should add an additional 10. It should just keep on switching the 50 by 10. So if I close the bracket and I enter right now, you can see I have 50, 60, 70, 80, 90. So I'm stepping this 50 by 10. So I create a sequence of 10 numbers and those 10 numbers, I start from 50 and I'm stepping it by 10. I can do the same thing for dates. Let's say for example, I want to create a random date that change based on seven days in a week, right? So I can say equals to sequence, right? Give me a 10 row of data, right? I'm not doing anything in column. Then start from dates. I'm going to specify and use a date formula, right? Start from 2023, month is maybe January. So I'll just put one here and days one as well. So then I want you to step this by seven, which is every seven, seven days. So it's returned the date by seven days. So if I hit enter right now, okay, this is not formatted. So let me just format it. Yep. So if you look at this date now, you can see that it is stepping it by seven, seven days because you have one here, then you have eight here, then you have 15 here, then you have 22, 29. So it's stepping it by seven, seven days. So sequence is kind of interesting. If you are not using it before in Excel, I think you should start using it, right? So moving on to the next top four. So the four for me is the choose column. Choose column is kind of very interesting in Excel because I found out this choose column, I think some couple of weeks ago, and I was very, very amazed by what this guy can do because one of the problem people have when using filter in Excel, the people that are used to using filter or those that use filter very well, one of the problem we have in Excel when using filter is you want to choose columns, right? When you're using the filter function. But what some of us now do is that we now combine sequence with filter to be able to select some appropriate columns that we need and all that. And the formula kind of look very, very big, but we choose columns. We can do a lot of things very easier because just allow you to choose columns. Let's say for example, these tables I have to the right, right? I have name and I have department and I have gross allow, right? But the columns I really need here is name and then gross allow. I don't need this department column. I just want to choose name and gross allow, right? So I'm going to use my choose column measure, I would say equals to choose columns, right? And I'm going to choose, I'm going to highlight the entire table, which is this table. And then I'm going to put comma here. So, and I'm going to choose the columns that I want. So I want to reach out the columns. I want column one. Then I also want really comma again and I want column three. So I want one and three. So I'm choosing column one and three. Once I hit enter, you see that the dynamic array we speed this and it's only choosing column one and three. Right? It's leaving the department column out. Imagine me using this with filter. It's to be very, very interesting. There's also choose rows as well. So you may as well want to choose rows. There's a formula called choose rows as well. You want to choose, okay, I want to choose this row and this row and this row and this row. So, but for that, I will know it's not part of my top 10. So the next formula I found very interesting is VStack. I was so happy when this VStack came out because a lot of people in Excel don't know how to use a part query, right? They just want to do some manipulations right there in the Excel, right? They want to do some consolidation right there in Excel. Although we have a tool in Excel called Consolidate, but the tool is kind of very old. I'm not sure a lot of people even use it. If you go to your data tab, your Consolidate is up here beside your Flash field, right? Your Consolidate is up here beside, I think in my years of experience, I only use this Consolidate once or twice and it's even during the par excellence training, right? So people use a part query now to Consolidate stuff. But with VStack in Excel, you may want to dynamically Consolidate some tables so that you can use it in your report. Imagine even use VStack plus two columns and filter inside the same expression. It's going to be very, very amazing, right? So let's say, for example, I have these two tables to the right here. I have the tables that give me the names of all staff and their departments and I have their go salary. Maybe this is for January and I have another table here that has the same names of staff, departments and go salary and this is for February. And I want to kind of Consolidate these two tables in June one, because I don't want to stress myself now doing the Consolidate one by one. What most people do, especially people that don't know Excel is they copy, they paste on that, they copy, they paste on that. So with VStack, you can kind of Consolidate the table together without stress. So I can call me and say equals to VStack. VStack here and VStack is asking for arrays which array did you want to Consolidate, right? So I'm going to go to my array or come here. I'll say I want to Consolidate this guy, right? That's the first array comma and then this is the second array. I want to VStack these two array. Then I'm going to close my bracket and it enter. Once I do that, you see that it has Consolidated the arrays for me. Now I have one single table. I have one single table, which is very interesting. Another example I can use for VStack is, let's say for example, you have a table like this. I have a sheet that has different tables. So this is people that has this January, February, March, different tables, right? This is just a lot of people, right? But it's in different sheets, right? And in different table. You can even be here to 11, 12, 12 and all that. And I want to Consolidate all these tables into one Consolidate file without using Park really. I just want to Consolidate them here. So I can kind of Consolidate it by selecting the array one by one, the way I did in the first example. But I can also name the array, right? For those that are used to naming in Excel. When you name the array like this, right? I'm just going to highlight it and you go to your name box here and you name it. So I have these array names as Chan. So what I'm using my VStack is kind of very easy. So I can come to here and say equals to VStack. I just want to Consolidate January to December and I will type Chan then comma Feb. These are the things I'm Consolidating March, April, May, June, July, July, then August and then September and October and then December. So by doing this, I have put all of these 12 sheets, right? Inside one single sheet, right? I've Consolidated it and when I hit Enter now, it's going to Consolidate everything, which is from January up to December. If I scroll down now, you will see this as January data, February, April, so everything is kind of here. So imagine me, you also want to do a report and I want to use this VStack as an anchor inside my account. So that's an example of how you can use VStack. Now, I also have HStack as well. HStack is kind of similar to VStack. So VStack is Vertical Stack and this is Horizontal Stack. So let's say I have something like this that I want to work on. I have months like this, I have a list of my month and I want to split this month into 2011 and then to do that, I want to split this month and I want to split this month into 2011 and then 2012 and I have a table right here that I have the year and the amount, right? So instead of me using pivot table or whatever, I can dynamically use the HStack here to do this. I'll just say HStack of what is my first array. My first array is the 2011 array. I will stop here in the input comma and start from the 2012, that's the second array, which is the number that I want to pull. All right, that's my second array and I'm going to close my bracket. So once I do this, it's going to spew the 2011 and the 2012 into two different columns, right? Which is interesting for each stack. So that's an example of another interesting formulas that I have, this is my top six. Now for top seven, top seven is kind of interesting for me because it just kind of make cleaning easier in Excel and working with text easier in Excel. So this is called to call and to row. So imagine you want to put all these list of names into a column. So I have different departments, right? I have the operation department, these are the people in operation. I have the sales department, these are the people in sales. I have the marketing department. So this three department are kind of one department. This is like staff names of everybody in D-Brow. Now I want to call, so you did this three guys. So instead of me doing copy and paste, I just copy this, paste it on that, copy this, paste it on that. What if I have like 15 or 16 things like that? Copy and paste may not really work well for me. So I can just do a formula called to column, right? I'm putting everything into a column. So that's called to call. So once I do to call, I will highlight the names, right? Which is the names of all the things I want to put into a columns, right? Then I'm going to hit enter. Once I do that, to put everything inside one column, when you scroll down now, you will see I have everything but I see I have the 000 here. So this zero here is just some blanks that I have here. So I can actually do that in the formula as well. Inside the formula, when you put a command, it will tell you, do you want to ignore something? That is the ignore arguments. Do you want to keep all value default? Do you want to ignore blanks? Do you want to ignore errors? Do you want to ignore blank and error? So I will pick three instead to say ignore all blanks and error. So when I close my bracket and enter to give me the list and right now I don't have all those zeros again. So I just have the list of all the stuff right here. Which makes a lot of sense. Now, the example of the second formula, which is to row is maybe probably I want to convert columns to rows. So look at this department. Let's say this department, for example, I want to change it into a row, right? That's something like I want to transpose it in Excel. So I can do equals to row equals to two row and I'm going to select my arrays. So I want to put this department into rows. Once I close my bracket and I hit enter, then it goes to split this into rows. Look at that. Going to split this into rows, which is kind of amazing. So when you're building reporting Excel, things, it's make things easier. Now my top eight, right? Is take. Take is kind of very interesting too. I like that. I've been trying to think of how I can get a use case for this time. When do I use a take, right? So let's say I have a list of tables somewhere and then I want to do like something like a pool to take only the top five first or to take only the top 10 first or to take only the first five. So that is what take does. So you have a list of tables. You have a list of rows inside your table and you want take to just take maybe five out of it or take the first 10 out of it or take the first 20 out of it. So you can kind of use that to kind of minimize kind of details that you have as well. Maybe do a top five. So I can say equals to take, right? And what do I want to take? I want to take, this is the array, right? I'm going to select the array and put a comma. So what do I want to take? I want to take the rows, not columns, right? I can kind of take columns as well. So I want to take rows and I want to take top five, right? Once I close my bracket and I hit enter, you see? It takes only the first five for me. Takes only the first five. Kind of makes sense. Can do top 10, can do top two. Just take the two and all that. Another reverse of this formula is something called drop which is the top nine for me. So drop also makes sense. Drop also just means that out of these columns, right? It's just the reverse of this one. Out of this table, which one do you want to drop? I can say I want to drop the first 10. Just remove the first 10 and give me the remaining one. I can say I want to drop the first 11 and give me the remaining one. So it depends on you, right? Or whatever you want to drop. So if I want to give an example of that, I'll just say equals to drop. Drop and I'm going to select my array which is this table up here. This table here and I'll put a comma and say what am I dropping? I'm dropping the first 10 row. So give me whatever remains. So you can see I've dropped the first 10 row and give me the remaining one which is just four rows inside my table. So this formula dynamically formalize. Amazing, that's my top nine. Now my top 10 is the filter functions, right? A lot of people have been using filter functions but for me, I just love using the filter functions, right? So I think it should be part of my top 10. Even if I have so many formulas, I just limit it into 10. Now for people that need to filter functions, filter functions in Excel kind of help you to filter a table or create some dynamic report in Excel without the need of using people table and maybe some other formulas in Excel. So let's say I have these tables right here. These tables have the list of names, the staff code, the department and glossary. And I want to do a filter that filtered this by department and also filtered it by maybe when salary is less than or greater than something. So I can do a simple filter here and say equals to filter, right? What is my array? This is my array. The table called table one is my array. Now what do I want to include inside this filter? So I want to include department when department is equals to maybe whatever I type up here. So look at this department here. And the department is equals to this. I'm going to close my bracket and then if I even close my bracket, if I put a comma there's an if empty. What if I cannot find any department you put here? Then I can put a condition that say just put not found right here, right? And I hit enter. So these filters only show me the department that are here. If I put L and D, right here, L and D, filter only showing me department related to L and D. Let me put operation and let's see if operation is found. So you can see there's no department of operation so it's showing not found, right? Which makes sense. So let me type marketing back right here. Now I can have another condition that says only show me the marketing when the salary is greater than one million. And if you look at this marketing here, there's only one person that has salary of one million, right? So there's only two people have salary of like three million or two. Let's say I want to see the people that their salary is above one million or above two million. Then I only see these two people. Then I'm going to have made my formula. So inside my formula, I can say look at this. I can have two conditions. So one of the issues that people have is in Pabii, I said in Pabii, sorry, in Excel, the include argument is only showing one. So where will I add the second arguments that says when the salary is greater than one million, right? So there's a trick to do that. And how to do that is you put the first argument into a bracket, that's the first trick. You put the first argument into a bracket and you put multiplication sign to say, usually in Excel, it's supposed to be unsigned or maybe all signed, right? But future functions kind of work well when you put the multiplication sign. And you put the second argument into a bracket as well. Into a bracket. So I'm going to say when salary, so now select the salary column. When salary is greater than, maybe greater than whatever I type here, right? We turn that whatever I type here. That is my second argument right here. So I put those two into a bracket and I'm going to put comma and say not found as well in case you didn't find anything that is close to that. So close my bracket and just let's it enter. Okay, so I think I'm missing a bracket here. So table, okay, department. So my department is equals to this. But my department is equals to this multiplied by when gross salary is greater than whatever I put here. So if I come here and I type two million right now, I'm sure it's going to fit right into only three, only two. So you see, it's only two. Let me type L and D department. Did L and D departments have anybody that had any salary of above two million? Yes, it's only one person, right? What of above one million? So there are two people that hand above one million. So what of another department like sales? Do they have anybody above one million? Yes, so that is me just using few tasks to just check different things. I can check admin department. How is admin department doing? Do they have anybody above two million? Not found. There's nobody that hands above two million there in the admin department. So this is me just kind of doing a short report with filter function. Now let's try and see how we can combine filter functions and then choose call together. So let's say for example, I just want to return only the name and gross salary. I don't want to return everything. I don't want to put name, staff ID, department and then gross salary. I really want to do a filter for name and gross salary. So my formula is going to be very similar to what I have up here, right? But I'm just going to add choose call to it. So here I'm going to say equals to filter. Equals to filter. I want you to choose call, right? Choose column. What array do you want to work with? This is my array here. So I'll select this table, right? This table is my array. And then right here, I'm going to put the comma and I'll say, which column did you want to pick? So I want to pick column name and the gross salary. That's column one and column four. So I'll put one comma four, one comma four. And I'll close my choose call a bracket. So that's my array, right? Then I'm going to do a comma and say when my department, when department is equals to whatever I type of there. So when department is equals to whatever I put here, which is add me, right? I'm going to close my bracket and it enter. Now look at this. So this is only showing me names and gross salary now with the app of choose call. Because I've choose columns that I need. And I said I only need two columns. Try it. Now the people in the department that I selected up here excel here who are already familiar. So if I put another department here, marketing, marketing then you see the people here in marketing. These are just a deli key, something and then give me like a share gone, right? So this is my top 10 except formulas I feel that you shared with you.