It’s so common that almost everyone knows the keyboard shortcuts to copy Ctrl + C and to paste Ctrl + V. When using this in Excel, it will copy everything including values, formulas, formatting, comments/notes, and data validation. 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
The example data used in this post contains various formatting.
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. To use the paste special keyboard shortcut. This will paste your data without any formatting, formulas, comments/notes, or data validation. Nothing but the values will be there.
Paste Special Legacy Keyboard Shortcut
This keyboard shortcut is a legacy shortcut from before the Excel ribbon command existed and it’s still usable. In fact, when you try and use this you’ll be greeted with the above warning to let you know this is from an earlier version of Microsoft Office. When you have a range of data copied to your clipboard, you can open up the Paste Special menu by pressing Alt + E + S on your keyboard. Once the Paste Special menu is open you can then press V for Values. One advantage the legacy shortcut has is it can easily be performed with one hand!
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. To use Paste Values from the ribbon. The cool thing about this menu, is before you click on any of the command you will see a preview of the data you’re about to paste. This makes it easy to ensure you’re selecting the right option.
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. Pressing Alt ➜ H ➜ V ➜ V will activate the paste values command.
Paste Values from Right Click Menu
Paste values is also available from the right click menu. Copy the range of cells you want to paste as values ➜ right click ➜ select the paste values clipboard icon.
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? This way it’s only a click away at all times! 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. You can add the paste values command from the Excel Options screen. The command will now be in your quick access toolbar! 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
There’s a mouse option you can use to copy as values which most people don’t know about. 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. When you do a regular copy and paste, a small icon will appear in the bottom right corner of the pasted range. It will remain there until you interact with something else in your spreadsheet. These are the paste options and you can click on it or press Ctrl to expand the options menu. When you open the menu, you can then either click on the Values icon or press V to change the range into values only.
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.It will remove the formatting, comments, notes and data validation.
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. This will open up the Convert Text to Column Wizard. In the first step you can select Delimited and press the Next button. You can also select Fixed width as we won’t be using the text to column functionality it doesn’t really matter. In the next step, remove any selected delimiters and press the Next button. In the last step, select the destination cell for the output and press the Finish button. You can see the results have all the formatting gone but any formulas still remain.
Paste Values with Advanced Filters
This one is another not-quite paste values option and is listed for fun as well. It will remove any formulas, comments, notes and data validation but will leave all cell formatting. With your data selected go to the Data tab then select the Advanced command in the Sort and Filter section. From the Advanced Filter Menu. This will create a copy of the data as values and remove any formulas, comments, notes and data validation. 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?
