10 functions of Paste in Excel you need to know

Copy and paste are two common operations of office people. However, pasting data in excel, in addition to normal paste, the data in excel is also formulas, functions, formats, and so on. To copy and retain those formats, it is necessary to paste special. So you already know all the 10 functions of Paste in Excel yet?

1. Copy the width of the column

When copying data to a new column, the length of the column will not automatically adjust to suit the new value.

To copy the data to another region and keep the same length with just a few steps:

+ Select the data to copy and press [Ctrl] + C to copy the selected value to the Clipboard.

+ Select a cell in the target column.

+ On the Home 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: Choose PasteSpecial from the Paste drop-down menu. Next, click the Column Widths option in the Paste section and click OK.

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

2. Perform the calculations

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

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

3. Data exchange

To convert data from a column to a row or vice versa, just select the source data and do the following:

Press [Ctrl] + C to copy data to the Clipboard

Select the top left cell of the goal line.

Click the Home tab (if necessary) and select Transpose from the Paste menu as a drop-down.

4. Replace the formula with the calculated results

Occasionally, users may want to replace the formula with its actual value. For example, you want to replace a sequence 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, perform the following actions:

+ Select a range of formulas and then press [Ctrl] + C.

+ In the Clipboard group, click the Paste menu as a drop-down.

+ Select Values.

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

Replace formulas with calculated results

* Tips to use the mouse to perform the task:

Select range.

Right-click on the border and drag the range to the next column (or row).

Drag the selected range back to the source. This will cause Excel to display the shortcut menu, without transferring the selected values.

Select Copy Here As Values ​​Only.

5. Copy format

Normally, when we want to copy a format from one cell to another, we often use Format Painter. However, using Format Painter is a bit inconvenient for entire columns or rows. Here's how to use Paste Special instead:

+ Select the cell containing the format you want to copy and press [Ctrl] + C.

+ Click inside the column or row you want to format. (Press [Ctrl] + Spacebar to select entire column column or [Shift] + Spacebar to select entire row).

With the column or row selected, select Formatting from the Paste menu as drop-down in the Clipboard group. Excel 2007 requires an extra step: Choose Paste Special from the Paste drop-down menu, click Formats in the Paste section, and then click OK.

6. Copy chart format

It can take a lot of effort to format a chart so beautifully and then have to do all the same things again with other data types. In this case, do not reformat the new chart, but use Paste Special. This hypothesis is similar to the way 5, but the steps are a little different:

+ Select the chart with the format you want to copy and press [Ctrl] + C.

+ Select the chart you want to format and then select Paste Special from the Paste menu as a drop-down menu. Excel displays the Paste Special dialog box, with 3 options.

+ Select Formats and click OK.

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

7. Ignore a range of blank cell values

Using the Skip Blanks option, you can replace the current value while still ignoring cells with no values ​​in the original data. In other words, if there is a blank cell in the original data and a value in the corresponding range to paste, this option does not replace the current value with a blank cell. You can use it with all other options:

+ Select the original data range and press [Ctrl] + C.

+ Select the top left cell in the target range.

+ From the Paste menu drop-down menu, then select Paste Special.

+ Tick Skip Blanks option and click OK.

Excel will not overwrite the current value with a blank cell.

8. Copy Data Validation

After taking the time to set up a 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 follow all the steps again. Using Paste Special, you can simply copy and paste:

+ Select the cell containing Data Validation rule and press [Ctrl] + C.

+ Select the target cell to paste.

+ From the Paste drop-down menu, choose Paste Special.

+ Click on the Validation option and then click on OK.

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

9. Delete text on the Web

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

If you are using Excel 2010, you will surely enjoy using this Paste Special solution:

+ Copy text from the Web.

+ In Excel, click where you want to insert the text.

+ Choose Paste from the drop-down menu and select Match Destination Formatting.

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

10. Paste the reference

Users can reference a cell by referring to a cell with an equal sign. For example, type = A1 to return to the content of cell A1. Usually, we use these references in larger expressions and formulas. Occasionally, we refer to a cell with itself. Paste Link can help when referencing multiple cells:

Select the range to refer to and press [Ctrl] + C.

Select the target box and then Paste Paste from the Paste menu as a drop-down.