Matt Refghi

How to Make Excel Formulas More Readable With ALT+ENTER

by

Transcription

So the Excel sheet in front of you acts like a phone book. You enter a name and then you’re provided with a phone number. And this works because I have formulas within this cell, we’ll take a look at that shortly. But I also added another feature that lets you lookup by number, not just by name. And if you do this, you can see the result is “Nothing found” because there’s no phone number that’s called “Sawyer, Allen”. So we’re going to delete this, and instead we’re going to search for “Sawyer, Allen”‘s phone number, so in this case it’s 999-174-5493, press ENTER, and there you go, it finds a name. That’s great and all, but let’s take a look at the formula that makes it happen.

Here it is. As you can see, there’s quite a bit of stuff to read here. I can click to have some coloring, it helps, you know, I can tell what’s what, but you do have to read it, and there’s a lot of text up there. So the moment I learned about this trick, I never stopped using it. So here’s the first step, increase the formula bar so that you have more height. And then go to a part of the formula where you wish you could break the line, where you could actually have a break line to make things more readable. In my case I would do it right here, because that’s the first parameter of the IF function, which is at the highest level, as you can see from the pop-up there, that Excel gives you. There’s three parameters, right? There’s a logical test, there’s a value of true, and there’s a value of false. So why not separate them one line at a time? There’s a way to do this, people, and it blew my mind. ALT+ENTER, look at this! How beautiful is this! And then you go to the next parameter, and then do the same thing, ALT+ENTER, so now you know just from looking at it real quick, this is the logical test, this is what happens if it’s true, and this is what happens if it’s false.

Now, it’s still cryptic, there’s other things you can do to improve this, but already this really helps with the readability of the formula. Now there’s one downside, once you start doing this, you have to remember, I’m going to save this. Once you start doing this, you have to remember that your sheet might have formulas that are cut off. [Laughs] This is something you have to keep in mind, so if I’m clicking on a cell and quickly glancing at the formula bar, I might actually think that that’s all there is up here, “oh, the formula’s this short”, but wait, wait, no. [Laughs] First if you drag down, you’ll see there’s more to it than that, and I guess that’s the thing, once you start leveraging this for readability, you have to consider that, you know, that there might be something there, even if you’re just seeing one row. But to me this just a quality of life improvement – and such a readability boost, because I’m used to working in true programming/development environments where I’m able to put tabs, I’m able to put indents, curly brackets, just organize things. So I can’t do exactly that here, but I can certainly do at least this to break down what I’m doing. And in another video, I’ll show you other tips on how to improve readability because it’s super important.

Using Excel’s Formula Arrows to Understand How Cells Relate to One Another

by

Transcription

Excel is capable of drawing arrows that show you how cells relate to one another. I’ll show you how it works. So in front of you is kind of a phonebook type application – you can see that I can type a value, in this case I typed “Armstrong, Audrey”, and that it actually pulls up Audrey’s phone number, and if you look here, it also highlights it in green on the side. But what we’re going to be focusing on is this cell right here, we’re going to leave it selected and we’re going to go up to the formulas tab in the ribbon, and in the “Formula Auditing” section we’re going to click “Trace precedents.” What this does is it shows us the cells that are influencing the value of the current cell. Now if you know VLOOKUPs, you know that this makes perfect sense because there’s a lookup value involved as well as a lookup table. So on the right here you see the table, and on the left here, you see a value. So of course these two cells influence the value of this current cell. It’s by design, it’s how we wanted it.

Now we can also do the opposite, so for example, let’s go up and remove the arrows here, and you have to remember to do that, or the arrows will stay, and then we’re going to select the lookup by name field, right here, and then we’re going to select “Trace Dependents”. So we know that this cell, which we have selected, is influencing another cell, and only one cell, it’s this one right here. Now let’s remove the arrows again.

Now, you might say Matt, these arrows, who needs them, because what I can actually do is select the formula bar, and when I’ve done that, I can actually see, you know, that C2 is blue and blue is this cell right here, and the lookup table is in red, and this is the actual area. But the thing is with the arrows is that they actually show the directionality, they show you which cell influences which other cell, they show you that at a glance, so there are certain situations where it’s useful. Another example where I found it useful is when I’m in a workbook that has a bunch of columns, and you sometimes have fields that are actually off-screen, so they’re like maybe over here, so there’s formulas over here, values over here, hidden off-screen, and then by actually turning on the arrow mode, you’ll actually see an arrow going off in the distance, on the right, beyond the scrollbar, and then you’re like oh, okay, something’s over there! Could you have found the same information from analyzing the formula? Sure, you could have. But pressing that arrow sometimes just makes things a little quicker. At the end of the day, it’s yet another tool to help you analyze formulas in Excel.

How to Move Columns and Rows in Excel

by

Transcription

Did you ever wonder how you could move columns in Excel? Here’s how you do it – in my case, I want to move the “extension” column, which is column D right here, so that it appears right after the “name” column. To achieve that, I have to select the “extension” column, and while it’s selected, you’ll notice that there’s green borders to the left and right. I have to move my mouse to one of these borders, doesn’t really matter which one, and as soon as I hover on the border itself, you’ll notice that the mouse cursor changes.

It’s at this moment that I have to press left-click and hold – I can then move the mouse around and you’ll notice when I hover it over another column, it outlines the entire column, you can see this when I hover over “name” – what this is showing us, is if I release, it’s actually going to overwrite the contents of that column, that’s not what I want. What I want is the “extension” to appear right after the “name” column – to achieve this I have to hold SHIFT – this modifies the behavior of this drag functionality, and now it shows us that if I release here, it’s going to put it in between these two. If I were to release here, it’s going to put in between “ID” and “name”, but this is what I want, between name and job title, and so I release.

Now for rows, it’s pretty much the same thing. Let’s say I wanted to move “Cory” here so that he appears right before “Coleman”, to do that, I would have to click the row, then hover my mouse over one of the borders, and as soon as the cursor changes, I press left-click and hold, I drag up – hold SHIFT – and now I can tell exactly where it’s going to land, and I just release between 2 and 3, and there we go – “Cory” appears before “Coleman”.