ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

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 imageAUTHOR

      Robbie C Wilson 

      4 years ago

      Hi Michael,

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

    • profile image

      Michael Leng 

      4 years ago

      Thanks for this beneficial tutorial. I like it.

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://hubpages.com/privacy-policy#gdpr

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)