Guide to the changes to Paste and Paste Special and the new Live Preview feature in Excel 2010
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 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
- 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.
- 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:
- 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.
- 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 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:
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.
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).
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.
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.