 Hello everyone, welcome back to another video. In this week's video I've picked five new formulas available to us in the Office 365. I just wanted to do a dedicated video towards to go through each one just to hopefully go a bit more in-depth and show you what each of these formulas does. Each one is really simple to understand and get the grasp of. You might have actually seen me use a couple of these in previous videos But I thought it just really beneficial to do a dedicated video to go through each one independently Because we'll probably be using them a lot more in the future. So to go through each one We've got round array sequence sort by and unique So we'll go through the first two round away and round away and then also sequence In this sheet then we'll jump over to the other tab I have there just because I need a bit of extra data to do so. If you do enjoy the video Please do give it a thumbs up. Also, if you're new to the channel or your previous viewer Please do subscribe. Hit that bell notification button as well And that way you'll be notified of our new videos as they come out. So without further delay, let's jump straight in So random array is really great for generating random numbers So people often ask me about this in regards the content I use for this tutorial Is there a quick way or easy way to generate random numbers in? Excel as a standard or not as a standard but in Excel we have a formula called random number of them beginning with random So we've got round round array and round between so previously I would have used this formula what gives you a give you a random number and Simple way to use it is say we want a random number between one and ten Do this hit enter and you can see that a random number has been produced for me If I hit f9, which just as refresh on the sheet You can see that every time the sheet is refreshed another or the calculation is Recalculated and a new random number is produced This is great And obviously what you can do is you can drag this down and then you can obviously and drag this out and you can Gradually build out a range of numbers that again when you hit f9 are going to continue to randomly generate The benefit of round array so the new dynamic formula We have is it kind of takes a lot of this work out for you allows you just to pre-determine What and how much information you need? So with round array all we need to do is equal to equal brands and hit in array And you can see we've got a number of values that we can enter in here So the first one that we've got is rows So if we just enter in say we want ten rows close brackets and hit enter You can see how our formula spills so it covers the range of ten rows as we required and it gives us a random Load of random numbers that we have in there. You'll notice at first. There are decimal decimal numbers But we'll get on to in a second. What's that the last? Edition we need to add into this where it allows us to do if we want an integer So a whole number instead if you go back into the formula and Let's just remove the bracket off the end there to get back into here And let's say we go into columns. I'll just do a comma there and let's say we want three columns and Then open or close our bracket, sorry You can see now how that range has been spilled over to now three columns in addition to the ten rows And once again if you hit F9 each of those are going to be randomly generated each time the page refresh Let's get back in and it's worth mentioning here if you weren't aware Because the formula has spilled it looks like if you click anywhere here that you will see the same formula But in fact, you just need to go back to that first cell there just to make any edits that you're quite So this is where we can now get a bit more Put in the determination that we require So let's say we want to have a minimum value will be number one and let's say the maximum is 10 like we looked at earlier Go to 10 and then do another common and this is where we can now determine So if we enter false, we'll get decimal and as you probably aware because we didn't put Previously false is the default value, but let's say we want to use an integer So we just click true or you could have typed in there alternatively Close your brackets and hit enter and you can see you've now got those random numbers generated for you So just to go and start over again Let's just remove that and the real benefit obviously using round array is say we wanted quite a lot of random data All we need to do is go around between Open brackets Well, I'll put the wrong one on there now. I've gone back to the first one. Let's go round array and Let's say we want a hundred rows By a hundred rows and we wanted random numbers between one and ten and we wanted them to be Integers for the brackets so you can see that is a lot quicker way I've obviously putting those random information in there rather than doing that round between What we've previously had available in Excel So we can close that and there. It's gone. So that's the benefit of using around the way It just gives us the benefit of quickly getting a load of random information Depending on a couple of key pieces of criteria that we acquire So that's round array done. Let's just mark that as yellow. So for sequence So sequence is really good for creating an array of sequential numbers So going from one to ten or whatever you require If you do equals and then enter in here sequence And brackets so you can see rows is a mandatory field So we do have to put in a number of rows and we'll gradually go through this So let's say again, we want ten rows You can see that it's given us ten rows of information and it goes sequentially up from one through to ten So that's just the default value. Obviously if we put in here only five It's going to give us the same but only you'll see up to five rows worth of sequential data If we now want to change this, I'm just gonna put it back to ten just because it gives a bit more information and I say we now want to go over five columns close brackets You can see how again, we still got sequential data over ten rows But what it's actually now done is it's spread it over a number of five columns to be exact So that we have sequentially from one to five across the columns So obviously this is what we're referring to here one to five and then obviously ten rows worth means that obviously It's going to go from an overall total of one to fifty So this is really beneficial if you again want sequential data if you're potentially building a chart or some kind of I don't know Examples escape my mind But again, it's really beneficial if you're trying to show growth over time So that's probably a better definition of why you want to use this So to go back in and probably Re-clarify why you probably use sequence we can now say And then columns we can now enter what our start number is. So let's say our start number is a hundred and We want to step so what is our step so step is the sort of the addition We want to apply to our starting number. So if we were to go step is let's go ten and close brackets You can see that our starting number is a hundred and then each sequential number is going to add ten to that So it's now going to go from a hundred all the way up to five hundred and ninety and again This could be a good Then or use the data if you're trying to do like maybe a graph or something to show sequential growth over time You might not want to do it over columns But you get the idea that if we again, maybe didn't want over a number of columns We only wanted over the single column That we can obviously just do that and we can show see the starting number of a hundred growing by ten each time over ten rows So that is how to use sequence So for the next ones I'm going to jump across to our other tap here I've been named the central Excel because we've got some information in here to show us And let's just copy them across just so we can remember what we are looking at Let's put them over top here. We're always doing that one there So format is a bit different. Cool. So we're looking at sort now So the real benefit of using sort or basically a neighbor gives us the ability to dynamically sort the data We're working with and I emphasize the word dynamic because as the data changes So will the information that is spilled using this formula to use sort What we do is do equals and then the word sort and open our brackets And then the first thing when you do is select our array So you can either select your range by obviously doing selecting the range as I've done here Or because the information I have here and you might recognize this from a previous video I've done Because it is a table if I hover in the top left hand corner get this black diagonal arrow I can just select the table as a whole. So that's another little tip there Once I've selected the array either by selecting the table or actually manually selecting a range I can just do common and so sort index is going to be okay What is the column within that range that we want to sort by? So we're going to sort by salary. So for us, that's going to be column number three Moving along that's sort order. So you can even go obviously ascending so small to largest or descending largest or smallest in our example For us, we're going to go ascending orders. So we start with the smallest salary So we need to do the number one there or of course, you could just click this here if you wish And lastly, we've got our true or false. So true being sought by column So what that will do it'll put on what the value or the column was sorting by being salary It'll put that in the first column, but we just want the same in the same order So department name and yearly salary. So we can just go into false here But actually you don't even need to put this information in because it is an optional field So if you want to do that's what you you do with that else All you need to do at this point is do a close bracket and you can see how the information is sorted for us And there we go We can see that we've got our department name and yearly salary So the same orders are table on the left, but this time our salary is sorted in you ascending order as Mentioned before because this is dynamic if we get and change anyone. So let's say Bruce here. Let's put his salary to 15 You can see how automatically that's pulled through to our sorted information Just to do one more. Let's go into Naomi and let's put that down to maybe 1500 Again, you can see how that's sorted as well. And if we suddenly changed our mind and we wanted to do this and into Let's say descending order rather than ascending What we need to do is click our ascending or descending there or minus one hit enter And it's going to automatically sort for us as well The next one we're going to look at is sort by Well, it's exactly the same as sort. It's just that sort by allows us to add multiple Sort sorting criteria. So basically sorting by multiple columns would be an easier way of putting it So let's say we want to work the same way we're doing. So we'll go equals sort by And this time we want to select our array. So nothing different now Okay, so by array one. So what is the first piece of information? We want to sort by well I'm going to do it by department. So again the same way I selected the range of information You can either do this manually by selecting here or because my information is within the table All I need to do is hover at the top here and you see it selects that particular column within the table if I just go Column here not column comma. Sorry. I didn't have the choice of deciding whether I wanted to be ascending or descending Because we are working with text here. This will be alphabetical in ascending. What sounds logical So I'll go for that. Let's go ascending order there Let's do comma and then next one is name. So I also want to sort by name So let's go to the name column Do a comma and this time again. I want to do it in alphabetical order So I just want to do the number one again close brackets and hit enter and you can see now how our table has been sorted by first department and then name in alphabetical order and then also by yearly salary I only did the two criteria there, but we can indeed do more if we so wish We want to go one step further and say, okay, we also now want to do the yearly salary and We want that also in ascending order. Well, actually, let's go into descending so minus one So we can now see all our information is sorted for us and once again If we had to change any of this information so Bruce down here So you'll see him on this line at the moment if we changed his salary to 100,000 You can see how it's automatically populated for us And I guess another example here. So let's say Beverly Atkins Where is she so she is in finance. Do do do do Beverly Atkins in here. So let's say for whatever reason Z just because it's the last letter we take that you can see how a Zevely now named Obviously moves into a different order because she's now her name is now last in the ascending order for the cap department of finance So that's how we can filter or not filter. Sorry. That's how we can do sort by with Multiple criteria across our columns The last one we have to look at is using unique. So unique is a real powerful Former for using many times personally, I'm sure you do as well when you're working with data You might have duplicates in there either rightly or wrongly and you just want to understand Okay, what are all of the unique values? So I don't want if someone appears 10 5 6 how many different times I only want a list that shows me each value appearing only once and this is where unique comes in perfectly handy for us in Previous or in Excel you obviously have the removed duplicates button and there might be some complex formulas You can often use to get unique numbers, but there's nothing as easy as there is now So if we go into unique open our brackets And for us, I'm just going to go and select the department option here So let's go to department That column and if I was just to do close brackets now and hit enter You can see it gives me a unique list of all the values that appear in that list And as you can see on this list straight away We've got sales in here just like five times at least or six times that you can just see Obviously what it's done is removed all those duplicates and just giving us a list of unique values If you wanted to go by further what we have the ability of doing here is to say what do we want to do? So we've got true here if you want to return unique columns or false if you want to return unique rows Well for us, we just want to do unique rows because that's what we're working with here It is an optional field so you could even do a comma and leave this blank and then another comma to move on But we'll just populate this so we want return unique rows and The last option we have here is we can do return items that appear only once So if we had to do this option for true and close brackets You can see at the moment we get an error because there's nothing that only appears once if I was to change sales to sales room Because that only appears once unless let's just do here. Let's just do sales law You can see how those two values are being pulled through simply because they are only appearing once in the list as our formula says But if you want to have an actual unique list of all values just appear in once then we just need to do the false option here and hit enter and you can see we get that now and You can see what sales room and sales floor in here as well But let's just put you back to sales as they should be What also demonstrates how great and dynamic this formula is and we got our dynamic list there as well So hopefully enjoyed that tutorial. We went through obviously five new formulas that Hopefully you weren't aware of and you've learned something new or if you were aware of them Maybe you've seen them in our previous videos or a couple of them at least at least It's gonna be a bit more insight into how each of them works and the benefits of using them They're not available at the moment in Everyday Excel you need to have the office 365 subscription to be able to use these functions as it stands But I believe it's really personally beneficial to try and get access to these and start familiarizing yourself because as soon as they do become available these can be really powerful formulas and They're gonna save you a lot of time and hassle with doing some Sometimes quite complex things, but now we can obviously achieve them using relatively simple formulas So if you haven't already, please do give the video a like It's not only appreciated by myself But it also helps that all important YouTube algorithm and make sure more people do find this video and others on our channel If you are new to the channel or you have seen our videos before please do subscribe Also, make sure you hit that bell notification button so you're notified of all of our future videos So that's everything in this video. Thank you very much for watching and I shall see you in the next one Before you go don't forget to check out the other videos on our channel You'll see everything from other functions and formulas through to tips and tricks We've also created some playlists so you can see these categorized together So make sure you check those out and get all those useful information And obviously as always don't forget to subscribe and hit that bell notification button