That way we don't have to refer to absolute cell reference. We could just name ourselves. So let's say we have this chose pizza ledger here and we want to calculate the taxes. If I was to do this the regular way, I would do equals Multiply the 3063, which is an I4, and I multiply that by the tax rate found in J1. Now we know this is going to work, but then if we go to autofill, it's not going to work because we're now using relative reference.
When we should be using absolute. So what most people will do is they'll go back into the formula, and they'll put in their dollar sign to absolute reference it. This works very well, and then when we re autofill, We can see the results, but there is another way that we can absolute reference by naming our ranges So the first thing i'll do is just clear this out and i'm going to click into j1. You can name your range two ways You can use the name manager over here,.
Which says J1, and I can quickly click into J1 and rename it whatever I want. I could call it Joe if I wanted to, but you usually want to name it something that's relative to what it is. So for instance, tax rate. Now, Naming ranges, you can't use spaces in the name. So if I do tax rate, I can't do this. If I press enter, it'll give me an error. What you can do is write tax rate, all as one..
Or, a lot of times, you'll see, most common, an underscore, and then rate. So it'll be tax underscore rate, replacing that space. You can name it whatever you want, though. Maybe you just want to call it rate. But I'll call it tax rate and press enter. Now that I named it using my name box, I can now click into the taxes area. I'll click J4 again and rewrite my formula using the named range. I'll do equals I4 multiplied, and instead of it being J1, it.
Could go to the name manager. Let's say I didn't remember what the tax rate was. I don't remember what I named it. I can do equals, select, and multiply. And then I can go over to use in formula, which is found in the formulas tab. And you'll see a list of all the names ranges. And I'll select tax rate, press enter, and there you go. Another great thing about named ranges is you can use it in the entire workbook. So even if I'm on a different worksheet, I could just click and.
Maybe I want to see what happens if I increase it by 10 percent. So I'll do 0. 1 multiplied, let's put our equal sign, 0. 1 multiplied, And then you can use some formula. Actually, let's enter. And then you can turn this into a percentage. So, as we can see, name changes can help us a lot because now we're not referring to Excel's way of naming cells, like K5 or B3. We're now using our own English language to name ourselves. If you want to see a full list of these, you can go to Name Manager and.
See all of your named ranges here. You'll see I named the tax rate, the value of the cell, and where it's referring to. You'll also notice that it automatically absolute references those cells with the dollar signs for us. Now, we just saw that named ranges work really well, but what happens if we want to do an entire data set of named ranges? Well, the good news is, is that we don't need to just select each time and use the name box. What we can use is something called create from collection, which is found in the formulas tab under the defined names command group..
So if we're going to name more than one cell, this would be a great way of doing it. Let's say that I want to name all of this data. The first thing I'll do is select all the data. I'll then go over to the formulas tab and click on create from selection. Once I do that, it'll pop up this dialog box and I'll say, okay, Joe, where do you want to grab the name from? Do you want the names to come from the top row? Which, yeah, that would make sense. That would mean that all of this data, like pizza, garlic knots, salad, wings,.
French fries, drinks, would be named item. All of these prices would be named price. And so on and so forth. So that's a good idea to say that I want to use the top row. So I'll deselect left column and only keep top row selected and click ok. Now, if I want to see those names ranges, I could go to name manager and look at this. We now have a ton of named ranges. Item, price, subtotal. So if I want to quickly do something like this, maybe I want to see how much money did I make total. I could see equals, the sum, tab, and then I can just say subtotal..
Posts Related:
- FICHA DE MEMBRO - PLANILHA CADASTRO DE MEMBROS E CONTROLE DE DÍZIMOS
- How To Create A Work Maintenance Manager With Dashboard & Scheduler In Excel Free Download
- How To Design A Responsive Portfolio Website Using HTML, CSS & JavaScript Part 1 BlogSpot
You'll notice that when you start writing it out in English that this is what it'll look like when it pulls it in the menu.
It looks kind of like a little table with some selected cells and you'll see the name. And you can quickly select that by double clicking and pressing enter. And it'll give us the total of this entire column, because remember, all of this is equal to subtotal. If you only select, let's say, from I4 to I8, that's what it says in the name box. It just gives you the I4 that you selected. But if you select I4 through I9, You'll see it neatly displays subtotal, because.We've used create from selection, which means it took all of these cells and it just named it from the top row. One of my favorite things about Excel 365 is some of the new features, one of them being XLOOKUP. Now, you've probably heard of lookup functions before, whether it have been VLOOKUP for vertical lookup, or HLOOKUP for horizontal lookup, or maybe you've used, let's say, INDEXMATCH. Well, XLOOKUP searches vertically or horizontally for a lookup value, and it does it in a first row, or a column, or a range, or even a table..
So it looks pretty much left to right. That means that XLOOKUP does replace VLOOKUP and HLOOKUP. And not only that, it's easier to use, and they have some really great built in features. So let's take a look at X lookup. We'll see here that we have the product name and the price. And all I wanna do is whenever I look up the product name like Notebook, I wanted to return the price for me. So I wanted to look up Notebook and then return the price. Now, if you've ever used V lookup or H lookup in the past, you'll know that.
Right now, the way that our data set up, this would not work because in order to use vlookup, there was limitations. Like, in order to use VLOOKUP, you would have to have whatever the lookup value is, which is The product name, that would have to be the first column, which it isn't here in our data set. And it would have to be in ascending order, which once again, it isn't here. So VLOOKUP would not work. But that's the beauty of XLOOKUP. It replaces it because it looks left or right. You don't have to abide by those limitations anymore. So let's click into V2, and we're going to start this off with our equal.
Sign, and we'll type out XLOOKUP. We'll press tab on our keyboard next to the Q to open up the parentheses, and if you're new to writing functions, feel free to always click on the FX next to the Q. formula bar here, and that will open up our fun little function arguments box. That way we don't have to worry about putting commas in, we can just fill out the three boxes. Now there are three main things that we need to fill out. The lookup value, so what are we looking up?.
We're looking up notebook. The lookup array, Where are we looking for the word notebook in this list of product names? And then when we find notebook, what do we want to return? Well the price of it So those are the only things that are mandatory that we need for xlookup to work There are a couple other arguments like if not found or match mode or search mode And those are just extra benefits of XLOOKUP. They're really amazing tools, and I'll talk about them a little bit. But let's start off by saying, okay, how is this going to work?.
Well, we're going to tell it what we're looking up, whatever's here, which is Notebook. It's going to look for it in this list, it'll find it, and then it's going to return whatever's next to it, which is the price. That's it. That's how much easier it is than VLOOKUP. No longer do we need column index numbers, no longer do we need to specify whether it's an exact match, XLOOKUP by default is an exact match. So, let's try this out. What is the lookup value? Whatever is in B1..
What is the lookup array? Well, from B6, I'm going to use a keyboard shortcut here, CTRL P, SHIFT Key, down arrow, to select from B6 And when I find it in B6 to B73, I want to return C6 to C73. Once again, I'll just use Control Shift Down Arrow. And that just simply selects all the data to the next empty cell. Now that I have those, I just click OK, and there we go. We find out that notebook is five dollars..
Let's see, how much would be Maybe a printer. A hundred bucks. Okay. What about a binder? Four dollars. So we see it's working perfectly. Now there is so much more to XLOOKUP. For instance, Let's say that I type in something like Joe. Well, it's gonna give me that nasty looking error, right? And this error simply means that Joe is not in this list..
But typically when somebody types in something and they see an error, they think either the formulas broke or they did something wrong.
We used to have to use if error and we would nest our VLOOKUP inside of the if error to fix this. But with XLOOKUP, the really great thing is that they built in what's known as an if not found argument. Which means that if it's not found, we can write anything else and not get that error. So if somebody types in Joe, we could just say not found..Make sure you put your quote so it's a string of text. When I click okay, instead of getting that error, now we get not found. So if somebody puts in, let's say notebook, it gives us the price. If somebody puts in. Let's say, hello. Well, then it does not found. And that's going to really just help with the functionality of your function here. So, I'll type in printer again. And there we go..
We also have two other arguments. And I'll just reopen by clicking the FX. We have a match mode. Like I said, XLOOKUP by default is an exact match. But you can do closest match if you're looking at, let's say, maybe like a range of items. But we also have another one called search mode. And this is really unique. From the default, it'll tell you that it's first to last search, which means that when it's looking through this list, it's looking this way. So it's going notebook, pen, stapler, decimal..
And we can change that search mode to look from the bottom to the top. So by default, it looks from the top to bottom, but we can reverse it to search last to first. So that's another thing that's really nice. The reason why you may use something like this is because if something was updated in sequential order, let's say for instance down here, I redo the notebook. So I'll just put in six, nine, we'll say notebook again. And then I'll say that I'm going to up the price to 8..
Now we'll just say, yeah, I'll update this and call it eight. So now it says 8, which is great. And if I go over here and I type a notebook, it still says 5. But if I decide to, what I can do instead is I can rewrite this function. I'm going to now just go to B74 to include that new entry there. To D74, click OK. You'll see it still says 5..
If I go to search mode, however, and I decide I want to change the search right now, it's saying five. If I do a negative one, it'll now say 8. If I put a one where I leave it blank, then it searches from the top to the bottom. So it hits this notebook first and then this price for five. If I do negative one, it's searching from the bottom and it'll give me the updated price because now it's searching from down here. So that's just an example of why you would use something like a search mode. But yeah, we have so many different options with XLOOKUP..
As we just saw, XLOOKUP is an amazing replacement for VLOOKUP and HLOOKUP. We can now break through those limitations that we often ran into with those lookup functions. Now you'll see here that XLOOKUP can also be used to do data mining, and data mining allows for more flexibility for data analysis. Let's say that we have these three separate tables. I have one table over here that tells me all the products and the pricing. I have another table here that tells me all of our sales reps and what region they sell in..
But then I have this table here, this is my facts table, where I see all the things that my sales rep has been selling. The problem is, every single time, I have to go over and I have to say, Oh, Christie, okay, where did Christie sell? Oh, let me look up Kristy, Kristy, Kristy, Kristy, there. West. And then I have to type out West here manually. Now you can imagine, with a lot of data, and I mean, we have a lot of data here, how long that could take me. So instead, I'm going to use XLOOKUP. to do some data mining where I'm going to pull the data from these two tables and.
Then bring it over to my facts table here. So the first thing I'm going to do is click into L2. I'm going to do equals xlocal. I'll press tab to open up that parentheses. And then if you're new to writing formulas, feel free to just click the fx next to the formula bar and I'll open up the function arguments box to help you out. So looking at this, if I want to grab the price, how would I do that normally? Okay. That's what I always think of first. How would I do this if I didn't have x looking?.
Well, I'd probably, if I was looking at price, I see products and price, I would say, okay, product is doublers. I would look here, I found doublers, and I would look next to it and say, oh, the price is 79. 95. That's how I would do it in real life. That's how I'm going to do it with XLOOKUP. The lookup value is going to always be what you're looking up by. In order to find the price, we need to look at what are we looking up the price for? The product, which would be i2. Then I'm going to say, well, where am I going to find the product i2?.
Well, in this list of products. And then, what do I want to return? Well, I want to return the price. And there we go. So that's going to be my function. So now that I have that, let's do it. The lookup value is going to be i2. Now, since I am looking up the price, I know it's going to go to I3, I4, I5, so that's fine. For my lookup array, however, I'm going to say where am I looking.
Up for the product from A2 to A13. We do want to make sure that we absolutely reference this. So you can either use named ranges, which we learned about in an earlier section, or you are more than welcome to just put the dollar signs in. I like to use the function four key, the F four on your keyboard. So I'll click on F four and it puts in the dollar signs for me. However you want to do this, whether you want to do named ranges or just put the dollar signs in, you're more than welcome to. For the return, I wanna return the price from B two to B 13..
Once again, I'll put in my dollar signs. By using my four on my keyboard. And when I say F4, I don't mean pressing the F and then the 4, I mean the function keys up top. You'll see F1 through F12 on the top of your laptops. So now that I have that, I'll then click OK. And look at that, I get the price right away. And instead of doing all that work, I'll now use Autofill, and it does all the work for me. So Sunshine is 19. 95. And if we look here, sunshine is 1995..
So we're using data mining and we're using XLOOKUP to do that. Let's say that I want to grab the price with the discount. Well, since we already have the formula for XLOOKUP here, we can do two things. We can either just copy this formula and then wrap it for the discount. Or you're more than welcome to just reference the cell. So for me, I would say price with discount is going to be equal to, and if I just want to do the price with the discount, you don't want to necessarily do price multiplied..
Discount, because all that's going to do is give you what the discount amount is, which is 2. 39, or if we round it 2. 40. What I want to do is I want to see the price with the 2. 40 minus down. So in order to do that, when we write our formula, we're going to take the price Which is an L2, and subtract that from the price, L2, multiplied by the discount..
So all it's doing is subtracting that discount amount, which is 2. 40 inside of this parenthesis, minus out from the regular price, which gives us 2. 40. The 77 and then of course, you're more than welcome to go through and now we see the price with the discount. Now, if you don't like the way these two numbers look because they're prices, feel free to select L and M, go to the home tab and inside of that home tab, head over to the numbers command group, click the dropdown and choose currency..
That way it looks more like a actual price for the region. Feel free to do this on your own. You're more than welcome to pause the video and try it out, but then once you're done pausing, let's go over it right now with region. So I'm going to do equals X lookup, press tab to open the parentheses. Once again, you can always click that fx if you need help with writing some functions, you don't want to put the dollar signs, you don't want to put the commas, you don't want to put the parentheses, any of that. You can just use the function arguments box..
The lookup value is going to be, since I'm looking up region, I see region and salesperson. So how would I normally look up where Christie's working is I would search for Christie's name. That's going to be my lookup value. I'm looking for it in the list of sales rep names from D2 control shift down arrow to D73 and when I find it, I want to return the region, which is an E2 to E73. And once again, I use that keyboard shortcut control down arrow, and that.
Helps me to select the next empty cell. Now I'll click on my lookup array. Make sure you put your dollar signs in. And I'm just using that F4 function key to help me do that as a keyboard shortcut. And I'll click OK. Now I can double click. Do my autofill and look how amazing this is, something that could have taken months has now taken a couple minutes and we now have a full list of data..
One of the next functions we'll talk about is a popular function known as the if function. The if function allows you to make logical comparisons between a value and what you expect and then it'll return one result of true and another of false. So really it's just asking a question and answering true or false. It's sort of like if you asked me, Hey, Joe, is it cold outside? Well, if it is cold, if that's true, I'll put on a jacket. If it's not true, if it's false, I won't have a jacket. So depending on what the answer is to that question, whether it's true or false, two different things would happen..
Let's say that I'm looking at this bonus review here where I have the employee's name, their performance rate, and I wanted to simply see if they're going to get a bonus or not. Now, the bonus is going to be that if they make three. or more, then they get a bonus. If they don't, then they don't get a bonus. So in order to do this, I'm going to do equals if, and press tab on my keyboard next to me. That will open up my parentheses. As always, if you feel comfortable using the fx button here next to the.
Formula bar, that will open up the function arguments, and that helps out so we don't have to put in our commas. The logical test is going to be the question we're asking. We're asking, did they grab three or more here in their performance rating. So I'm going to select from B5 to B10, or if you want to do this on one level, you just select B5, and you're going to say, is it greater than or equal to three? Now, that's true, as we can see here, because, well, it's a three. So if it's true, now comes the easy part..
What do we want to do if it's true? We could do anything. We could put a smiley face, we could say yes, we could say no, we could say bonus, whatever you like. I'll make sure to put it in quotations, which is a string of text, and I'm going to write bonus. Just like that. So now, if this number is greater than or equal to three, it will say the word bonus. And if it's not It will say no bonus. Once I click OK, it says bonus here. And then all I have to do is just auto fill..
And now we can see who met the bonus and who didn't. So you'll see bonus because it's greater than or equal to 3. Greater than, greater than, greater than. It's not, it's not, and it is. So there we go. We now get the bonus right away. The next thing that I want to do here is let's say that I wanted to see the selection for lunch. Well, the problem is, is that there's three different selections. One, two, three. And as I just said, when it comes to the if function, it.
Allows you to return two things. If it's true, I return something. If it's false, it returns something. So, how are we going to do this? Well, we can actually keep nesting if functions inside of each other to come up with more results. So instead of just doing two results, we can come up with three or four or five or as many as we want. So, I'm going to click into v5, I'll do equals If, and press.
Tab to open the parentheses. In this case, I'll go over to my fx to grab that function arguments box, and we're going to say that there's three selections, and we'll make them up. We'll say that the logical test is that if, we'll start off with the first one, if f5 is equal to 1. Now we know that's not true with default, so 5 is actually equal to 3. But if it was equal to one, if that were true, what would it be? Would it be a sandwich? Maybe a cupcake?.
Whatever. I'm going to say that if they chose number one, it would be. Now we have to figure out though, because if they didn't choose one, and they didn't choose two, then how are we going to return three? Well, we need to nest another if function here. We're going to type in open and close parentheses. And when we do that, we have another if function now nested. So if I go over to my formula bar here, you'll see that if.
F5 is equal to 1, I want soup. Or if f5 is equal to 2, I want something else. Or if, and we could keep saying that, we can have as many results as we want. But let's click into this if. You can quickly switch between function argument boxes. For instance, I can click on this if, and now I'm in the second one. Or I could go back to the first if, or the second if. You can switch quickly between, and we want to be sure that we're in the second one..
And we're going to write the next question. If f5 is equal to, it's still not. But if that was true, if it was equal to 2, it would be, let's say, a sub. Now that we've done 1 and 2, right? We're saying, if f5 is equal to 1, it's 2. Or if f5 is equal to 2, it's a sub. Since we only have three results, we can now just fill in the false. If it's false, if it's not one, if it's not two, or if it's not two, it's not a sub, well then what could it be?.
