Tech Tips

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.

Transcription

So have you ever been in a situation where you try to double-click an Excel file, and it just doesn’t open? Watch this, if I double-click File2, nothing happens. You do see the wheel going in Windows, so it’s actually working, but nothing’s happening. What’s also puzzling about this is that if you check the task bar here, Excel’s not flashing, nothing’s flashing, it’s like nothing need my attention… so what gives?

Now to understand why this is happening, let’s click the Excel icon in the taskbar. So here you can see that I was already updating a file called File1.xlsx, and the file we were trying to open is actually File2. Now what’s interesting here is that there’s only one Excel window, if you check down here. There’s just one. Where’s File2, I double-clicked it twice, why is it not opening? Well here’s the story. If you’re in the middle of updating a formula in Excel, and then you minimize, go somewhere else, work on something else, and then try to open another file, Excel can’t do anything until you conclude what you’re doing here. If I go and I actually press the red X to cancel the formula, File2 appears.

So the next time you try to open an Excel file by double-clicking it, and you don’t understand why Excel is taking so long, check it out, it might actually be what I showed you.

Transcription

Coming soon.

Transcription

Hi everyone. Let’s say I needed to take a screenshot of this, what you’re seeing right here, the Google search page, but more specifically, the Web Store button and I wanted to explain to someone that hey you can remove this, you can get rid of this button. Well, the way to do that is you have to hover on it, then you press this, then you press remove. Simple, right? Well, the problem is, if I want someone to know how to do this, often what works best is having a screenshot, so if I want a screenshot, I have to kind of like hover on this, to make sure that the triple dots are visible, the button that you see here, right? But I can’t leave, because when I leave, the button leaves. So I have to stay here, okay… but now if I want a screenshot, what do I do? Well, I’m going to press Print Screen, right? Print Screen is the default way of taking screenshots, so if I go here, into Paint, and paste what I just print screened, you can see that yeah, okay, I see the triple dot icon, unforunately I also see “Web Store” some tooltip that appeared here, but I do see the triple dot icon, you know, so cool. The problem is, I do have to crop, which is extra work, I don’t really need to crop – is there a better way, perhaps? 

Well, let me show you another option. Another option is to press, to hover again, right, and then press ALT + Print Screen. I press on Paint, I paste, and here you see that hey, it worked this time, great. And what’s good about ALT + Print Screen is that it only takes the window that had focus, in this case, literally the browser window, that’s cool. But again, I have to crop… why would I have to crop if don’t need to crop? Right? Is there a better way? I’ll show you a better way.

So let’s close this right now, and uh, I’m going to show you two different ways actually. The basic in Windows 10 was Snipping Tool for a very long time, the basic screenshot functionality. Snipping Tool is great, basically pop this thing up that you see right here, you can press New – and then it lets you select something, right? Select anything you want, release, and there is your screenshot pre-cropped, perfect. Now there’s just one problem, and you can see – where’s my triple dot icon? It’s missing, right? Because you need a hover state, right? You need to be hovering on that icon for it to appear. So… when I move my mouse away to press New for Snipping Tool, I lose the hover state, so I can’t see the icon anymore. How do we fix this?

Well, Snipping Tool is fantastic like I said. So if you go back to Snipping Tool here, and notice that there’s a nice little button here, called Delay. So if you actually use the Delay feature, you’ll notice there’s a few different options from 1 to 5 seconds. So if I do 3 seconds, and then I’m gonna press New, and then immediately moev to the button to trigger the hover state. So now the hover state has been triggered, and I can just cut here and take exactly what I want to show my friend where he’ll need to click. So that’s as easy as that can be.

So the next way I’m going to show you is the Snip & Sketch tool which is built into Windows 10. This was advertised as the Snipping Tool successor for a time. So I’m gonna go ahead and launch it, it’s available from the Windows App Store. So here what you’ll notice is it has a slightly different interface – I’m going to make it just a bit larger. It has a slightly different interface, okay? When you zoom in, you’re going to see next to the New button, there’s a blue arrow pointing down. That arrow, when you press it, has additional options. So for example, if I press that arrow, you’ll notice that it says Snip Now, Snip in 3 seconds, Snip in 10 seconds. Now, when I press this, the timer is going to start, and I’m going to have to immediately move into position to get into the state that I want. So I’m going to press 3 and then you’ll see I’ll immediately move to the Web Store button. As you can see, triple dots appear, snipping tool kicks in – and by that I mean the Snip & Sketch tool, and then I select these icons, and release and then Snipping – Snip & Sketch appears here and shows me exactly what I took a screenshot of.

As a final note I’ll just say this isn’t always about hover states. There are cases where you’ll need to take screenshots of things that don’t require hovering. Let me show you an example – so I’ll pop open the old Snipping Tool here, so okay let’s say I want to take a screenshot of this. Right? So I want to take a screenshot of where it says “Bookmarks”, okay? So I go here, I go to the Snipping Tool – and hey look, it closed! But I wanted to show that, what do I have to do, do I have to go print screen again, you kidding? No, no. The solution is the same I showed you before. Delay 3 seconds, New, and then you pop open the menu – there you go, the menu stays visible. And then you can do whatever you want – put an arrow pointing to something. And that’s pretty much as simple as it is.