• Excel Book Excerpt

Excel A Faster Way To Paste Special

This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

A Faster Way To Paste Special

Problem: What’s up with the Office 2010 paste command? What do all of those icons mean?

Strategy: The new Paste Options menu could be one of the biggest time savers in your hour-to-hour use of Excel.

Microsoft took a look at data to see which command is the most frequently undone command. It turns out that it was Paste. Yes, Paste seems simple enough, but there are myriad Paste Special options, and many people were confused about which elements of the copied cells would get pasted.

Even if you are an absolute pro at using Paste Special, you are going to love the new Paste Options menu because it will let you perform tasks that you perform several times an hour with far fewer keystrokes.

Do a normal copy and paste. As in Excel 2003, a little Clipboard appears on screen. This Clipboard has been in Excel for a while, but because I had to grab the mouse to open the drop-down, I never used that menu.

LE10000045.jpg

Figure 81 Press Ctrl after doing a paste to open this menu.

In Excel 2010, the little Clipboard icon also indicates that you can open the drop-down by pressing the Ctrl key one more time.

When you press Ctrl again, you see a series of icons that let you change the type of paste that you just performed. For example, you could click the first icon in the third row to paste as values.

Furthermore, if you hover over that icon, you will learn that pressing V is the shortcut key for pasting values.

So, if you are a keyboard person, you might find yourself doing the following:

  • Ctrl+C to copy
  • Ctrl+V to paste
  • Ctrl to open the Paste Options dialog
  • V to change the paste to values

For keyboard-centric people, this is a pretty fast way to convert formulas to values.

If you are a mouse person, try this amazing trick: hold down the shift key while you drag the border of a selection. When you release the mouse, choose Copy Here as Values Only from the menu that appears.

Additional Details: If you usually paste by using the right-click menu, you will see that the right-click menu includes icons for Paste, Paste Values, Paste Formulas, Transpose, Paste Formats, and Create Links.

06Fig02.jpg

Figure 82 The right-click menu offers 6 options.

When you hover over one of those icons, the rest of the context menu disappears so that you can see the effect of the paste in Live Preview.

If you hover over Paste Special in the right-click menu, the menu will disappear and you have access to all 15 icons.

If you regularly use the Paste icon in the Home tab, the drop-down at the bottom of the tab now leads to a menu with the 15 icons. There are still some options in Paste Special that are not available in the icons. You can access those commands by using the Paste Special menu item at the bottom of this figure.

Many times each day, I convert formulas to values by using Ctrl+C, Alt+E+S+V, Enter.

Sometimes, I need to copy values and formats to a new place. In the new place, I have to do Alt+E+S+V, Enter, Alt+E+S+T+Enter.

As mentioned previously, you can use Ctrl+C, Ctrl+V, Ctrl, V to change formulas to value. To paste values and formats, you now use the new Ctrl+V, Ctrl, E to paste values and number formatting.

LE10000046.jpg

Figure 83 The paste dropdown in Excel 2010 offers the icons.t

There might be an even faster way. This key is the right-click key. Use Ctrl+C, Right-Click Key, V to paste as values.

LE10000047.jpg  

If you need to paste values and formats, the Right-Click, E keys will do it.

The one complaint that I have heard about the Paste Options menu is that it is tough to figure out what the icons mean. Photocopy Figure 84 and hang it up by your desk. I used a similar image next to my computer for the first month to remind myself to start using the new shortcuts.

06Fig06.jpg

Figure 84 Shortcut keys in Paste Options menu.

The following list describes each of the 15 items in the Paste Options menu:

  • Paste is a regular paste. You get formulas, borders, and formats.
  • Formulas pastes the formulas. It will not change formatting.
  • Formulas & Formatting will paste the formulas and any numeric formatting. Borders, comments, and fills are not pasted.
  • Keep Source Formatting is similar to a regular paste.
  • No Borders pastes everything except for the borders.
  • Column Widths copies the column widths from the source range.
  • Transpose turns data sideways. Rows become columns.
  • Merge Conditional Format is for the few people who liked a default behavior in Excel 2007. Conditional formatting now consists of Icon Sets, Data Bars, 2-Color Color Scales, 3-Color Color Scales, and the traditional conditional formatting rules. Something new in Excel 2007 is the ability to apply two different conditional formats to the same range. For example, you might combine an icon set and a traditional formatting rule. That makes sense. Unfortunately, a lot of people would inadvertently combine a two-color color scale and a three-color color scale, leading to a lot of brownish-colored cells. In Excel 2010, when you paste, the conditional formats in the source range will overwrite the conditional formats in the target range. If you know what you are doing and you want the conditional formatting from both the source and target range to stay, use this icon. Note that you won’t see this icon unless the source range had conditional formatting.
  • Values eliminates the formulas and paste their current values.
  • Values & Number Format converts formulas to values, but brings along any numeric formatting applied to the source range.
  • Values & Formatting converts formulas to values, but brings along the cell formatting, too.
  • Formatting pastes only the formats.
  • Paste Link will create formulas in the pasted range that point back to the source range.
  • Static Picture pastes a picture of the copied range. This picture might include cells, SmartArt, charts, and so on. When the original range changes, this picture does not change.
  • Linked Picture pastes a live picture of the copied range. When something changes in the original range, the picture reflects that change. This used to be called the Camera Tool in Excel 2003.

<-Previous Topic Next Topic->

For more resources for Microsoft Excel: