Starting in Excel 2007, Microsoft started offering the Paste Values command as a shorter method than using the full Paste Special dialog. In Excel 2010, 13 other shortcuts were added (see Figure 1). When you click the bottom half of the Paste icon, a dropdown appears with choices for Paste Formulas, Paste Transpose, Paste Links, and more. For those of you who prefer to right-click, the commands shown in blue in Figure 1 are available as icons in the right-click menu.
With 15 ways to paste available through the Paste icon, it’s possible that many people using Excel have abandoned or forgotten the Paste Special dialog. While Microsoft brought the most popular choices from the dialog to the ribbon, there are other powerful commands still available in the Paste Special dialog.
PASTE SPECIAL OPERATIONS
Let’s say that you needed to reduce the units shown in the Safety Stock column of Figure 2 by a quantity of three. Follow these steps:
- Select any blank cell in your worksheet and enter the number to be subtracted. In this case, you would enter a 3, as shown in cell D2 of Figure 2.
- Copy that cell, either by using the Ctrl+C shortcut or the Copy icon found on the Home tab or the right-click menu.
- Select the first Safety Stock cell (C5). Press Ctrl+Shift+Down Arrow to select to the bottom of the data.
- Click the bottom half of the Paste icon and choose Paste Values to open the dialog.
- In the Paste Values dialog, select Values in the Paste section and Subtract in the Operation section. Click OK. Excel will subtract 3 from each cell in the selection.
One subtle click in those steps was choosing Values in the top part of the Paste Special dialog. This step is necessary to make sure that you don’t lose any formatting in the paste range. In Figure 2, the numbers are formatted to show commas as a thousands separator. And while not visible, there’s conditional formatting to highlight any time that the Safety Stock is less than the OH (On Hand) column. If you forget to choose Values, you’ll be pasting the formats from the unformatted cell D2, and that will clear out the thousands separator and the conditional formatting.
Here’s a video of Paste Special Subtract:
THE SOUND OF AUDITORS CRINGING
If you regularly audit spreadsheets, I know you’re cringing at this article so far. After all, using Paste Special is risky if not done correctly, and you lose the audit trail. Someone could easily enter -3 in the copied cell and then choose Subtract from the Operation section, which would add 3 to each cell instead of subtracting.
I agree that the operation is risky. You have to use it with care. The alternate steps of inserting a formula, copying the formula and pasting as values give you a visual look at the results of the formula before you paste as values. If you had made an error in the formula, you have a few extra steps to realize that you’ve made a mistake before you lose the audit trail of the formula.
OTHER FEATURES
There are additional operations that you can use aside from subtracting. Something with a safety stock of 6 would lose half the safety stock if all the values in the column were reduced by 3, but something with a safety stock of 333 would lose less than 1% of the safety stock. Thus, it might make more sense to reduce all safety stock levels by a percentage, such as 2%. You could do this by copying 0.98 and then using Paste Special Multiply.
It also works with cells that contain formulas. Perhaps your safety stock is calculated as =ROUND(B5/24,0). If you use Paste Special Multiply to multiply all of those cells by 0.98, the formulas will change to be =ROUND(B5/24,0)*.98.
Finally, below the Operations section of the dialog is a checkbox for Skip Blanks. Say that you have a price list with prices in column C. Your manager has typed a new price in 10% of the cells in column D. Most of column D is blank. You only want to copy the nonblank values from D to C. Do that by checking the Skip Blanks box.
See this video on Skip Blanks:
In Figure 4, the new price of $35.95 from C4 will overwrite the $34.95 in B4.
Similar replacements will happen in rows 8 and 12. But for all of the rows where column D is blank, the original price will remain unchanged.
SF SAYS
The Paste Special dialog is home to some obscure but powerful tools.
July 2017
