Functional Excel Skills | K3A | Skillshare

Functional Excel Skills

K3A, K3A

Functional Excel Skills

K3A, K3A

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
22 Lessons (2h 18m)
    • 1. Intro

      1:02
    • 2. Lesson 1 Keyboard Shortcuts

      7:07
    • 3. Lesson 2 Before We Look At Formulas

      4:23
    • 4. Lesson 3 Formulas

      12:55
    • 5. Lesson 4 VLookup

      5:00
    • 6. Lesson 5 Delimiters

      2:43
    • 7. Lesson 6 Data Validation

      7:34
    • 8. Lesson 7 Filling Down & Conditional Formatting

      12:13
    • 9. Lesson 8 Source Sheet

      5:02
    • 10. Lesson 9 Data Setup

      11:54
    • 11. Lesson 10 VLookup Continued

      9:09
    • 12. Lesson 11 Absolute Referencing In Tables

      2:27
    • 13. Lesson 12 XLookup

      4:39
    • 14. Lesson 13 Data Extraction with the Find Function

      6:52
    • 15. Lesson 14 Nested IF Statements

      5:03
    • 16. Lesson 15 The OR Function

      1:54
    • 17. Lesson 16 The AND Function

      1:34
    • 18. Lesson 17 Putting Our Logic Together

      5:56
    • 19. Viewer Request 1 - Pivot Tables

      12:54
    • 20. Viewer Request 2 - Multi Column Comparison

      9:32
    • 21. Bonus Video - Filling in Missing Table Data

      4:06
    • 22. Bonus Video - Inserting Rows Into a Data Set

      4:18
  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.

608

Students

--

Projects

About This Class

This class is for people who use Excel everyday. It is aimed towards users who want to learn a few more functional skills

We will cover:

- Keyboard shortcuts

- Absolute and Relative referencing, including when using Tables

- Some useful formulas including VLookup and the new XLookup

- Delimiters

- Data Validation

- Conditional Formatting

- Dealing with raw data

- Inserting Tables

- Inserting Slicers

- Nested IF Statements

- OR Statements

- AND Statements

Bonus Videos:

- Using Logic to fill in missing values

- Inserting rows beneath alternating rows

Viewer request:

- Pivot Tables

- Multi Column Comparison

New videos are uploaded occasionally, so please keep an eye out for updated content.

Please let me know if you have any requests about a particular feature of Excel you would like to know more about.

Download Resources Here.

Meet Your Teacher

Teacher Profile Image

K3A

K3A

Teacher

Welcome to K3A! I hope you find something of value while you are here. Currently my "Functional Excel Skills for Beginners" and "Microsoft 365 Online - Getting Setup" classes are available to watch.

 

See full profile

Class Ratings

Expectations Met?
  • Exceeded!
    0%
  • Yes
    0%
  • Somewhat
    0%
  • Not really
    0%
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Your creative journey starts here.

  • Unlimited access to every class
  • Supportive online creative community
  • Learn offline with Skillshare’s app

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.

phone

Transcripts

1. Intro: welcome to this applicable Excel skills course for Guinness. This course is aimed at anyone who is working in an admin type role that works with Excel on a daily basis. But it was only ever used the application on a surface level. The things so it's in this course will help you take your Excel skills just a little bit further and help improve your speed and accuracy. The course will be broken down into different sections, study with keyboard shortcuts, going through some useful formulas and finishing up with the processing of some raw data to be turned into something usable through other course. You can follow along with the videos by downloading the workbook were working in from the resource area. Or you can visit my website, which you confined in in the course description and download from there. Feel free to ask me any questions in the community section off the course at I will try and answer your questions as soon as possible. I will see you in the next video 2. Lesson 1 Keyboard Shortcuts: way right. Welcome back to applicable Excel skills for beginners. In this lesson, we will be looking at some basic keyboard shortcuts that will help increase your speed of use. Will be looking at the more popular and useful shortcuts, but they are far more that can be found. There's a dominant section of my website. You can find a full list off Excel shortcuts. You can find the website link in the course description. The 1st 1 will look at is movement. This is as simple as control and arrows, and it helps you move around the document much faster and helps you cut down on your scrolling time. So if we insert a few, content is intense themselves, and all we do is you started the first on on. We just control and arrow down, and it takes us to the bottom. Control on Dara across her takes us to the right control and arrow up, and it takes us up in control and narrow to the left, and it takes us to the left as simple as that. So we just clear this out. The next one we're going to look at is ah, control shift and arrows, and this is basically going to select everything in the path off the movement. So if we control shift and arrow down, its elects everything down to the last entry into NSO, and if we do the same thing across it, select across and up and it's select up. Simple is that the next one we are going to look at is inserting columns, rows and cells, and this is control shift on the plus key. If we enter in some text into Columns E and select the entire column, what we're going to do is control shift and plus, and you can see how it's moving that one across the document as I hit the plus key continuously. The reverse of that is control on the minus key, and now you can see the one is coming closer to where it was and it's gone equally. You can do the same thing. We put some data in two C 22 select the entirety of Road 22 if we hit the plus key now, the date another one is moving downwards. And if we do the same thing control and minus that's moving up again and it's gone. The next one is control B, which is very simple. Just control. Be bold text control be you take the bold text away the next one as simple control. I italics control I italics is gone. The next one is as simple It's just control you and we've now underlined the text and control you again on the underlying has gone very, very simple. The next one we're gonna look at is control and D, which is to copy down. So if we put in an entry that and we say control de it copies down Very, very simple. And this also links into the next one which is control are which is copy, right? And all it does is copy data across to the right. Very simple. The next one we're going to look at is redo. A lot of people know about undue which is controlled said. But not a lot of people know about Redux, which is control. Why So if I use my previous short cut, which is control are to copy, right? We now have the text from 11 in B 11. But if I want to undo that, I then do control Zed and it takes me to there, which is which is undue. But if I decide I want that back, I control why and it comes back, right? And then obviously the next one we've really looked at, which is undo. And it's simply just under does what you've you've just done. The next one is select all which is control A. This has two different levels. The 1st 1 you select the the current region and then the next time you press it, it will select the entire document. The next one we're going to look at is inserting of a table which is controlled t and this you can use with the previous one. You can say control A which selects the entire region. And then you say control tea and it will bring up the create table box. And if we say okay, it'll then insert a table into where our data waas. But obviously we don't want to table s, so we're going to undo that. So this one, it controlled K, which is inserting of a hyperlink. Essentially, it is text that is over laid with a link that will take you to a another part of a document . It will also take you to a website, or it can take you to an email. Oh, you can insert an email into it. So what we're going to say is, control OK, and it's going to bring up the hyperlink box. So from here, you can say, Choose an existing file or Web page. You can. You can choose a place in the document so you would select your worksheet. And then in your worksheet, you would choose what cell you want it to go to when the hyperlink is collect. Or you can choose an email and you can insert an email address on a subject. And then when your email pops up, it'll pop up with that email address on that subject Already included. Next we're going to look at is control, shift and end. This is not so much for use in Excel itself, but for usage in Windows when you're when you're creating files, so basically, control shift in and just creates a new file in the Windows File directory. Very simple. The next one will look at is control and Space bar on this. In this select an entire column So obviously this could be used with the previous one that we spoke about, which is inserting and deleting of columns. So from here I can insert a column and I can delete the color and the next one. We look at this shift in space ball, which insert an entire row. And again we can insert Rose from here and delete Rose. From here on, the final one will look at is F two, which is simply just entry into a cell so we can now start to edit. Okay, so it's actually a shortcut are just useful so that you don't have to keep going backwards and forwards to your to your mouse. You can stay on your keyboard for longer, and it'll help to speed up the way you are doing things. All right, we shall see you in the next video where we are going to start looking at some basic formulas. I I shall see that 3. Lesson 2 Before We Look At Formulas: way. Welcome back to a quick video. Just before we get into formulas, we quickly need to cover cell referencing. Cell referencing allows us to control how formulas behave when we move them around a sheikh . So a definition for you. A cell reference refers to a cell or a range of cells on a worksheet and can be used in a formula so that Mark Soft Office Excel can find the values or the data that you want that formula to calculate. In one or several formulas, you can use a cell reference to refer to data contained in different areas off a worksheet . The way that we introduce cell referencing to a formula is with the key F for their four types of cell referencing that we that we're going to look at it's relative referencing. Absolute column. Absolute row, Andi, Absolute referencing. The 1st 1 we're going to look at is relative referencing on. All we're gonna do is we're gonna say equals D eight and enter, and it's going to give us the word relative Now. If we continue to drag this down, we are going to get equals d eight. And if we drag this across. We are going to get Test one and a blank, which was zero. We can see why this is happening. If we look into the formula itself, you can see the 1st 1 obviously is D eight, because that's what we typed in. The 2nd 1 is D nine. The 3rd 1 is E eight, and the last one is E nine. Now, as you can see, as we've dragged this formula around the sheet, it's changed where the formula is looking as well. Now, if we want to keep the formula from doing that, we need to use absolute referencing, which is which is what we're going to look at now. So if we say equals F eight, okay, which, as you can see, is referencing on this cell appear we're going to say F four, and it's going to give absolute referencing with the dollar signs, and we're going to say F four again, which is going to be a row reference on. We're going to say four again, which is a column reference. It only refers to column F we say Answer. It says absolute coal. If we dragged us down, it gives us equals dollar sign F eight, which is exactly what this did. However, if we drag this across, it doesn't change the column that the formula is looking at its still looking at F eight and this is still looking at F nine. The next one is absolute row referencing, which has a dollar sign in front of the row number. So we equals h eight and we hit F four F four. We get the absolute referencing for Rose and we say Enter, It's given us absolute rope. If we drag this down, the formula will then stay the same because the row has not changed. If we drag this across, however, it changes the column, but the row remains the same. The final one and I'm sure you can see where this is going is absolute referencing. If we say equals J eight and we hit F for once, we get absolute referencing as you can see by the dollar signs and we hit enter And if we drag this down, it stays the same and we dragged these across and it stays the same. It has not changed at all. The formula, regardless of which one we look at, is the same. Okay, so that is relative and absolute referencing. Very important thing to know is you start dealing with more and more formulas, which we will, which we will look at in the next lesson. I shall see you there. 4. Lesson 3 Formulas: Welcome back on in this lesson, we will be looking at some useful formulas on the potential scenario in which he would use the formula. You can practice the formula as we go through the document. You can obviously download this document from the course materials area. OK, so the way this is gonna work is I've got the information down the side. I've got the formula typed out in the middle and you can then practice the formula on the side here. So the 1st 1st formula we are going to look at is the proper formula which essentially takes the text that we are looking at on turning it into proper text. What that means is the first letter of each word in the string of text will be capitalized . So if we say equals and we go P R o p, you can see that the intelligence here gives us the options based on what we've typed in, and you can carry on typing. Or you can press Tab Onda. We can then choose the cell that we want to look at, which in this case is be to, and we are going to close the brackets on when we hit Enter It has capitalized the first letter off the first letter of each of these names, but the rest is in lower case. So the next one is upper, which is pretty self explanatory. That's upper case and if we say equals u pp On we hit tab, we will click on B three and close our brackets and hit Enter and it has made the text in that cell entirely capitalized. Next one we are going to look at is lower case. We can say equals L O W On we hit tab on, we are going to click on a four closer brackets on the entirety of it is in lower case This next one can be done in a few different ways. The first way to do it is to type out the entire formula, which is con captain eight Incan Cassin eight is the joining off two strings of text together. So the 1st 1 we're going to look at is the formula version. So we say equal C o N c eight on Come, Captain, it is that we hit tub and now this is going to say text one which will be my email on text to create the comma, and it's gonna ask us for text to, and you can either do this by selecting a another cell, or what we're going to do in this case is we're going to open the inverted comments and we are going to put in an at symbol and we are going to say E mail docked. Come. And when we close the quotes and closed the brackets, then we hit Enter and we have my email at email dot com. When you're working with a formula, any text has to go into quotes, but numbers do not. So if we want to say 1234 and we hit enter, you can see it's con captain ated. 1234 However, if we tried the same thing with text, it will give us a name era. So bear that in mind. If you're using this method, any text has to go into quotes. OK, The next version is instead of using the Con Captain eight formula, we're just going to use what's called an ampersand, which is an and symbol. All we do is, we say, equals B six in this case on, we are going to put in an m percent and we open our quotes on we say at email dot com and we close our quotes and hit Enter and it's done exactly the same thing. But we didn't have to type in come catatonic so two different ways to do it. I personally use method number two, but you feel free to use which have a method you feel is easier. Okay, so the next thing we're going to look at is actually three formulas that can work together and they're part of the sort of same group off formulas. The 1st 1 we're going to use is right, then left and then made The right formula will start at the right hand side off the text and it will work its way. However many characters you decide along the string of text on pullout, only that text in this example we have some customer or ah, student information where we have the surname, the name on an I. D. Number off some sort if we know that all of our I D numbers in this case all have eight characters, obviously including the bracket That's nine we want to say We want to take out nine characters from this string of text so we will say equals right Open our brackets. You click on the cell that we want to look at comma and then the number of characters that we want, which in this case is nine. And we close our brackets on When we hit Enter, we are left with the I D on just a single bracket. The next one is left and what we're going to do here is we say equals left. And we know in this case that we have eight characters in our I d So we hit eight and we close our bracket Onda we hit enter and we've got a clean I d That is free of brackets Now how do we extract that? Because if we just we just copy and paste. This will copy and paste the entire formula. So we need to copy this and when we paste it, we want to right click on Do we want to choose paste values? So this little symbol with with a clipboard and 123 and when we paste that we have just a number and no actual formula. So that is how we then extract the information and pace that over the existing information . The next one is the mid function. Now. You would think that the mid function would be far easier because it's only one function to use. In our previous example, however, you need to know how many characters into the string of text you want to start. If we have surnames and names, the length of tax is always going to change. It's gonna fluctuate so we don't have a static. Starting point is not the best formula to use for our original example. However, we can use it if we've just got the I d with the brackets around it. So, for example, we say equals mid and we will open our brackets and we will select the cell we want to look at. And then we need to choose where we are going to start with start number. You can you can see the intelligence is telling us which number to start up. So we want to start at number two because we don't want the bracket and we are going to put in our comma on. Then we need to say the number of characters and we know our I d is a digits long and we close our bracket on. We hit enter and we get our clean i d. Without the brackets. Trim is a useful cleanup tool that you can use to clean up any data that you've received or clean up a spreadsheet that you found that you thought was put together really badly on. Basically, it allows you to take off any proceeding or trailing spaces off of the string of texture. If you if we look at B 10 we have a bunch off proceeding on trailing spaces on our string of text, and essentially, what room is going to do is cut that off. So all we do is, we say, equals T. R. I am open brackets and we put in now cell number that we want to look at close the brackets hit, enter and we now have a clean string of text. If we copy and paste values that over, we now have no spaces on either side off our data, whereas before obviously we had trailing spaces. The next one we are going to look at is a basic if statement an if statement is a logic test that you can ask Excel to perform for you, and it'll check whether a statement is true. And if it is true, then it produces a result. And if it is false than it produces another result. In this example, we're just going to ask Excel if B 11 is equal to test. If it is equal to test, then we wanted to return true. And if it is equal to false than we want to return false, very, very simple. If statement they can get very complicated. But in this first course, we're just going to look at very basic ones. So in this example, we're going to say equals if and we will start with the logical tests. And the logical test is if B 11 is equal to test, then if we insert our calmer value. If true and the value of truth is we want a true on the false is to return the value off false and we'll close our brackets and hit enter and we get true. You can see that this if statement has returned a true but the true looks very different to the other values that we returned its capitalized itself, and it's also centered itself. That's because what we've asked Excel to do is give us back a Boolean value in a 1,000,000,000 value in Excel is a true false value. If we then were to change the value off, be even slightly to se t s and we hit enter, it's automatically changed. False. Now we don't have to ask the if statement to return a Boolean value. We can also ask it to return other values. For example, we can ask it to return other cell values so we could ask it toe return b two If it's true and we can ask it to return b three, if it's true and if we hit, enter its returned B two, which is John Smith. And if we change this two ts and hit, enter it now returns Mark read. We can also ask you to return a determined value. We can say Yes, Onda, we can say no and we enter and it returns. Yes, when we change the value in B 11 and it returns No, the next one we look at is very simple just today and all that does is it returns today's date. We close that and it gives us today's date. Now, if we want, we can choose to change that by one, and it will give us yesterday's date and we can choose to change that to add one, and it will give us tomorrow's date. The next few can all be worked together, and we've got day, month and year now. What you can do is you can extract the day off a date. For example, if we are going to look at today's date, we can say Equal day, open our brackets, click on today close brackets and will give us 17 which is obviously today's date. We can also return the month, which is equals month, and click on today's date on close the brackets and we get the month that we're currently in. And then lastly, we've got the year. If we click on today's date and we close our brackets, we get our Year 2019. This is the now function very similar to today, but it also gives us the time as well. And now we say now, open bracket close brackets hit now and we get our time. The last formula will be looking at is a little bit more complicated than the previous ones . Eso We're gonna put that in a separate video so that we can explore that one in a little bit more depth s so I'll see you there. 5. Lesson 4 VLookup: way. Welcome back on in this video. We're going to look at the V. Look up. Formula V. Look up. Formula is a vertical look up, which is essentially a search formula. So what this is going to do is it's going to look at a table of content and it is going to take the variable that you give it on, look for it and then return any information that you want to retrieve. So in this example, we are going to be looking for Matt and we're going to be looking for Matt in this small table of contents. Here we were to do this, we say equals the look up. We can hit tab to auto filled. The first value we want is the look of value. And the look of value in this case is math. And we will hit comma. The table array is the array off data that we are going to give the V look up to use to find out variable. When we select our ray, we always want the first column to contain the variable that we're going to be giving the V look up, which in this case will be the name. We're going to start at the top and drag our selection down until we encompass everything. Now importantly, in this one, we're going to ask Excel to make this an absolute reference or we're gonna hit F four on the search criteria and we are going to hit F four on both of the start and endpoint for the array. The next thing we are going to tell the we look up to look at is the column index. And that is the column of information that we want the V look up to return to us upon search. So in this case, it is going to be column to the last piece of information is whether we want an approximate match or an exact match. In this case, we are just going to ask it for an exact match and we are going to close our brackets on. When we hit enter, we will get 38. And if we go and look for math in here, Matt, 38. If we were to change this information to Alex, we get 27 and if we change it to Jess, we get 28. And if we change, Tom. We get 36 on DSO on and so forth till the end of time so that I use a V look up. It's very simple where you can further this is if you were to put a list into the cell and then you can use a drop down list to go and find the person that you're looking for on return any other information. You can also have multiple columns. So if we were to choose de part ment fruit, insert another column here. Call it Department on. We are going to say English maths economics art is easiness. So if we want to return a another piece of information, we can copy this formula across and change the column index that we're looking at. And we're going to change this to three. But we also need to change where we're looking and it's going to be instead of I it's going to be J. We've now encompass the entire table. Arraigned. We hit Enter. We return English because Tom is in the English department on Guy must going to just change this to look at the same. And if we change this to Mike, we don't want Mike Scott. We want Mike and we returned. This Mike is 21 he is doing business on in the table. Mike is 21 he's doing business. It is as easy as that now. The problem with the look up is that when you have a large quantity of data, it can become very slow and cumbersome. So in future courses will maybe look at index and matching, which is a much better way of doing exactly what we've just done there. But if you are starting out and you want to add a little bit off functionality to your sheet, you can incorporate a V look up to help fine stuff a little bit quicker. One thing that I must say is that you cannot look backwards. You can only ask it to look for the name and then return the age. It does not work from right to left. It only works from left to right. Okay, So with that caveat in mind, going experiment with it on and I will see you in the next video where we will look at some more useful things to know 6. Lesson 5 Delimiters: Welcome back, everybody. Now that we have completed our section on formulas, we only have one more section to look at. And then we can start working with some raw data and putting everything we have learned into practice. In the next few lessons, we will look at the limiting data validation filling down on conditional formatting. The 1st 1 will be looking at is the limiters. This is the separation of cell content based on a character. In this example, we have a surname and the name separated by a comma. And we want to split the surname in name into two different cells at this comma. What we want to do is select all of the data that we're going to separate. Make sure that there is no information or data in the column next to the data we want to separate because it will override it. Once we have done the delimiting, then what we're going to do is we are going to go to data and we are going to click on text to columns in text to columns. You can see that there's two options will leave it with limited on. We will say next And in this we want a comma will antic tab. But we just want we want to separated by comma And then we hit next and you can see that the first column is being highlighted. Black on. There's a line where the comma is in the in our original data on we hit finish and now you can see we've got name one and surname, one in two different columns. The only thing with this is that it doesn't get rid of that space that we had between the comma and name one. So now what we need to do is used a trim function to get rid of that. So what we'll do is we'll just inserted come common there, you see equal shrimp. No, If I didn't spell, it's a equal trip open brackets, see, then enter on. And will that down control C and paced values over All right. And then we've got our information separated where the comma used to be. Very, very simple. You can also do it with a space. So if we wanted to get rid of the numbers from here, we could say text column and we could say the limit at this space and say next and finish. And then we've got a with numbers separated from name. So that is delimiting. Ah, I will see in the next video where we'll look at data validation. We'll see then. 7. Lesson 6 Data Validation: thing thing in this lesson, we're going to look at data validation considered a more complicated topic by some people. And I have actually seen job postings that considered data validation to be advanced. But don't be scared by that. It's not difficult in this lesson. We're going to look at whole numbers. Decimals, Leszcz, date time, tax length on. We're also going to look a input messages, error messages and checking invalid date or checking four invalid data. The place we want to look for out data validation is in the data tab. Andi, then over to the data tools. And we are looking at data validation. So we click here. We have data validation circle invalid data, which I imagine you can work out what that does on then, clear validation circles, which links to the 2nd 1 here. So you click on data validation, and then we've got three tabs at the top. We've got settings, input messages and error messages. We're going to start with settings and we have a bunch of drop downs here. Click on the drop down box. Obviously, any value is any value. Whole numbers. The 1st 1 we can decide if we want a whole number between not between equal to not equal to greater than less than greater than or equal to less than or equal to. So you've got loads of options to play with will just leave it with between. And we only want whole numbers between 10. Andi, let's say 15 and we said OK, we then try to put in a one, for example, and hit Enter. We get this error message saying this value doesn't match the data validation restrictions to find for this cell we hit OK or retry. Sorry, the option to import data again. If we say 11 we said OK, it allows us to import the later the next one is decimals. And if we go to data validation on, we click decimal once again, we've got all the options between not between etcetera on. We can say, for example, 0.1 Andi 0.9 and we said OK, and if we try to put in a one and one allows the dirt. But if we try to put in a 10.1, it will allow us to do it. 3rd 1 will look at is a less our list can be either a defined list, so we can say yes. Comma? No. For example, have we hit? OK, then we've got a Yes, no, last year. Yeah, we can also put in what's called a defined name range. Now we're gonna look at that in another video. But essentially all you would do is say, equals and then type in the less name and you would hit. OK, now that obviously doesn't exist. The moments that we won't do that but that's that's how you can put in a list into a cell. So we'll say. Okay. And now we've got we've got a Yes, no list. And if we try to put it anything else, it will give us the error message again. All right. The next one is a date. We will click on date and again, we've got all our options and we can say a date between the first off January 2000 and 18 on the first off January 2019. So you can only put dates between those two days. And if we tried to say 11 17 it won't let us do it. But if we try to put in one to 18. There we go. We've got the date. The next one is a time Onda. We will put in a stop time off. 9 a.m. That's when most people start work and we'll put in an end time off. 5 p.m. And we said OK, and if you now try to say eight, it's going to say no. If we say try nine, there we go. We've got a time of nine oclock. The last one will look at is text length, which is pretty self explanatory. You just say text length, and you can only have tax lang equal to eight digit, and now we can only have numbers or text up to eight digits. If we try one more, it'll tell us, and you can't have that data. Validation is important to ensure that data goes into your document the same way every time . If you have multiple people putting in data on a daily basis, sometimes that data goes in incorrectly, and it can mess up the way that you intended your sheet toe work. Now you can give people instructions on your sheet. For example, if we had a last year, you can go to your data validation tab again. Click on input message and you can say you can give your your instruction a title and you can say Please, please select a value from the drop down list. And if we say okay, now, we have instructions that whenever we click on that cell, you get the instructions there. Okay, the other one that you can use is the era alert. So you can say stop. You can say warning. You can say information that just changes the icon that they see. Sometimes the stop icon can be a little bit forceful. So maybe a warning message, for example, is a better option. And you can say in valid formatting these ensure that you input you data correctly, and then all we do is we said okay. And if you try and put in this era, alert allows people to continue with the incorrect input. If you definitely don't want people to import data into a cell in a certain way, then the stop message is a better option. If you've added data validation to a range of cells that already contained values and you want to see whether the values that exist already are consistent with the validation that you've added to that range. You conduce a check to see if the data is consistent so you can come to the data validation tab and you can select circle invalid data. And when you do that, you watch what happens to this cell that I've added a list too. So my less there's only yes or no. Why an end on the data that was there to begin with is invalid, as you can see. So you find then change this. Yes, it's now clear this circle. If I want to remove the data validation circles without changing any of the values, I can select clear validation circles instead. Right? So that's the end of data validation. It's not too difficult. Like I said, there are some more things that you can do with it, for example, with formulas. But we won't get into that just yet. In the last lesson for this section, we will look at filling down and conditional formatting. So I shall see you then 8. Lesson 7 Filling Down & Conditional Formatting: Theo way. With last lesson of this section will be looking at filling down and the sequences You can take advantage off in Excel, and we'll finish off with conditional formatting so essentially filling down, it's just a 1,000,000,000 information down a column to auto fill the cells below. You can also fill across a swell with a drag. But the nice thing about filling down is that you can double click in order to do it. So, for example, we have January Monday and a number one here that these are the examples we're gonna work with. And if we click on the month January and we hover over this little green square, we are going to double click and it falls down to June. Now you notice it stopped there because essentially what happens is it looks at the column to the left of it on and stops at the last entry. But we can carry on going if we drag it down further to December. And there we have our entire year filled out, and we can also do this by clearing out the rest of the month and just having the three. The 1st 3 letters and if we dragged this down to December, you can notice to the right of my cursor the month change as I drag up and down. If I leave it that we've got our entire year in three letter code, we can do the same thing with the days of the week. If we drag this down, you can notice that we're running through the days of the week off to the right outside that, and we stop it Sunday and we'll leave it there now if we keep going, it will just keep filling Monday shoes there Wednesday and so forth. So if we do this again and we clear out day and just have Monday and double click this, it's now also filled the three day code for the week as well. Now, when it comes to numbers, things get a little bit more complicated. If we just double click this, you see how it's just alter. Fooled one. Done the entire column. Now Excel doesn't always know what sequence of numbers you want to do, so I could do to. And if I full this down, we get 1 to 12. However, if I do three five and fill this down. I get 79 11 13 15. So now I have skipped out every even number in the sequence. And equally if I started to and go to four on again, I fill this down. I get 68 10 12 14 etcetera. So I've change the sequence of numbers that Excel is going to fill down. I can just do the 12 and it will fill down, but I can also do five, 10 ah, 10 20. And so on and so forth. You can do all kinds of different filling down. Now. Excel does actually have the option for you to create your own full down list so you can create a list of things that you use often. For example, if if you have a sheet where you've got car, motorbike boat, plane, train, etcetera, you can create that less than you can actually fill that list down as well. Now the way we can do that is by going to file and coming down to options and clicking on advanced on, then scrolling down to the general tab. And under General, there is a button here edit custom list. If we click that we have got out different lists and all we need to do, say, new list and add an item. So, for example, we can say boat we could say playing we could say Ah and train. And if we have that, we now have unless so if we said Okay, okay. And we sweet type in boat on drag this down. We see we get plane, car, train, boat plain, etcetera and full that down. We now have an auto filled list so you can put in your own custom lists off any information that you repeatedly use For example, ware, house names or regions or whatever it is that you're repeatedly using, you can you can add that to enlist, right? And I will look at conditional formatting. We're going to do a view so the 1st 1 is off value on the next one will be comparing dates . So conditional, formatting. All we need to do is come to the home tab and then we've got conditional formatting under styles. The only thing you need to be weary of when you're using conditional formatting is that it can be unstable if you're using it a lot If you use it excessively, you can end up slowing down your document and actually causing problems in terms off response times. When you're filling data in, you can also corrupt documents because they become too big to use. So be careful where if you if you've got thousands off rows of information, conditional formatting can cause problems in those documents, so just be aware of that. So what we want to do is go to conditional formatting. Now, you've got a few different options here. You can highlight cells, you can do top bottom rules. You can go data bars and you've got all these Grady and full, solid full. And you got a few more rules at the bottom. You can also go traffic light systems so you can have red, amber green. You can also do, um, icons, where you've got actual little traffic lights and arrows and all kinds of different things . Little ratings, loads of different things you can do with conditional formatting ones. We're gonna be interested in his new rules on manage rules. When we want is for Matt only cells that contain on DWI will say sell value. Obviously, you've got other options. He had dates occurring blank, specific text, arrows, no errors, no blanks, etcetera. And we will say equal to And we are going to say 10. And if a 10 we wanted to folk with green shoes that green, obviously you can choose all different colors. Um, on we are going to decide that the font is going to be bold on and yeah, I think we'll leave it at that. You can also add borders and do all kinds of stuff. But we'll leave it at that. We'll say OK, and we'll say OK, okay. And if we put in the number 10 we get a green highlight itself with bold text. Now you can't change this formatting on the cell itself. You can only do it through conditional formatting. So, for example, if I decided I want the cell to be yellow and I make it yellow, nothing's happened. So I decide that I want to text to be read. I can change the color of the text, but I can't stop it from being bold. Okay, If I decided in the conditional formatting that I want this if I want this text to be read , I can change their and Aiken force it to be read. But I unless I want to be black. So we'll say back. Okay, okay. On you see, now the text has reverted back to black. If I then change this to a nine, we now get the changes that I've made to the actual cell. But if you go back to 10 it changes back to the conditional formatting. So that's essentially what conditional formatting is doing its overriding every other change that you make to that cell based on information within the cell. Okay, so we'll clear that out and we'll return this back to know fill the next one that will do is based on a data. Now, there's a few different ways you can do this. You can do it based on a date occurring on. You can also do it on date between date before dates. After the one that we're going to look at is a little bit more interesting than that. It's date compared to today, so it's based on a formula we will click on. Use a formula to determine which sell two formats now say equals that cell is less than or equal to today. Remember, that's a day formula plus 30 days, which is roughly a month on the format that will go for is a full with red on and we will make it bold as well. And we'll say, OK, OK, okay. And we will say equals today. If you say equals today, you notice it's now going red on bold and that is because this is less than 30 days from today. However, if if we say equals today plus 31 we get a normal date and if we go back to 30 we get a red . Both text cell would you can use this for for examples. If you have a list of deadlines that you need to look at, you can. You can give yourself a certain amount of time and flagged that up and read whenever that deadline is happening and you can obviously you can. You can stack. These conditional formatting is on top of each other. For example, we can say manage rule and we'll add a new layer and will give exactly the same formula will say it equals this cell. This cell is greater than or equal to 60 0 sorry today, plus 60. And we'll format this in. Yes, I will form it in an orange and will say front is bold. Okay, Gate. And what we need to now do is stack the right one on top of each other. So well, move this one down. So the red one takes preference if that one is true. So what this will do is it will look at this. This formatting first, that one is false. Then it will go to the next one and to the next one to the next one. So we can put in another one yet and do exactly the same thing. And we can say equals if this cell is less than or equal to today. Plus 1 80 or 1 80 and we will make this Phil Green, that's it. Okay. And we'll put that one right at the bottom was okay. Now it's red. And if we go to 31 because yellow and if we go to 61 it goes green. So we've now stacked a traffic light system onto a date based on the date versus today. Okay, that's that's pretty straightforward. In the last lesson, we will look at some raw data on, we'll take some of these principles that we've been learning in these previous lessons and , well, put them into practice trying to make a spreadsheet. Okay, I will see you in the next lesson. 9. Lesson 8 Source Sheet: way said about data. We're going to look at setting up our source sheet. Source sheets are sheets you can use to store list on other em, for you may need to pull through to the main sheet. We can hide the source sheet and use formulas and dynamic named rangers to refer to it. So what we're gonna do if we come down to the bottom yet add a new sheet and we're gonna change our name off the sheet to source. The main reason for this sheet is to stall lists and other other information you need to use in other parts of the document. We are going to put in a less Yeah, we'll just call it item list on. We are going to say Item one and we are going to drag this down. That's dragged down that he wanted. We have 30 items, so now we have a list of items on. We are going to just differentiate the heading from the rest of the list on. We are going to go to formulas name, manager, and we are going to click on new Now. There's two ways that you can do this. You can either do a static range which basically is a defined range that cannot expand and cannot contract. You have to you have to manually do that in the name manager. And all we would do is we click yet and we were dragged down to the bottom of our list. Onda, we would say, OK, if we then click into where this refers to, you can see we've got our little barrier around our list and it's manual. It doesn't expand, it doesn't contract. If I clear out one of the items, the list will just see it as a blank. If we then use our data validation, We good data when we go to list and we say equal item lest so Okay, we've got a drop down box here and we can have We can see we have a well, all our items in the drop down. If we were to clear item 30 and come back to our list, we now have a blank at the bottom of our list. Okay, which is not ideal. We don't want to do that. If we decide we don't need any of those anymore and we come back to our list. We have all these blanks at the bottom of our of our drop down box, and that's that's obviously not ideal. So what we'll do is just delete that, okay, we're going to do instead is come back to formulas and name, manager and edit this, and we're going to use a formula. Now, when you first see this, it's a little bit complicated to get your head round. But it's actually not that difficult when you start to understand what is happening. Okay, so we say equals we're gonna use what's called the offset function, which starts at a certain point. And it offsets however many cells to the left or down or to the right. Oh, up that you tell it to do so. And for this we are going to start at a one you can also started eight to, but we're gonna start a one. We're then going to say comma, and we want the offset to start at 82 because that's where the first piece of information is in our list. So we will say, offset one road and we will say comma and we're going to offset by zero columns. Then it's going to ask us where we want the formula to stop looking. And for that, we're going to incorporate another formula called Count A. So count a goes down the list, and it just counts how many pieces of information are in that column? And for this, we're going to just say column A we're going to close our brackets, and now what we're gonna do is we're gonna say minus one, because you don't want to count the blank after the last piece of data we want to stop, but the last piece of data and we are going to say Comma, and we only want to count the first column. If I was to say to, It's going to look at column B as well as column A. But we only want we only want one column. We close our brackets and hit Enter. Now, if we click into it, you can see we now have our range. And if we then close this Andi, expand our range back to the wet Waas and come back to name manager. In clicking here, you can see this is looking at our entire range Now, not just those 1st 10 items that we looked at. So this is a dynamic named range. All right, So what we're going to do, then, is once we've got our list is we just come down to the bottom. We right click, and we say Hide tab. And that's how we set up a source sheet. And the next lesson will deal with this information here, and we'll set up a table that can be used to see the data. But more clearly, I will see you that. 10. Lesson 9 Data Setup: way. Welcome back in this lesson, we are going to set up out table. The scenario in this lesson is that we we have information that we've done loaded from a record system or information that we've been sent from a central point and we need to now format the data to do what we want with it. So we need to now separate name and surname on the I D. Number all into their separate columns. We also need to process these email addresses and get them standardized. And then we need to put in some lists criteria down the side as well. So what we're gonna do to start with his separate this information? Obviously we we don't want our surname name and I d number all in the same cell. It makes it more difficult to deal with and filter out information properly. So I will do. First is insert a couple columns here will then highlight all this information will go to data. We need to say text the columns. If you remember delimiting on in this, we will say Next on, we're going to delimit with a comma onda also a racket. Uh, and you can see in in this window. We've got surname one name, one on then the I. D. Number without the bracket. If we then say next on finish, we can see now this is all being split up into their different cells. We want to extract this bracket from the I. D. Number. Now, I could use a formula to do this, but what we're going to do instead is use a finer place function. We're going to say control and F, and it brings up the find and replace box We're going to go to replace and we're going to say, close bracket on, we're going to replace, or now you can see we've made 30 replacements on our numbers are nice and clean. That was pretty simple. The next thing we're going to do is we're gonna just trim the information that we've got you just in case because we don't know exactly how the information's come to us. That could be trailing spaces or leading spaces. So we'll just do this extra step. It doesn't take too long, and we just control see on paste over, and then we will just change this to B and copy this down control. See, Andi Paste values. And in the last one, we will change. Change that control C and paste over. Okay, so now we have all this information set up. Okay? The next thing we want to do is process our email addresses. So this is an organization. Everyone has the same email address ending. So we are just going to say equals on. We're gonna come, Captain eight This So we say E two and we are going to put out Absent on. We're going to say at email Ducked. Come. I was absent on hit. Enter and we can see now we have ah, process. Email address. If we copy that down, we're going to say control. See on paste this over and we can clear out that cell. So now we have all of our email address is done. Well, just get rid of this right now. What we're going to do is we're gonna put in some data validation on these last two columns of this one. We have a number of years that the person has been working for us, or if it's student information, the number of years that the student has been studying at the institution, so we can put in a whole number data validation on both of these and depending on what your information is, you can also decide if you want any sort of limit. So, for example, if you to university, you only want between one and say five years because anything more than that is is not necessary because it's only gonna be four or five years that the person is going to be studying there as an undergraduate, for example, So what, we're going to say data and we are going to say data validation and we're gonna put in hole number. And for this I am going to say it has to be less than five years. You said okay, if we now try and put in six, wouldn't let me do that. The next one we're going to do is just whole numbers for the age and we're going to just say hole number. We're gonna choose greater than 17 because that's that's when we can start on me. So okay, and if we try, go 16 doesn't understood. But obviously, anything more than that is fine. Okay, so we have a lot of headings. We have all our data set up. Where we're going to do next is put in a list box down the side of each one of these rows will put in item is the heading, and we will highlight this entire column on will put in data validation and we'll say, lest on we're going to say equals item list. Now it is a good idea when you are naming lists that you put less that the end of it so that you know what it is. So we said, OK, now we've got dropped on boxes and weaken. Decide what item? What we want to associate with every person. The next thing we're gonna do is say control eight, select everything we're gonna say control T on this is to create a table going to say OK, now we have a table. Well, just get diesel space that nicely. If we click on our table and come up to design, we can give our table and new name because we don't want it to be called table one, because we'll never know what it is. What will say students T b l full table. So now we have our information set up a bit better. We've got our list boxes here where we can decide what each person is associated with and what we can also do now if we want to be able to sift through this data. But better, obviously, with the table you get these. You get these filter tabs that you can use at the top of which you can. Also insert is what's called the slicer. I'll just put in, say, five rows and I will click on the table will go to design at the top, and we are going to click on insert slicer. Now. Slices are a quicker way off. Filtering out information in a table we're gonna use is the year, the age and the item. We don't want to go crazy with slices because otherwise the slicer will get too big, and it will be. It'll be pointless to use because you'll have to sift through it every time you want to use it. We'll say OK, and we have three slices. I'm just gonna set control eight and we'll bring them up to the top. The next thing we're going to do with them is if I position one of them off to the side here and press control A will select every slicer on the sheet or every every object on the sheet. We are going to say a line to the top and you see how everything's moved up, and we're then going to say a line and distribute horizontally now that'll space them out a bit better. Now what we're gonna do is drag this up to the top so that it's a bit smaller. The last thing we're going to do with the slicer is right. Click on it. We're going to go to size and properties on. We are going to come to position and layout and disabled, re sizing and moving. That'll prevent anybody from messing around the slice of too much. Andi. Before we carry on, I need to insert another couple rose. Just two. Improve the spacing. Now we've got our slices. We've got the year, we've got the age and we've got the items here. And when we click on one of these options, if you notice what happens to the table, it filters everything else out, and it also does his changes. The slices here to show you what other possibilities you have. So final click 18. It's filtered out, everybody that's 18. And then if we click at the top here, that will get rid of the filters. The other thing we can do is do a multi select on. We click on the Multi Select, and we can now choose what we want to get rid off rather than what we want to select. Okay, and then we can obviously undo that so that we don't We don't have that feature activated anymore. Okay, so that is some information set up. Very simple. It would basically just turned that data into something a little bit more usable. It's now a little bit easier toe work with as well. So you can imagine if you've got thousands of rows of information and you want to sift through commonly occurring information like the year or the item or the age you can use a slicer to sift through that information a bit better, right? One more thing we're going to do to this table before we finish up is putting a little bit of conditional formatting on. What I'm going to use is conditional formatting on the year. So I want to know if somebody is coming to the end their time at the institution where they're studying on for me, that's going to be in year four. Since the end of the time of the institution where we're going to do is click on this first cell here. We're gonna go to conditional formatting, a new rule. We're going to use a formula to determine which sell toe format. What we want to say is equal this cell. But you notice we've got an absolute reference on where this is going to be looking at, and we don't want that. We only wanted to look at the column, but we want this information to fill down to all the other cells in this column. So we're going to get rid of that dollar sign. And this is now absolute column reference, but not an absolute rule reference. And if that is equal to four, then I want to format it to the shade of green to inform the user that this entry is now in year four and they will be graduating. So we said OK, and we say okay, and now what? We're going to do is say, this applies to the entire range, but he's OK. And you can see now that everywhere there's a four, we have our conditional formatting. Now, the next thing we can do with this, if we come to the top here surname weaken, say, sort by color and we can get everybody up to the top who is graduating soon. I've if we have somebody down here and we change that to a four, we consult this again, and everyone in year four is now at the top. So now you know how you can not only sort but you can also sort by color as well. But we are going to get this back to a BC's, and that is our completed range. Okay, I hope you enjoyed that. I hope you learned something as well. If there is something else that I need to add in at the end of this, I will make a follow up video and posted at the end of this course so that we can cover everything that needs to be covered. But hopefully that will get you started on allow you to format your own data. Andi, She eats a bit better and make them easier to use and lock them down a little bit more so that there's less errors in the future. I will see you in future courses. Thank you. 11. Lesson 10 VLookup Continued: welcome back to functional Excel skills for beginners. In the last video, we wrap things up on the class, but I said I would post a new video if I thought of anything else s o in the in the V look up episode We looked at single v lookups, but I thought I'd come back and show you how we can put them together to pull multiple pieces of information from our data. So if we go back to our original document, the first thing we will do is reorganize the data. Remember that a V look up can only look left to right. So we need a unique piece of dates of each entry, and equally, we need some kind of searchable data that is, that is actually relevant to our database. Um so essentially, like a product code or a personal I. D. Number. Something like that, in this example will be using a unique i d for each entry that we can use to pull the other data points out, the way we'll do this is by moving the I DS to the first column on Once that's done, we can turn the column into a list and added to a drop down box on the interface page. And once we have that, we can link a few different cells to that list and have each cell referred to a different column in our database. So if we come back to our original page, you'll see that we've got our unique I DS in the third column, and obviously we want that to be in the first column. So if we highlight this column by clicking at the top twice and then we'll just move this over to the end Now, if we go again, you'll see the line of green changes from a box to a long line with a T shaped at the top. Now, the difference between these two is that that will move the data over, and it won't be part of the table anymore, and this will move the data within the table to that position so we'll drop it there on. Then we'll leave it highlighted will come to the name manager on. We will add a new list to how document and we'll call it i d list. It was okay, General, comes our interface Onda. We will add data validation to this cell has a list or get it to refer to idealist. No, we have. You see what I've done that I've accidentally added? They're heading as well. Obviously, we didn't want that. So clear this, and we'll come back to our name. Manager Andi will just get this to refer to that instead, we enter on. There we go. Okay. Crisis averted. Right. The now we'll do. Is we'll just change this color to, like Gray just to make it stand out of it s so we don't lose it. And then what we need to do now is out of the lookups linking this cell to the database so that we can pull out all the different information. Now the different columns that we've got. Ah, you see these? These six columns. So what I'm gonna do is I'm gonna just copy these out, and I'm going to paste them here, and I'm going to transpose them. Then what I'm gonna do is I'm going to remove the formatting from them, and obviously the text was in white will just take it back to automatic. And now we've got all six pieces of information. Will just get rid of the borders on. There we go. Right. So what we'll do is we're gonna get our we look up formula going. It's gonna be that value. Obviously, we want to keep this as an absolute reference. Then the array that we're going to deal with is this entire table. OK, so you can see it's the student table. Close that off and come back to our interface. Now, the surname is going to be in column two. So that is going to be that we're going to make it a an exact match and we'll close our brackets Onda and that is reference number one. Now we've got this N A. But if we go to our first piece of our first list entry, it will be surname one, and then surname 10 on then surname 12. And that's because of the way that this sort has worked. Obviously, if we clear this, we get not applicable. So what I'm gonna do is I'm gonna put in an F if an A and we'll wrap the formula within that, um, about we need and what it's going to return. If it's false, I will just put in a dash on. There we go. So if this is blank, then that'll give us a dash. Now I'm going to copy this down, and I'm just going to change the column that this refers to, so we will. Ah, for five. Six on 7 August. Test this out quick. There we go. So that is all the information from each column based on the I. D. Number. Now we can do a little bit more to this, and we can maybe change the coloring on it. It's think this darker blue on, then what we'll do is maybe former paint around here just to make it look pretty, uh, other than MK Birgit. But you get the idea. So you got your formulas there, Um, and based on this, we can retrieve different information from each person based on the I. D. Number. Now, you could also do with the email address if you want. If you really want to, you could do that as well. You could also have two different ones so you could have a list of email addresses on the I . D number, then could be added. So if you've only got the email address. So you've only got the i d. You could pull out the other information on that entry. All right? It is really simple to do. Um ah, I'll leave it there. If I think of anything else, obviously I'll come back. I am currently working on other courses. I'm gonna be working on a V B A course, which is a programming course within excel. If you're interested in that to keep an eye out, that's going, it's gonna becoming the next ah, month or so. It takes quite a long time to put everything together because obviously, you know, I've gotta work and stuff. If you'd like me to go over anything else in this course Lemina on, I can put the video together and upload it at the end of the course. And either you can contact me by going through this class, Andi leaving a comment and I'll I'll see it and ah, reply to it. And Andi, look at getting it together. Or if you want, you can go through my website on my website. There's also other downloadable resource is that you can play with M and you can see things that I worked on on. You can contact me through the contact page. Ah, the length of the website is in the description of this course on on my profile. Also, let me know if you want me to put another Excel course together. Currently, I don't have another Excel course in the pipeline. I'm going to switch now to VB a Rather. And there's a lot of projects that I'd like to do on vb A. I want to put together different classes that build different applications within V. B A, and I'll basically take you through that process and writing the code and putting it all together and showing you how it works. And you can use those projects straightaway in your work environment if it applies to something that you do that you're doing, so that's what I'll be focusing on in the future. But if you want me to put together another class specifically addressing something in excel , let me know and I will see if I can put that together for you as well. But yeah, keeping out for that. Thank you for watching. Um, I'll see you next time 12. Lesson 11 Absolute Referencing In Tables: Welcome back. We're not going to take a look at one of excels new features, which is called the X Look up now. You may have heard of this. It's bean coming for ah, for a while now, but it's finally here where at least I I finally have it. You might want to check and see if you have it. A swell available to you basically. V Look up, obviously is a vertical look up on an X look up allows you to look in multiple different directions. Obviously, the big floor with the look up is that you can only look left to right. Where is now with X. Look up. You can look right to left as well before we look at the X. Look up. One thing we do need to talk about his absolute referencing in a table. Now, Normally, when you use a column in the table, it'll give you the syntax off table and then the column Where if you then dragged that across to the side, it'll then move the column reference with it. But if we want to keep the column reference stationary, we need to re reference the column like I've done in this in this cell here. So you need to say column as Colon and then column again. And when you dragged the reference across, it'll keep. It'll keep the the column that you're referencing Another thing that we didn't actually touch on in the tables video is if we hit equals, what we can do now is reference the table. Open a square bracket reference our column close the square bracket hit, enter and it'll give us the entire column and all of its content. Now, if we drag this across, this is what I mean about referencing in, um in a table. So we drag this across that's now referenced itself to surname. We're If we move this across, that's not referenced itself to name. We get rid of this F two into this cell at another square bracket at the beginning. Semi colon close the square brackets open the square brackets idea again. Close the square brackets and hit Enter. If we drag this across, it's now kept the I d. Right. So just bear that in mind when you're when you're using Ah, table references in formulas in future 13. Lesson 12 XLookup: Welcome back, eso. Now that we understand absolute referencing in tables, we can then use that in our formulas in this video. So previously, if you wanted to look from right to left, you would need to use what's called an index and match because obviously, as I explained in the V look of video, you can't look from right to left with a V. Look up. You can only look from left to right now. Mark Soft has obviously worked out that people have been using index and match for, you know, the longest time and have decided to make it into a single formula. So this is what you would have to use in days gone by M. But now we don't need to do this anymore. We now have the X look up on X Look up is essentially the look up value which in this example is our I d number up top. Here it is the row in which another of the column in which you want to find that value, which in our case, is I d and you can see I've added absolute referencing to this column and then you want to add the column in which he was searching, which in our case is now is sunny. Now you can see that I haven't added absolute referencing. So when I dragged this formula across, it will change the value that it points to a the column that it points to now. There are a few other parameters to this formula that we can look at off is you can see that I've got a bit more information in this formula here. Now what this formula is doing is that it's saying after the return array, which is our i d field after the return array, if the value is not found than what text do you want to display and we want to display not found, you can change that to whatever you want, but we'll say not found. What type of match would you like? So would you like an exact match? An exact match or next smaller item? Would you like an exact match or the next larger item? Would you like a wildcard character matter? We're just going to simply say, zero for exact match. The next option that you have is do you want to search first? The last. Do you wanna search? Lost the first, etcetera, When we're just going to say one. And this is just gonna be first to last. Enter on and you'll see that the way that this is currently structured. If I look at my raw data, my table dates. At least the surname is in this in this column C and the ideas in column B. Now, with the look up, we wouldn't be able to search back like that. But you can see with the index and match we were able to do that. Onda with the X Look up, we were able to do that on. We were able to do that without needing to use to formulas or in this case, actually three formula. So the way that I've structured this, that's formula here is that it's an index. So you need the array, which is the student table. This is obviously referencing to another sheet, but it just puts these lines here, so just be aware of that. You need the row number, which is where the match comes in. So basically what it's going to do is it's going to match this value, which is in B to this idea is going to match it within the student table. I d column. So in the student table, it's going to look in the i D column. It's gonna look for that value, and it's going to return the road number. So if I just keep this by itself and we say Equals that it'll give me a row collar a row value of one. Okay, so what it's doing, like I say, is it's taking the ah, it's taking the array, and it's looking for that road. And then what I've done with the column is I've said column B, and this allows me to drag this formula across, and it will. It'll change like it's like it does with the with the extra cups. If I drag this across, this will change automatically to e. Now. I've basically used three formulas to achieve the same effect as this one formula. So that's quite a cool addition. Teoh to your bag of tricks. I hope you found value in that and I shall see you again. Have a good day 14. Lesson 13 Data Extraction with the Find Function: Welcome back, everyone on in this lesson, we're going to look at the find function. You'll be able to find this as a downloadable resource in the download section off the class, and you'll notice that there's three worksheets in this document. The find function. And then there's another to, ah, bonus videos that will also look at at the end of the class, so make sure you go ahead and check those out. So the find function is a function that looks at a particular character in a string of text , and it returns the number of which that character A sits in that string of text. So in our scenario, we have a name, surname and then in brackets. We have email address, and we want to remove the email address from the string of text and just output the email address. So how can we do this? We used to find function to look for our first bracket because that is our first ah consistent point in our piece of information, and this is going to give us back the number 18. So the way this works is you say, find the bracket in that so on the start character is Number one, which in this string of text is the end of name, and you'll see that it outputs the number 18. If you would account from 1 to 18 you would get the bracket at Position 18. The next formula to look at is the length formula, and this will output the length off characters within a string of text. So in our case, that's 35. The next step in this process is to say, the length of our string of text, which is 35 minus the position at which this character exists, which in our case is 18. And because we have another character on the other side of our string of text, we want to say minus one, because we only want to extract these characters and not this last bracket. So if we enter on that, we get the number 16 that we start putting this together, we can use the mid function, which we looked at in a previous lesson, and we're going to say med, the ah string of text that we want to look at, which is a one the we're going to hear and show you the intelligence, the number at which the extraction starts. So that's gonna be number 18 which is where we found our bracket. And we want to extract 16 characters from ourselves. So what we end up with is bracket email one at email dot code, which is not technically correct. What we need to do is in the next step, we need to add one to our character cause we actually don't want the bracket. We want the character next to the bracket, so we'll say mid a 1 1916 on that gives us an email address. And if we were to throw all of this together, we end up with this formula here. Now it might look a bit scary, but again can be broken down quite easily. All this is doing, is it saying mid and we basically wrapping this entire formula up in a mid function. We say mid house text is at a one. We want to get our start number, which is we find the bracket, which is going to give us the number 18. We add one to it because we don't actually want the bracket. We want the bracket, the character next to the brackets. So that's character 19. And then the number of characters is going to be this function here with the length minus Find Who's a length of a one minus the point in which we found the bracket, which again was 18. Remember, minus one. So this is going to say the number of characters, which is 35 minus the point at which we found the bracket, which was 18 minus one. So 35 minus 18 17 minus one is 16 and that's how we get out. 16. So we are extracting 16 characters from the string of text, and we end up with our email address from that string of tech. Let's write all this together so we say mid brackets how string of text, which is a one. Remember to make sure your references are correct, so we're gonna absolute reference the column and leave the road as a relative reference. Then we'll say the start number is to find the brackets right on the ah text within is going to be sell a nine on. We're going to reference that again. Then we are going to choose start number one Now. This is an optional character for an optional parameter. But we're going to we're gonna use it anyway. Close our brackets on that function. Then we're gonna say, plus one because, remember, we want to look at the character next to the bracket, not the bracket itself. So we need to go to character. Ah, 19 in this example. But it will dynamically change. Then we need to get the number of characters. Now the number of characters will be the length off the a string of text. And don't forget to absolutely reference the column again. So the length of the text minus where we find that string of text so is going to find in bracket and ah, this is going to be in a nine. We absolutely reference the column, being a close off the find function, and we're going to minus one because we want to get rid of that and lost brackets. And if we close that and hit answer, we end up with out clean email address, and if we double click to copy this down, you will see that it has dynamically extracted all of the pieces of information that we need without any issue. and we can actually go ahead and change this, for example, to John Smith. And if we enter, that's extracted John Smith as it should be, and it's dynamically expanded with the number of characters. So that's how we can use the find function, coupled with the mid function and the length function, to extract information from a string of tech. 15. Lesson 14 Nested IF Statements: Welcome back, everyone. And in this lesson, we're going to take a look at nested if statements you'll be able to find this downloadable resource in the resource is section off the class. So essentially, what a nested if statement is is one if statement within another, if statement. Now we did look at, if statements in basic detail earlier on in the class. But I thought it might be beneficial for you to see how nest that if statements work so that you can take this functionality forward a bit more and give some more functionality to your spreadsheets. So in the first example, we have a very basic if statement and all this, if statement is doing is it's same. If the value of D two is greater than or equal to 50 then give me the bullion value of True . And if it's not, then give me the bullion value of false okay. And if we come to D two and we change this to 50 you'll see the value changes to true on. We'll go back 30 Now all we're doing with the necessity of statement is we're asking exactly the same question. So we're asking exactly the same first question. However, you'll notice that instead of saying false, what we do is we open up another if statement straightaway. So now we're saying if the value of D two is greater than equal to 50 Okay, then give me true. However, if it's not, then we want to look at another condition. Okay, so then if this is not true, then if e two is greater than or equal to 50,000 then give me the value of truth. And if that's not true, then give me the value of false. You'll see that I've got a false value here. And if I come here and I change this to 50,000 and hit, enter this now changes to true But you'll notice that the 1st 1 has not changed because this condition is not present. So we take that back to what it waas and we change this to 50. You'll notice again that this changes to true because our first condition was met. Yes, So that's how that's our basic nested. If statement works, okay, so let's change that back in this next example, I've got a formula called ifs now ifs is very similar to having nested if statements, but it only asks you for a true value. So essentially, the way this works is we're going to say if D three is greater than or equal to 50 then give me true If e three is greater than or equal to 50,000 then give me true the reason I've chosen d three and E threes. Because if I change this to DE to and e to neither one of those is true. Okay, so neither one of these conditions is going to be true. And this IFs statement only asks for true values, so you'll see value of true logic test value of true logic test. It only asks you for true value. So if I enter on this because neither one of those conditions is going to be Matt, we get an n A. Now we can fix this with with a another formula called if n a right if n a asks us a simple question. So it says If this turns out to be an A, then give me another value so we'll do that and you'll see that it gives us that value. So, for example, in this, we can say give us false And now we have essentially the same sort of functionality as we did with a nested if statement. But instead we're using ifs wrapped inside oven. If any basically, if any, just looks at that particular error. So let's take this back to the weight Waas and you will notice that we go back to N A. Okay, so let's see this all in practice, we come in here and we say equals if the value off quantity within our table is greater than or equal to 50. Okay, that's greater than or equal to 50. Then we want this to be a priority. Okay, If it is not, then we want to shift where we're looking to right to the column off value and this is the same is going to be greater than or equal to 50,000. OK, and if that is true, then we want to return true. And if it's not true, then we want to return false. And then we close off our nested If statements we need to brackets because we need to close off the first if statement on the second bracket is so that we can close off the second if statement. And if we hit enter on this, you'll see that it filters down because it's a table and we've got all of our true false values in play. You'll see true, because although this is less than 50 this is greater than 50,000. You'll see false because both of these are less than our conditions. You'll see true here because although this is less than 50,000 this is greater than 50. And that's how a nested if statement works. 16. Lesson 15 The OR Function: welcome back, everyone. And in this lesson, we're going to look at the or function. So the or function is a logic statement that returns a true false value based on one of the conditions within the formula being true. So you'll see, in my example here I have got if de to is greater than or equal to 50 or if e two is greater than or equal to 50,000. And if one of those conditions is true, then I'll get a true value. And if neither one of those is true, then I'll get a false value and you can see in the example I've got a false value. But if I come to quantity and I change this to 50 we now get a true value because D two is now equal to 50 right? So if I change this back to 30 and I change this to, say, 60,000 again, this gives me true because the second half of this condition has been met. So essentially, that's all the oil function does is it turns a true false value based on multiple conditions. So if we put this into practice and we say equals or rice open brackets. That's going to say the first logic test is quantity is greater than or equal to 50. If we put in a comment, it's gonna ask us for a second logical test. And we're gonna say value is greater than or equal to 50,000 and we close our brackets and when we hit answer, that'll fill down. And we have false, true false etcetera. And you'll see that in this example, neither one of our conditions. This math. In the 2nd 1 the first condition is not met, but the second condition is met, etcetera. So that is how you use the or function. 17. Lesson 16 The AND Function: Welcome back, everyone. And in this lesson, we're going to look at the and function now similar to the or function. This is going to ask multiple conditions, and it's going to return back a Boolean value. So either a true false values. However, instead of the all function meeting one of the conditions to be true, the and function needs all of the logic statements within the formula to be true. So you'll see in our example, we have if d to is greater than or equal to 50 Andi if e two is greater than or equal to 50,000. So, for example, if I change this to 50 you'll notice that the false value has not changed. However, if I change this to 60,000 that's now changed to true. So that is essentially asking for both of those conditions to be met before it will change to truth. So let's see this happen. So we say, equals Andi. Open brackets on. We select quantity, and that's going to be if that quantity is greater than or equal to 50 right? Andi, if value is greater than or equal to 50,000 close our brackets and if we get aunt and fill everything down. You'll see now that for the most part, these are all false. But you'll see one of them here, 64 56,000. That's true. So that is how the and function works. 18. Lesson 17 Putting Our Logic Together: welcome back, and in this lesson, we're going to take all the logic that we've just learned about, and we're gonna put it all together so you'll see in our first example we've got our and statement from our previous lesson, and this is essentially going to say if e two is greater than or equal to 50. Andi. If F two is greater than or equal to 50,000 then it's going to output either a truthful. So you'll see in our example it's out putting two false. But if I change this to 60,000 that will then output to truth. So you'll see in the next example we have taken our and statement and we wrapped in or statements around this. So this is now saying, if C two is equal to yes, and C two is on new column, which is the V I P column. If that is equal to yes or our and statement is true, then it's going to output to truth. So if I take this back to 50,000 you'll notice that this is output. The truth. If I take that back to the wet Waas and I changed the V I p column to Yes, you'll see that that's now output to truth, the next one that we're going to look at. It's just a very basic logic statement. This is going to say, if g two, which is how Shipped column. If that is equal to yes, then we want to change the value to low and you'll notice. Obviously, in our example, the value is no. So we change the say yes, that now changes too low. We change that back, and it's taken back to false. You'll notice that if you don't put in a false value in your logic statement, then it automatically outputs to false. If you want to have it out, put anything but false, you need to make sure that you account for that value. In the next example. We're taking all statement, and we're going to wrap it into a if statement. So what we're saying here is we're saying, if this is true, then give me the value of high and if it's false, then give me the value of love. We've already explored her or statement that's wrapped around her and statement so that whole block is true. Then output high and if it's not true than output low. So if we come to the i p and we change this to yes, you'll notice that it changes too high and we change it back, it changes back to load. And then finally, we've taken our first if statement on, we've nested our second if statement inside of our first if statement now are saying if G two is equal to yes, sir, of shipping is equal to yes Then give me low Okay? And you could even change that to not applicable or order finished or whatever the case may be. So what we're gonna do is going to say that is equal to yes. Then give us alone. However, if that's not equal to yes, then if or si two is equal to yes ah e two is greater than or equal to 50. Andi F two is greater than or equal to 50,000. Then give me high. And if none of that is true, then give me love. It's quite a lot to take in, but let's write this out on it should hopefully make more sense to you. So we say equals if we're going to say if shipped is equal to yes, right, that is equal to yes. Then we will. In this case, we will say, um order complete Good. However, if so, we want to start without all statement. I'm going to say Ah si two, which is V I. P right. If that is equal to yes, okay, comma or on, and how quantity is greater than or equal to 50 right on our value is greater than or equal to 50,000. We close of our and statements and we close off our or statements. You'll see we've got different colored brackets to show you which bracket relates to which part of the off the formula. So you'll see the purple one relates to the or function on the green. One relates to the and function we put in our comma. And we could say the true value of that is going to be that it is a high priority. And the negative or false value of that is that it is a low priority. And we close off our last bracket and we enter and that has now filled down and you'll see that these two orders here are both high priority, even though they are not V i. P. And if we change this to yes, that is a high priority, even though both of these are under 50 and 50,000. And if we change this to yes, that now says order complete. That is how you can use a nested if statement with the or function and the and function to give us multiple, different pieces of logic in one formula. Now that is a bit more complicated than you maybe have seen before. But play around with it and try and break the formulas down. And if you get stuck or have any questions, let me know and I will see if I can help you out. I will see you next time. 19. Viewer Request 1 - Pivot Tables: Welcome back, everybody to a another video. I wasn't actually going to do this topic originally, and but because it was requested, I thought I'll put something together on and see what I can teach you. Um, now, this is gonna be about pivot tables, which, if I'm being honest, I don't actually use very often. And I haven't used the pivot table in a very long time, M But I know a thing or two about them. So I thought I'd put something together on and hopefully you can learn something at this sheet that I'm working in can now be found in the resource is area of the course. So if you want to follow along, you are welcome to go and find it and downloaded there. It's called dummy data license plates on. It's basically just 499 entries in a table. We've got surname, first name, email, address, contact number, the city at the license plate, number of the car type and the car color. In our scenario, we are going to identify the different car types, the different car colors by city. OK, so we're gonna use a pivot table to help us. Look at that data and get account for that data. So there's a couple different ways that we can do this. I would recommend that you start by having your data in a table. And unlike I have done, make sure that you name your table something that makes sense to you. Okay, so we're gonna then come to insert up at the top on. We've got a couple different options here. Either we can do a recommended pivot table or weaken. Do a pivot table from blank. If we choose a recommended pivot table, it will give us a few different options that it suggests that we use so for certain in by city, so named by Car Qala and so named by car type. But we're not going to use any of those because we are rebellious. So we will come to the pivot table and you'll see that it's already chosen a table range for us, which is table one now, like a cell recommend that you use a um I recommend that used tables because obviously tables are auto expanding. You don't need to worry about absolute referencing, etcetera. So I would use a table from the beginning, and then you can also use an external data source. But we're not going to do that. We're just going to use this table. The next thing that is gonna ask you is where do you want to put a pivot table? Now, if you put it somewhere that you don't want it to be accidentally, you can move it or you can just redo it. But in this case, either you've got new worksheets which will create an entirely new worksheet. Or in our case, we're going to do it on the existing worksheet so we can insert a pivot table, existing location, and they were gonna choose somewhere random because it doesn't really matter for this. So we will put it there and we say, Okay, and there is our blank for the table. Now on the right hand side here, you will see that we've now got for the table fields. Now this this menu pain can be removed. Andi Ah, shown again by clicking on fields list up the top here in the new tab that's being added. Was it feel list? And then we've got all the columns within our table. So we can. Obviously, we want the count off the license plates because that's that's what we're interested in. So we can take that and drag it into values, and that will give us the total value of the license plates within our within our data set . The next thing that we want is the car type in the car color. So we can either choose to put either of those into either the row or the columns field off this off this pivot table. So we're gonna take our type and put it into Rose. And that will break down the number of cars in for each car type and the count of each of those cause the next thing we want is the color which we are going to put into the columns . And if you look at what has been produced, it is a table with all the different counts for all the different categories that we have. So, Alfa Romeo in black, we've got one and BMW in black. We've got to in Brown, we've got to in white, we've got to etcetera So and then at the end here, you've got the grand total for each of them and then at the bottom. You've got the grand total for each of them, which I'll show you how to remove in in a bet. If you don't want to see that, you don't have to, then the next option off see we want is the city, because that's what we're in. That's what we're interested in, so we'll drag the city into the filter option. Andi. Now we have a filter, Um, for the pivot table so you can you can obviously choose to filter by multiple items, but in this case, we're just going to choose one. And we say Bristol, it's OK. That's now given us all the cars and all the different colors just for Bristol. Okay, so let's let's go back to all. So that's that's quite easy to to do. If you don't want it as a filter, you can also move this into the rose just above car type, and you'll see now it's broken it down into cities and then into the car types. And if we want to move the colors in, you can move the colors in underneath the car type, and now we've got city, and then car type and then the color. And then if we wanted the other way around, we can do that as well. So now we can say colors and then car type, Um and you can do it the other way around in the columns, but it it really doesn't look very good. Eso I wouldn't suggest using columns to do that. If you want to do that, we're gonna keep it the way that it was. So we're gonna bring card type back in since he's back up to the top. And that is our pivot table. Now you can also choose to show the some or the count of the average etcetera off the different fields by clicking on this little arrow at the side here. Andi choosing field field setting value field settings so you can you can change the way that that things look or the way that things were presented. Should I say another thing you can do is if you right click in the perfect table itself, you can obviously refresh it, and you can also come to pivot table options. You can come to totals and filters, and you can choose to remove the grand totals from the perfect table. So now those are gone. But if we don't want to remove those, we come back here and we can turn them back on, OK? And the other thing that you can do, which is quite cool, is if you come to display and you come to classic pivot table out. If we say okay, you'll see. Now the pivot table has a bunch of lines around. What this allows us to do is move the pivot table around and get it the way we wanted without having to use this field setting thing on the side. Eso, for example. We can move the car color over to the side here, and it will give us car color and then car type. Now it also displays it slightly differently. I m so be aware of that, Onda. We can move the car type off the side here we can move the cities and at the side. There we can move the car color up to the top. You can move things around as you as you want. You can just drag them around, which is something, something to know And what? We're gonna move this back to the way it wasa. Let's put cities up at the top on car type over here on car color in total, on its back to the way it was. So let us turn that setting off. We'll just keep it. Keep it the way it was. Now, your pivot table does also have some features, like a normal table. So you can just filter on the side here, and you can just filter up at the top here so you can filter the color. You can filter the type. Um, and then obviously you have a main filter which will filter the city as well. Okay, so that's that's the table. Now, if you come up to the new tab that gets created this perfect table tab, you have all your options for the pivot table. Now, one thing that I would recommend against in rename your pivot table to something. Okay, Andi, um, make sure that it makes sense to you. All right? The other things that we can do obviously weaken insert slices as you. As you've seen in the previous video, you can also insert timelines. If there was any dates within your data, set, you could insert a little timeline and it will give you a line showing you the ups and downs. Ah, you know, across the date range that you can refresh it up at the top. Here, you can change your data source. You can clear any filters. You can select an element of a perfect table. You can move the pivot table, etcetera. The other nice thing about pivot tables is that you can get an interactive pivot shot that will filter the perfect table and the perfect chart together. So what I mean by that is if we take a pie chart and I'm gonna change the way that our pivot table is structured because I find pie charts, if you have too many fields within the pivot table, they don't look right. So we'll move this off to the side, and we're gonna take the car color out of the equation. Um, and we now have just a There's a good we now have just a pivot chart with output, the table showing the counter the license plates to the, uh, car types. Obviously we have the same filters on the side on the top. Here, we've got the city we've got What the what the pie chart is looking at, which is the counter the licenses. And then we will so got our car type on the side here. So we expand that so we can see better. We say filter card type on We select Audi and Bentley and BMW. Let's go Citra on Land Rover on Mercedes. So now we have a count off each one of those and you can also see that our pivot table has has been filtered as well. So they talk to each other. So if we remove all of that and we filter the pivot table on Birmingham, we can see totals for Birmingham that's now being filtered. Um, for just permanent eso we can we can we can see that I'll we can see the makeup within our pie charts and we go back to all on. That'll change the pie chart back to the way it waas. Okay, so there's a little bit of a little bit of ah ah information about pivot tables for you. And like I say, I don't actually use them very often. So if there's anything that I missed that you would have liked to know. I do apologize. Um, but that is Ah, that is where I end. Um, if you have any other requests, just put them into the discussion section of the course, and I hope that I can help you with that as well. But until then, I am also working on other courses related to excel, but not in excel as such. So stick around for that. If you If you've ever heard of V B A and then I hope you will join me for when? For that cause when it finally comes out. Um, I've bean quite busy, but I will. I'll get on that and see what I can do. But until then, I hope you enjoyed that. Have a good one. 20. Viewer Request 2 - Multi Column Comparison: Welcome back, everyone. And in this lesson, we're going to take a look at a viewer request. How can we compare two pairs of columns in a data set? So our scenario is we have one set of columns, one pair of columns, and for whatever reason, we want to compare those two columns to another. Two columns may be from a different system or from a different spreadsheet. Maybe you're working on one spreadsheet. Someone else is working on another one. And you want to make sure that your data sets match. How can we find any out flyers? How can we find any pairs off data that are different or don't exist? So you'll see in our example, obviously this is so that you can see that they don't match. The idea is that we want to find all the pairings in this data set. So in these two columns that aren't present in these two columns, Okay, so you can imagine over the course of our no 1,002,000 rows, you don't want to have to go through each row and see OK, Which one is missing? Well, which pairing is missing out? You could do this with a V look up if it was a single pair of columns, but obviously a single column of. But obviously it's not our scenarios that we have two columns, so we've got a sales I D. And then a name that goes with that sales I D. And these are all unique. So there's no duplicates in this information, and that's unfortunately going to be. A restriction in this example is that they have to be unique. You can't rarely find duplicates within them. Eso without do without going to quite a complicated solution. So in this example, it's just going to be unique data sets in these two pairs, or the ones that aren't present in these two columns, so that that's that's our scenario. So our first solution is the first thing we're going to do is to create a merger column. So within each one of our data sets, we're going to create a merger column, and all that this is doing is using a trim function on a concoct a nation to merge the two strings together. And the reason we using tremors, because if there's any trailing spaces, those once those wouldn't show up. But if we removed out trim, then that they would show up if we, for example, had a space there. They'll show up in our in our merger, and we obviously don't want that. So we've got our murder on the side, and we've got our merger on this side. So this is our first example. So the easiest way to do this if we look at our first column, if we just say, equals we look up this value in this data set and we want column one, and we want an exact match. We don't want an approximate math. Make sure it's an exact match. If we had enter, you'll see that everywhere the data set his present, you'll get the name of the data set and everywhere that it's not present, you'll get an N A, and you could wrap this up in and if in a and just do that oh ah, so everywhere that the data is present, you'll get the name and then you get sales idea in the name and everywhere that it's not present, you'll get a dash easy peasy. So that's that's an easy solution. So the next option that we have is we can actually remove this entirely and we can say equals. They look up right and we can say shrimp, this column, right and we'll close our trim on. We'll do a 1% to come, Captain eight and we'll say shrimp again. Onda. We want the name field so that we ate two or the name field. We close our brackets. Okay, so that is our merged string. We were basically removing a column and creating the merger within our formula. Then we want to say that we're going to look at the merger column in table two. We want to return column one and we want in exact matching. If we close our brackets, that does exactly the same thing weekend. Close this up in an EF n A and we can return out death, and that's working as well. Okay, but we can take this even further, But in order to do that, we need to have a look at the match formula. So the match formula returns a row number. So we sent match and we say the value of the sales i d. Just as an example you say, match comma, we need to look up a right. We will say sales. I d in column two and we want an exact match. And if we close our brackets, you will see that it gives us the road number in which that value is present. So we can use this to our advantage by saying equals. If match trim, open brackets sales, I d close brackets M percent to come. Captain eight, trim the name column, close brackets. So we've created our strength. Then we want a look up array, which will be the merger column of table two. Right, if that as an exact match is greater than zero right, because remember, it returns the row number so the road number is greater than zero. Then we want to return. True, right. Otherwise we will return an n A. Because that's how the formula works. So be it. And so on that you will see everywhere that there is a match. It says true and everywhere that there is no match. He gives us an F and A here we say on N s. So if we say if any and we put in our condition and hit enter, you will see now, Every time the pair is matched, we get true. And every time that the pair is not matched, we get a dash. Okay, so that's how we can use the match formula. However, we can take this one step further. We can actually get rid off this merger column entirely. Now, this formula is gonna be a little bit more complicated. But essentially, what we're going to do is concatenation out two columns in our formula. So what we're gonna do is we're gonna say equals if match, Right, So we have our match formula, then we say trim and we're gonna trim our first value, which is the sales I D. And then we're gonna can Cassin eight on another trim to our name, right, and we want to match this. Now. This is where it's important if we dream this. So if you hover over sales, i d. In table two, you'll get a little black arrow, and if you click on that, it will select the entire column within the table. Can we close off our brackets? And then we can captain eight on and trim the name field and do exactly the same thing and you close that off. Right? So now we are in Katyn, ating our two columns onto each other. Then we want an exact match, right? And we close off our match function So you will notice on my my formula. By here my brackets are the same color. So we've closed off our match function. And if that is greater than zero So if the road number is greater than zero then we returned true. Otherwise we returned nothing in this instance. And like I said previously, it'll give you an N A and we hit. Enter on this. You will see that everywhere there is a match, it's given us true and everywhere that there is no match. It's given us n a right. So how can we finish this off? So, first of all, if an A and we will put in our condition and we hit, enter and that's has now given us now dashes. Now we need to do. He has come to our our filter and will say sort smallest to largest. And you'll see that everything that doesn't exist in this data set has been brought up to the top because of our dashes. And that is how you can compare two columns in one day to set to two columns in another data set to see what the discrepancies are in those two data sets. I hope that helps you. If you have any other questions, please put them in the discussions, and I'll see about making a video specifically to your problem and see if we can help everyone else out. Thank you very much. You'll be able to download this file in the Resource Is section, and I will see you next time. 21. Bonus Video - Filling in Missing Table Data: welcome to this bonus video in filling in data. Now, looking at this little table of contents here, I'm pretty shirt. At least 1995% of you watching this have has probably seen a spreadsheet like this where we have a number of different columns. And one of the columns, which should be completely full of information, has only got one entry. And the idea is that this one entry is in relation to all of these entries next to it and below it. And then the next time we have another category in this example, we then insert that value on. Do we have it relating to all the pieces next to and below? Now, this is a very irritating way of setting up a table, and it's very incomplete. You can actually do very much with this information. You can't put it through a pivot. You can't put it through graphs that it's just it's difficult to digest. It makes sorting very difficult as well. So if you wanted to sort this by category, for example, and we said 80 said, we have no idea what any of these things air in relation to. So you can't do that. It's a very incomplete, annoying way off putting the table together. And there's a simple way to fix it just by using our little logic statement that we've got you to the way that this works is we're gonna say equals if on the logic statement is if this cell is not like, Okay, so this is going to save this cell is not blank. Then we want the value of the cell. Okay, So basically, every time there is a entry in this column, we're going to return that entry. Okay? Otherwise, if it's blank, then we want to return the value of the cell above our formula. Okay, which is the cell here on? The reason we do that is going to be made very clear when we actually drag this formula down. But if we hit enter on this, you'll see that we've been given category one because a two is not blank. It's given us the value of a two, which is Category one. Okay, now, if we drag this down, you'll see it's given us category one again. And the reason it does this is because it's now saying if a three is equal to blank, which it's not right. This is not equal to blank. This is equal to blank. So it's going to miss this first condition and give us back F two, which is the cell above which in this example is Category one. So if we drag this down to there, you'll see that this is filled in category one on every occasion. OK, but if we then dragged us down one more, it's now given us category, too, because now those the first condition has been met because now a seven is not equal. It's a blank, so it's equal to Category two. It therefore outputs category two. So if we then fill this down, it's now equal to category two again because the first condition hasn't been met. So it gives us the second value, which is the value above, which is category, too. So if we now fill this down all the way to the bottom, you'll see Category one, Category two, Category three, Category four and Category five have all been output correctly, and what we do is we highlight all of this. We can copy it. We can click on our first entry on. We can paste values into our column and get rid of that. And we haven't entirely updated set of values. With all the missing information now filled in right, I hope that helped Onda. It's a very useful piece of, ah, information to know, and you can go and fix all those spreadsheets that you've got sitting that you might have inherited from other people. Or maybe you've been guilty of this yourself. Go and fix them and I will see you in the next video. 22. Bonus Video - Inserting Rows Into a Data Set: welcome back to another bonus video, and in this lesson we are going to look at a interesting little piece of information that you might never use. But if you do happen to use it or need to use it, it's quite an interesting thing to know. And it could save you quite a lot of time, So let us get into it. Essentially, in our scenario, we want to be able to insert a row between each piece of information. Okay, so we want to insert a row between Ah 1234 away down to 20. Now you could just such a all day and insert, or you could even do the control shift and plus, and you could insert columns and rows that way, but a much quicker way to do this, that we come to the column next door. And if you don't have a column next door, insert a column, create yourself a helper column, Andi one to and click down, and we basically want to get a number from one down to 20. Or however many rows of data we have. Then we want to select all of our new numbers control. See control V to paste those numbers in. So we've now ended up with 12 20 on, then 1 to 20 again and you click into the area of information control A. And if you click on the sort option, you will get the ability to add different layers of sort and we'll just say, sort by column B smallest, the largest click OK, and you now have a row between each piece of information. We can then clear out the help of column and you've now got all your data spaced out the way that you wanted to be spaced out. If you have a similar situation where you've got multiple pieces of information altogether on, they all need to be spaced out differently. You could do something very similar, but all we need to do is use a simple formula to do this for us. If you insert a row above the information that you're trying to deal with and you use our formula that we've got here, which is very similar to the one that we use in the filling in data lesson we say equals if d one right if d one her d one is not equal Teoh d to Right now, we're only looking at this column. Then we want to say e one plus one. So that's going to increase our number that were there. That's going to increase the number that we're looking at, you know, helping column. Or it's going to give us the value off E one. And if we close our brackets off and it enter and drag us down, you will notice that every time the number changes, we increment our help in number by one. So, for example, we've got one to three for 56 etcetera. And all we do is copy this and we need to pace in the values and well, pace in the values again. We can get rid of the cells above. And then what we need to do is get rid off any duplicates into this copied information because we only want one road inserted. We don't want multiples. So if we remove duplicates and continue with our current selection, we remove duplicates and we'll say okay, and we click into our array of information. Her control A to select everything will sort it by column E smallest, the largest click OK and we now have a row between each unique piece of information in our array of information. We can just get rid off all that and we've met the requirement that we set out to achieve, and that is how you can insert rose between different pieces of information within your data set.