Keeping it in Perspective - The Name Box

Previously, we spoke about anchoring our data in Excel Formulas by using the dollar sign ($). Well, there is a way of "anchoring" our references without actually using the $ symbol; but we need to name the cell. (We're not technically anchoring the cell, but we'll end up with the same result as if we did anchor it.) Above Column A and to the left of the formula bar is a Name Box that tells us which cell we are in.

We can rename that cell by overwriting that "D3" in that name box to something more meaningful. Using same data as our "Anchor" post, let's say cell D1 contains January Sales and cell D2 contains February Sales. We can easily rename those cells to Jan and Feb respectively. All you have to do is overwrite the D1 with Jan and D2 with Feb.

Once that is done, instead of typing in =D1+D2, we can have our formula read =(Jan+Feb).

Other uses

• If you need to get to a particular cell that is buried deep in your data you can access it quickly using the name box. For example, if you know there is a piece of data you need in cell S2806 instead of scrolling across to column S and then scrolling down to row 2806, you can simply type in S2806 in the name box and hit Enter and you will be taken there.

• You can name entire ranges, not just single cells. All you have to do is highlight the entire range THEN name it by typing a meaningful name in the Name Box. Looking back to our VLOOKUP Formula post, if we had named our range of birthdays as "Birthdays" the formula would have looked liked this: =VLOOKUP(A2,[Birthdays.xls]Birthdays,2,FALSE)

This neat little feature can really make Excel life a little easier by being able to quickly and easily trace back formulas!

Keep Excelling!

Do you like this post? Comment below and / or share on Facebook or Twitter!

It’s in there! – Nesting

Nesting is not a formula or function. What it means in Excel is that you can include a formula within a formula. You are “nesting” a formula within a different formula. The best way to explain this is to give you a visual representation. We’ll look at nesting a TRIM function  within a VLOOKUP formula, but remember that there are many formulas that can be nested within each other (I have nested VLOOKUP formulas within other VLOOKUP formulas!) Generally, if you need to perform a separate function to get what you need for a different formula, it can be nested.

Let’s say we have a list of State Capitals (just as we did for our TRIM function post) and we want to find each capital’s state.

If you simply created the VLOOKUP with the data as is (with extra spaces) it would look like this and you would get an #N/A error: =VLOOKUP(A1,$D$1:$E$16,2,FALSE).

So what you want to do is add in the TRIM function within that formula. As you see from our original TRIM blog-post, the formula is =TRIM(A1). Therefore by “nesting” the two the VLOOKUP would now look like this: =VLOOKUP(TRIM(A1),$D$1:$E$16,2,FALSE).

 

If you remember, the first part of the VLOOKUP formula is “What you’re looking up”. In this case, we’re looking up the result of TRIM(A1) formula. I hope this makes sense... If you need further clarification, please post a comment below.

Keep Excelling!


Do you like this post? Comment below and / or share on Facebook or Twitter!

Holding Down the Fort - Anchors

After you create a formula, you may not simply be able to paste the results in a different area such as a new worksheet or a different workbook. If the formula arguments are still in the cell you're trying to copy over to a new cell, what you're doing is actually copying over the formula!


When creating a formula, Excel doesn't look at the data in the cell, and it doesn't necessarily look at the address of the cell (A2, B5, D12, etc) either. It looks at the relative position of a cell in reference to the cell that in which the formula is being created. For example, if you're putting a formula in cell D3 that is adding cells D1 & D2, you would type =(D1+D2). What Excel is understanding by that is add "me minus two rows" + "me minus one row". Therefore, if you were to copy that formula and past it to cell F37, it would read =(F35+F36) which is, in reference,  "me minus two rows" + "me minus one row".

In order to be able to copy that formula to cell F37 or any other cell without changing the reference (D1+D2) you need to "Anchor" the cell references. This is done by adding a dollar sign ($) to either or both of the cell references, row and column. By adding an anchor to the column reference, the column is anchored ($D1+$D2), and by adding an anchor to the row reference, the row becomes anchored (D$1+D$2). By adding the anchor to both the column and row references, they both become anchored ($D$1+$D$2).

As you're typing in the formula, you can add anchors by simply striking the "F4' key before the next part of the formula (comma, parentheses, plus sign, etc). This allows you to cycle through the anchors: pressing the F4 key once will anchor both the row and column, pressing it again will only anchor the row, one more time and it will anchor the column, pressing it a fourth time will remove all anchors and basically anchor none (pressing it one more time will start the cycle over again).

So the next time you copy your formulas over to a new cell and you get an error (#N/A, #VALUE!, etc) it's probably because your references are not anchored. A quick fix will take care of it for you.


Keep Excelling!

Do you like this post? Comment below and / or share on Facebook or Twitter!