Safe Copy & Paste in Excel (Paste Values Only)

Users of my Excel Check Register spreadsheet occasionally email me to let me know that they're having a tough time moving their data from one Register spreadsheet to another. (For instance, they've run out of room on one spreadsheet, and are beginning another one.)

Inevitably, the blow-ups occur because the default "copy & paste" operation in Excel is very ... umm ... comprehensive. It copies and pastes everything: cell values, formats, formulas (if any), and cell links (if any). Many times, this is not what you want to do.

If all you need to do is move cell values from one worksheet to another, then you need to use a little-known feature of Excel called "Paste Special." Here's a brief run-down on making it happen!

Best Practice: Always, always, ALWAYS make a backup copy of your initial spreadsheet before you start doing in-depth work to it. "In-depth" activities might include deletion of rows or columns, resizing rows or columns, modifying objects (shapes or images), or (you guessed it!) copying and pasting of data from one worksheet or spreadsheet to another. You can't be too careful!

First, you'll want to open up both of the Excel spreadsheets you wish to use — in other words, the one you're copying from as well as the one you're copying to.

Next, in the one you're copying from, find the data you wish to copy. (It can be one cell, or a group of cells.) Use your cursor to select the data you're copying.

Once you've selected the data, right-click it. Then select COPY, as shown here:

Select the data to copy, then right-click and select COPY.

Now move to the destination (second) spreadsheet. Place your cursor in the cell where you want the pasted data (or area of data) to begin. Right-click in that cell, and select PASTE SPECIAL:

Place your cursor in the correct cell in the second worksheet and select PASTE SPECIAL.

When you select PASTE SPECIAL, another window comes up. Here, select VALUES:

Select VALUES to paste only cell values.

And then click OK.

If you did everything correctly, this COPY → PASTE → PASTE SPECIAL procedure will bring over the data you want (i.e., cell contents only) without modifying the cell formulas or formatting at all!

Here's a short Flash video showing safe copy/paste in action: