Paste Special – Excel’s Very Special!
If you are one of the regular readers of our Excel articles, you must have seen paste special many times in action. This feature catapults pasting to a whole new level. Follow along as I discuss each and ever thing it can do while doing copy-paste!
12. Paste special – Copy paste just values
Normally when you copy and paste the cell Excel copy and paste everything including text color, cell color, number formatting, formula and everything else. However, if you only want to copy and paste the value and leave everything behind then following these steps:
Step 1: Select the data using mouse or keyboard and copy it
Step 2: Paste the data where you like. The moment you paste the data, you can see Ctrl smart-tag or smart-menu. Click on it and from paste value group select the first option to paste just values and leave everything behind.
Alternative#1 to Step 2
Once the data is copied then go to cell where you want to paste the data and then go to Home tab > Clipboard group > instead of pressing paste button click the bottom half of button i.e. drop-down button > And from paste values sub-group select the first option i.e. Values.
Alternative#2 to step 2
Once the data is copied go to cell where you want to paste the data. Press right mouse button and you get several options in place of paste. Select the one that says “123” and you will paste only values.
Alternative #3 to step 2
Once the data is copied go to cell where you want to paste the data. Hit Alt+Ctrl+V (yes! this is paste-special shortcut) and this will bring paste special dialogue box. From paste group select values and click OK. And this will paste just the values from the copied cells.
13. Paste special – copy paste just formats
Sometimes instead of pasting values you want to copy and paste the format settings. Format includes, the text style, size, text color, fill color, number format setting etc. To do this simply copy and hit Alt+Ctrl+V and from the dialogue box select formats radio button and click OK. This will skip the values from copied cell and paste the format rules to the destination cell. This option is good if you already have a certain data in destination cell and you want only format settings to be pasted.
14. Bonus tip – Format painter
To copy and paste just the format settings, Excel comes with dedicated feature called format painter. This basically copies the format settings of one cell and apply/paste/paint them to specific cell or cells.
To copy the format settings of a single cell or multiple cells simply select them and then go to Home tab > clipboard group click format painter button and then go to destination cell(s) and hit format painter button again to paste the format settings.