Introductory introduction to the optional Paste feature for Beginners

First, the initial knowledge of selective pasting

Selection is one of the powerful features of the optional paste Excel. Excel pastes the dialog box as follows, we can make it into four areas, that is, "Paste mode area", "Operation Mode area", "Special processing Settings area", "button area". The way of pasting, the method of operation, special processing settings can be used at the same time, for example, you choose the formula in the Paste mode, and then select Add in the Operation area, but also in the special Settings area to choose to skip the empty cell and transpose, OK, all selected items will be pasted on.

Second, the function of the introduction

Paste way areas of the function introduction:

"All": including content, and format, the effect is equal to direct paste;

"Formula": Only paste text and formulas, do not stick to the font, format (font, alignment, text direction, number format, shading, etc.), borders, comments, content validation; (When you copy a formula, the cell reference changes depending on the type of reference you are using.) To ensure that the cell reference is unchanged, use an absolute reference. )

"Value": Only paste text, the contents of the cell is the calculation of the formula only paste the results, these two do not change the format of the target cell;

Format: Paste only the source cell formatting, but not the validity of the cell, sticky format, including font, alignment, text direction, border, shading, etc., does not change the text content of the target cell. (function equivalent to the format brush);

Annotation: Copies the annotation contents of the source cell, without changing the contents and formatting of the target cell;

Validation: Pastes the data validation rules for the copied cells into the paste area, sticking to the validation content only and remaining unchanged;

All content and formatting except borders: Paste all content and formatting except the border, keeping the same content and formatting as the target cell and source cell;

Column width: Pastes a column width or column area into another column or column area so that the target cell and the source cell have the same column width, without changing the content and formatting;

Formula and number format: Pastes formulas and all number formatting options from selected cells only;

Value and number format: Pastes values and all number formatting options from selected cells only;

The function of the operation mode area is introduced:

"None": to the source area, do not participate in operations, according to the choice of pasting paste;

"Add": Add the value of the source region to the new region, and the result is added;

"Minus": the value of the source region, and the new region to subtract, the result is subtracted;

Multiplication: Multiplies the value of the source region by multiplying it with the new region, and gets the result of the additive multiplication;

"except": the value of the source region, and the new Region division, the result is divided (at this point if the source area is 0, then the result will show #div/0! error);

Special processing set up the area of the functions of the introduction:

Skip blank cells: When there are empty cells in the copied source data range, pasting the space-time cell does not replace the value in the corresponding cell in the pasted region;

Transpose: Turns the columns of the copied data into rows, and rows into columns. The top row of the source data range is in the leftmost column of the target area, and the leftmost column of the source data range is displayed in the top line of the target area;

Button Area Features Introduction:

Sticky Link: Links pasted data to the active worksheet. The pasted cell displays the formula. If you copy A1 cells and paste them into D8 cells through paste link, the formula for D8 cell is = $A $. (a formula such as "= Source cell" is inserted, not a value). If you update the value of the source cell, the contents of the target cell are also updated. (If you copy a single cell and paste the link to the destination cell, the reference in the target cell formula is an absolute reference, or a relative reference if you copy the range of cells.) )

OK: Select the items you want to paste, and then left-click to perform the action;

Cancel: Discards the selected action.

Iii. examples of Use techniques

1, the report data from the meta conversion millions yuan

Requirements: Convert existing table data from meta to millions

General Practice: Create a new table, set the formula equal to the original table data divided by 10,000.

To use paste-selective processing:

①, enter 10000 in any blank cell, then select this cell, copy (this is important);

②, select the data in the table, you can use multiple selections, but to pay special attention to, select only pure data cells, do not take the cell with the formula in the selection;

③, click "edit" → "paste selective", select the "addition" of the Operation area, click "OK" button, then complete the data processing.

Special Note: The remaining several methods of operation are similar. Multiple tables of the same structure combine data, you can also use this method, only the previous selection of the copy is the entire table, in doing selective sticking to select the "plus".

2, Data merge and paste

Requirements: Merge column A's data into column B, and if column A's cell is empty, preserve the original data of the corresponding cell in column B, and if there is data in the cell in column A, replace the value of the column B corresponding cell with the data in column a cell.

Processing steps:

①, select A1:a8, copy (if you use cut, you can not use paste);

②, select B1:b8, click "edit" → "selective paste", in the dialog box tick "Skip Empty unit", OK, get the desired effect.

3, the ranks of the table interchange

If there are too many columns in an Excel table, there are too few rows. Use selective pasting to implement row and column transpose, where columns become rows and rows become columns. The process is to select the range of cells that you want to transpose, such as A1:M5, copy, click the upper-left cell of the transpose table range, such as cell E1, click the edit → paste paste command, select the Transpose command, and OK to see the result of the row and column transpose.

4, the realization of the table synchronization update

The advantage of using a paste link is that the copied data is automatically updated with the original data. Paste link is actually refers to paste the original data address to Excel, when the original data changes, Excel data will also change, this time will automatically update.

Paste link and paste-numeric value is: Paste-value, is pasted into Excel in the value of the data source has no relationship, and the paste link is to keep the data synchronized update.

5, remove the formula in the table only to retain the value

When an Excel table is made, many functions and formulas are used in the cell, and when the document is transmitted to others, we do not want others to see the corresponding formula structure. By pasting, you can quickly convert the formula results to a fixed number.

Operation: Select part of the data to be transferred, then click "Edit" → "paste selectively", click "Data", then confirm, then complete the operation.

Tip: This operation is mainly used in a large number of formulas to affect the table open, refresh speed. Of course, you should do this to prevent others from seeing the formula, or to modify the formula to cause data problems.

6, apply the format of other forms (the effect of the same format brush)

If the original cell data already has some formatting, such as font, font size, color equivalence, and if you need to apply these formats to the target cell, you can use paste-format in the paste-in. Paste-format is the format of copying the original cell data, rather than copying the data content.

For example, suppose a school has 1-6 grade six grades to fill in, the first grade of the grade table of the font, size, line has been set up, and the other five tables can be applied to the first grade style. In general, we can copy the first-grade table and fill in the data. But what if the data for several other tables are filled in?

Operation:

①, all selected first grade table, copy;

②, select other Grade table (you can select multiple tables at a time), click "Edit"-"paste Selective", then click "Format", OK;

③, and then click "Edit" again-"Paste selectively", click "Column width", OK;

④, if you want to do the same, then click "Annotation", OK, and if the effectivity setting is also to be processed, tap validate accordingly.

7. Paste the formula into an inconsistent size area

When you paste a formula, you are prompted to "this operation requires that the merged cells have the same size," and therefore cannot paste. At this point, you can use the selective paste → formula to achieve.

Use normal pasting, although you can paste the value of the source cell into the merged cells, but if the region is not the same size, the format of the region changes. The Paste Selective-value feature does not paste numeric functionality into merged cells. You can paste a value into a merged cell by using paste-by-formula, which preserves the merged formatting and has a number of cells in the merged cell. Of course, you can use the "selective pasting-value and number format" to achieve these functions.

Prompt: This feature cannot paste values into merged cells.

8, the use of selective paste 100% copy the range of cells

It is often a waste of time to copy a table from a worksheet to another worksheet, but the row height and column widths of the table have changed and need to be reset. Here's a tip that allows you to copy and paste a table and then keep the cell formatting and row Gaolekuan unchanged.

①: Select the entire row of the table area you want to copy, copy;

②: Switch to the target worksheet, select the first line of the target area and paste;

③: After pasting, keep the selected state of each row, click the right mouse button → "paste selective" command, select "Column width" in the dialog box.

9. Convert text numbers to numeric values using paste-selective pasting

If the number of a range of cells is in text format and cannot be reduced, you can convert a number in text format to a number format by entering a value of 1 in a blank cell, copying a cell with a value of 1, selecting the destination area, and pasting → multiplication/divide. You can also convert by using a selective paste method that adds or minus 0 values to the target cell.

Use this method to convert a report in dollars to thousand or thousands of units is very convenient! For the same reason, you use the paste selective → multiply feature to convert the number of a cell range to a positive number.