Copying and pasting cells is probably one of the first things a person learns to do when starting out with spreadsheet software like Google Sheets. However, you must have, at some point, tried to copy a cell or a range of cells containing formulae and ended up simply copying the formula instead of the result returned by the formula.
The good news is that most of these applications usually have one or two ways to copy only the value, not the formula. In this tutorial, we will show you two ways to use Google Sheets to copy value and not formula:
- Using a keyboard shortcut
- Using the Paste Special menu
Table of Contents
Why am I Getting Unexpected Results When I Copy-Paste Cells Containing Formulae?
If you are getting unexpected results when copy-pasting cells containing formulae, it’s probably because the references in the formulae are relative and not absolute.
By relative references, we mean references that change when the formula is copied to another cell. An absolute reference, on the other hand, does not change when copied. Absolute references can usually be identified by a dollar symbol before the row number or column letter. For example, $B$2 is an absolute cell reference.
Usually, when you copy cells containing formulae and paste them into another cell, Google Sheets adjusts the cells that the formula’s relative references point to. It adjusts these references so that each formula operates on the data within its respective row or column.
When you copy these formulae into a separate sheet or a cell far from the original cell range, the formulas are adjusted to refer to cells within a similar distance to the new cells.
However, sometimes these newly referenced cells are blank or have different values, resulting in the copied formula returning unexpected results.
The image below explains this at a glance:
There are two ways to address this problem. You can either convert all the relative references in the formula to absolute references, or you could copy just the value and not the formula.
Two Ways in Google Sheets to Copy Values and Not Formulas
Let us take a look at two ways in which you can copy the exact value, not the formula, in Google Sheets:
Using a Keyboard Shortcut to Copy by Value
If you’ve been using any computer application for a while, you are probably aware of the famous Ctrl+C / Ctrl+V keyboard shortcuts for copying and pasting. This also applies to Google Sheets.
However, problems arise when you’re trying to copy formulae. In such cases, the shortcut you should use is slightly different.
To copy values, select the cells you want to copy and press Ctrl+C from the keyboard. Next, to paste the values only (not the formula), select the cell where you want to paste and press down the Ctrl+Shift+V keys on the keyboard.
Note: The only thing we did differently here is add the Shift key to the shortcut.
Using the Paste Special Menu to Copy by Value
Another way to paste only the values is by using the Paste Special menu. For this, select and copy the cells that you need. You already know how to copy values in Google Sheets.
Next, right-click on the cell where you want to paste the value and select “Paste Special“Â from the context menu that appears.
This will display a sub-menu with different paste options. Click on “Paste values only“Â (shown below):
 
You should now get only the values of the original cells displayed. No formula gets pasted, and no formats get pasted, either.
Why Use Google Sheets’ Copy Values and Not Formulas?
There may be many cases in which you would need to copy values only and not formulas. Some of these cases have been explained below.
When You Want to Copy Values From One Sheet to Another
Copying and pasting cells with formulas especially becomes problematic when copying cells from one sheet to another. In such cases, it doesn’t even matter whether the references in the formula are absolute or relative because even if the reference is absolute, it is going to reference a cell in a different sheet. That is bound to end in problems.
In such cases, it is best to copy just the value instead of the formula.
When You want to Make Sure Volatile Functions Don’t Keep Updating
Some functions in Google Sheets are volatile, like the RAND, RANDOM, NOW, and TODAY functions. They are known as volatile functions because they keep recalculating and updating every time there is a change in the sheet. Â
We might not always want these values to recalculate, or we might want to freeze the calculated results once and for all. For this, the best solution is to copy these cells and paste only the values onto the same cells.
For example, the RAND function in the list below displays results that keep changing:
 
To freeze these results, we need to select the cells A2:A6, press Ctrl+C, and then press Ctrl+Shift+V to paste the values of these cells into the same cells:
Â
When You Don’t Want to Copy Formatting
Finally, there might be cases where you want to copy the values but not the formatting. For example, the image below has the cells B1:B6 displayed in a specific format:
 
If we paste these values into cells E1:E6, the original format would be pasted onto the new cells, which we don’t want. To ensure this doesn’t happen, we can select cells B1:B6 and copy them by pressing Ctrl+C, and then paste only the values in cell E1 by pressing Ctrl+Shift+V.
 
Note: The pasted values in cells E1:E6 do not carry the format from their original cells.
Conclusion
In this tutorial, we showed you two ways in Google Sheets to copy values and not formulas in Google Sheets. We also explained why you would need to do so and showed you different scenarios where it can be helpful to copy-paste values only in Google Sheets. We hope you found our simple tutorial valuable and informative.
Related:
