Excel Tip: How to automate Paste Special

The latest tip from David Ringstrom was inspired by a question in one of his High Impact Excel webinars.

During the High Impact Excel: Macro Edition webinar (now available online for free viewing) one of the participants asked if the Macro Recorder could be used to automate Excel’s Paste Special Values feature.

Yes it can, but there are better ways to do it. As always with Excel, useful keyboard shortcuts and icons lurk just out of sight from most users. This tip illustrates a couple of ways to streamline Paste Special Values actions without using a macro.

Excel’s Paste command is a familiar tool for most Excel users. The image below depats a typical payroll calculation. To duplicate this section of the worksheet for a new week, you would click on cell A1, press Ctrl-A to copy the contiguous cells and press Ctrl-C to copy the data.

To replicate the data starting from cell A7, you could then do any of the following:

  •  Left-click the Paste icon in Excel’s user interface
  • Right-click on cell A7 and choose Paste
  • Press Alt-H-V to use ribbon keyboard shortcuts in Excel 2007 and later
  • Press Ctrl-V in any version of Excel
  • Press the Enter key in any version of Excel, which will simultaneously paste the data and clear the clipboard.

How to copy and paste formulas and values in Excel

Using any of these approaches, Excel will transfer the formulas and values into the newly filled cells adjactent to cell A7. But if you wanted a snapshot based only on the data, but not the formulae, you’d still copy the data to the clipboard using Ctrl+A. To move it to a new cell, F7 in our illustration below, you would click the arrow beneath the Paste button and then choose one of the following techniques, according to the version of Excel you were using:

  • Excel 2010 and later: Choose from the three Paste Special icons, which permit you to paste Values, Values and Number Formatting, or Values and Source Formatting.
  • Excel 2007: Choose Paste Special, and then double-click on Values to skip the OK button.
  • Excel 2003: Choose Edit, Paste Special, and then double-click on Values to skip the OK button.
  • Excel 2007 and later: Press Ctrl-Alt-V to display the Paste Special dialogue box, from which you can press Alt-V, and then press Enter to avoid using your mouse.
  • Any Excel version: Press Alt-E-S to display the Paste Special dialogue box.

There are a number of additional ribbon keyboard shortcuts available in Excel 2007 and later editions:

  • Excel 2007 and later: Alt-H-V-V to Paste as Values
  • Excel 2010 and later: Alt-H-V-A to paste as Values and Number Formatting
  • Excel 2010 and later: Alt-H-V-E to paste Values and Source Formatting

Copy and paste the values to another part of an Excel worksheet.

Instead of trying to remember all those different approaches, it may be easier to rely on our reliable old friend, the Quick Access Toolbar (for users Excel 2007 and later versions):

  • Click the arrow at the end of the Quick Access Toolbar, and then choose More Commands. Or right-click on Excel’s ribbon and choose Customize Quick Access Toolbar.
  • Select Commands Not in the Ribbon from the Choose Commands From list.
  • Click once on Separator (or any icon in the list), and then type the letter Q to be taken to the start of the Q section, but more importantly, the end of the section of commands that start with P.
  • As you can see below, double-clicking on any or all of these three icons will add them to your Quick Access Toolbar:
    1. Paste Values—the classic way of converting formulas to values
    2. Paste Values and Number Formatting—converts formulas to values and applies the same number formatting as the cells you copied from
    3. Paste Values and Source Formatting—converts formulas to values and applies all formatting, including number formats, borders, font colours, and so on from the cells you copied from.
  • Click OK to close the Excel Options dialogue box.
  • To use your new-found functionality, press the Alt key to reveal the numeric keyboard shortcuts for your icons. You can now paste special without resorting to alphabet-soup-like keyboard shortcuts.

Adding Paste Special Values commands to Excel's Quick Access Toolbar.

If you find these Quick Access Toolbar icons useful, then you may want to add shortcuts for “classic” print preview in Excel 2010 and later, freeze panes, and strikethrough.

"Either you work Excel, or it works you!" says David Ringstrom CPA, the head of Atlanta-based software and database consultancy Accounting Advisors. He presents High Impact Excel training webcasts for our US sister site AccountingWEB.com contributes articles on Excel to both AccountingWEB and Microsoft Professional Accountant's Network newsletter. He can be reached by email at david[AT]acctadv.com. More Excel tips from David H Ringstrom available here.