Perform Calculations and Operations

0:00Make sure that you've got this file open from the supplemental files so that you can follow along and taking a look at for example cell F10 here well I'm sure you already know how to do adding up. We did talk about this at some point before we can say cell reference plus cell reference plus cell reference but we also already know that if you want to add up lots of cells you may as well use the SUM function. So for the January figures here let's in the total cell in cell F3 let's use the SUM function that's exactly what I want it to add up good that'll do nicely and we also already know that we can use the fill handle to repeat the formula all the way down so yeah adding up and summing I think we've kind of already done. But taking a look at our January figures here we've got some figures for Manchester so sales figures and costs figures so therefore our profit is going to be our sales minus our costs so that's how we would do subtraction again I don't think that's too tricky no great surprises let's just repeat that all the way along for our other stores so yes that's looking good. But now let's imagine we're just estimating some figures for London so let's say we're estimating that the London figure is going to be double the Manchester figure so it's the Manchester figure multiplied by so the

1:16little asterisk multiplied by two so that's double and we're going to estimate that the costs for London are going to be half of the Paris figure so there's the Paris figure divided by two so that was just quickly some examples of subtracting multiplying and dividing making sure that you're happy with the symbols that we type you know the the front slash there for the divide the little asterisk there for the multiplication good. But when we've got a combination of different mathematical operators in the same calculation we have to be aware of the order in which Excel will calculate this. Now you might be already comfortable with this you know depending on what your knowledge of mathematics is already but it's something that we do need to be aware of so we 're going to do an example so if you look at this January block of data you'll see that we need to work out what the profit share is and I've just added in a little comment there how we're going to work out the profit share so the profit share is calculated by dividing the profit plus a fixed bonus of 500 by 5. So let's do that without any real thought for the order in which that's going to be calculated so it's the profit plus 500 divided by 5. So that's what the note said that's how we calculate the profit share for January but when I enter that

2:32in you'll see well hang on a minute how can the profit share possibly be bigger than the profit itself and it's simply because when you have multiple operators Excel is going to work it out in a particular order it's going to do any timesing and dividing first and then any adding or subtracting whereas for this formula here to give us the right answer we need it to do that adding up first we want the profit plus the fixed bonus of 500 to be done first and then the result of that to be divided by 5. So to force Excel to perform calculations in a particular order we need to use these little parentheses or brackets as I would say so I want it to say well do the C5 plus 500 first and then divide what you get by 5 so if I press enter now good so that's now a smaller number than the actual profit that's what I was expecting for the profit share so just be aware of that and add in those parentheses where you need to to get the multiple operators calculated in the correct order but I'm pleased to report that the profit share calculation for February is much easier the profit share for February is simply 12% of the profit so again you might already be very comfortable with working out percentages you know I'm not trying to force you to do things my way but if you're not that comfortable with percentages the way I

3:49do it so if I want to work out what is 12% of that cell where well I type in 12 and then the percent signs are 12% and when you're trying to speak calculations in natural language of usually relates to a times by so 12% of or times by that and that's going to give us 12% of that figure so I can just repeat that all the way along as I said if you've got another way that you would work that out then that's fine as well so that's all well and good that's how we work out as you saw here 12% of a figure or any percentage of a figure but what about now we're saying that for these March figures here we're expecting a 10% increase on the February figures so for example if the Manchester sales figures we're expecting it to be 10% more than the Manchester sales figures for February well I don't just want to know the 10% bit I want to know the 10% in addition to the whole so to do that again there would be different ways of doing it but the way I think of it is it's 110% of whatever it is I want the 10% increase on so 110% of that figure is going to give me a 10% increase on that figure so you can see visually that yes that does look correct and I can

5:07just repeat that for the other ones there and again talking about percentages just take yourself to this conversion rates sheet you can see here let's say these are product trials and how many people actually purchase the product I want to know what is the conversion rate well it's going to be the number of conversions out of which is I could divide by out of the total press enter and then I format that as a percentage so number formatting remember to give me for example in this case a 13% conversion rate and I can just repeat that all the way down so that was just a quick reminder about percentages but there's one more mathematical operator that we haven't talked about and that's to the power of you know squared or cubed or to the power of 3 4 5 6 7 8 9 10 whatever I think the proper term is exponentiation but anyway if you take yourself to the manufacturing calculations sheet let's imagine we're producing some kind of widgets and they are spherical so the little balls so we want to work out the volume of these widgets that we produce and we produce our widgets in one two three four different sizes and we know the radius of the ball so we want to work out the the volume of our different widgets as per their radius measurement and I have given you in the skill the formula that we need to use in order to

6:25calculate the volume of a sphere so you can see that it's 4 divided by 3 times by pi which is 3.14 times by the radius to the power of 3 so that's a calculation we can type into Excel using the mathematical operators so let's give it a go so equals 4 divided by 3 times by pi now we could use a function for pi but I'm just going to type in 3.14 as an acceptable approximation for us today multiplied by the radius which is in that cell there to the power of 3 and it's the to the power of bits that we haven't done yet so the way we type that in is with the little carrot sign so on my keyboard it's a shift 6 to give me the little hat so that's to the power of 3 so if I press enter that's going to give me the volume of our widgets that's got a 2 centimeter or a 2 inch radius whatever the units are that's the volume so let me repeat that down there we go and just to finish off this spreadsheet you can see that we've got a current price per unit of volume so let's say this is our manufacturing price so we're saying it's the volume the number of units of volume there multiplied by our current price per unit of volume remembering that we need to fix that cell absolutely do you remember we talked about absolute cell referencing and I can

7:42give you a keyboard shortcut here so it's your F4 function key that will pop in the little dollar signs you need wrapped around the cell reference in order to fix that cell reference in our formula let's just input that and fill it down perfect

0:00In this video I would like us to pick up on a couple of other quick and easy functions that you may or may not have yet come across. And you already know about the SUM function. You'll see the SUM button, the auto SUM button up here. And if you click the drop down that's a bit of a clue, a sneak preview as to some of the ones we're going to look at in this video. And you are very used to using the SUM function to add up a load of figures that are above the cell where you want the total or to the left of where you want the total to appear. But you can use that SUM function to actually see a total somewhere else on the spreadsheet if that makes sense. So looking at this information here we've got loads of data and this little yellow shaded box is kind of like a summary. So if I did want to see the total sales to date then yeah I'm going to use a SUM function but I'm summing that lot there. Nice and easy. But these are the ones you may or may not have spotted. So average age that's going to be an average function. Youngest age that's a min function min for minimum.

0:56Longest length of service that's a maximum value. So max and number of sales people that's going to be some kind of count function. So let's try them. So we could either pick them from the drop down here. So average and you can see it's typed in the word average and then it's guessing that I want to average the well that doesn't make any sense. But while this is still shaded I can click and drag to override what it is I want it to work out the average of. Well it's the average age. Thank you very much. So if I choose that. So yes you can pick some of these functions from the drop down here or you could just type them. And they 're not I mean they're not difficult to type. So youngest age on joining well that 's a min function. So if you type in equals min. Oops good if I typed right equals min and then open parentheses there we go. So equals min min of what? Well it's the youngest age on joining. So here's my age on joining values. So that's what I want to find the minimum of. Longest length of service. Well here are our length of service figures and the longest length of service well that's going to be a max you know short for maximum. Gosh my typing is really very poor today.

2:05So equals max open parentheses or open bracket and then choose the length of service figures there. Press enter. Great. But this next one is interesting the number of salespeople. Now we do have a count function. So let me type in equals count open bracket and let's just you know count the number of salespeople we've got here and I'm disappointed that I've gone a zero. Well you may or may not have noticed when you typed in the count function in fact any function you get these pop ups that literally pop up giving you as much help as XL can muster to help you choose the right function because there are hundreds literally hundreds and hundreds of functions in XL and there are a number of different counting functions. So straightforward count by itself as you can see from the little helper information here it counts the number of cells in the range that contain numbers. So because I asked it to count that range of cells there the first name well that was all text so it wasn't going to give me the value I was expecting. But if instead I choose count A that counts the number of cells in a range that are not empty so that's going to be more useful for me to count the number of cells people by pointing it at the list of names. So if I choose that then I can then click and drag over the names like I did before press enter and that's better I've got nine. The other count function worthy of note let's just

3:25quickly you know call up the helpers again is count blank so counts the number of empty cells in a specified range that can be useful for a variety of reasons but I pointed out in particular because it is on the exam outline for the Microsoft XL exam that accompanies this course. But we've had a go at some other functions there but noticing if you were to select a group of cells let's select these cells here look down on your status bar you may well see some information like I've got here so I have selected the age on joining and it's showing me what the averages of that selection what the count is so how many values what the numerical count is so how many numbers there are in the selection the minimum and the maximum value and the sum well if you don't have those figures showing here because I can't remember what's the default if you right click and just make sure there we go down here that all of these are checked well if you don't have to check them all but I've got them all checked that's why they're all showing there and that will mean you know sometimes you do just want to see at a glance here what is the average age well you may or may not want it displayed permanently on your spreadsheet you may just want to know so I've just selected those cells I can see oh yeah the average age is 48 or you know how many how many values are there in that selection ah there we go the count there is nine so that can be very useful as well

0:00Using named ranges and structured references in our formulas can make them much more readable and you will see what I mean by that when we try it. Let's start with named ranges and we have already looked at this before when we talked about ways of navigating a spreadsheet. So in this particular file we do have a named range, so over on the left hand side here on the name box I've just clicked the drop down, so we've got a named range called Joining Dates, which is that collection of cells there. And as I said we talked about it in the context of helping you to navigate a spreadsheet because it means you can easily hop to important groups of cells that you have named.

0:37But in terms of referencing them in a formula, it means that the formula can reference the range called Joining Dates rather than the range F2 to F10, which is how Excel will normally reference that range in the absence of a name that we've given it. Now I feel like I'm making this much more complicated than it needs to be, so let's just crack on and name this range, let's name that range there current age and let's see how we can use that in a formula. So current age, I've just typed in the name box that I want to call that particular range, that means it should be available, there we go, so current age that's a new range.

1:12So now for this average age formula here, rather than averaging E2 to E10, well let's just get rid of that and type in the average formula again, and I actually want to use the named range, so it's going to be current age, and I start typing the name of that named range and it's popped up as a suggestion, so let me choose that. So average of current age, press enter, of course it gives me the same result because it's looking at the same group of cells, it's just that, if you look at that formula in the formula bar, that I think is much more readable than average E2 to E10, so average of the current age is using the named range rather than how Excel would refer to the range.

1:54Just while we are talking about named ranges, don't forget that you find your list of named ranges on the formulas ribbon tab here in the name manager, so there's the one that was already there in the workbook and that's the one that we just created so you can edit them or delete them or even create new ranges from within here as well. But the other thing I wanted to mention in this video is the idea of a structured reference, so I'm just going to dive onto the other sheet in the same file, so the performance data sheet, and what I'm going to do is convert this block of data here to a table, and just before I do that, just to remind you that the only named ranges we've got are current age, which we created just a second ago and joining dates which was already there, they're referencing cells on the other sheet, remember, but there's nothing else in the list is there.

2:39So what I'm going to do now is format this as a table, we know how to do that, so I think my, oh yes, that's one of my favorite formats let's choose that one. Yes, my table has headers, yes it's got the right block of data selected, click OK, so that is now formatted as a table and we know about that, but look, if I click the drop down here, I now have table one identified as, well, you know, it is just like a named range, it's a block of cells that Excel knows is table one. But it doesn't have to be called table one, because look on the table design ribbon tab, the table name there, well, I could change that, I could call it whatever I like, so I'm going to call it, let's call it performance data, something like that, and that does mean when I pick the drop down here, it knows that performance data is that block of cells, it's that table.

3:27So that's interesting to know, but the next thing I'm going to do to help us see how we can use what we call structured references, I'm going to add an extra column to this data because you can see I've got the sales value of the sales that have been made by various sales people, the percentage commission that each sales person's going to get whatever it is, but I want to add another column for the actual amount of commission, so commission amount. There we go, so let's just make that a bit bigger, you'll see that that's been incorporated into the table with the formatting and whatnot, which is another nice thing about tables, but I'm going to put in a formula that's going to work out actually how much commission gets paid, so it's going to be the percentage multiplied by the sales value, and look what happens when I do that.

4:09So 4%, so I've clicked on the cell and rather than Excel putting G2 in the formula, which is what you would expect. I've got this here, this at commission percentage, it's picked up the actual heading of the column, and again that's more meaningful than just a random G2 cell reference. So anyway, so commission percentage multiplied by the sales value, and again, can you see it's picked up the name of the column there, much more meaningful than F2.

4:39If I press enter, what you'll see, again, this is a lovely magical property of tables, it has assumed correctly that I do want that repeated all the way down the whole column, and it just works so nicely. I just love Excel tables, and the use of these column names in place of the usual cell references is what we call structured references, and it's yet another magical property of formatting as a table.

0:00We've already learned about the rand between function to generate random numerical data But just so that you've got all of the functions in one place and you know to recap We will just quickly do another example. So let's imagine we want to you know generate some dummy data for student schools or something So let's imagine down the side. We've got student one now Don't forget Excel is very good at continuing the pattern for you So let's imagine student one two three four five and then Assignment one and again, maybe we've got assignment one two three something like that So let's just make those columns a bit wider. So we need to populate this bunch of cells here with some example data So remember the rand between function asks us to whoops The rand between function. Let's just try that again now You'll notice that I selected all of the cells before I started typing because I want to put in a random number into all of those cells But what I was about to say is you will remember that the rand between function here It is requires to what we call arguments It needs to know well What's the bottom of the range that you want me to create a random number from and what's the top of the range and these two Arguments, you know the two inputs that it needs get separated by comma So for these student assignment scores I want a range of numbers between one and then a comma and a hundred

1:15So those are the two arguments that I'm populating there one and a hundred the top and the bottom of the range And because I want this to go into every cell I had selected Can you remember the keyboard shortcut we used to do that? Control enter will pop that into every cell Now if you click on each cell You'll see that sure enough there's that formula or the function rather in every cell And you might remember that this will update every time your spreadsheet changes So don't be distracted if you notice these numbers change as we continue But anyway, we've talked about rand between before but the other function for this video We have not seen before it's the sequence function I think it's relatively new to excel about you know 2021 I think we first saw it and it's designed to enable us to create a sequence of numbers And you might be thinking well, can't we just use you know the little fill handle like we did for student one two three four or assignment one two three to create a sequence Yes, we can the trick is though if you want to create a sequence of numbers using that method You have to do the first two steps of the sequence So if I want numbers one to ten as a sequence there Then I would have to do the first two steps there and of course that does work perfectly Well, and so you might now be thinking well, why do we need a function to do that?

2:25Well, there are a couple of reasons I mean, let's imagine you wanted a sequence of numbers going and I know all the way down to row 2090 or something you would be scrolling out of control by you know trying to do it that method So the actual sequence function would give you a much more controlled way of doing it But also the sequence function becomes useful when you use it in conjunction with other functions as you learn more about Excel and do more sophisticated examples But also in my opinion the sequence function is especially useful if you want to create a grid of numbers a grid of Sequential numbers, so that's the type of example we will build up to in a little sort of what we're going to create a little calendar But let's build up to that So let's just first of all try and create a sequence of numbers You know just a column of numbers going from one to ten using the sequence function So start typing the word sequence and then choose it from the list with the double click and this is where you get to see the Arguments that it needs, but there's only one compulsory argument these arguments in the square brackets are optional So let's imagine we just want to a column of numbers going from one to ten Well, that's actually ten rows that we're populating because that's going to give us a list of ten numbers in the same column You know using ten rows

3:41So if that's all I want one to ten in a column then that's all I need to do Done, but let's get rid of that and try some of the other arguments So now let's see imagine so again using the same function this time Let's imagine I want a single row of numbers But this time starting at the number five and going five ten fifteen twenty twenty five So increasing by five each time up to twenty five So this time I need to use all of these arguments It's only going to be a single row because I want the numbers to go horizontally How many columns do I need well five ten fifteen twenty twenty I need five columns Where do I want it to start? I want it to start at the number five and I want it to increase by five That's what the step is. Yeah, what's the step increase? I want so let's put in five again So that's a single row with five columns starting at five and increasing by five and that should if I press enter There we go five ten fifteen twenty twenty five perfect But as I said, I think this does become most useful when you want to fill a grid of sequential numbers So let's imagine we want to put together some kind of little calendar representation So Monday Tuesday Wednesday Thursday Friday Saturday Sunday so Excel knows the days of the week and let's imagine week one week

4:56Two and again, let's just get it to fill that up to week five So let's imagine for the month of August. Let's just pull that up there So for the month of August so August starts on Friday the first But I want to fill in the dates for August in this in a sequence But this time it's filling a grid rather than just a column or just a row So what I have to do is this so equals sequence Okay How many rows do I want?

5:26Well, I want five rows one for each week on my little grid here How many columns do I want? Well, that's the days of the week. So it's seven columns Where do I want it to start now? I have to be a bit strategic here because I want the number one to be in Friday the first Don't I because that's you know, that's the date of the first of August So if that's going to be one I need that to be nought there for Thursday And we're going to get rid of these in a second So nought minus one minus two minus three So I'm going to go minus three as the starting point And I can actually leave the step value empty because the default step value is just one and that is an optional argument So I'm just going to close the bracket and press enter Fantastic. It's done. It's a yes Friday the first and then you might be thinking well these look a bit ugly Then you know, they do look a bit silly But if you were to try and delete them ah well You've actually deleted the whole function because if I undo that the way it works is we typed the function into the top left-hand corner of the grid or the array here and therefore it uses what we call spill that's the the proper term for Spilling the results over into the rows and the columns that you specified as part of the arguments in the original function You know, we told it how many rows and columns to fill So therefore the rest is automatically generated because of what we typed in

6:41originally So we can't delete that because it'll delete the whole lot But I'm a big fan of cheating so all I'm gonna do is make the text color white. There we go It's a really nasty cheat, but oh and that's also make Thursday the north white as well, but hey it works So that's the sequence function for you

0:00You can see that we're back in that sales team info file that we used earlier on in this skill. And I'd like to use this to try out a couple of examples of IF functions. Now, with an IF function, you are asking Excel to perform what is described as a logical test, and then return different values based on whether the outcome of that logical test is true or false. And I think that'll make more sense when we actually see an example. So let's imagine I'm just going to add an extra heading here for bonus with a question mark.

0:30So let's imagine we are going to pay our salespeople a bonus, but only if they have earned more than a million pounds worth of sales to date. So I want this column to say yes, if they have earned more than a million pounds in sales, so therefore yes they get a bonus, otherwise it'll display no. So we'll do this as our first example. So it's going to be an IF function, so equals IF. Let's just double click to pick it up from the list of suggestions there. As we know, it'll give us this helper to indicate what the arguments are. And the first is the logical test that I mentioned in the introduction.

1:02So we're asking Excel to perform this logical test. And the test is, is that cell there greater than a million? So a million is 1, 2, 3, 4, 5, 6, 0. So that's my logical test, is cell J2 greater than a million. So that's the first argument. So let's put the comma in to move on to the next argument. And now I'm indicating to Excel what I wanted to display in cell K2 here, IF that logical test is true. Well I want to display the word yes, they do get a bonus. And because it's text that I want to display, I have to put it in quotes.

1:35If it was just a number I wanted to display, I could just type the number, but as I say, text has to be in quotes. So that's the value of true argument. Let's type the comma to move on to the next argument and let's just make it say no if they don't get a bonus. So there we go. So if their sales to date is more than a million pounds, display the word yes, otherwise display the word no. So let me just enter in that formula. You can see for that first row that is correct because Bernie here has indeed earned more than a million pounds in sales to date. If I repeat that all the way down we should see, yeah, let's just check the nose, yeah, those are all less than a million good.

2:11If rather than just saying yes or no, we wanted to actually display the amount of bonus that they get, so this time we're saying, okay, if their sales to date is more than a million, pay them an extra thousand pounds, otherwise pay them 100 pounds. So this time it's going to be the same logical test, so equals if, and the logical test is, is that greater than a million? One, two, three, four, five, six. If so, display a value of what did I say, a thousand pounds, I've already forgot what I said. So we're going to pay them a thousand pound bonus if they have achieved more than a million pounds sales to date, if they haven't.

2:43So if the logical test comes out with a false outcome, they're only going to pay them 100 pounds. You get the idea, so again, close the brackets and just repeat that all the way down. Good. Let's just imagine though that this salesperson down here is so Vera, so you can see that she has just about got over a million pounds in sales to date. I'm going to change that so that it is exactly a million, so one, two, three, four, five, six. And you'll see therefore that her bonus has gone down to 100 pounds rather than a thousand pounds, because of course our test was if this value is greater than a million.

3:16And then we realize, actually our management has said if they've earned a million or more, they get the full bonus. So I'm just going to go back up to the top of the column and change this so that the logical test isn't just greater than, it's greater than or equals two. So that's how we would write greater than or equals two, let's just input that formula, repeat it all the way down and hope that Vera, yes, few, she's been credited with achieving the million pound sales to date, yes, she gets the full bonus. Let me just take you to this other sheet here, this performance data sheet that we have seen before and show you this column, this commission column, because that has already got an if statement in there.

3:55It's basically saying if the country sell, so if e2, but of course, because this is formatted as a table, it's using the structured reference. So if the country equals UK, so that's the logical test, if the country equals UK, and because we're using text there, we have to put it in quotes, a bit like the yes and no example we did a second ago. So if you're using text in an if function, you have to put it in quotes, so if the country field equals UK, so that's the test, if it does, then display 4% for the commission, otherwise display 2% for the commission amount.

4:28So for whatever reason, UK sales earn a bit more commission than other sales. And the reason why I point that out to you is that it's quite nice, we can see the structured reference in action there. If we were to go back to the file before we formatted it as a table there, you can see the difference in the commission if statement there. So it's if e2 equals UK, display 4%, otherwise display 2%. So with these examples that we've just looked at in this video, we are only working with two possible outcomes, aren't we? You know, is that logical test true or false? There are just two outcomes there .

5:01But we can get really clever and nest if statements within each other, and we can even have multiple logical tests. But looking at the skills measured for the Microsoft Excel exams, it appears that that more advanced fun stuff has been reserved for the Excel expert course . And so what we've just done in this video is all we need for this particular course, which accompanies the Excel specialist exam. So we're going to leave it there.

0:00In this video, we're going to look at the sort and unique functions. So make sure that you've got this workbook open to give you the same material to play with here. And the sort function at first glance doesn't seem that useful given that you already know how to sort a list, don't you? So I've got a little tiny list here and we do have the filter button switched on because this has been formatted as a table. So as you know, if I wanted to sort this list in, I don't know, reverse alphabetical order, then I could, you know, I could do exactly that. So when I show you what the sort function will do, as I said, at first it'll seem a bit redundant given that we can sort a list like this.

0:35But hang on in there. I'm going to show you another example as to where this sort function could be useful. So let's just take a slow to start with. So let's imagine we want to see a version of this list over here which is sorted alphabetically. So for that we could use the sort function. So equals sort. And then it says, well, what do you want me to sort? So the sort array, you need to point it to what you want to sort. That's any compulsory argument here. So I'm going to say, well, please sort that lot. And if I press enter, you'll see that the default sort order is it a little sort by the first column in ascending order.

1:08So it's just the same as having done a to z, you know, sorting it by the name of the item in an eight or z fashion. So that's exactly what we've got. But we do have other arguments as well. So if I go back to the sort function, and yes, I still want it to sort that lot . But if I press the comma to hop us on to the next argument here, the sort index is where I can identify which column I want to sort by. So let's imagine I want to sort by the price of the item. So that is going to be the number of the column of the array.

1:40So the default is it's just going to sort by the first column in the array. That's why the first time I did it, it just sorted it alphabetically by name. But if I specify a sort index by typing in a two, that means it's going to use the second column to do my sort. So it's effectively the same as clicking the drop down here and saying descending order by price. But I'm doing it through a function. So that's the sort index. And then the sort order, well, the default is ascending, but I want to make it descending. So that means it puts in a minus one. So one would be ascending, minus one would be descending.

2:12And then the last optional argument there is, am I sorting rows or columns? And the default is I'm sorting rows. So that's exactly what I am doing. I'm sorting rows of data here, so I don't need to put anything in for that. So I'm hoping if I put that in right, I should get a version of this list, which is sorted by price by the figure with the highest at the top. Let's see if that works. Yep, that looks like it has. So let's just confirm that by doing the same over here. So largest, a small list. Yeah, that is the same, isn't it? And as I say, you 're going to be thinking to yourself, well, why would I want to do that when I can so easily sort a list using the arrows?

2:46Well, hang on in there because I want us to now look at the unique function. And then we will see how we can use sort and unique together for, you know, for quite useful purposes. Okay. So the unique function, what that will do, it'll look at a list of data. So let's look at, I don't know, this particular set of data we've got here. Let's imagine I've got a little electronic lending library and I record the books that people download from my lending library. So in January, all of these are the downloads. I've got the genre of the download and the author. So I've got the same genre repeated many times in the list.

3:20So if I just want a short list of just the genres in my list, I can use the unique function to pull out the unique entries in that list. So let's try it. So it equals unique. So there we go. Returns the unique values from a range or array. So it says, well, what is it that you want me to look at the unique entries from? So if I click that, now you could click and drag to select the, you know, all of the cells in the column. But if it's quite a long column, when you click and drag, it might get carried away with you. So I'm going to press Ctrl Shift down arrow to select all the way to the end of the active cells there.

3:55And if I press Enter, you can see that it has just pulled out the unique entries there taken from that list. And here's the clever thing. If I would like the result of that unique function to be sorted, that's where we would combine unique with sort. So very often, the sort function that we just saw a second ago is most commonly useful for sorting the results of another function rather than just sorting a random list in Excel, which you could just, you could just as easily do using sorting and filtering that we already know about.

4:29So look at what I'm doing up here in the formula bar. I am wrapping literally the sort function around that little unique function that we had in there. I had to put an extra bracket on the end because the first bracket belonged to the unique function, but because I'm wrapping the sort function around it, then that needs its own pair of brackets. If I press Enter, you can now see that that little list of genres is sorted. It 's got an adventure at the top there, so it's sorted alphabetically. And the nice thing about this is because it's generated using a function if the list of genres changes.

5:00So let's say I look at this and think, "Oh, no, no, that wasn't a historical novel. That was an action novel. Now, action is not on our list of genres at the moment, but if I just change this to action, there we go, watch the blue shaded cells change. It's added action to the list, and it has sorted it into the right place." And this does become quite useful when you do have a great long list of data and you want to extract unique values and, as you can see here, sort them. So let's just practice that. Let's do the same again for authors because the author's names are repeated through here.

5:33Let's extract the unique author's names and have them sorted. So we're going to start with the sort, and then what are we sorting? Well, we 're sorting the unique values. So sort, unique. And then it's the authors this time press enter. Oh, this is where it has spotted that I haven't put the extra bracket on the ends. I'm going to say, "Yes, I will accept that correction." But there we go. That should have given us the list of all of the unique entries in the author column sorted alphabetically, and you can see that is the case.

6:03And don't forget at the end of this skill you will have another chance to practice an example of every single one of these new functions that we're learning.

0:00In this video, we're going to try out some of the functions that Excel has got for formatting text in cells. And I'm not going to lie, I was mildly disappointed that these functions were included on the list for the Excel specialist exam, because as we will see, there are other, easier ways of doing what these text functions do. And I personally would have prioritized different Excel functions above these ones, but never mind, it is what it is. So let's work through an example of each one on the list. Make sure that you're in the text functions spreadsheet that I have given you, and put yourself onto the part numbers tab here for the first couple that we will try, which will be left, right and mid.

0:39Now, let's imagine looking at these part numbers, we want to extract the first two characters from these part numbers. They're all letters, aren't they? The rest are numbers, but the first two characters are letters. So I need to extract the letters, the first two characters there, of these part numbers. So basically, I want to extract the first two characters counting from the left . So that's what the left function will do. It will enable us to say, with this function, please, look at that cell there. So that's the first argument is what we want it to look at. And then it says, how many characters from the left do you want me to extract?

1:11Well, I want the first two characters, please, from the left. So if I choose that, then you'll see, sure enough, it has pulled out OP, the first two characters there, and if I just fill that all the way down, yep, it's done it. So that's what the left function is. The right function is kind of similar, but it counts from the right hand side. So if I want to extract, for example, the last five characters, so in the case of the first one there, it will be everything except the letters. So it's the same cell that we're looking at there, but counting from the right hand side, please extract for me the last five characters.

1:42And you can see it has pulled out the number. But if I just fill that all the way down, of course, that doesn't really work too well with this particular example. If I was trying to extract just the numerical part of the part number, it hasn 't worked because not all of my part numbers here have got five characters. And I kind of hard coded into the function. They didn't either. I wanted to pull out the five characters from the right. So if what I was trying to do was to pull out the numbers, whether it's five numbers or four numbers, then perhaps I should have used a different function. And that's where mid can help me out.

2:14So what mid does, it returns a specific number of characters from a text string where you specify where to start the extraction. Let's try it. So if I type in equals mid, and again, where do you want me to look? It's saying, so it's that cell again I'm interested in. Where do you want me to start? Well, I want you to start from character three. That's where the numbers start in my part numbers because they've all got two letters at the beginning. I've already extracted those, but now I want to extract the numbers. So start the extraction at character position number three, please. And it's five characters that I want you to extract.

2:46Of course, if there are only four, it'll only find four. But if I choose that, then hopefully, yeah, good. That's done the job. So sometimes it's pulled out five characters. Sometimes it's pulled out four. But the point being it's done the extraction starting from the position of the third character. So these functions work perfectly well. And I did express my reluctance that these have been included in the Excel specialist exam because there are easier ways of doing exactly what we've done here. And you might already be guessing what the easier ways were. The easier ways would have been just to have used flash fill.

3:20Do you remember when we were talking about tidying up data, you could just start typing what do you wish the data looked like and Excel will carry on the pattern for you. So if I did want to extract the first couple of characters, well, I just started doing it manually and it gets the pattern. If I now want to extract the numbers, again, just do the first couple of steps of the sequence manually and then it'll spot the pattern and suggest that it could, you know, it could complete it for you. And that, of course, is much easier than messing around with left or right or mid functions.

3:51The next one on our list is Len. Now what Len does, it counts the number of characters in a particular cell. So if I, let's just do an example there. So if I type in equals Len, open bracket, well, that cell, please. And if I close the bracket, you can see it's counted that there are seven characters in that cell. So as you already know, some of our part numbers are seven characters long, some are six characters long. So that's what it's done. It's just counted the number of characters in the cell. And arguably, looking at that, it's not that useful. I mean, admittedly, Flash Fill could not have done that for us, but how useful is that?

4:24Well, let's think of a scenario where we might want to combine the number of characters with another function. So let's say in our organization, our part numbers look like this, and the ones that are seven characters long indicate that the product is stored with a distributor. If the part number is six characters long, it means it's stored in our warehouse. So actually, we could use an if function to tell us what the location of these parts are. So actually, let's add a little heading up here of location and make that bold because it's a heading.

4:57So what we're saying is if the part number is six characters long, then the product's in the warehouse, otherwise it's with a distributor. So I'm going to put in the if function, and the logical test is going to be the Len function. Wow. So we've got a function within a function here. So my logical test is the length of that, if that equals six, gosh, can't type, then the product is in our warehouse, and we know that with a if function, we have to use quotes for text-based parts of the function.

5:29Otherwise, so therefore, our seven character part numbers are stored with a distributor. Right. Make sure I get the right number of brackets here because we've got functions within functions, but I think that's looking good. So therefore, that first one, the locations with a distributor, so I'm hoping that some of them will show warehouse. Yeah, the shorter ones will show warehouse, the longer ones show distributor. So that's quite an interesting use therefore, yes, sometimes it is useful to be able to count the characters, and the chances are we'll be using the result of a Len function as part of another function like we did there.

6:03Okay, take yourself down to the next sheet along, the data sheet, and let's take a look at upper, lower and proper, the next three functions. So you can see I've got some data here. Oh, my goodness, it's a mess, isn't it? Maybe it's coming from some other system, and it's all a mixture of upper and lower case and whatnot. So if I want this data here to be presented all in upper case, all in block capitals, then that's where upper comes in. So if I type it upper of that, please close the bracket and then to fill that down, you'll see perfect. It's re-typed that whole list in upper case, but I could have used Flash Fill for this as well.

6:37And it's the same if actually I want it all in lower case, so lower of that, please, and fill it down. So you get in the idea here, and then proper, so there we go, proper would give us initial capitals, you know, capitalization for each word, which probably is actually what we might want for names. As I said though, Flash Fill would have done all of that for us without needing to use either upper or lower or proper functions. So more commonly, again, I think we're likely to use these types of functions wrapped around other functions.

7:09So for example, do you remember, yeah, it was this one here, wasn't it, where we had the unique function? It already had sort wrapped around it, but I could further wrap around the upper function. So let's say upper, and then put all of that within the brackets for the upper function, so that's going to put that whole little list in block capitals. Let's just go back to here, text functions, and then onto this last tab, this names tab, for our last two functions, Concat and TextJoin, which are all about joining cells together.

7:40So let's try the Concat one to start with to see what that does. So Concat, so Openbracket, Concat of what? Well, it's of those, please, and if I do that, you'll see it has literally glued the contents of those two cells together. That doesn't look that great though, because you'd normally have a space, wouldn't you, between the first name and surname, but you can see what it's done. It has literally glued together all into a single cell, the contents of the range that you specified in the function. So Concat does what it says on the tin, but for this particular example, I think the alternative is going to be better, and that is TextJoin, because what TextJoin will do, yes, it will join cell contents together, but you get to specify if you want to a delimiter, and a delimiter is the little character that you have as a separator.

8:27So, you know, if you talk about comma delimited values, it means that there's a delimiter, a comma, separating the values. So for us, let's specify a space as the separator, and because it's a character , we know we have to put that in quotes, don't we? So I'm just putting a little quote, and then hitting the space bar, then another quote, so that's my delimiter. The next argument is, do we want to ignore empty cells? I'm saying, yeah, okay, ignore empty cells, and then the next one, it says, well, where's the text you want me to join together, and it's going to be those cells there, so close the bracket.

8:59That's looking good, let's just repeat that all the way down. Excellent, it's done the job, but I think you know what I'm going to say, I could have done this again using FlashVille. If I wanted Bernie and Halton together in the same column, I just start typing that for the first one, so Bernie, Halton, and then Christoph Renau, and it would have done it. Nevertheless, concat and text join are different ways of joining together contents of cells into a single cell, and as I said, in the very next part of this skill, you get the opportunity to try an example of every single function that we have learned in this skill.

0:00My goal with this workbook here is to give you the opportunity to practice the functions that you need for the MO210 Excel specialist exam and also to give you something to refer to when you want to remind yourself what the functions do and what their arguments are. So looking down at the bottom you can see that there are four tabs. I've kind of grouped the functions together into these four tabs here so you need to work through all four tabs and on each tab what you'll see is a yellow box giving you the instructions those are the things I want you to do.

0:30You're going to be filling in functions into the yellow cells and then in the blue and it's the same for each of these sheets but in the blue I've given you a reminder of what the functions look like so if you flick across there you'll see that for some of them I've given you a little screen snip of the tip that pops up to show you what the arguments are. So have a go working through all of this you'll see that the text functions one well that's the meatiest one and there's even a challenge there that you may or may not feel strong enough to tackle but nevertheless give yourself a bit of time to have a go at working through as many of these functions as you can and then I will record another video working through the answers which you can watch once you've had a go yourself.

0:00Let's start on the sheet that I have labeled "quick functions". I think these should be the easiest ones for you to do. So let's put the totals into column F as per the first task in the peachy- colored box. Be a bit careful here though. If you had clicked the auto-sum button, it might have just picked up that cell immediately to the left there because there's a blank cell there for an unmarked assignment. So make sure that you do get it to include all three of the cells in that row. And then of course you can just fill that down. So the totals, hopefully that was easy. Let's fill in this little summary down here. So the average total score, so it equals average.

0:35And you'll notice in the reference, the way that I've written it in the blue box here, I've put in the equals, the actual function itself, and then the open bracket just to remind you that that's how you get it to pop up with the arguments that you need to put in . So as soon as you type the open bracket, then it knows you're ready to go. Anyway, it's the average of that lot. You should of course close the bracket or parentheses, I should say. But if you do just press enter, then it won't mind. Anyway, the next one along, the highest total score, that's a max function. So it's the max of that lot. And the next one is a min function. So the min of that lot.

1:11This time though, number of students. Well, we've got three different count functions. So the number of students, if I want to count their names, then I need to be sure to use the count A function because that'll count cells that aren't blank. So let's choose that. And then the number of scored assignments. Now that's just going to be a normal count because I do want to count where I've got numbers. And then the number of unscored assignments, well, that 's the other count function, which is count blank. So count blank, so odd to know how many blanks there are in that lot.

1:42So hopefully your figures matched mine. But anyway, on to the next sheet, it's rans between and sequence. So your task. Number one, to use the sequence function to generate a list of 20 sequential invoice numbers in column A here starting at five. So equals sequence, open bracket, well, how many rows? Well, it's 20 rows. I'm not worried about how many columns. So I'm just going to type a comma again to move me on to the next argument. You see the comma separates the argument, but if there's one you don't use, then just type the comma again to move you on to where we start because it says I need to start at five, and I don't need to put in the step because that's an optional argument and the default will be, it'll step by one. So that's exactly what we need. And then here, it says task number two, use rand between to generate some random numbers between 10 and 500 for the values in column B.

2:34So it equals rand between, there it is. And the range was between 10 and what did I say, 500. Whoops. Yeah , there we go, 500. Good. And then I can just double click the fill handle to repeat that all the way down. Excellent. And then the next one, if sort and unique. So your task number one, use an if statement in column D to display yes if the rating is less than three, and no, if it is not. So it equals if open bracket. So I need the test. Now the test is, is that less than three? And if it is, I want to display yes. And if it isn't, I want to display no. So let's just pop that in and again, just repeat that all the way down.

3:23So we can see, yeah, there we go, the low scores. There we go. Do require some further action for feedback. Good. And then in cell F five, since this is task number two here, combine the sort and the unique functions to produce, whoops, a little typo there. Let me just change that to produce a list of sorted topics in the yellow box. Gosh, another typo. Let me just get that. So hopefully by the time you get to see this file, you won't have those typos. So I want something in there using sort and unique. And there's also a hint that there is a range called topics.

3:56So let's just check out what that range is, a named range. Yeah, it's got those cells already selected in the named range. So that should make it easier for me to create this function. So I'm going to start with the sort. Now, what am I sorting? Well, I'm sorting the unique values in that named range . So topics, there it is. Close the bracket and I need another pair of brackets. There we go. Yep, brilliant. I just stuck with the default sort, which is alphabetically, that looks perfect . Ont the text functions, the next cheat along. Oh, this is quite meaty. So task number one.

4:30Use the right function to extract just the rating from column D here to display in column E. So the right function, not forgetting that I do have that reference for you with all of the functions, but the right function, so it's counting from the right hand side. So I want it to look in that cell and just count one character in from the right hand side, please. Good. Let's just fill that all the way down. Task number two says use the left function to extract just the employee number from column A and put it into column F.

5:01So equals left. So, yep, there it is. Look at that cell, please, and count the first, what the first four characters, I guess, to extract just the employee number. Good. Let's repeat that all the way down. Good. Task number three here says use the mid function to extract the employee's last name from column A and put it into column B. So mid means that we specify the point at which we want it to start counting. So that's the cell to look at, please, and start counting from, I suppose, the fifth character for how many characters.

5:35Well, I don't know. I'm going to say 12 characters. That should be enough to capture everybody's surname. Everybody's last name. So let's just repeat that all the way down. Good. That looks good. And then number five here. Nope. Number four here. Experiment with the concat and text join functions to display the employee's first name. And last name in a single cell in column G using the data from columns B and C. Right. So concat. So that's one of the ways we could use to join together different cells. And this is going to be interesting because normally with concat, so that one and that one, it doesn't give you a gap. It doesn't give you a space between the cells that you're gluing together. But happily for us on this occasion, because when we did the split to split out that surname, it included the space because we did the split from the fifth character onwards.

6:24And the fifth character is actually a space. If we had done the split from the sixth character onwards, i.e. at the very beginning of the surname, we wouldn't have this bonus space. Concat wouldn't normally give us a space. So instead, we would normally in that situation want to use text join because that does give us the ability to add our choice of delimiter, our choice of space. So let's just do it properly. So the delimiter, the separator, if you like, is going to be a space which I put into quotes. Yes, I'm happy just to accept the default. I would ignore empty cells. It's that and that please that I want to join together with a space.

7:01And that's done it with a proper space this time. And then task number five says, reminds us to think about, do we want to use things like upper or lower or proper to convert how that's appeared? So I'm just wrapping the upper function around what we did with the text join to make that appear in block capitals. And then I can repeat that all the way down. Good. And then finally, your last task here says, people with a four digit employee number, which is most people here, are described as being full time employees, FTEs. And those with a three digit employee number, like Klaus here, are described as being full time contractors, FTCs.

7:41So saying therefore in column H, it's saying use the LEN function to identify who are FTEs and who are FTCs. So basically all we're saying is here, we want the number four to appear, which is our indicator that that person is an FTE. But if it's chosen three, that's our indicator that they are an FTC, a full time contractor. So the LEN function you will remember does a bit of counting for us. So it's going to return the number of characters in a text string. So it's saying, take a look at that and count the number of characters.

8:11And sure enough, for this first one, it's giving us four. It's done the count exactly right. Perfect. So let's repeat that for the whole column. And this is where you will have spotted, if you gave this a go, that it doesn't seem to have worked. You know, this is a three digit employee number. Why is it showing us four? And it's the same for all of these that are three digit employee numbers. It's still showing us four. And I don't know whether you managed to spot why that's the case, but let me tell you. So do you remember when we use the left function here in the employee number column, we use the left function to extract the employee number from column A, didn't we?

8:47And we asked that left function to look at column A and extract the first four characters. So for the four digit employee number, yeah, it extracted the first four numbers, perfect. But for the three digit employee numbers, it still took the first four characters. The first three were numbers and the fourth character was the space. Oh. So actually, all of these three digit employee numbers, they looked like three digits, but that extra space was also included. So when the then function did the count, it counted four characters. There are four characters there.

9:20So the then function got it exactly right, but it didn't quite do what we hoped . Well done. Make sure you save this workbook with all the examples so that you can use it as a resource to help you revise these functions if you are preparing for the Excel specialist exam. I hope this has been informative for you, and I'd like to thank you for viewing .