By default when you copy (or cut) and paste in Excel, everything in the source cell or range—data, formatting, formulas, validation, comments—is pasted to the destination cell(s). This is what happens when you press CTRL+V to paste. Since that might not be what you want, you have many other paste options, depending on what you copy.
Đang xem: Cách sử dụng phím tắt paste value trong excel chuẩn nhất
For example, you might want to paste the contents of a cell, but not its formatting. Or maybe you want to transpose the pasted data from rows to columns. Or, you might need to paste the result of a formula instead of the formula itself.
Important: When you copy and paste formulas, you might need to fix cell references. However, references are not changed when you cut and paste formulas.
Tip: You can also use the fill handle to copy a formula into adjacent cells on the worksheet.
WindowsWeb
Paste menu options (on the ribbon)
Select Home, select the clipboard icon (Paste) and pick the specific paste option you want. For example, to paste only formatting from the copied cell, select Formatting . This table shows the options available in the Paste menu:
|
Paste |
All cell contents. |
|
|
|
Keep Source Column Widths |
Copied cell content along with its column width. |
|
Transpose |
Reorients the content of copied cells when pasting. Data in rows is pasted into columns and vice versa. |
|
|
Formulas |
Formula(s), without formatting or comments. |
|
|
Values |
Formula results, without formatting or comments. |
|
|
Formatting |
Only the formatting from the copied cells. |
|
|
|
Values & Source Formatting |
Values and formatting from copied cells. |
|
|
Paste Link |
Reference to the source cells instead of the copied cell contents. |
|
|
Picture |
Copied image. |
|
|
Linked Picture |
Copied image with a link to the original cells (if you make any changes to the original cells those changes are reflected in the pasted image). |
Paste Special
To use options from the Paste Special box, select Home, select the clipboard icon (Paste), and select Paste Special.
Keyboard Shortcut: Press Ctrl+Alt+V.
In the Paste Special box, pick the attribute you want to paste.
Note: Depending on the type of data you copied and the Paste option you picked, some other options might be grayed out.
|
Paste option |
Action |
|
All |
Pastes all cell contents and formatting of the copied data. |
|
Formulas |
Pastes only the formulas of the copied data as entered in the formula bar. |
|
Values |
Pastes only the values of the copied data as displayed in the cells. |
|
Formats |
Pastes only cell formatting of the copied data. |
|
Comments and Notes |
Pastes only comments and notes attached to the copied cell. |
|
Validation |
Pastes data validation rules for the copied cells to the paste area. |
|
All using Source theme |
Pastes all cell contents in the document theme formatting that is applied to the copied data. |
|
All except borders |
Pastes all cell contents and formatting applied to the copied cell except borders. |
|
Column widths |
Pastes the width of one copied column or range of columns to another column or range of columns. |
|
Formulas and number formats |
Pastes only formulas and all number formatting options from the copied cells. |
|
Values and number formats |
Pastes only values and all number formatting options from the copied cells. |
|
All merging conditional formats |
Pastes the contents and conditional formatting options from the copied cells. |
You can also specify a mathematical operation to apply to the copied data.
|
Operation |
Action |
|
None |
Specifies that no mathematical operation will be applied to the copied data. |
|
Add |
Adds the copied data to the data in the destination cell or range of cells. |
|
Subtract |
Subtracts the copied data from the data in the destination cell or range of cells. |
|
Multiply |
Multiplies the copied data with the data in the destination cell or range of cells. |
|
Divide |
Divides the copied data by the data in the destination cell or range of cells. |
|
Skip blanks |
Avoids replacing values in your paste area when blank cells occur in the copy area when you select this check box. |
|
Transpose |
Changes columns of copied data to rows and vice versa when you select this check box. |
|
Paste Link |
Click to create a link to the copied cell(s). |
Verify and fix cell references in a pasted formula
After you paste a copied formula, you should verify that all cell references are correct in the new location. The cell references may have changed based on the reference type (absolute, relative, or mixed) used in the formula.
For example, if you copy a formula in cell A1 and paste it two cells down and to the right (C3), cell references in the pasted formula will change as follows:
|
$A$1 (absolute column and absolute row) |
$A$1 |
|
A$1 (relative column and absolute row) |
C$1 |
|
$A1 (absolute column and relative row) |
$A3 |
|
A1 (relative column and relative row) |
C3 |
If cell references in the formula don’t give you the result you want, try switching to different reference types:
Select the cell containing the formula.
In the formula bar
, select the reference you want to change.
Press F4 to switch between the reference combinations, and choose the one you want.
For more information about cell references, see Overview of formulas.
When you copy in Excel for the web, you can pick paste options in the destination cells.
Select Home, select the clipboard icon, select Paste, and pick the specific paste option you want. For example, to paste only formatting from the copied cell, select Paste Formatting . This table shows the options available in the Paste menu:
|
Paste |
All cell contents. |
|
|
Paste Formulas |
Formula(s), without formatting. |
|
|
Paste Values |
Formula results, without formatting. |
|
|
Paste Formatting |
Only the formatting from the copied cells. |
|
|
Paste Transpose |
All cell content, but reorients the content when pasting. Data in rows is pasted into columns and vice versa. Xem thêm: Game Lam Bac Si Kham Benh – Doctor Kids (Bác Sĩ Trẻ Em) |
Need more help?
You can always ask an expert in the Excel Tech Community or get myalbum.vn in the Answers community.