MrExcel.com - Your One Stop for Excel Tips & Solutions

A Faster Way To Paste Values

September 13, 2021 - by

A Faster Way To Paste Values

Problem: I have a range of formulas that I need to convert to values.

Strategy: Use the Paste Values icon on 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.

Right-click a cell. The top choices are Cut, then Copy, then a row of six Paste Options. The second icon in that row is Paste Values.
Figure 73. 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 prefer to use the mouse, 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.

If you regularly use the Paste icon in the Home tab, the dropdown 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.

People in my seminars tell me to use Alt, H, V, V or to use Ctrl+Alt+V, V.

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. If you don’t have the Right-Click key, use Shift+F10. If you need to paste values and formats, the Right-Click, E keys will do it.

A photograph of a computer keyboard. The Alt key is on the left. The Ctrl key is on the right. A Program key, showing a mouse pointing at a drop-down menu is in the middle. Not all computers have this key. Shift+F10 is the awkward replacement when the key is missing.
Figure 74. The right-click key opens the context menu
If you use the Paste drop-down arrow (located on the bottom half of the Paste icon), there are a series of consfusing icons. The very last menu entry is Paste Special and takes you to the dialog with all of the choices.
Figure 75. The paste dropdown offers the icons.

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 this figure and hang it up by your desk.

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 Formatting allows you to mix two different conditional formats.
  • 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.
Here are the shortcut keys available from the Paste drop-down: P does a regular Paste. F pastes Formulas O does Formulas & Number formatting K is Keep Source Formatting B is Paste but don't copy the borders W is to paste the column Widths T is for Transpose (which means turn sideways) G is only available sometimes and stands for Merge Conditional Format V is Values A is Values And Number Formatting E is Values and Formatting R is Formatting N is Paste Link U is Static Picture I is Linked Picture.
Figure 76. Keys in Paste Options menu.
  • 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.

This article is an excerpt from Power Excel With MrExcel

Title photo by Tristan Boucher on Unsplash