During my recent High Impact Excel: Macro Edition webinar—which we've posted online for free viewing—an attendee asked if it’s possible to use the Macro Recorder to automate Excel’s Paste Special Values feature. I replied that yes, that is possible, but that there are other alternatives that are likely more suitable than creating a macro. As with many aspects of Excel, useful keyboard shortcuts and icons lurk in the periphery, out of sight from most users. In this article I’ll demonstrate a couple of ways that you can streamline Paste Special Values actions without relying on a macro.
Virtually every spreadsheet user is familiar with Excel’s Paste command, which allows you to paste one or more cells that you’ve copied to the clipboard. For instance, Figure 1 shows a simple payroll calculation. If you wanted to duplicate this section of the spreadsheet, say for a new week, you could click on cell A1, press Ctrl-A to copy the contiguous cells, then press Ctrl-C to copy the data. Then, in any version of Excel, move your cursor to a new cell, such as A7, and carry out any of these actions to paste:
- 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.
Figure 1: Copy and paste the both the formulas and values to another part of your worksheet.
In this case Excel will duplicate both the formulas and values into the block of cells anchored by cell A7. Let’s say, though, that you want to store a picture of the data, meaning just the totals, as illustrated in Figure 2. To do so, you’d still copy the data to the clipboard and move to a new cell, such as F7. Next, you would click the arrow beneath the Paste button and then do the following depending on your version of Excel:
- 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 dialog 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 dialog box.
You can also sse ribbon keyboard shortcuts in Excel 2007 and later:
- 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
Figure 2: Copy and paste the values to another part of your worksheet.
Now, rather than relying on that panoply of techniques, consider my favorite approach instead, which utilizes the Quick Access Toolbar in Excel 2007 and later:
- Click the arrow at the end of the Quick Access Toolbar, and then choose More Commands. Alternatively, 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 shown in Figure 3, you can double-click on any or all of these three icons to 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 colors, and so on from the cells you copied from.
- Click OK to close the Excel Options dialog 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.
Figure 3: Add the three Paste Special Values features to your 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.
About the author:
David H. Ringstrom, CPA, heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at firstname.lastname@example.org or follow him on Twitter. David speaks at conferences about Microsoft Excel, teaches webcasts for CPE Link, and writes freelance articles on Excel for AccountingWEB, Going Concern, et.al.