Software

10 powerful ways to use Excel's paste features

Excel's Paste and Paste Special commands harbor a multitude of useful options. Use them to do everything from transposing data to copying Validation rules.

The Paste Special command is in most Office applications, but Excel users probably benefit from it the most. Using this basic feature -- as well as assorted Paste options -- you can make short work of a few common tasks and some rather complex problems. These tricks are so easy to implement that you'll probably find yourself using the paste features a lot more than you used to.

In Excel 2007 and 2010, Paste is in the Clipboard group on the Home tab. In Excel 2003, Paste Special is on the Edit menu. For the shortcut enthusiasts, we've included a table at the end that contains keyboard shortcuts for most of these operations.

1: Copy column width

When you copy data into a new column, the column's width doesn't automatically adjust to accommodate the new values. With an extra click, you can copy the source column's width to the target column, as follows:

  1. Select the data and press [Ctrl]+C to copy the selected values to the Clipboard.
  2. Select a cell in the destination column.
  3. On the Home tab, click the Paste option in the Clipboard group and choose Keep Source Column Widths. This option requires an extra step in 2007: Choose Paste Special from the Paste drop-down, click the Column Widths option in the Paste section, and click OK.

Excel will copy the data and the source column's width to the target cells.

2: Perform calculations

You can use Paste Special to add, subtract, multiply, and divide. Simply enter a number and press [Ctrl]+C. Then, select the values you want to change by the number you just entered and click Paste Special in the Clipboard group. In the resulting dialog, click the appropriate operator and then click OK. Excel will perform the appropriate operation using the value you copied to the Clipboard.

You can also perform calculations using multiple values. Use the same process, but copy a range of several values instead of just one. Excel doesn't care if the range dimensions match; it will perform the calculations relative to the positions of the copied values.

3: Transpose data

Excel offers a transposing feature, but sometimes Paste Special is quicker. Select the source data and do the following:

  1. Press [Ctrl]+C to copy the data to the Clipboard.
  2. Select the top-left cell in the target range.
  3. Click the Home tab (if necessary) and choose Transpose from the Paste drop-down.

That's all there is to it! You can copy a column of data to a row or vice versa.

4: Replace formulas with their evaluated results

Occasionally, you'll want to replace formulas with their literal values. For instance, you might want to replace a range of RAND() functions with their values instead of letting the formulas recalculate. This is a common task for Paste Special. To copy formulas with their values, do the following:

  1. Select the range for the formulas and press [Ctrl]+C.
  2. In the Clipboard group, click the Paste drop-down.
  3. Choose Values.

Doing so replaces the formulas with their literal values. Before you use this technique, you might want to make a backup of your workbook, just in case.

There's also a shorter mouse trick that performs the same task:

  1. Select the range.
  2. Right-click a border and drag the range to the next column (or row).
  3. Drag the selected range back to the source. This forces Excel to display a shortcut menu, without actually moving the selected values.
  4. Select Copy Here As Values Only.

5: Copy formats

Most of us use Format Painter to copy formats from one cell to another or to small ranges. But it can be a bit awkward to use Format Painter with an entire row or column. Here's how to use Paste Special instead:

  1. Select the cell that contains the format you want to copy and press [Ctrl]+C.
  2. Click inside the column or row you want to format. (Press [Ctrl]+Spacebar to select the entire column or [Shift]+Spacebar to select the entire row.)
  3. With the row or column selected, choose Formatting from the Paste drop-down in the Clipboard group. Excel 2007 requires an extra step: Choose Paste Special from the Paste drop-down, click Formats in the Paste section, and click OK.

6: Copy chart formats

You can work hard to get a chart looking just right and then find that you need to do it all again with different data. When this happens, don't reformat the new chart manually -- use Paste Special. The premise is the same as #5, but the steps are just a bit different:

  1. Select the chart with the formatting you want to copy and press [Ctrl]+C.
  2. Select the chart you want to format and choose Paste Special from the Paste drop-down. Excel will display the Paste Special dialog, with just three options.
  3. Choose Formats and click OK.

Excel responds differently when copying chart formats, but it can easily handle the task.

7: Skip blanks in a series

Using the Skip Blanks option, you can replace existing values while ignoring blank cells in the source data. In other words, if there's a blank in the source range and an existing value in the corresponding paste range, this option won't replace the existing value with a blank. You use it like all the other options:

  1. Select the source range and press [Ctrl]+C.
  2. Select the top-left cell in the destination range.
  3. From the Paste drop-down, choose Paste Special.
  4. Check the Skip Blanks option and click OK.

Excel will not overwrite existing values with blanks.

8: Copy Data Validation

After taking the time to set up a Data Validation rule or list to improve data input, you might want to apply the same rule to another cell or range. The good news is you don't have to go through the entire setup process again. Using Paste Special, you can just copy it, as follows:

  1. Select the cell that contains the Data Validation rule and press [Ctrl]+C.
  2. Select the target cell.
  3. From the Paste drop-down, choose Paste Special.
  4. Click the Validation option and click OK.

Setting up Data Validation can be a bit tedious and time-consuming; Paste Special is much easier!

9: Clean up Web text

When copying text from the Web, you can end up with a mess because Excel retains the source formatting. Most of the time, that's not what you'll want. If you have Excel's Show Paste Options feature enabled (available in the Advanced pane of the Excel Options dialog box), you'll see a Clipboard icon immediately after pasting the text into your worksheet. You can choose Match Destination Formatting from the Clipboard drop-down to get rid of the unwanted formatting.

If you have Excel 2010, you may prefer to use this Paste Special solution:

  1. Copy the text from the Web.
  2. In Excel, click where you want to insert the text.
  3. Select the Paste drop-down and choose Match Destination Formatting.

Use this option to copy any foreign data, not just Web text, into Excel.

10: Paste references

You can reference a cell by prefacing a cell address with the equals sign. For example, enter =A1 to return the contents of cell A1. Usually, we use these references in larger expressions and formulas. Occasionally, we just reference a cell by itself. Paste Link can help when referencing a large range of cells instead of just one:

  1. Select the range and press [Ctrl]+C.
  2. Select a target cell and choose Paste Link from the Paste drop-down.

Paste Link is quicker than entering cell references manually, if you need several.

Keyboard shortcuts for paste actions

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.

3 comments
zphilz
zphilz

I know how to use the =RIGHT and =LEFT functions, but is there a way to copy data from a field and copy everything BUT the last 9 characters from one field to another one?

mary
mary

@dimesnionalmodeler: she IS good, isn't she!?!

dimesnionalmodeler
dimesnionalmodeler

Thanks for those tips - they helped a lot. You have excellent tips - let me know if you write a book!

Editor's Picks