When using Excel, have you ever needed to paste values in your spreadsheet, but you didn't want the formulas or the formatting to be pasted? In Excel there are a couple simple ways of doing this, either by right-clicking and accessing the "Paste options" menu, or by using the keyboard shortcut as explained below.
To copy and paste values only in Excel (Not formatting and not formulas), select the cell or cells that you want to copy values from, copy the selection by pressing Ctrl + C on the keyboard to copy, and then press Ctrl + Shift + V to paste only the values into the cell. Formatting and formulas will not be NOT transferred when you hold "Shift" while pasting with the keyboard shortcut.
Scroll down to see detailed examples and a variety of ways that you can use this handy feature.
This lesson focuses on copying and pasting values in Microsoft Excel, but click here if you want to read the Google Sheets version of the lesson.
There are a variety of reasons that you may want to paste values only in Excel, such as:
- 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 spreadsheet 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 a page from the internet, without transferring the formatting
Excel "paste values only" keyboard shortcut:
There is an easy keyboard shortcut that will allow you to quickly paste values only in Excel.
After copying your selection and clicking the cell where you want to paste, simply press Ctrl + Shift + V on the keyboard.
When you are using the paste shortcut, and you want to paste values only, remember to hold the shift key.
Paste values only from the "Paste Options" menu
To copy and paste values only in Excel, copy the selection that you want to paste into your sheet, right-click on the cell where you want to paste your selection, find the "Paste options" section, then click "Values". This will paste only values into your sheet, without formulas, and without changing formatting.
Below there are a variety of examples that show how pasting values only can be used in situations when you do not want to paste formatting or formulas into your Excel spreadsheet.
Paste values without formatting
In this first example we will copy and paste values in an Excel 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 selected cells by pressing Ctrl + C on the keyboard
- Click on cell E3
- Paste values only by pressing Ctrl + Shift + V on the keyboard
Copy and paste values, and not formulas
Now that you see how pasting values only will not change the formatting in your sheet… 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 you have 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 displays the same list of clothing items that we used in 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 formatting), 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 selected cells by pressing Ctrl + C on the keyboard
- Click on cell I3
- Paste values only by pressing Ctrl + Shift + V on the keyboard
Paste values into Excel from an outside source (Websites etc.)
You can also paste values only into a Excel 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 bold 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 Excel
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 cells that are already formatted
In the 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.
Part 2- Pasting formatted data into a range with plain format / no format
In this second part of the example we will switch the locations that we copy and paste from/to. This time we are going to copy data from a range o f cells that are formatted with color and text customization etc., and then we will paste the values into a range of cells that is formatted plainly / has no formatting applied.
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 used in situations where 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.
Now you know how to easily transfer values from one location to another in your spreadsheet, without affecting the formatting or copying formulas!
Click here to see more Microsoft Excel lessons.
Or check out the Microsoft Excel templates.
