When using Google Sheets, you may often find yourself needing to copy and paste values only, without formulas, and without formatting. There are a couple very simple ways of doing this, either through right-clicking and accessing the "Paste special" menu, or by using the keyboard shortcut as explained below.
To copy and paste values only in Google Sheets (Not formatting and not formulas), simply copy the selection by pressing Ctrl + C for Windows (Command + C for Mac) on the keyboard to copy, and then press Ctrl + Shift + V for Windows (Command + Shift + V for Mac) to paste only the values into the cell. Formatting and formulas are NOT transferred when you hold "Shift" while pasting with the keyboard shortcut.
Scroll down to check out the detailed examples and see a variety of ways that you can use this handy trick.
This lesson focuses on copying and pasting values in Google Sheets, but click here if you want to read the Microsoft Excel version of the lesson.
There are a variety of reasons that you may want to paste values only in Google Sheets, such as the following:
- When you have a spreadsheet that is already formatted in the way that you want, and you want to paste values into it from another sheet without changing the formatting of the destination
- When you have formulas in your sheet that generate values, and you want to paste those values into another location without the formulas
- When you want to paste something into a spreadsheet from an outside source such as an article from the internet, without transferring the formatting
Check out this article if you want to learn how to paste formatting only, without formulas or values.
The video below is the longer more detailed version (with more examples) of the lesson on how to copy and paste values in Google Sheets. The quick and simplified version is further below if you prefer it.
Google Sheets "paste values only" keyboard shortcut:
There is an easy keyboard shortcut that will allow you to quickly paste values only in Google Sheets.
After copying your selection and clicking the cell where you want to paste, simply press Ctrl + Shift + V on the keyboard (Command + Shift + V for Mac).
When you are using the paste shortcut, and you want to paste values only, remember to hold the shift key.
Paste values only shortcut (Windows): Ctrl + Shift + V
Paste values only shortcut (Mac): Command + Shift + V
⌘ = "Command" for Mac
Paste values only from the "Paste special" menu
To copy and paste values only in Google Sheets, copy the selection that you want to paste into your sheet, right-click on the cell where you want to paste your selection, then click "Paste special", and then click "Paste values only". This will paste only values into your sheet, without formulas, and without changing formatting.
Click here to get your Google Sheets cheat sheet
Or click here to take the dashboards course
Below I have provided a variety of examples that demonstrate how "paste values only" can be used in situations when you do not want to paste formatting or formulas into your Google spreadsheet.
How to copy and paste values without formatting
In this first example we will copy and paste values in a Google Spreadsheet without changing the formatting of the cells. Later I will show you how the same process also works when you are using formulas, but for now let's stick to pasting without affecting formatting.
The data below shows a list of clothing items that are in inventory, and the quantity of each item.
In this scenario, let's say that we have an updated quantity list, and that we want to copy and paste the new quantities over the original values, without changing the formatting.
Notice that in column E the cells are colored purple, are bold, and are centered. When using "paste values only" to paste into this location, only the values will be pasted/updated, and the formatting of the destination will remain the same.
To copy and paste the values from column G into column E as shown in the example below, do the following:
- Select the range G3:G14
- Copy the selection by pressing Ctrl + C on the keyboard (Command + C for Mac)
- Click on cell E3
- Paste values only by pressing Ctrl + Shift + V on the keyboard (Command + Shift + V for Mac)
How to copy and paste values, not formulas
Now that you see how pasting values only will not change formatting… let's see how you can use the same method to copy and paste the values that formulas display, without copying and pasting the formula itself.
When there are formulas in your sheet and you want to copy and paste the values that they display instead of pasting the formulas themselves, then pasting values only is the perfect way to handle this.
The data below shows the same list of clothing items from the last example, but this time there are two lists that show quantity, and there is also a column that has formulas which add/total the two "Quantity" columns.
In this scenario, let's say that we want to paste only the values from the "Total" column into a different column, without the formulas being transferred.
Notice that in column G the cells are colored purple, are bold, and are centered. When using "paste values only" to paste data that was copied from this location, only the values and not the formulas will be pasted/updated, and the formatting of the destination will remain the same as well.
Note: In this case the paste destination is not formatted (plain), and this brings up a good point… which is that sometimes you will want to copy data from a nicely formatted location and paste it into a range with no/plain formatting… and vice versa you will sometimes find situations where you have plain data that you would like to paste into a range that is nicely formatted.
To copy and paste the values from column G into column I as shown in the example below, do the following:
- Select the range G3:G14
- Copy the selection by pressing Ctrl + C on the keyboard (Command + C for Mac)
- Click on cell I3
- Paste values only by pressing Ctrl + Shift + V on the keyboard (Command + Shift + V for Mac)
Copy and paste values only into Google Sheets from an outside source
You can also paste values only into a Google spreadsheet from an outside source such as something that you want to copy and paste from the internet.
For example, below is a picture that shows what it will look like if you copy and paste the title of this article that is found at the top of this page, into a spreadsheet.
In cell A1, the article title was pasted as "values only" by pressing Ctrl + Shift + V. You can see that the title was pasted in plain format there.
In cell A2, the article title was pasted normally by pressing Ctrl + V, without pressing the shift key. You can see that the title was pasted with the format taken from this article page, in big letters that are center aligned (which is why only a portion of the title shows, since it was pasted so far to the left).
Copy and paste values only in Google Sheets
Now let's see an example of using "paste values only" to paste an entire data report.
Below, the picture shows a student progress report that includes student name, course, progress, and grade.
Columns A through D are formatted with color, alignment, and text style… where columns F through I are not formatted.
Part 1- Pasting plain data into a formatted range
In this first part of this example, let's say that you need to paste the plain unformatted data into the colorful formatted portion of the sheet, while only updating the values and not affecting the formatting.
(This is commonly done in situations when you have a CSV report that you want to copy and paste into a spreadsheet that is already formatted in the exact way that you like it)
To do this, select the range F2:I17, copy the selection, click on cell A2, and then paste values only by pressing Ctrl + Shift + V (Command + Shift + V for Mac).
Part 2- Pasting previously formatted data into a non-formatted range
In this second part of the example we will copy and paste from/to opposite locations.
Let's say that you need to paste the data from the colorful formatted portion of the sheet, into the plain non-formatted portion of the sheet, without pasting any of the color or other formatting.
(This is commonly done in situations when you want to create a plain version of a data set to send to a colleague, where the data was previously formatted in a way that the recipient does not need for their intended purpose)
To do this, select the range A2:D17, copy the selection, click on cell F2, and then paste values only by pressing Ctrl + Shift + V (Command + Shift + V for Mac).
In the video below you'll learn how to copy and paste values in Google Sheets. This is the quick version of the video, if you want to watch more examples on video check out the extended version at the top of the page.
