Software

Bypass Excel's Paste Special command when pasting literal values

Replacing formulas and functions with literal values takes several clicks. There's a quicker way if you know the right click (literally)!

Excel has a built-in command that lets you replace formulas with literal values. It's a simple process, but it requires several steps:

  1. Select the range of formulas.
  2. Press [Ctrl]+C to copy the selected cells to the Clipboard.
  3. From the Edit menu, choose Paste Special.
  4. In the resulting Paste Special dialog box, click Values in the Paste section. In Excel 2007, choose Paste Values from the Paste control's drop-down list. You'll find this control in the Clipboard group on the Home tab.

  5. Click OK, and Excel will replace the formulas in the selected range with their respective results (literal values).

Would you be surprised to learn that there's a quicker method? You can achieve the same results with two clicks and a drag! But first, let's explore the origin of this trick, which results from copying or moving a range using a right-click.

The sheet below shows a copy task in progress. Specifically, I selected A1:D7, right-clicked the right border, dragged it to column E, and released the mouse button. In response, Excel displayed a context menu.

You can take advantage of this functionality to replace formulas with literal values, as follows:

  1. Select the range in question (A1:D7).
  2. Right-click a border, drag it to the next column (or row), and then drag it back. Doing so forces Excel to display the list of commands without actually moving the selected values.
  3. From the resulting context menu, choose Copy Here As Values Only.

Two clicks and a quick drag and Excel will replace selected formulas with literal values. The trick is just remembering that the solution is just a right-click away.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks