The first thing to note is that the options are specific to the type of item you are pasting. If you attempt to paste in a picture then you will have options relating to the image type only. Things get more complicated when pasting in something from Word since you can paste as a word document, text or as an image. These are the options I will focus on. In Word, select a piece of text and click Ctrl+C to follow the article. Paste Special can be accessed with right click or from the ribbon by clicking the small triangle under the Paste button.
When selecting Paste Special, the user will be presented with several options. The first is to paste as a Word Document. This will insert a small instance of Word into your Excel sheet with whatever you copied into your clipboard neatly displayed. The size can be adjusted and formatting options can then be applied. If you double click on it, an instance of Word will open with your copied item open and ready to be used.
There is also the option to add a word icon rather than having the pasted item directly in the spreadsheet. Just click the "Display as icon" checkbox option. This means nothing will be shown in Excel except for the Word icon which the user will have to double click in order to open Word and see the copied text. This option is most useful if you want to copy something from Word and keep the Word formatting.
The next option is to display as a "Picture (Enhanced Metafile)" which will display the text as an image. If you select the text you cannot alter it and double clicking the image will simply present the image options on the ribbon. In very basic terms the enhanced metafile part is just a type of image Windows uses and means it should print easily. Paste with this option if you do not need or want the user to alter or adjust the pasted item detail.
The next option is to insert as html. This does not mean that Excel will magically turn your text into html code or a web link. This pasting option is not a way of checking your pasted text as html or validating the code. It means that if you have copied a section of html code, which will include brackets and tags etc, then it will paste it in the correct format. Otherwise Excel may simply read the html as a series of instructions and implement them. You will see the results of the html rather than the code itself, just as when you view a web page you would see the resulting page rather than the html code itself. For example, paste the following code into Excel using a simple Ctrl+V:
What should happen is that Excel will read the html and implement the different size headings as if you are giving it html format instructions. Pasting as html will simply place the actual html code into the document as it is written above.
In a similar way, pasting as Unicode will not magically translate to Unicode, but will allow you to insert the Unicode rather than have Excel mess about with it upon pasting. Unicode enables the user to use computers in any language with particular emphasis on names and places. It does this by giving every character a number which is the same no matter which language or programme being used. This industry standard means everyone can use the same thing which prevents conflict and misunderstandings between platforms and programmes used in different countries and by people speaking different languages.
Pasting as text can be a useful tool if you are copying anything that has lots of formatting that will be unnecessary in the Excel sheet. The original text could be using all sorts of fonts, colours, sizes and a range of other formatting options, but Excel will strip it out and simply paste the text. If you choose instead to paste without using this option, then Excel will attempt to bring across as much of the original formatting as possible and you may have extra steps to remove it manually.
Inserting as a hyperlink is useful if you are attempting to copy a web link to Excel and need the user to be able to enter the web page from the Excel document. Using this method will keep the format of a web link as well as the URL behind it. This means that when the user clicks it, they will automatically be directed to the specified web page.
For all these options, you can also 'paste link' instead of a simple paste. Instead of inserting a static word document or image, this will create a direct link from whatever you are pasting from, for example Word to your Excel document. Changes that are made in the original file will be immediately picked up and changed dynamically in your Excel sheet. This is only necessary when pasting things that are likely to change frequently. For example, if you were pasting a report which is updated daily then this option may be useful.
As discussed there are many intuitive ways to Paste into an Excel sheet. Someone has thought long and hard about how and what the user might want to paste. If you are pasting then it is well worth becoming familiar with the options to save yourself time and to ensure you are using the best option.