What Is the Keyboard Shortcut to Paste Range Names
9 Ways to Copy and Paste as Values in Excel
Copying and pasting is a very frequently performed action when working on a figurer. This is as well true in Excel.
Information technology'southward and then common that about 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 exist frustrating as sometimes you'll merely want the values to re-create and not any of the other stuff in the cells.
In this post, y'all'll learn eight means to copy and paste merely the values from your Excel information.
Example Information
The case information used in this post contains diverse formatting.
- Cell formatting including font colour, make full color, number formatting and borders.
- Notes.
- SUM formula.
- A data validation dropdown list.
Paste Special Keyboard Shortcut
If you desire 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 employ this is with a keyboard shortcut.
To employ the paste special keyboard shortcut.
- Copy the data you desire to paste as values into your clipboard.
- Choose a new location in your workbook to paste the values into.
- Printing Ctrl + Alt + V on your keyboard to open up upward the Paste Special menu.
- Select Values from the Paste pick or press V on your keyboard.
- Press the OK button.
This volition paste your data without any formatting, formulas, comments/notes, or data validation. Nothing but the values will exist there.
Paste Special Legacy Keyboard Shortcut
This keyboard shortcut is a legacy shortcut from earlier the Excel ribbon command existed and it's still usable.
In fact, when you try and employ this you'll be greeted with the above warning to let you know this is from an earlier version of Microsoft Office.
When yous take a range of data copied to your clipboard, yous can open up up the Paste Special menu by pressing Alt + Eastward + S on your keyboard.
In one case the Paste Special carte is open you tin can then press Five for Values.
One reward the legacy shortcut has is it can easily be performed with one hand!
Paste Special from the Dwelling Tab
If you're not a keyboard person and prefer using the mouse, and then y'all can access the Paste Values control from the ribbon commands.
To use Paste Values from the ribbon.
- 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 Dwelling house tab.
- Click on the lower part of the Paste button in the clipboard section.
- Select the Values clipboard icon from the paste options.
The cool affair about this carte, is before you click on any of the command you will meet a preview of the data you're nigh 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 admission it with the Alt hotkeys.
Detect when you lot printing the Alt key, the ribbon lights upward with all the accelerator keys available.
Pressing Alt ➜ H ➜ V ➜ V will activate the paste values command.
Paste Values from Right Click Bill of fare
Paste values is also available from the correct click bill of fare.
Copy the range of cells y'all want to paste as values ➜ correct click ➜ select the paste values clipboard icon.
Paste Values with Quick Access Toolbar Command
If information technology's a command y'all apply quite frequently, and so why not put it in the quick admission toolbar?
This way it's but a click away at all times!
Depending where in the quick access toolbar you identify it, it will likewise get it's own easy to use Alt hotkey shortcut besides.
Check out this post for details on how to add commands to the quick access toolbar, or this mail on other interesting commands you can add to the quick access toolbar.
You tin add the paste values command from the Excel Options screen.
- Select All Commands from the drib down listing.
- Locate and select Paste Values from the options. You lot can press P on your keyboard to quickly navigate to commands starting with P.
- Press the Add together button.
- Use the Up and Downwardly arrows to change the ordering of commands in your toolbar.
- Press the OK button.
The control will now be in your quick access toolbar!
If you identify it in the 4th position like in this Example, then you can you lot Alt + four to access it with a keyboard shortcut.
Paste Values Mouse Pull a fast one on
There'due south a mouse option you can use to copy every bit values which most people don't know about.
- Select the range of cells to copy.
- Hover the mouse over the agile range border until the cursor turns into a 4 directional arrow.
- Right click and drag the range to a new location.
- When you release the right click, a card volition popular up.
- Select Copy Hither as Values Merely from the carte du jour.
This is such a dandy way, and there are a few other options in this subconscious menu that are worth exploring.
Paste Values with Paste Options
There'due south another sneaky method to paste values.
When you practise a regular re-create and paste, a small icon will appear in the bottom right corner of the pasted range. Information technology will remain in that location until you collaborate with something else in your spreadsheet.
These are the paste options and you can click on it or press Ctrl to aggrandize the options carte du jour.
When you open the card, you tin can and so either click on the Values icon or press Five to modify the range into values merely.
Paste Values and Formulas with Text to Columns
I don't really recommend using this method, just I'm going add information technology just for fun.
A few caveats with this method.
- You tin can only copy and paste 1 column of data.
- It will keep whatsoever formulas.
- It volition remove the formatting, comments, notes and data validation.
If that's exactly what you're looking for, then this method might exist of interest.
Select a single column of data ➜ become to the Data tab ➜ select the Text to Cavalcade command.
This will open up the Catechumen Text to Column Sorcerer. In the first step you can select Delimited and press the Side by side push button.
Yous tin besides select Fixed width equally we won't be using the text to column functionality it doesn't really affair.
In the next pace, remove any selected delimiters and press the Next button.
In the final step, select the destination cell for the output and press the Finish push.
You can run into the results have all the formatting gone but any formulas still remain.
Paste Values with Advanced Filters
This one is another non-quite paste values selection and is listed for fun as well.
It will remove any formulas, comments, notes and data validation but will leave all prison cell formatting.
With your data selected go to the Information tab and so select the Avant-garde command in the Sort and Filter department.
From the Avant-garde Filter Menu.
- Select Copy to another location.
- Go out the Criteria range empty.
- Select a location to place the copied data.
- Printing the OK push button.
This will create a re-create of the information as values and remove any formulas, comments, notes and information validation.
You tin can then remove the cell formatting that'southward left past going to the Habitation 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.
Y'all're eventually going to need to do this and there are quite a few ways to go this done.
What's your favourite way? Did I miss any methods you utilise?
Nearly the Author
John is a Microsoft MVP and freelance consultant and trainer specializing in Excel, Power BI, Power Automate, Power Apps and SharePoint. Y'all can find other interesting manufactures from John on his blog or YouTube channel.
Source: https://www.howtoexcel.org/copy-paste-values/
0 Response to "What Is the Keyboard Shortcut to Paste Range Names"
Post a Comment