Copying and pasting is a very frequently performed action when working on a computer. This is also true in Excel.
It’s so common that almost everyone knows the keyboard shortcuts to copy Ctrl + C and to paste Ctrl + V.
This can be frustrating as sometimes you’ll only want the values to copy and not any of the other stuff in the cells.
In this post, you’ll learn 8 ways to copy and paste only the values from your Excel data.
Example Data
Cell formatting including font colour, fill colour, number formatting and borders.
Notes.
SUM formula.
A data validation dropdown list.
Paste Special Keyboard Shortcut
If you want to copy and paste anything other than an exact copy, then you’re going to need to become familiar with paste special.
My favourite method to use this is with a keyboard shortcut.
Copy the data you want to paste as values into your clipboard.
Choose a new location in your workbook to paste the values into.
Press Ctrl + Alt + V on your keyboard to open up the Paste Special menu.
Select Values from the Paste option or press V on your keyboard.
Press the OK button.
Paste Special Legacy Keyboard Shortcut
This keyboard shortcut is a legacy shortcut from before the Excel ribbon command existed and it’s still usable.
Once the Paste Special menu is open you can then press V for Values.
Paste Special from the Home Tab
If you’re not a keyboard person and prefer using the mouse, then you can access the Paste Values command from the ribbon commands.
Select and copy the data you want to paste into your clipboard.
Select the cell you want to copy the values into.
Go to the Home tab.
Select the Values clipboard icon from the paste options.
Paste Values with Hotkey Shortcuts
Since the paste values command is in the ribbon, that also means you can access it with the Alt hotkeys.
Notice when you press the Alt key, the ribbon lights up with all the accelerator keys available.
Paste Values with Quick Access Toolbar Command
If it’s a command you use quite frequently, then why not put it in the quick access toolbar?
Depending where in the quick access toolbar you place it, it will also get it’s own easy to use Alt hotkey shortcut too.
Check out this post for details on how to add commands to the quick access toolbar, or this post on other interesting commands you can add to the quick access toolbar.
Select All Commands from the drop down list.
Locate and select Paste Values from the options. You can press P on your keyboard to quickly navigate to commands starting with P.
Press the Add button.
Use the Up and Down arrows to change the ordering of commands in your toolbar.
Press the OK button.
If you place it in the 4th position like in this Example, then you can you Alt + 4 to access it with a keyboard shortcut.
Paste Values Mouse Trick
Select the range of cells to copy.
Hover the mouse over the active range border until the cursor turns into a four directional arrow.
Select Copy Here as Values Only from the menu.
This is such a neat way, and there are a few other options in this hidden menu that are worth exploring.
Paste Values with Paste Options
There’s another sneaky method to paste values.
Paste Values and Formulas with Text to Columns
I don’t really recommend using this method, but I’m going add it just for fun.
A few caveats with this method.
You can only copy and paste one column of data.
It will keep any formulas.
If that’s exactly what you’re looking for, then this method might be of interest.
Select a single column of data ➜ go to the Data tab ➜ select the Text to Column command.
You can also select Fixed width as we won’t be using the text to column functionality it doesn’t really matter.
Paste Values with Advanced Filters
This one is another not-quite paste values option and is listed for fun as well.
With your data selected go to the Data tab then select the Advanced command in the Sort and Filter section.
Select Copy to another location.
Leave the Criteria range empty.
Select a location to place the copied data.
Press the OK button.
You can then remove the cell formatting that’s left by going to the Home tab ➜ Clear ➜ and selecting the Clear Formats option.
Conclusions
Wow! That’s a lot of different ways to paste data as values in Excel.
It’s understandable there are so many options given it’s an essential action to avoid carrying over unwanted formatting.
You’re eventually going to need to do this and there are quite a few ways to get this done.
What’s your favourite way? Did I miss any methods you use?