Paste Smart Tag

The Paste Options smart tag will not appear unless you have chosen to display the Paste Options buttons.
Check your (Excel Options)(Advanced tab, Show Paste Options buttons when content is pasted).

Displaying the Smart Tag

This will appear whenever you paste anything into a cell.
The drop-down menu will appear in the bottom right corner of the cell.

Paste - (P) (Paste Special, All). Pastes the value (or formula) with the number format. Any formatting that matches the theme colours.
If you are copying and pasting within the same workbook then all the formatting is the same.
If you are copying and pasting from a different workbook that has the same theme colours then all the formatting is the same.
If you are copying and pasting from a different workbook that has different theme colours then the index position is maintained but the colours will be different.
Formulas - (F) (Paste Special, Formulas). Pastes the value (or formula) without the number format. Any formatting ???
Formulas & Number Formatting - (O) (Paste Special, Formulas and Number Formats). Pastes the value (or formula) with the number format. Any other formatting is not copied across.
Formulas & Source Formatting - (K). Pastes the value (or formula) with the number format. The theme colours from the original are kept intact.
If you are copying and pasting within the same workbook then all the formatting is the same.
If you are copying and pasting from a different workbook that has the same theme colours then all the formatting is the same.
If you are copying and pasting from a different workbook that has different theme colours then ???
Formulas & No Borders - (B) (Paste Special, All except borders). Pastes everything except borders. This is identical to "Formula & Source Formatting" except that all the borders are removed.
Formulas & Keep Source Column Widths - (W). Pastes everything including the column widths. This is identical to "Formula & Source Formatting" except that the column widths are pasted as well.
Transpose - (T) (Paste Special, Transpose). Pastes the cells but with the orientation changed. Rows become columns and columns become rows.

Values - (V) (Paste Special, Values). Pastes the values (or the result from any formulas).
Values & Number Formatting - (A) (Paste Special, Values & Number Formatting).
Values & Source Formatting - (E). This the same as Values except

Formatting - (R ) formatting only
Paste Link - (N) link cells
Picture - (U) Pastes only the pictures from the clipboard.
Linked Picture - (I) Pastes a link to a picture.

Important

The Smart tag will only appear when data has been copied and pasted. It will not appear when data has been cut and pasted.