Excel Paste Special Shortcut
MS Excel is one of the widely used spreadsheet programs that helps organize and manipulate vast amounts of data. Copying and pasting are among the most used tasks in MS Excel as it helps use data from different sheets or the web instantly. Although copy-paste in Excel is easy, it has many additional options too for pasting. It is not just limited to copy-pasting using the simple shortcuts Ctrl + C and Ctrl + V.
Excel Paste Special is the feature in Excel that helps us make the pasting operation more useful. It allows us to choose which formatting (source or destination) style we want to keep, or we only need the values or formulas rather than any formatting. The Paste Special Feature typically extends the operations when pasting anything in Excel.
This article discusses the Excel Paste Special feature and corresponding Excel Paste Special Shortcuts with examples.
What is Paste Special in Excel?
The Paste Special feature in Excel offers a wide range of options to help users paste data differently. We can either paste only desired elements of the copied contents or perform a specific mathematical operation with the copied contents accordingly. For example, we can copy contents with formulas and paste only the values in any specific cell without formulas in it. It is just one option. However, Excel Paste Special feature allows us many more options like this while pasting data in the sheet.
Also, one of the main advantages of using the Paste Special feature is that we can use all the Paste Special options within a single sheet or in different sheets and workbooks if needed.
How to use Paste Special in Excel?
Excel offers multiple ways to access any feature, which is also the same for the Excel Paste Special. That means that we can access the Excel Paste Special feature by using different shortcuts. However, we must have copied something to paste. Once we have the copied data in the clipboard, we can use the following shortcuts and use Paste Special feature in Excel:
By using the Keyboard Shortcut Keys
The best and quickest method to access Paste Special feature is to use the keyboard shortcuts. Excel has a wide range of keyboard shortcuts. To access Paste Special feature and paste a specific aspect of contents from the clipboard, we can use any of the following two shortcuts:
- Ctrl + Alt + V (works with Excel 2007 and higher)
- Alt + E, then S (works with all Excel versions)
By using the Ribbon Shortcut
Another simple method of using the Paste Special feature involves the use of shortcuts present on the ribbon. Specifically, we need to navigate to the Home tab, click on the drop-down icon under the Paste tool, and then select the Paste Special option from the list.
By using the Right-Click Menu Shortcut
Alternately, we can use the Paste Special shortcut directly from the right-click menu list. We need first to select a specific cell where we want to paste the content and then right-click on it. Next, we must select the Paste Special from the list to access all the relevant options of pasting.
In the above image, we can see that some of the commonly used options (such as Paste everything, Paste values, Paste Formulas, Transpose, Paste formatting, and Paste link) appear directly in the right-click menu above the Paste Special option.
To access more, we need to hover on the Paste Special option on the list. This will instantly open a small popup window with almost 14 paste special options. The best part is that we can hover over any of the options on the popup window to see a live preview of the contents, like how they will be pasted on the selected cell with the corresponding option.
All three shortcuts typically launch a Paste Special dialogue box. The Paste Special dialogue box contains many options that we can select either by using the mouse buttons or via the associated keyboard shortcut key. The following is the image of the Paste Special dialogue box:
Note: We can also access right-click menu options by using the keyboard shortcut Shift + F10.
Understanding Excel Paste Special Options and Shortcut Keys
As we discussed above, we can access all the Paste Special options directly from the Paste Special dialogue box via keyboard shortcut Ctrl + Alt + V. After the dialogue box is displayed, we can select the desired options from the box via a specific keyboard button instead of using a mouse.
The following table displays the shortcut key to select the desired option from a Paste Special dialogue box:
| Shortcut Key | Option | Action |
|---|---|---|
| A | All | To paste the cell contents with formatting. |
| F | Formula | To only paste the formulas. |
| V | Values | To only paste the values without formulas. |
| T | Formats | To copy or paste the cell formats without the values. |
| C | Comments | To paste only comments associated with a cell. |
| N | Data Validation | To only paste the data validation configurations. |
| H | All using source theme | To paste all the contents in the same theme formatting as per the source cell. |
| X | All except borders | To paste all the cell contents without the borders. |
| W | Column Width | To only paste the column width from the copied source. |
| R | Formulas and number formats | To paste the formulas and number formats like the currency, date formats, symbols, etc. |
| U | Values and number formats | To paste the values without formulas and the number formats. |
| D | Add | To add the copied contents to the data in destination cell (s). |
| S | Subtract | To subtract the copied contents from the data in the destination cell(s). |
| M | Multiply | To multiply the copied contents by the data in destination cell(s). |
| I | Divide | To divide the copied contents by the data in destination cell(s). |
| B | Skip blanks | To prevent copying the values in the destination range with blank cells that come in the copied range. |
| E | Transpose | To convert the columns of copied contents to rows and rows to columns. |
| L | Link | To link the paste contents to the copied contents using the formulas. |
Before we use any of the shortcuts from the table, it should be noted that we must have copied the data first, and the Paste Special dialogue box is already opened.
Examples of using Paste Special in Excel
We discuss some most popular Paste Special features with the help of relevant examples. We use Excel Paste Special shortcuts during these examples:
Copying Comments using Paste Special Shortcut
Suppose we want to copy comments from one cell to another without the cell values and formatting. We need to follow the below steps:
- First, we need to select the cell or cells from which we want to copy comments and then press the shortcut Ctrl + C to copy its contents into the clipboard.
- Next, we need to select the destination cell or the upper-left cell of the specific range to paste comments. Once the desired cell is selected, we must use the shortcut key Ctrl + Alt + V to launch Paste Special dialogue.
- Once the dialogue box is displayed, we need to press the ‘C’ button on the keyboard to select the ‘Comments’ option from the Paste Special dialogue box. Lastly, we must press the Enter key.
By using these steps, we can copy comments to desired cells without the values or formatting. In the image below, the comments are copied from cell A2 to C2, while the existing values in the destination cell (C2) are preserved.
Copying Values using Paste Special Shortcut
Suppose we want to copy values from one cell to another, ignoring the other copied contents, such as formula and formatting. We need to follow the below steps:
- First, we need to select the cell or cells that contain formulas and then press the shortcut Ctrl + C to copy the cell contents into the clipboard.
- Next, we need to select the destination cell or the specific range to paste values. It is important to note that we can also select the same cell that we copied to remove the formula and keep only the resultant values. Once the desired cell is selected, we must use the shortcut key Ctrl + Alt + V to launch Paste Special dialogue.
- Once the Paste Special dialogue box is displayed, we need to press the ‘V’ button on the keyboard to select the ‘Values’ option from the Paste Special dialogue box. If we want to keep the values and the original number formats, we can press the ‘U’ key instead of ‘V’. Lastly, we must press the Enter key or click the OK button.
By using these steps, we can copy only values to desired cells without the formulas or formatting. The values are copied from cell A3 to C3 without the formulas from the A3 cell in the image below.
When we select cell C3, we see the values in the formula bar because the formula is not copied.
Transposing Contents using Paste Special Shortcut
The transpose feature helps us to change columns into rows or vice-versa. For this, we need to follow the below steps:
- First, we need to select the table that we want to transpose and press the shortcut key Ctrl + C to copy its contents.
- Next, we need to select the upper-left cell of the specific range to paste the transposed contents. Once the desired cell is selected, we must use the shortcut key Ctrl + Alt + V to launch Paste Special dialogue.
- After the Paste Special dialogue box is displayed, we need to press the ‘E’ button on the keyboard to select the ‘Transpose’ option from the Paste Special dialogue box. Lastly, we must press the Enter key or click the OK button.
By using these steps, we can copy contents to desired cells in a transposed manner. In the image below, the contents are transposed from the columns to rows:
Copying Columns Width using Paste Special Shortcut
We can use this method to quickly set the required width to all the columns in Excel. We need to follow the below steps to configure width using Paste Special Shortcut:
- First, we must adjust the width for any single column as we desire. After the column width is configured, we need to copy the column using the shortcut Ctrl + C.
- Next, we need to select a range of columns that we want to adjust as per the desired width. To select columns, we can use Ctrl or Shift key based on adjacent or non-adjacent columns.
- Once the range is selected, we need to use the shortcut Ctrl + Alt + V to open Paste Special dialogue box. After that, we need to press the ‘W’ key on the keyboard to select the ‘Column widths’ option.
- Lastly, we must press the Enter key or click on the OK button in a dialogue box.
By using these steps, we can set the width to desired columns equally. In the image below, the column widths are adjusted using the Paste Special Shortcut:
Copying a Column’s Width and its Contents using Paste Special Shortcut
Unlike the above method, we can also paste copied contents with the adjusted width from the source. We don’t need to adjust the width manually to accommodate the new values. For this, we need to follow the below steps:
- First, we need to select the data to copy to another cell and press the shortcut Ctrl +C.
- Next, we need to select the target range and press the right-click on it.
- We must select the ‘Paste Special’ option from the list and then click on the ‘Keep Source Column Width’ option. Alternately, we can press the ‘W’ key after selecting the Paste Special option, and it will directly choose the desired option. It looks like this:
We can copy contents from cells or multiple cells to other cells in a sheet with the source width by using these steps. Once we are done with these steps, our copied data will be pasted like the following image:
In the above image, we can see that the cell width is adjusted automatically as per the source formatting.
That is how we can use Paste Special Shortcut in Excel.