Are you trying to copy and paste values returned by a formula in a cell?
Google Sheets comes with a tool named Paste Special, which lets you copy and paste either values, formulas, or formatting. You will find this option by going to the main menu and clicking on the “Edit” tab.
There are many instances when you copy-paste a value from a cell, and Google Sheets either returns an error or unexpected values.
You have been there, correct?
Don’t worry! We’ve got you covered.
This is a quick guide about how to copy values and not a formula in Google Sheets. Let us dive deep into the quickest methods and keyboard shortcuts to copy-paste values.
- How to use the Paste special tool to copy-paste values only
- The keyboard shortcuts to copy-paste values only
- How to use a formula to copy the values only
Download the Example Google Sheets
The following Google Sheet contains an example dataset used to demonstrate tools and formulas to copy-paste values only. You can download it and follow me along.
It is supposed to strengthen your skills through practice.
Now, without any further ado, let’s begin.
How to use the Paste special tool to copy values and not a formula in Google Sheets
This is the most commonly used method to copy values from the cells, including the formulas.
It is very similar to the Paste Special tool of Microsoft Excel.
Consider the following example, where we have calculated the total annual salary the given company has to pay to their employees using the SUM function.
As you can see, the SUM function is used in the cell “C21”.
Let us copy and paste the values from the cell “C21” to “F1”.
- Select the “C21“
- Copy the contents of the C21 by pressing “CTRL + C” on your keyboard
- Now, select the cell “F1” where we wish to copy-paste values
- Right-click using your mouse to see more options
- Choose the “Paste special” option from the popup
- Next, select the “Values only” option
It is pretty straightforward, and you don’t need to be an expert at Google Sheets to use the Paste special tool.
Q. What is another way to access the Paste special tool in Google Sheets?
Apart from right-clicking on the desired cell, you can follow the steps below to access the Paste special tool.
- Select the desired cell
- Go to the main menu
- Click on the “Edit” tab
- Select the “Paste special” from the popup
- Now, select the “Values only” option
So, as you are guessing, both of them require you to do a similar amount of effort.
Keyboard shortcut to copy values and not a formula in Google Sheets
Computer users are well aware of the two popular shortcuts:
- “CTRL + C” to copy the content (“CMD + C” on Mac)
- “CTRL + V” to paste the copied content (“CMD + V” on Mac)
You may be using both of them over a long, long time!
Google Sheets supports a powerful keyboard shortcut to copy-paste values only.
“CTRL + SHIFT + V” on Windows and “CMD + SHIFT + V” on Mac
Now, let’s use this keyboard shortcut to copy-paste the values only and get the total annual salary as we did in the previous method.
- Select the cell whose value needs to be copied
- Press “CTRL + C” on your keyboard
- Now, click on the cell where you wish to paste the value
- Press “CTRL + SHIFT + V” on your keyboard
It is the quickest method of copy-pasting values only.
As you can see from the above GIF, after using the keyboard to copy-paste the values, most of the cell formatting has been lost except the number formatting.
How to use a formula to copy values only in Google Sheets
Not exactly the formula, but you can call it a Comparison Operator to get similar values.
It simply reflects the values from one cell to another.
Note that this method creates a dynamic value that will auto-update whenever the value of the primary cell (from where you wish to copy the value) is changed.
Here’s the example data as shown in the following image,
Our job is to get the value of the cell “C21” into “F1”.
Now, let’s begin to copy the values using the Equal To (“=”) comparison operator,
- Select the cell “F1” where you wish to paste the value
- Type “=”
- Now, provide the cell reference “C21“, as the values of this cell need to be copied
- Press the “Enter” key
The formula is pretty simple, and it should look like below,
Now, whenever the content of cell “C21” is changed, it will automatically update within cell “F1” as shown in the following GIF.
Why should I copy values and not formulas in Google Sheets?
Copy-pasting values from the cells, including formulas, will lead to errors or unexpected results. Those errors will also mess up further calculations.
To avoid this, you can copy-paste values only.
Also, sometimes a spreadsheet may contain functions such as TODAY, NOW, RAND, RANDBETWEEN, and so on. These volatile functions generate output whenever users open the Google Sheet and even perform any operation within the spreadsheet.
To freeze the values generated by such volatile functions and use them for future reference, users need to copy-paste the values within the same or different cell.
That’s all about how to copy a value and not a formula in Google Sheets.
You can use the Paste special tool or keyboard shortcut to get the values from a cell holding the formulas.
On the other hand, the Equal To (“=”) operator lets you reflect the values of the original cell, which can be used for further calculations without any worries. But note that it creates a dynamic value that auto-updates based on the content of the original cell.
Comment below if you need any help copy-pasting values in Google Sheets. Our team of experts will answer all of your questions as soon as possible.
Also, don’t forget to explore our blog section for more tips and tricks to use Google Sheets like a pro.