Microsoft Excel 2010, like its older version (2003 or 2007) are packed with huge sets of features and shortcuts waiting to be uncovered. In this article, we will discover how to copy the values of a cell excluding the formulas.
Normally, when you copy the cells containing a formula, the formula is copied automatically and the cell references are updated dynamically. This is a very essential feature of Excel. Since the formula is being copied and the cell references in the formula are updated, the cells will then show up with the right results or value.
Now, suppose you want to keep the values only and NOT the formula to make the contents of the cell permanent or static, there are two (2) ways to do it. (Note: You may need to copy the values of the cells and not the formula to make the contents of the cells static, which means that when the referenced cells’ values changes, the last values will not be affected.)
A. Using Several Clicks
First, select the cell or cell range where there are formulas. You will see that they have formulas instead of static data entries because the formula bar will show the formula of the active cell. Then press Ctrl+C to copy or you can right click on the selection then click on Copy.
Then right click on the cell where you want the copied cells to be pasted. Then point to “Paste Special” then click on the “Values” button. This will make Excel copy the values only of the copied cells and NOT the formulas.
Now, when the referenced cells’ values change, the copied values will remain because they are no longer formulas but static values/
B. Using the Drag and Drop Method
This method is easier and is considered a shortcut, a power shortcut.
When the cells or cell range is selected, no need to press Ctrl+C to copy then. instead, position the mouse pointer in any of the selection borders (referring to the black border that appears when doing a cell selection) and you shall notice that the pointer changes to a four-headed arrow.
Now, when the pointer turns to the four-headed arrow after you hover it to the selection border, press the right mouse button then drag the selection to the next column or row where you want to copy. When you release the right mouse button, you will get a context menu and select “Copy Here as Values Only”.
NOTE: The screenshots above were taken using Excel 2013 but the same procedures apply in recent versions of Excel.