The Paste Special: Values Function

 If you use Excel to create formulas, you will inevitably need to move the data result of those formulas somewhere else. We spoke a little about this in the “Holding Down the Fort – Anchors” post, but in that post what we actually did was move the formula (when we move a formula, as long as its anchored, we move the result also).

But what if you want to move ONLY the data, WITHOUT    the formula? This is where the “Copy & Paste Special - VALUES” comes in to play. By copying the formulas, then selecting the Paste Special option, you can select Values from the dialog box and paste only the result.

Throughout the years and the different versions of Excel, there have been different methods of accessing the Paste Special dialog box. Some have a button the tool bar, while others don’t. Since I don’t know which version you have, there is one method to access the Paste Special dialog box that works with all versions: Alt+E+S. (I find it quicker this way anyway!)

To use this shortcut and go directly to the Past Values function, just add a V to that shortcut: Alt+E+S+V. Striking the Enter Key (or clicking the OK button) will apply your selection (Values) and you will then have removed all the formulas while keeping the values.


Keep Excelling!

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

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!

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!