21. Paste Special – Copy paste data validation
Data validation lists are great feature in Excel. However if you like to copy and paste the data validation rules then simple copy/paste does work but it paste the data validation lists with active data in the copied cell. However, if you like to copy/paste only the data validation rules then copy the cell containing data validation rules and go to cell where you like to paste hit Alt+Ctrl+V to open paste special dialogue box and from it select validation and click OK.
Most of the time we copy and paste the data because you want the same data at another place as well. However, some situations require the source and destination cells to have the same data but linked in a manner that if values in source change the values in linked cells should also change. This is possible easily if you put cell references in the destination cells. But for this you have to insert the manually. But you can do this easily using paste special feature to paste link to source cells.
To do this simply copy the cells and go to cells where you like to paste the cells hit Alt+Ctrl+V and from the paste special dialogue box click the paste link button. This will insert the cell reference of the copied cell or cells instead of values in the cells. This way if source cell’s value changes the destination or linked cell will update automatically.
23. Paste Special – Copy paste picture of data range
Ever fancied that you put the picture of selected cells? Having the picture of certain data range is easy to move, rotate and give other picture effects. But for this you really don’t have to go outside Excel’s environment to do print screen and crop picture to fit your needs.
You can do this easily with paste special but somehow this facility is not available in paste special dialogue box (or I am not aware of it) however, you can do this via right click contextual menu or drop down options of paste button in the ribbon under home tab.
Yes pictures are boring if you are a number nerd. Because they are too static without life at all. But we might face a dilemma that we need a picture that updates if the source data range changes. Think that is impossible? Not anymore.
Now you can have linked picture that means you will get a picture however if the source data changes the data in the picture will update too. Believe me thinking that picture can change is really scary for me…
25. Paste special – Skip blanks
In some situations you have data in source data range and destination range as well. Normally when you copy a range that contains blank and paste it then blanks are preserved and if data is copied over another range then blanks existing data will be replaced with blank cells. However, if you use skip blank option while pasting the data then blank cells will not replace existing data value in the destination cell. To understand how skip blank option works watch the following animations