Excel Paste Options and Their Effects

Although you’re probably familiar with basic cut, copy and paste operations in Excel, there are quite a few different options available when you paste data or formulas. It can be difficult to understand all these options at first.

When you copy and paste data, a Paste Options button will appear at the lower right corner of the pasted data. Clicking this button will reveal several options:

The first option is a default paste. It will paste all of the formatting and formulas that existed in the source data. Although the formulas are preserved, the cells that are referenced in the formula will be relative. In this example, the acceleration data are calculated from the force, and those formulas reference the force column that we’ve just pasted rather than the original. If you update the original table, those changes won’t be reflected in this new copy.

Sometimes, this is undesirable. For example, if you want to copy the acceleration column without the force column, the default paste would give you a column of zeroes because their formulas would be referencing empty cells (or whatever cells are directly to the left). In this case, you do not want to copy the formulas, but instead, the values. The second section in Paste Options has three options to Paste Values instead of formulas. Because these options paste static values, they will not update if you change any other cells on the spreadsheet. Only use this option if you won’t need these values to update later on.

There are a number of other options for pasting data:

Elevate Your Engineering With Excel

Advance in Excel with engineering-focused training that equips you with the skills to streamline projects and accelerate your career.

Find Out More

Button	Description
	Paste formulas only. The formatting (colors, font, number formatting) won’t be transferred to the new cells.
	Paste formulas and number formatting. This will eliminate any text formatting and cell colors, but maintain number formatting such as the number of decimal places, percentage formats, or accounting formats.
	Paste formulas and source formatting. This is identical to the default paste.
	Paste without borders. This maintains the formulas and all other formatting (colors and number formatting).
	Keep source column widths. This maintains the formulas and all other formatting, but changes the widths of the columns to match the source.
	Transpose. This will take vertical columns and turn them into horizontal rows of data or vice versa. This is an easy way to quickly switch a vertical table to a horizontal orientation.
	Paste values and number formatting. The pasted cells will have a static value, but number formatting is maintained.
	Paste values and all formatting. The pasted cells will have a static value and all formatting from the source cells.
	Paste formatting only, without any values or formulas.
	Paste a link to the original data. This enters a formula into each cell that sets it equal to the corresponding source cell (i.e. =A2, =A3, etc.).
	Paste data as a static picture. This option is useful for inserting your data as an image into a Word document or PowerPoint presentation.
	Paste data as a “linked picture.” You can treat this image like any other (i.e. crop, resize it), but it will update if you make changes to the source data.