ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel
  • »
  • Technology»
  • Computers & Software»
  • Computer Software»
  • Office Software Suites»
  • Microsoft Office

Guide to the changes to Paste and Paste Special and the new Live Preview feature in Excel 2010

Updated on November 25, 2013

How to use the new Paste Special buttons and live preview while pasting in Excel 2010

Hi and welcome to my latest hub on Excel 2010. Copying and pasting is probably the thing we all do most frequently when working with data in Excel. When using Excel 2010 for the first time, one of the most striking changes from previous versions you will notice is a difference in the way that you use Copy and Paste as well as Copy and Paste Special.

The new Paste Special features live preview which shows you exactly how your data will look, should you decide to click a button, when you hover over it. You can continue to hover over buttons until you find the one that fits exactly what you need your data to look like. Excel 2010 has a wide array of buttons to choose from.

Among the changes that Microsoft has introduced in Excel 2010 to Paste and Paste Special include:

  • Paste in Excel 2007 is now called Paste Options in Excel 2010 and has been expanded

Paste in Excel 2007 (left) and Paste Options in Excel 2010 (right).
Paste in Excel 2007 (left) and Paste Options in Excel 2010 (right). | Source
  • Paste Special has not only been expanded in Excel 2010, but it has moved on from check boxes in Excel 2007 to buttons in Excel 2010

Paste Special in Excel 2007 (left) used check boxes, while in Excel 2010, (right) buttons have been introduced.
Paste Special in Excel 2007 (left) used check boxes, while in Excel 2010, (right) buttons have been introduced. | Source
  • When you hover over a button when using Paste or Paste Special, Excel 2010 will make the menu transparent and also show you how the cell will appear after you paste it before you press the button. You can see below how it looks when I hover over the Transpose button.

Live Preview in Excel 2010 makes the Paste Special menu transparent and also allows you to see how the paste will look before you commit to it.
Live Preview in Excel 2010 makes the Paste Special menu transparent and also allows you to see how the paste will look before you commit to it. | Source
When pasting, Excel 2010 shows the Clipboard icon to allow you easy access to additional Paste Special options.
When pasting, Excel 2010 shows the Clipboard icon to allow you easy access to additional Paste Special options. | Source
  • When using the keyboard shortcut for Paste (Control – V) in Excel 2010, Excel will append a clipboard icon to the bottom of the cells you are pasting so that you can select additional Paste Options should you wish to.






There are a number of changes to Paste Special including some new additions:

  • Picture
  • Linked Picture
  • Value and Source Formatting

Some options available in Excel 2007 have been moved to a sub menu and do not have a button in Excel 2010.

  • Comments
  • Validation
  • Skip Blanks
  • Operations such as add, subtract, multiply and divide

Paste Options buttons in Excel 2010

When you copy data by right clicking and selecting Paste (or when you select the clipboard after using Control – V) you are offered six buttons:

The six buttons available when selecting paste in Excel 2010.
The six buttons available when selecting paste in Excel 2010. | Source
  • The first is Paste which simply pastes the cells with all the cells formatting kept intact
  • Values pastes just the results of any formulas or the cells values themselves again without any formatting
  • Formulas copies any formulas rather than values minus formatting
  • The Transpose button will transpose the data and paste the cell formatting as well
  • Formatting will copy the formatting to the pasted cells but not the values or formulas
  • Paste Link will allow you to copy a hyperlink. If you don’t have a hyperlink the result will be the same as Values

You can see below the result of copying the cells and using the various buttons available. The cells all contain formulas rather than simple numbers. So for example, cell C3 contains =A3-A4 and C4 equals A4-A5 and so on:

Results of the various Paste Options in Excel 2010.
Results of the various Paste Options in Excel 2010. | Source

Paste Special buttons in Excel 2010

Alongside the six buttons for Paste, Excel 2010 adds a number of additional buttons when you select Paste Special. They are grouped into three groups.


New Paste Special button in Excel 2010.
New Paste Special button in Excel 2010. | Source

The first group Paste contains the following extra buttons:

  • The first new button is Formulas and Number Formatting. This will copy the formulas rather than the values as well as any number formatting (right click, select Format Cells and in the Number Category you have a variety of options available) but not any cell formatting
  • Keep Source Formatting is the next button. This copies the cells contents or formulas and keeps the formatting of the copied cells at the same time
  • No Borders retains the source formatting and copies the formulas or cell contents, except any cell borders
  • Keep Source Column Widths works n a very similar way as No Borders, except it keeps column widths rather than suppressing borders

The second group is Values:

  • First, we have Value and Number Formatting which copies the values and any number formatting you have used, but discards any cell formatting
  • Value and Source Formatting, copies the values and source formatting as well as number formatting

The final group is Other Paste Options

  • The first button is used to paste a Picture and the second button is used to paste a Linked Picture.

You can see the results of pasting using the buttons from the first two groups below (from left to right, using Formulas and Number Formatting, Keep Source Formatting, No Borders, Keep Column Widths, Value and Number Formatting and finally Value and Source Formatting).

Results of using the new Paste Special buttons in Excel 2010.
Results of using the new Paste Special buttons in Excel 2010. | Source

Accessing the Excel 2007 Paste Special options in Excel 2010

If you prefer the check boxes from Excel 2007, you can still access it in Excel 2010. It is worth noting that Excel 2010 does not have buttons for:

  • Operations (Add, Subtract, Multiply and Divide)
  • Skip Blanks

To access the Excel 2007 style menu for Paste Special:

  • Right click with a cell or cells copied and select Paste Special
  • At the bottom of the Paste dialogue box that opens, select Paste Special once more
  • Now you can select an option as if you were using Excel 2007.

The Excel 2007 Paste Special check boxes are still available in Excel 2010.
The Excel 2007 Paste Special check boxes are still available in Excel 2010. | Source

Conclusion

Excel 2010 has introduced a number of cool changes to Paste and Paste Special including:

  • Buttons that allow live preview of how your data will look after it is pasted before you commit to actually paste it
  • The buttons become virtually transparent during live preview
  • When using paste, a clipboard icon at the bottom of the recently pasted cells allows you quick and easy access to the paste special buttons should you want to change the pasted cells using one of the buttons

In addition to all the changes, Microsoft has kept the Excel 2007 Paste Special check box which is a nice touch as it allows those who prefer that method to continue to do so. I hope that you have enjoyed reading this hub as much as I enjoyed writing it and that you found it useful and informative. Please feel free to leave a comment below and thanks again for reading.

Comments

    0 of 8192 characters used
    Post Comment

    • profile image
      Author

      Robbie C Wilson 3 years ago

      Hi Michael,

      Thanks for your kind comment. Glad that you found my hub useful and informative.

    • profile image

      Michael Leng 3 years ago

      Thanks for this beneficial tutorial. I like it.