I wanna focus on some text functions like year, month, text, mid how we can extract text and even an introduction to the if function so we can extract and figure out logical tests using cell values. Now, once we cover our functions, I wanna spend some time in module, one learning about advanced pivot table techniques. This consists of maybe how to turn off, get pivot data, how to build a new field within the pivot table report so that you can use it or build a calculated item that gets held within a field. If that's not enough, we can also talk about group..
Whether it's a date, certain items within fields, and you can even adjust the settings of the pivot table to show percentages currency values by changing the show value as settings from the values box. Now this is just module one. There's actually a whole second module where we're gonna focus on a special type of functions called array functions that allow values to be spilled into corresponding cells that surround the cell that you put the formula in with that technique of array functions. I also wanna show you a way of using index and match to look.
Up multiple, look up values. Maybe you're looking for a shirt that has a certain price and a certain color, and all three of those items have to match for you to return your value. Now, once we cover the array functions and just go through a quick example of a few. I wanna focus on some visuals learning how to use conditional formatting with formulas, how to highlight an entire row based off of value, based off a certain logical test or how to change alternate colors for each row. I also wanna spend some time learning about advanced charting. We have a new recommended charts tool and quite a few new charts,.
One known as a combo chart as well. That allows you to put a line and a bar chart into one chart. Hence the name combo module two is gonna finish off with something pretty interesting known as macros, which is a small set of computer instructions that we write using VBA visual basic four applications. And, uh, we're actually not even gonna write it. We're gonna record ourselves doing actions and we're gonna save that, that way. We can reuse that recording over and over again, and essentially knock out those repetitive tasks that we have to do on our spreadsheet..
Now, as always, if you don't have an exercise file yet, be sure to pause the video and check out the description, to see a link for the exercise file that I'll be using for this course. I'm pretty excited. Hopefully you are too. Let's get started with Excel power user using Excel 2021. Now don't forget. I will occasionally ask you to pause the video, practice these things, and that's why the exercise files there in the description. Um, so don't be shy on practicing, uh, before moving on to that next topic that you wanna learn about that way..
We'll make sure that we've learned that topic before moving onto another one. All right. I've currently opened up the workbook that's available for you to download, and I'm actually on the very first worksheet called X lookup basics. With that being said, if you don't have the workbook open, sure. To pause the video and download the Excel file and open up the workbook and go to Excel basics before beginning, cuz there will be a moment in time. Once I'm done describing this function where I just want you to.
Practice filling it out yourself. So if you haven't downloaded it yet, pause the video and come right back. all right. So I'm actually looking at a list inside of a spreadsheet. Let me move this out of the way for a second. And this list is actually tracking the movies for 2021. The rating, it got the run time. All them are in 2021. So I can actually delete this column. I have the genre, the actual release date within the year and the director's name..
Now, what I wanna do is I wanna build a lookup where I can actually type in some movie name and in return, it's gonna give me the rating number or maybe the run time or the genres or the release date that I have, or maybe even the directors. Now, when we needed to do this in older versions of Excel, we had a function known as V lookup. Now the V lookup function was useful, but there was a lot of rules that we had to remember, like for every single argument that we had to fill out the.
Lookup value, the table array column index number, and the range lookup. There was a little rule we had to follow, like the lookup value had to be in the very first column of the data set the table itself, or the list itself had to have two or more columns. And in my case, I've actually satisfied that. So I don't need to worry too much. I have one column, I have two columns, three columns, and I deleted that year column. Remember that? So I have the fourth column here, fifth and then six..
Posts Related:
So I have six columns. I'm doing pretty good.
Now in order to do a V lookup, it's gonna ask you to fill out those four arguments with the lookup value, having to be in the first column of the data set, I'm gonna use the title of every movie. And then it actually asks you to highlight the data set. It wants you to search through has to be the whole thing, regardless of how large it is. You have to highlight the whole thing. Starting from the look up value..Now, one, I do grab this here and I grab the table. Ray, the next thing that's gonna ask for is a column index number. It wants to know what value you want returned from which column. So if you want the rating returned for the venom movie, you're gonna choose column number two. And that's actually putting a number in, you put the number of the column, but I personally with V lookup, like to just label the column numbers on top, that way I can just select them from there. And by default, the V lookup will search for an approximate match..
I'm gonna change that last argument, the range lookup to an exact match, which is set to false. So right here, this is gonna look for my rating for venom, which is 6.1. But if I do the same function and I actually just change one single argument, the column index number, everything stays the same, but when it comes time to get that third argument, the column index number this time I wanna find the run time I can choose the third column..
It returns the value that matches the venom row from the third column, which is actually the run time. If I needed to maybe find out the genre, I can do that too, but I have to do each one of these one by one. As you can see with the V look up here, very similar function in nature for each one that I'm filling out is just that one argument has to be changed. And that's simply the column index number for that third argument there..
And of course the range lookup always has to be set to exact match. Otherwise it defaults to approximate it. So I'm pulling this information. I'll do one more, just for good keeping. I'll get the release date of this movie, but notice how each one has to be done separately. And then my release date here. That's the final one I'm gonna grab there. And I'll also do an exact match here, so I can have the run time genre.
And I can have the release date. And I'm just gonna change this cell to actually be formatted as a date so that we can see the actual date that I was released. Now, the V lookup, very similar to the X lookup that I'm gonna introduce, but it actually has. It's a lot easier to fill out. In fact, you only need to do it once. Let me explain. There's a new function that we have available, and it's actually known as the X lookup function..
Let me bring this to our attention here, where instead of a V lookup, we can now use. An X lookup, which has very similar arguments, a lookup value that can actually be anywhere inside the list. You also have something called a lookup array, which is now a single column that you specify that Excel can use to find your lookup value. So my lookup value will be inside of my lookup array. This is my lookup value, and this is my lookup array..
Now the third actual required argument that it asks for is a return array. And that's the other columns that you would like returned. So you can highlight the list with these columns and essentially return the value that matches this row. So 6.1 97, the genre, the release day, and even the director. If I have it highlighted, these other arguments are optional. I have error checking that I can use in case it doesn't find the actual value. It defaults to exact match. I don't have to worry about setting the match mode and I can always search.
The list from the top or the bottom by changing the search mode settings. Let me do my first X lookup by finding venom as my lookup value and for my lookup array, a single column that I can find a lookup value. I'm gonna highlight the first column in the list. Now, the cool thing about an array, the return array is it can be more than one column. So if you wanna return everything in one function, you can highlight the rest of the columns as the return array. And this will return that one single row of values that matches the venom. And when I hit enter here, I can see that I've actually produced the same results, including the director name with just one single formula..
This is known as an array formula. We're gonna learn about these, you know, some more extent and a little bit, but as you can see, the values are spilling over to the surrounding cells.
And in fact, these are all connected. Notice how the formula grays itself out. If I were to try to edit the second cell, it's like, no, you have to edit it from here. These are all connected. It's a spill. So pretty interesting how we can easily use X lookup to replace our V lookup. And it does it in a much faster fashion. And the best part is, is our lookup value..Doesn't have to be the first column. I could have used a director to return a rating. I. If there's a unique list of directors, otherwise they just return the first occurrence of them. Now, there is one other type of method, or I should say function that we can use to deliver very similar results. When you're wanting to return values from a list or a table. And before X lookup was around, uh, we would use another form of doing this..
And this was known as nesting functions. We would use a combination of index, which simply an index function goes down a array. It's asking for an array, which is a single column, and it's typically the column that you're searching for. So if I'm searching for movie titles, I would have this selected. If I'm searching for ratings, I would have the rating column selected. And what I essentially would do here is I would match it instead.
Of giving it an actual row number. I would match it to a lookup value. It's almost like a backwards V lookup to where I want it to match it to this lookup value that it can actually find inside of this list here. So it's gonna essentially return the rating number for venom. And I of course, wanna do an exact match here, an exact match, and I'm gonna close my parentheses, both of them. And as you can see, I produced the same result of 6.1, but again, I have to do.
That one also each and every one, if I wanted to look for my run times, I have to change the index to select the run times. So I'm indexing the run times this time, cuz that's what I'm looking. And then for the row number that the run time can be found on. I don't know it. So I'm gonna use the match function. Now with this match function, I can essentially choose a lookup value, the movie title as the lookup value and where it can find the movie title in the first column of the list. And of course I wanna return an exact match of my lookup value..
So I'm gonna put zero there. This is gonna return the run time and same thing with genre. If you're looking for a genre that you wanted to match it to, right, you're gonna index the genres. There might be some blanks in the genres, as you can see, they've noticed there are some blanks and then I can match it to the look up value here. That I can find inside of this list. And of course, zero means exact match and I can pull out the genres there,.
Here, I'll do one more for good keeping. Maybe I wanna return the release date, the release date. So that means I have to index all the dates. This essentially makes the dates searchable. These are all movie dates that I have released. And for the row number here, match, I'm gonna do a look up value. I wanna look up with this movie title so that I can find the release date of it here. And it just gonna change this to a date so that you can see that the release date is the same..
So I've been talking for a little while and I've, I've done actually three different functions here. The first one was the traditional V lookup that we should know. And if we don't, that's a nice little refresher on it, but don't worry too much, cuz you can actually do a lot of what I showed you using X lookup and it spills the results. And then of course, if both of those fail for you, if you just prefer to fill out the index and match index and match, which is a nested function will always be available here. I'm gonna go to my data tab or better yet my formulas tab and turn on show formulas just so you can take a peek at these formulas without having.
To constantly go back to the video. And I want you to practice. I want you to practice filling out a V lookup, a X lookup and an index and match with the formulas on the screen. Pause the video here before we move on and learn about a few other examples that I have for X lookup. And then we'll start talking about those text functions that we wanna talk about in module one. All right. Welcome back. I'm actually on the second worksheet of the workbook called X lookup underscore.
Two way, and I'm looking at a very simple list of information where we have column values, and we also have row values that we're looking for. I'm looking for a certain material type and in our case it's gonna be wood, but I wanna find a specific price from group B. So I'm actually looking for this value here, 76 23. And if I want an X lookup can do a two way search your return.
Array can be another X lookup. I can use the lookup value. Let's find the wood column first. Or the void row value first. And I'm gonna use the lookup array with a list of materials. And because I don't know what to return yet. Cause I have to look through the columns. I'm gonna do another X lookup that returns column values from this list here and whatever it returns I wanted to find in this array that I have..
So it's gonna look through this list and eventually pinpoint it to 76 point 23. I'm gonna close out both of my parentheses here. If I were to hit enter, I'm gonna find a 76 23, something really cool. I want you to know is if you get this formula, this formula and you add it over here to the name box. the name box. I'm just gonna paste the formula into the name box there and hit enter. Excel will actually highlight the value from within the list..
Isn't that cool. So if I get this formula that made this value and pinpoint it and paste it inside of the name box, and I hit enter, it'll end up showing me the value from the list so I can actually pinpoint it and find out, oh, it's on row six in column C, same, same for any other one I've shown I could have, I could have done this for any other one. If I were to get this X look up here, that spills and I control, copy it. And I paste it inside of my name box, the actual formula, not the.
Actual answer, but the formula. It's gonna automatically highlight that row. Isn't that cool? I don't know. It's worth a mention. Might help you out one day, but before moving on, if you haven't tried the basics yet, feel free to try that. But I do want you to try doing an X lookup with a two way search and just to cover all bases, try getting this formula, pasting it into your name box and seeing.
If it'll go ahead and move your cell address box to the correct value practice that that's enough for X lookup here and V lookup and index and match coming up next. We're gonna go to the third worksheet of the workbook and learn a little bit about text functions that we have available in Excel and how we can benefit from them. I am currently in the workbook on a worksheet known as text it's the third worksheet in the workbook. And I'm looking at the books and beyond incorporated, um, employee list here.
Where we're tracking the hourly rate, the gross pay, how many hours they've worked. But before I build my report, I actually have to add some more values to track my employees with. I wanna figure out whether or not employees are part-time or full-time. I wanna do a year by year analysis to see how many people I'm hiring per year. And as well as a month per year analysis, see how many people I'm hiring every month within every year. I can actually extract these values using sets of functions, like one being the,.
If it just checks, whether a condition where, whether something is true or false and depending on if it's true, like if this hourly rate is over 40, it might say full-time and depending on if it's false, it might say part-time. So I'm simply gonna select a logical test here that will compare the hours worked it's greater than equal to 40, if it so happens to be, I want it to actually say full time. If you are unfamiliar with text in Excel, they call them strings..
It has to be put in quotes, an open quote in an end quote for every argument that has a string, which means words. And if it's part-time, I'm gonna put it in its own set of quotes, PT for part-time in an open and close quote, and I'm gonna close my parenthesis. And if I were to hit control, enter, it'll put the answer in. And if I would like, I can actually use my auto fill, handle that little tiny square and do two clicks to drag down. That's just one function. Another one I can use to extract the year from every higher date is.
A formula or a function called year. All it needs is a serial. Which is a cell that's formatted as a date. So these cells here in Excel, if I go to the home tab, they're formatted as dates. So it knows that these are serial numbers. And if I were to grab one of these serial numbers for myself and close the parenthesis, and if I hit control enter, it'll put the answer in without going down to the second cell and I can double click my auto fill handle. I can even put 'em together. There's also a month function where I can grab just the.
Month from the serial number. And then if I add an Amper sand and in quotes, I put a dash and another Amper sand. I can also conjoin the year and con contaminate it. So this is gonna gimme the month and a dash to put a space in between that dash the year. control enter 12, 2001. In fact, I don't like that space looks too spaced out. Let me fix that. There we go. And I'm just gonna go ahead and go auto fill all the way down..
So I've done three different text functions. One uses a logical test and it spits out the whole time or part-time another one grabs the year from the date and another one gets the month and year and concatenates it together. Um, I recommend that you give this a try before moving on and we're gonna start learning about pivot tables, some advanced pivot table techniques. Let me go to my formulas tab and show formulas here. Uh, cuz I just wanna give you a chance to see these formulas before you actually have to fill them out. Let me make the screen a little bit bigger here. In addition to this, everybody there's one function that I did..
In the very beginning of the video. And I just wanna point it out. If you wanna put the current date, like today's date in a cell, you can hit control plus a semicolon, whatever cell you're on will have today's date within the cell. So keep that in mind, if you ever need to add the current date, like maybe the payroll for the period ending, you wanted to quickly add the current date. Very easy to do so go ahead and practice this. I will leave the formulas on the screen for you to give it a quick little pause and we'll move on to some advanced pivot table techniques to wrap up module one..
I'm over here on the fourth worksheet in the workbook called complete data where it's tracking the order details for December of 2014. And this is actually 26 different columns of data, 26, where we're tracking various sales people. What region they come from. What products they're selling, what categories they're selling and how much units they've sold and how much revenue each one has brought in. So if someone were to ask you, Hey, FA that's me, who's the top seller for the year..
And who's the bottom seller for bonus purposes. What we can do essentially is create something known as a pivot table. Now, what I'm looking at here inside of this data set is called the table. That's why it has the blue and white colored rose. In fact, I went into my list and I inserted the table. That's why it's grayed out. It's already inserted. This table, gives me a set of tools like naming the data, set the ability to remove duplicates from the data set and even change the color of the formatting. We talk a lot about tables in our Excel, advanced course with that being..
We can also summarize this data with pivot tables. I'm gonna build a pivot table to track that the top seller and the bottom seller using the sales people names. I'm gonna summarize this with the pivot table on a new worksheet. So when I click on that, it's gonna let me build a pivot table on a new worksheet. Using the 2014 sales data from the table. I'm just gonna hit. Okay. And I'm gonna call this one pivot table. I'm gonna rename the worksheet pivot table by double clicking into it..
Pivot table here. I'll call it worksheet there. And if I zoom into the spreadsheet, it's actually telling me something it's saying click in this area to work with the pivot table report. Sure. Many of us have been playing around with pivot tables before we're in the power user course. But if you don't know, as soon as you click in here, Excel will give you access to the analyzed tab, the design tab and your pivot table field list..
And as soon as you go away from this area that thinks you no longer want build the report and it hides all those things from you, no fun, but again, click in this area to work with those areas. One thing that I personally do when building my report since 2016 now is I go to this toolbox and I change the fields. The column names are called fields, and I put 'em in alphabetical order in this list. I want it easier to browse through. I can do that by changing the sort order. In addition to that, I reorganized this area to put it side by side..
That way I can see all of my fields without having to use a scroll bar. And I still have access to all four of my boxes to create my report. I'm just building a basic report where I'm tracking the salesperson and I wanna see the revenue that each one has brought in for me. And since it is revenue, I'm just gonna change the value field to show currency, show values as, and, uh, change the number format here to show currency with no decimal places. So that should all be very basic. And if it's not, I definitely take a peak at our Excel advanced video where.
We've cover building pivot tables from the ground up, but this looks good. I have my pivot table. I have an area where I can filter each salesperson. I have their revenue that each one has. Now. There's something I wanna show you though, inside the pivot table analyzed tab. There's a small little dropdown in the very first command group called options. And one of the options are to generate, get pivot data. And what this essentially allows you to do is, well not allow you to do is.