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.