10 ways to use the Paste feature in Excel

The Paste Special command appears in most office applications, but perhaps Excel users still benefit from this command the most.Using this basic feature - as well as other Paste options - users can perform some common tasks and some more complex problems.These tips are very easy to implement and you will definitely use them more than you once knew them.

1. Summary of expensive shortcuts in Microsoft Excel

In Excel 2007 and 2010, Paste is in the Clipboard group on the Home tab. In Excel 2003, Paste Special in the Edit menu. For those who often use keyboard shortcuts, we have a table at the end of the post, including shortcuts to perform common actions.

1. Copy the width of the column:

When copying data to a new column, the length of the column will not automatically adjust to match the new value. With just one extra click, you can copy the original width of the column to the target column:

1. Select the data to copy and press[Ctrl] + C to copy the selected value to the Clipboard.
2. Select a cell in the target column.
3. On theHome tab , click the Paste option in the Clipboard group and select Keep Source Column Widths . This option will require one more step when you use Excel 2007: Select Paste Special from the Paste menu as a drop-down. Next, click the Column Widths option in the Paste section and click OK.

Excel will copy the data and the original width of the column into the target cell.

2. Perform calculations:

Users can use Special Paste to add, subtract, multiply and divide. Just enter the number and press [Ctrl] + C. Then, select the value you want to change with the newly entered number and click Paste Special in the Clipboard group. In the results window, click on the appropriate calculation and click OK . Excel will perform the corresponding action by using the value you just copied into the Clipboard.

Users can also perform calculations with multiple values. Use the same process as above, but copy a sequence of values ​​instead of just copying one as above. Excel will not notice the size if it is not enough, it only performs calculations in turn in the order of the copied values.

3. Swap data:

Excel provides users with a swap feature, but sometimes Paste Special is a faster way to do this. Select the data and follow the following actions:

1. Press[Ctrl] + C to copy the data to the Clipboard.
2. Select the top left cell of the target line.
3. Click theHome tab (if necessary) and select Transposefrom the Paste menu as a drop-down.

That's what users need to do. From now on, you can copy a column of data to a row and vice versa.

4. Replace the formula with the calculated result:

Occasionally, users may want to replace the formula with its actual value. For example, you want to replace a range of RAND () functions with their values ​​instead of allowing this formula to calculate itself. This is a common task for Paste Special. To copy formulas with their values, follow these actions:

1. Select a range of formulas and press[Ctrl] + C.
2. In the Clipboard group, click thePastemenuas a drop-down.
3. ChooseValues.

Do so to replace formulas with their true values. Before using this method, you may want to back up your work as a precaution.

Trick to use the mouse to perform the task:

1. Select the range.
2. Right-click on the border and drag it to the column (or row) next to it.
3. Drag the selected range back to the source.This will force Excel to display the shortcut menu, without moving the selected values.
4. ChooseC opy Here As Values ​​Only.

5. Copy the format:

Most of us use Format Painter to copy the format from one cell to another or to a certain row. However, when using Format Painter, there is some inconvenience with the entire column or row. Here is how to use the Paste Special instead:

1. Select the cell containing 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 whole column or [Shift] + Spacebarto select the entire row).
3. With the column or row selected, selectFormatting from the Paste menu as a drop-down in the Clipboard group. Excel 2007 requires an extra step: Select Paste Special from the Paste menu as a drop-down, click Formats in the Paste section and click OK.

6. Copy chart format:

Users can take a lot of effort to format the chart so that it is beautiful and then have to do all the same work again with other data types. In this case, don't re-reformat the new chart but use Paste Special. This hypothesis is similar to the way number 5, but the steps are slightly different:

1. Select the chart with the format you want to copy and press[Ctrl] + C.
2. Select the chart you want to format and then selectPaste Special from the Pastemenuas a drop-down.Excel will display the Paste Special dialog box, with 3 options.
3. SelectFormats and click OK.

Excel reacts differently when copying the chart format, but it can easily complete the task.

7. Ignore a series of blank values:

Using the option Skip Blanks , you can replace the current value while ignoring the non-valued cells in the original data. In other words, if there is an empty cell in the original data and an array value needs to be matched, this option does not replace the current value with a blank. You can use it with all other options:

1. Select the original data range and press[Ctrl] + C.
2. Select the top left cell in the target range.
3. From the Paste menu, drop-down form and selectPaste Special.
4. Check the optionSkip Blanks and click OK.

Excel will not overwrite the current value with a blank.

8. Copy Data Validation:

After taking the time to set up the rule or Data Validation list to improve data entry, you may want to apply the same rule to another cell or range. The good news is that you don't have to repeat all the steps above. Using Paste Special, you only need to copy it again:

1. Select the cell containing theData Validation rule and press [Ctrl] + C.
2. Select the target box to paste.
3. From the Paste menu as a drop-down, selectPaste Special.
4. Click on theValidation option and then click OK.

Setting up Data Validation can be boring and time consuming; Special Paste is a much easier way!

9. Delete text on the Web:

When copying text from the Web, you may be in trouble because Excel remains the original format. In general, this is not what you want. If you have enabled Excel's Show Paste Options feature (available in the Advanced panel of the Excel Options dialog box), you will see the Clipboard icon immediately after pasting the text into the spreadsheet. Users can select Match Destination Formatting from the Clipboard drop-down menu to remove unnecessary formats.

If you're using Excel 2010, you'll probably enjoy using this Paste Special solution:

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

Use this option to copy external data, not just Web documents into Excel.

10. Paste reference:

Users can refer to a cell by leading an address with an equal sign. For example, enter = A1 to return to the contents of cell A1. Usually, we use these references in larger expressions and formulas. Sometimes, we still refer to a cell with itself. Paste Link can help when referencing multiple cells:

1. Select the range to reference and press[Ctrl] + C.
2. Select the target box and then clickPaste Linkfrom the Paste menu as a drop-down.

Using Paste Link is faster than manual reference.

Shortcuts for Paste actions:

Good luck!