Seemanto Saha
We often Copy and Paste values from one cell to another in Google Sheets. One might even copy values from other files like Google Docs, MS Excel, MS Word, etc. While we copy values to our required cells, we often face scenarios where the Paste Values option is not working correctly in Google Sheets. In this article, I’ll discuss two examples of such scenarios and provide solutions for each of them. Also, we’ll discuss how to apply a formula to paste values only in Google Sheets.
A Sample of Practice Spreadsheet
You can copy our practice spreadsheets by clicking on the following link. The spreadsheet contains an overview of the datasheet and an outline of the described solutions when paste values are not working in Google Sheets.
2 Fixes When Paste Values Is Not Working in Google Sheets
First, let’s get familiar with our dataset. The dataset contains the Batch, Roll, and Name of several students. We have used the CONCAT function to concatenate Batch and Roll to create unique Student IDs. Now, we want to copy these Student ID values to a different cell without the CONCAT formula formatting.
For this purpose, if you copy the data and apply the keyboard shortcut CTRL+V to paste it into the required cell, the formula will be copied along with it. Hence, you won’t get the required result. We have a remedy for this problem that we will discuss in the next section.
1. Using Keyboard Shortcut to Paste As Values
The keyboard shortcut CTRL+V is used for copying with formatting. Therefore, the formulas are copied as well. Instead, you can apply the keyboard shortcut CTRL+SHIFT+V to paste as values only.
Steps:
- First, select and then copy the range D5:D8 (the range you want to copy) using the keyboard shortcut CTRL+C.
- After that, select Cell G5 (where you want to paste the values).
- Finally, use the keyboard shortcut CTRL+SHIFT+V to paste as values. Thus, you can paste values without formatting in Google Sheets.
2. Activate Google Docs Offline Extension
We often copy data to Google Sheets from other sources like Google Docs, MS PowerPoint, MS Word, or even a browser. In such cases, the paste values option may not work properly. We have found a remedy for this problem.
Steps:
- First, let’s explain the problem. We want to paste the Wikipedia links for two movies into a Google Sheets workbook.
- We will copy the links from a Google Doc file.
- So, let’s first select the first movie link using the keyboard shortcut CTRL+C.
- Now, let’s paste the link using the keyboard shortcut CTRL+V. As you can see, the link is copied as expected.
- So, like the previous one, copy the second movie link using the keyboard shortcut CTRL+C.
- But, unlike the previous one, this time we’ll paste the link as a value. Therefore, use the keyboard shortcut CTRL+SHIFT+V. As you can see, the unwanted result has occurred. The link has not been copied. This type of error occurs when the Google Docs Offline Extension is deactivated.
- To activate the Google Docs Offline Extension, click on the vertical ellipsis symbol (⋮) from the top-right corner of your browser window.
- From the pop-up options, first, select the More Tools option and then select the Extensions option.
- This will open a new window like the following in your browser, where you will find the Google Docs Offline Extension deactivated.
- Activate the Google Docs Offline Extension.
- Now, return to your Google Sheets file and reload the page.
- Now, if you use the keyboard shortcut CTRL+SHIFT+V to paste values, it will work according to the expectation.
How to Apply Formula to Paste Values Only in Google Sheets
We can also apply formulas using various functions like TEXT, VALUE, etc. to paste values only in Google Sheets. Here, we’ll discuss the TEXT and VALUE functions.
1. Using TEXT Function
The TEXT function can convert a value into text according to a specified format. Since we want to paste values without formatting, we can paste them as text values.
Steps:
- First, select Cell F5.
- After that, type in the following formula-
=TEXT(D5,“#”)- Finally, press the Enter key to get the required value.
- Hover your mouse pointer above the bottom-right corner of the selected cell. The Fill Handle icon will be visible.
- Now, use the Fill Handle icon to copy the formula to other cells of Column F.
2. Applying VALUE Function
Another function that can help to paste values only in Google Sheets is the VALUE function. This function transforms a text in any of the date, time, or number formats that Google Sheets understands into a number.
Steps:
- In the beginning, select Cell F5 and type in the following formula-
=VALUE(D5)- Then, press Enter key to get the required value.
- In the end, use the Fill Handle icon to copy the formula in other cells as well.
Things to Be Considered
- The keyboard shortcut CTRL+V is used to paste with formatting, whereas the keyboard shortcut CTRL+SHIFT+V is used to paste without formatting.
- Deactivation of Google Docs Offline Extension will inhibit the execution of several operations.
Conclusion
This concludes our article to learn the solutions when paste values is not working in Google Sheets. I hope the discussed solutions were sufficient for your requirement. Feel free to leave your thoughts on the article. Visit our website OfficeWheel.com for more helpful articles.
