Matt Refghi

Transcription

Coming soon.

Transcription

Hey, so you know how in Windows if you want to move a file, all you have to do is left-click the file, hold the left mouse button down, and drag the file somewhere else and release? It’s that simple. But what if you wanted to do the same thing, but with a copy. Here’s how you do it. Let’s start by putting the file back to where it was… ok. So now instead of left-clicking the file, I’m going to right-click and hold, and then move the mouse just like I did before, and release on my desktop. Notice how a little menu pops up? Copy here, move here, and create shortcuts here are my options. In this case, I want to copy here – it’s that simple.

You might say Matt, what’s the point? I could just copy-paste the file on the desktop, yeah, I mean, you could – let me delete this file and I’ll demonstrate the downside of that. If you copy this file, CTRL+C, click on the desktop, CTRL+V, it goes to the top left, there, it doesn’t know where you want the file to appear. So this little right-click and drag approach is just a little optimization – and I use it all the time – so I figured if one of you finds it useful, the video was worth making.

Transcription

Coming soon.

Transcription

Coming soon.

Transcription

Okay, so I have a CSV right here, also known as a comma-separated value file. And let’s say I wanted to change the delimiter in this file from commas to semi-colons. Now there’s not many rows in this particular file, so I could almost manually do it, but what if you want to do it quicker than that? Well I could do a find replace, so for example I did CTRL+F in this case in Notepad++, and then went to replace, I’m going search for comma, and I’m going to put semi-colon to replace it, but the problem with that, if I replace them all, you’ll notice that the names had commas in them, Kiley, Terra, and now that’s gone! It’s just a semi-colon, so I can’t do that, in this case. So I’m going to undo it, and then I’m going to save again.

Okay, so how do we get this file to have semi-colons instead of commas? Let’s say you have a lot of rows and you really don’t want to do it manually, here’s how you can do it. It’s a bit deep in Windows though. I’m going to open up the CSV in Excel, so we have it here, ready to go, alright. And then the next thing we’re going to do is we’re going to go to the Start button, we’re going to search for “region”, where it says “region settings”, I’m going to left-click that, it’s going to pop up. In this section, I’m going to scroll all the way to the bottom, click “additional date, time, & regional settings”. When the next window comes up, it’s going to be in the “clock and region” section, and I’m going to go to where it says “Region” and I’m going to click “Change date, time, and number formats” – okay, now when I’m there, what I’m going to do now is go to the bottom and click “additional settings.” See what I mean by deep in Windows?

Alright so right here you can see that there are a number of fields but the one we care about is the list separator field, it’s set to a comma. Change that to a semi-colon, or whatever you feel like changing it to, press Apply, okay, apply, okay, let’s just clean up by closing all these windows, okay. Excel is still open, we’re going to go to File, Save As, browse, and then here I’m gonna name it simply “Example2”, save, okay. I’m going to minimize this file – in Notepad++ I’m going to bring Example2 here, and what do we see? The semi-colons are there. Great! So it actually did what we wanted.

Now here’s the thing, after getting the outcome that you want, I recommend that you change the setting immediately back to what it was, otherwise every CSV moving forward would be treated as if it was supposed to have a semi-colon in between, which is not what the filename was meant to be [laughs], comma separated values, not semi-colon separated values. So I’m going to go back to the start menu, I’m going to search for regional settings, I’m going to go to the bottom of that, additional date time and regional settings, change date time and number formats, additional settings, and the list separator goes back to a comma. We press apply, we click okay, apply, okay, close our windows, and that’s it!

Transcription

Let’s say you’re in Excel, and you want to select all the rows that are in your sheet. You could just use your mouse, select the first option, click, hold the left mouse button, drag, and then pull all the way down. But you know, you might have a bunch of rows that you’re dealing with, and you don’t want to do this manually. Well thankfully there’s an easy way around this and I just wanted to show you guys.

So if you go back to the top, for example, and you select the first cell and then hold shift on your keyboard, and use the arrow keys, here I’m pressing to the right, you can see that I’m able to select cells, so I’m gonna select all of these cells. I can then press down to select more cells, but that’s not what I’m here to show you. I’m here to show you that if I just select the first row, and then, while I”m still holding SHIFT, I press CTRL, and then press down on the arrow keys, it selects everything. So when you get used to this, it’s actually a really quick way to select just the cells you want to select.

Here it is one more time. Select the first cell, hold SHIFT, press the right arrow key, until you’ve selected them all. Hold CTRL, press arrow key down – there you go, they’re all selected.

Now let’s do the opposite, let’s go from the bottom. So I’m going to select this cell, we’re going to do SHIFT, left arrow key until we select them all, hold CTRL, arrow key up. Oh, look at this! It worked, but it selected the title here. Maybe we don’t want that. So there’s two ways to fix this – you hold SHIFT, press arrow key down, and it adjusts. Or what I like to do in my sheets sometimes is select the cell right below the actual title row, and just say Insert. This will create an empty row. Then all I have to do with this is just make it really tiny, okay. And then if you go to the bottom, and select the bottom cell again, we’re gonna hold shift, arrow key left, hold CTRL, arrow key up – you’ll notice that it selected everything but it stopped. And it does that because the row that I added is empty. Because it’s empty, it kind of knows that that’s the end of what I wanted to select.

The same thing works for horizontal sheets – so for example, in this case, I’ve got a bunch of columns – they’re nonsense columns, but they’re a bunch of columns nonetheless. If I wanted to select them, I would click on the cell, I would hold SHIFT, arrow key down, until the end, then I would hold – start holding CTRL, then I would press arrow key right, once. And it would select everything.

Transcription

Coming soon.

Transcription

Coming soon.

Transcription

So today, I’d like to show you that the VLOOKUP function, by default, is really not as precise as you think it is, unless you understand how it works. The same applies to HLOOKUP. So, as you can see, the file in front of you right now is kind of like a phone book – on the right hand side, you can see I have names and their associated phone numbers. I can scroll down, you’ll see I have a bunch of these. And on the left-hand side I have a few features – so there’s a text box here that we’ll be interacting with shortly. This is where I can type in a name, and if there’s a match, it’s going to appear right here where it says phone number. You can also see that there’s 201 phone book entries right here, and I also added a little feature here that shows how many entries start with each letter.

Okay! So let’s start. The VLOOKUP function is amazing, here it is – I use it all the time, it is super useful in Excel. So let’s start by looking up a name. I’ll do the easiest, I’ll select “Adams, Olivia”, which is the one immediately to the right of this cell. So I’ll say “Adams, Olivia” – as you can see, my sheet reacts. It found the match, Adams, Olivia, it highlights it for me, this is because of some conditional formatting I have here, and then down here, it selects the phone number. You can see 175-2344. Cool! Now, let’s try another name, I’m going to select now, “Baker, Nicola”, okay. So it found 575, which we can see here, 9527, but… wait a minute, that’s not… look at the name next to it: Kelvin? Kelvin Baker? Nicola Baker? That’s not the same thing. Nicola Baker’s right here. Why is it assuming Kelvin? Hm. And that’s my point. By default, VLOOKUP does not look for an exact match. That revelation has completely changed the way I use VLOOKUP moving forward. So I’ll show you how to fix this.

As you can see right here, this is where the VLOOKUP is, as I showed earlier, you can click up here, right here, there’s a Lookup Value, which is, you know, C2, right here. It also has a Table Array, which you can see here, I’ve selected it. And it has a Column Index Number which is the second column in this case. So we’re looking up here, but we’re finding the second column, okay? Which is how the number shows up here. But here’s the interesting bit – Range Lookup, what’s this? So let me go ahead and actually put a comma, and you’ll see, oh, what? Approximate match: true. Hm. So by default, let’s be clear – true is selected. So the fact that I didn’t have it meant that approximate match was on. I don’t know about you, but when I’m in Excel… I want it to be precise. It’s Excel. You know?

So let’s go ahead and click false – exact match. I double-click it – see how false appears here? Now I’m just gonna press this, enter, and look at that! Hm, Nicola Baker, 837 – yeah! That’s the right number.

Now, let’s take a step back here. Let’s actually go back to the previous mode, okay? And look at this. Look at the way that they’re ordered here. So it’s clearly alphabetical, right? So Bailey Edwin, Bailey Kelsey, Bailey Naomi, Baker Carlos, Kelvin – okay, and then we have Nicole, hmmm. If it’s an approximate match, how is Nicola not even reached, and it concludes Kelvin. I don’t know what Excel is doing internally, but it’s getting it wrong. And we can find out more about what Excel is thinking by actually clicking on one of the links when we’re working on the formula. So for example, I’m going to click this, and then I’m going to click inside the parameters for the VLOOKUP, and then you’ll see there’s a little tooltip that appears. And in this tooltip, I’m actually going to click VLOOKUP – this will pop up the Help in Excel.

Now Microsoft’s documentation, I gotta hand it to them, I think it’s great. So on the right hand side, let’s scroll down through the help – and then you see the Technical Details section here, I’m going to click it, I’m gonna keep scrolling, and what we’re interested in is the fourth parameter. So let’s go back here to the formula, I’ll show you quickly. The fourth parameter, one, two, three, four, is range_lookup, so let’s find it. And here we have range_lookup on the right, in the technical section. So, what is it? Well, it’s “[a] logical value that specifies whether you want VLOOKUP to find an approximate or an exact match” – okay.

Now let’s found how the approximate match works. “[It] assumes the first column in the table is sorted either numerically or alphabetically, and will then search for the closest value.” Okay. And yes, it is the default method as I told you. And then exact it “searches for the exact value”, which, you know, you would want, if you’re building a phone book. But anyway, I’m going to close the help.

So we know it’s sorting alphabetically, so let’s figure out why we’re not getting the result we expect. Well if we look carefully in this list here, this isn’t actually sorted alphabetically, I mean it is – the only problem is that Nicola over here is out of order, she’s supposed to be higher up. If we actually make this change, we might actually get the result that we expect. So for example, I’m going to change Nicola here, I’m going to put an A on Nicole, put an E on Nicola, and as you can see it now pulls up the right phone number. So again I don’t understand why Kelvin was approximately close to Nicola, but fixing the alphabetical sequence actually corrects the problem.

Now, this means that that parameter in the VLOOKUP actually can have its uses. So for example if you know your data is sorted alphabetically, putting a “true” here will probably speed up the results, especially if you have a huge dataset. But for me, the idea that Excel is not providing me with exact results is unacceptable, so I always set this to false, and if I ever spot a VLOOKUP that’s not set to false, I set it to false immediately, because I want to precision.

And so that’s it!

Transcription

Coming soon.