What is Paste Special in Excel?
Paste Special in Excel provides a more powerful advanced pasting feature with a number of pasting options, such as cell content, formulas, values, formatting, and much more. The Excel Paste Special feature only works with the Copy command (Ctrl+C) but it wouldn’t work after using the Cut commands (Ctrl+X).
Select the cell or range of cells ➪ Copy them (Ctrl+C) ➪ use Excel shortcut Alt+E+S or Alt+Ctrl+V to open the Paste Special dialog box.
Alternatively, select the cell or range of cells ➪ Copy them (Ctrl+C) ➪ go to the ‘Home’ tab ➪ click on ‘Paste’ drop-down ➪ select Paste Special.
Alternatively, select the cell or range of cells ➪ Copy them (Ctrl+C) ➪ right-click on any selected cell ➪ click on ‘Paste Special…‘ command under the Paste Options.
What is Break Link in Excel?
Whereas, Break Link in Excel severs the ties with the linked workbook. Excel replaces any linked formulas with the most recent data. If we have external references in a workbook and we have no longer need the links, then we can convert the external reference formulas to values, thereby severing the links with the help of break link.
To do so, access the Edit Links dialog box using the ribbon (go to the ‘Data’ tab ➪ select the ‘Edit Links’ in the Connections section), select the linked file in the list, and then click Break Link.
Alternatively, we can use the Excel shortcut Alt+A+K to open the Edit Links dialog box.
Break Links generally break all the external links (i.e., VLOOKUP, HLOOKUP formulas are often used to retrieve values from the other workbooks).
To do the same work (converting formulas to values), Break Link works faster than Paste Special in Excel. At a glance, we should know the features and differences between them:
Advantages of Paste Special in Excel
There are a number of advantages using the Paste Special in Excel:
- Paste ‘All‘ is a straightforward paste, identical to Home ➪ Clipboard ➪ Paste.
- The Paste Special → ‘Values‘ or ‘Formulas and number formats‘ commands are very often used to convert any formulas to numbers.
- Paste ‘Formulas‘ is very helpful when we want to copy or move a formula, but do not want the associated cell formatting.
- Paste ‘Formats‘ allow users to copy cell formatting only.
- Paste ‘Comments‘ copy cell notes from one cell to another, but does not disturb the actual contents of the cell.
- Paste ‘Validation‘ copy the validation criteria.
- Paste ‘All except borders‘ is a handy feature to ensure that the borders of the new range aren’t disturbed when new data or formulas are copied over the cells.
- Perform a mathematical operation using the contents of the copied cells with the pasted cell contents.
- Ignore cell blanks.
- Transpose row data into column data or column data into row data.
- Link the pasted cells to the copied cells.
The Excel Paste Special command is located at the bottom of the Paste gallery. See Figure 1.
Summarized view of Excel Paste Special Options
Table 1. Paste Options under Paste Special
|Sl. No.||Paste Options||Excel Shortcut||Description|
|01||All||Alt+E+S / Alt+Ctrl+V||This option is the same as a normal paste operation (Ctrl + V), and it pastes both formatting and numbers. It does not adjust the column widths.|
|02||Formulas||Alt+E+S+F / Alt+Ctrl+V+F||Pastes the formulas used in the copied cells.|
|03||Values||Alt+E+S+V / Alt+Ctrl+V+V||Pastes only the results of formulas, not the formulas themselves.|
|04||Formats||Alt+E+S+T / Alt+Ctrl+V+T||Pastes only the formatting, not the data.|
|05||Comments||Alt+E+S+C / Alt+Ctrl+V+C||Pastes only comment attached to the cell.|
|06||Validation||Alt+E+S+N / Alt+Ctrl+V+N||Pastes data validation rules of a copied cell to the entire range(s).|
|07||All Using Source Theme||Alt+E+S+H / Alt+Ctrl+V+H||Pastes all cell contents and formatting using the theme that was applied to the copied cells. In fact, it’s the same as the ordinary Paste option, making it a minor Excel quirk.|
|08||All Except Borders||Alt+E+S+X / Alt+Ctrl+V+X||Pastes everything except any borders that were applied to the copied cells.|
|09||Column Widths||Alt+E+S+W / Alt+Ctrl+V+W||Pastes the column widths so they match the width of the columns of the copied cells.|
|10||Formulas and Number Formats||Alt+E+S+R / Alt+Ctrl+V+R||This option is the same as Formulas, except that it retains the formatting for any numbers you copy. So Excel will retain currency signs($), percentage signs(%), and thousands of separators in numbers, but it drops the formatting for fancy fonts, colors, and borders.|
|11||Values and Number Formats||Alt+E+S+U / Alt+Ctrl+V+U||Pastes only the results of formulas, not the formulas themselves. Pastes any numeric formatting such as $ and commas, etc., but not any text formatting.|
Note: Depending upon the content we are copying and the ‘Paste’ options that we have selected, some options may be unavailable.
Table 2. Operation Options under Paste Special
|Sl. No.||Operation Option||Excel Shortcut||Description|
|01||None||Alt+E+S+O / Alt+Ctrl+V+O||Do not perform any mathematical operation to the pasted cells.|
|02||Add||Alt+E+S+D / Alt+Ctrl+V+D||Add the values in the copied cells to those in the pasted cells.|
|03||Subtract||Alt+E+S+S / Alt+Ctrl+V+S||Subtract the values in the copied cells from those in the pasted cells.|
|04||Multiply||Alt+E+S+M / Alt+Ctrl+V+M||Multiply the values in the copied cells to those in the pasted cells.|
|05||Divide||Alt+E+S+I / Alt+Ctrl+V+I||Divide the values in the copied cells into those in the pasted cells.|
|06||Skip Blanks||Alt+E+S+B / Alt+Ctrl+V+B||Avoid copying blank cells to paste area.|
|07||Transpose||Alt+E+S+E / Alt+Ctrl+V+E||This option inverts all the data information before it pastes it so that all the columns become rows and the rows become columns.|
Note: Note Mathematical operations can only be applied to values. If we performing one of the mathematical operations (Add, Subtract, Multiply, or Divide) must select either All, All except borders, or Values and number formats from the Paste options.
(I). How to Use Paste Special in Excel
(i) Select the cells that contain the data or attributes that want to copy.
(ii) Press Ctrl + C.
(iii) Select the upper-left cell of the paste area. If we are performing a mathematical operation, we will need to select all the cells in the paste area.
(iv) Press Alt+E+S or Alt+Ctrl+V to open the Paste Special dialog box.
Equivalently, ‘Home’ tab ➪ in the Clipboard, click the drop-down of the Paste button ➪ select ‘Paste Special‘
- Select the Paste option according to the requirement.
- Select the Operation option according to the requirement. If we don’t perform a mathematical operation select None.
- Avoid copying blank cells to paste area by selecting Skip blanks.
- Change row data to columns, or vice versa by selecting Transpose.
(A) Using Paste Special in Excel to Perform Advanced Pasting
(01) Converting Formulas / Pivot table into ‘Values’
We can paste formulas or pivot tables with their values in two ways, but in both cases, we select the range or the entire database.
- Values: Select the range(s) ➪ Copy (Ctrl+C) ➪ use Excel Shortcut Alt+E+S+V or Alt+Ctrl+V+V ➪ press Enter or click OK.
- Values and number formats: Select the range(s) ➪ Copy (Ctrl+C) ➪ use Excel Shortcut Alt+E+S+U or Alt+Ctrl+V+U ➪ press Enter or click OK.
(02) Pasting all ‘Formulas’ of the Copied Ranges
Pastes the formulas used in the copied cells or ranges in two ways:
- Formulas: Select the range(s) ➪ Copy (Ctrl+C) ➪ use Excel Shortcut Alt+E+S+F or Alt+Ctrl+V+F to select the ‘Formulas’ option ➪ press Enter or click OK.
Figure: Pasting all ‘Formulas’ of the copied Ranges_using Formulas option
- Formulas and number formats: This option is mostly used rather than the ‘Formulas’ option. This option is the same as Formulas, except that it retains the formatting for any numbers you copy. So Excel will retain currency signs ($), percentage signs (%), and thousands of separators in numbers, but it drops the formatting for fancy fonts, colors, and borders.
Figure: Pasting all ‘Formulas’ of the copied Ranges_using formulas and number formats
Select the range(s) ➪ Copy (Ctrl+C) ➪ use Excel Shortcut Alt+E+S+R or Alt+Ctrl+V+R to select the ‘Formats’ option ➪ press Enter or click OK.
(03) Pasting similar ‘Formatting’ of the copied Ranges
Pastes only the formatting of the copied data, but not the data.
Select the range(s) ➪ Copy (Ctrl+C) ➪ use Excel Shortcut Alt+E+S+T or Alt+Ctrl+V+T to select the ‘Formats’ option ➪ press Enter or click OK.
(04) Pasting similar ‘Column Widths’ of the copied Ranges
Pastes the column widths so they match the width of the columns of the copied cells.
Select the range(s) ➪ Copy (Ctrl+C) ➪ use Excel Shortcut Alt+E+S+W or Alt+Ctrl+V+W to select the ‘Column widths’ option ➪ press Enter or click OK.
(05) Pasting all ‘Comments’ to the entire Ranges
Pastes only comment attached to the cell.
- Example-1: In the given example, Cell A3 has a comment and we want to copy that entire ranges instead of typing comments in each cell.
Copy (Ctrl+C) the cell A3 with the comment ➪ then select the area want to paste the comments ➪ use Excel shortcut Alt+E+S+C / Alt+Ctrl+V+C to select the ‘Comments’ option ➪ then press Enter or click OK.
Entire selected area copied with the same comments.
- Example-2: Similarly, we can copy all the comments sequentially from a range into the other range.
Select the entire range (i.e., A2:A11) with comments ➪ copy the range by Ctrl+C ➪ either select the first cell or select the entire range of the pasted range ➪ press Alt+E+S+C / Alt+Ctrl+V+C to select the ‘Comments’ option ➪ press Enter or OK
All the comments copied sequentially from one range to the other range successfully.
(06) Pasting ‘Validation’ rules to the entire Ranges
Pastes data validation rules of a copied cell to the entire range(s).
(i) Apply a Data Validation:
Select a cell wanted to apply Data validation ➪ press Alt+D+L to open the Data Validation dialog box.
Go to the Settings tab ➪ click on ‘Allow’ drop-down list ➪ select ‘List’ ➪ in the ‘Source’ box, type the criteria with a comma separator ➪ press Enter or click OK.
Data Validation is applied and ready to use.
(ii) Apply Data Validation Rules to the entire ranges:
Copy the cell where Data Validation rules applied ➪ then select the range of cells wanted to apply the validation ➪ press Excel shortcut Alt+E+S+N or Alt+Ctrl+V+N to select the ‘Validation’ option under the Paste Special dialog box ➪ press Enter or click OK.
Data Validation is applied in the selected range.
(B) Using Paste Special in Excel to Perform “Calculations”
Paste Special can perform calculations while pasting. Paste Special has operation options for Add, Subtract, Multiply, and Divide. A single cell value can be pasted to a group of cells while performing an arithmetic operation, or a group of cells can be pasted to another group of cells while performing an arithmetic operation.
(01) Adding Similar Value in the Range
Now, we want to add the value 5 in cell C2 to each of the cells in the range A2:A6. Figure 1
We would perform the following steps:
- Select cell C2 and then make a copy.
- Select the cells where we want to paste the operation. In this case that would be range A2:A6.
- Press Alt+E+S+D / Alt+Ctrl+V+D to select the Add operation under the Paste Special dialog box
(Alternatively, Right-click one of the selected cells ➪ then select Paste Special ➪ Select the Add operation). As a result, value 5 has been added to each value in the pasted range.
(02) Adding Different Values in the Range
We can combine a group or multiple groups while performing an arithmetic operation. If we wanted to add the different values present in range C2:C6 to the individual values in range A2:A6, as shown in Figure 2, we would perform the following steps:
- Select the cell range C2:C6 and then make a copy.
- Press Alt+E+S+D / Alt+Ctrl+V+D to select the ‘Add’ operation under the Paste Special dialog box.
(Alternatively, Right-click one of the selected cells a then select Paste Special a Select the Add operation).
(03) Adding Few numbers of Values in the Range
If we were copying fewer values than we were pasting, then the copied values would repeat their pattern. The value in cell C2 would be added to the value in cell A2. The value in cell C3 would be added to the value in A3. Then the value in cell C2 would be added to the value in A4, and the value in C3 would be added to the value in A5.
(04) Multiplying a Value in the Range
If we want to increase the sales target price of every Project Managers by 15%, then we use the Multiply option of Paste Special in Excel. Remember that after increasing 15%, we get an increased value of 115. That means if we multiply 1.15% in the range, we get the actual increased value.
- Copy cell D2 contains an increased percentage value, i.e., 1.15% by pressing Ctrl + C.
- Select cell range B2:B11.
- Press Excel shortcut Alt+E+S+M/ Alt+Ctrl+V+M to select the ‘Multiply‘ option under the Paste Special dialog box. After that press only V or U to select the ‘Values‘ or ‘Values and number formats‘, respectively.
- Press Enter or click OK to get the increased value.
(C) Using Paste Special in Excel to avoid “Skip Blanks”
When we copy a range of cells, any cells that are blank in the copied range will overwrite any cells in the pasted range, thus making those cells also blank.
However, if we use the Skip Blanks option of Paste Special in Excel, the blank cells in the copied range will not overwrite the data in the cells in the pasted range.
- Copy cell range D2:D11 with the blank cells by pressing Ctrl + C.
- Select cell range B2:B11.
- Press Excel shortcut Alt+E+S+B /Alt+Ctrl+V+B to select the ‘Skip blanks‘ option under the Paste Special dialog box. After that press only V or U to select the ‘Values‘ or ‘Values and number formats‘, respectively.
- Press Enter or click OK to get the result.
(D) Using Paste Special in Excel to “Transpose” Rows and Columns
The Paste Special command has a ‘Transpose’ option that is used for converting row data to column data and vice versa.
We would like to use the Transpose function when the row headings require to arrange as the column headings and the column headings as the row headings. Rather than manually retyping the headings, we can easily flip-flop them by using Paste Special’s Transpose option.
Select the range ➪ copy (Ctrl+C) ➪ press Alt+E+S+E or Alt+Ctrl+V+E to select the ‘Transpose’ option ➪ simultaneously, press ‘R’ to select the ‘Formula and number formats’ ➪ press Enter or click OK.
Finally, the dataset is transposed!
(II). How to use Break Link in Excel
(A) How to Manage Linked Workbooks
As we work with a linked workbook, we might need to replace a source file or change where we stored the source and destination files. However, replacing or moving a file can affect the linked workbook. Keep in mind the following guidelines to manage our linked workbooks:
- If we rename a source file, the destination workbook won’t be able to find it. A dialog box opens, indicating “This workbook contains one or more links that cannot be updated.” Click the Continue button to open the workbook with the most recent values, or click the Change Source button in the Edit Links dialog box to specify the new name of that linked source file.
- If we move a source file to a different folder, the link breaks between the destination and source files. Click the Change Source button in the Edit Links dialog box to specify the new location of the linked workbook.
- If we receive a replacement source file, we can swap the original source file with the replacement file. No additional changes are needed.
- If we receive a destination workbook, but the source files are not included, Excel will not be able to find the source files, and a dialog box opens with the message “This workbook contains one or more links that cannot be updated.” Click the Continue button to open the workbook with the most recent values, or click the Break Link button in the Edit Links dialog box to replace the external references with the existing values.
- If we change the name of a destination file, we can open that renamed version destination file without affecting the source files or the original destination file.
(B) Updating Linked Workbooks
- When the workbooks are linked, it is important that the data in the destination file accurately reflect the contents of the source file. When data in a source file changes, we want the destination file to reflect those changes.
- If both the source and destination files are open when we make a change, the destination file is updated automatically.
- If the destination file is closed when we make a change in a source file, we choose whether to update the link to display the current values or continue to display the older values from the destination file when we open the destination file.
(C) To Convert all External Reference Formulas into Values
- Go to the ‘Data’ tab ➪ Click on ‘Edit Links’ command ➪ an ‘Edit Links dialog’ box opens ➪ click the Break Link button. Alternatively, press the Excel shortcut Alt+A+K to open the Edit Links dialog box and then press Alt+B to click the Break Link button (figure below Example 2).
- A dialog box opens, Break Link alerts us that breaking links in the workbook permanently convert formulas and external references to their existing values.
- Click the Break Links button. No links appear in the Edit Links dialog box.
- Click the Close button.
(III). Best Uses of Break Links instead of Paste Special in Excel
(01) Example 1: While Database ties with the external links (or external reference formulas) of the same or different worksheets in the same workbook and we want to break all the links – ‘Excel Break Links‘ is the best way to sever the links.
(02) Example 2: While VLOOKUP and/or HLOOKUP functions are applied to retrieve values from other sources in a filtered Dataset and we want to break all the Links without removing the filter – Excel Break link is the only solution to break all the links and convert formulas to their values in the filtered dataset.
(IV). Comparison between Paste Special and Break Link in Excel
Table 3: Differences between Paste Special & Break Link in Excel
|Characteristics||Paste Special in Excel||Break Link in Excel|
|Source||Select cell(s) or range(s) ➪ Copy ➪ Home ➪ Clipboard ➪ Paste Special||Data ➪ Connections ➪ Edit Links ➪ opens Break Links dialog box ➪ need confirmation of Break Links|
|Excel Shortcut||(i) Paste as Values: Select cell(s) or range(s) ➪ Copy ➪ Alt+E+S+V or Alt+Ctrl+V+V ➪ Press Enter or click OK|
(ii) Paste as Values and number formats: Select cell(s) or range(s) ➪ Copy ➪ Alt+E+S+U or Alt+Ctrl+V+U ➪ Press Enter or click OK
|Press Alt+A+K opens the ‘Edit Links’ dialog box ➪ then press Alt+B to click the 'Break Links' button ➪ opens another window for confirmation, select Break Link to apply the activity.|
|Nature of Activity||Versatile. We use the Paste Special features in different purposes, including convert formulas to values.||Only use to severing all the external links ties with the different data source(s) or workbook(s).|
|Work in the worksheet(s)||Paste Special works only into the selected region, it may be selected cell(s) or range(s) or selected worksheet(s).|
For example, if we select single worksheet it works on it only, other worksheets have no effect.
If we apply it in a particular range of a worksheet, it works in that range only, but the unselected ranges have no effect.
|Break link applicable in all worksheets in a workbook. Means all the external links will break in all worksheets. Single external reference formula exists between the destination file and the source file. |
So, we have no choice of keeping a useful link between the destination file and the source file.
|Duration||If the database is large and workbook size is heavy, then Paste Special will take time to convert formulas to values.||Instantly break all the external links (or external reference formulas) between the destination file and the source files. As a result, all the external reference formulas converted into values.|
|Condition to apply||Paste Special in Excel works with both internal and external formulas.||Break Link in Excel works with the external reference formulas only.|
|Working with Filtered Database||Paste Special does not work with the conditions applied filtered database. In this case, unfiltered all the conditions applied in the database ➪ Copy ➪ use Paste Special||An important feature of the Break link is that it has no effect either database is filtered with conditions or not.|
|Undo (Ctrl+Z) the Last Action||We can easily undo the last Paste Special action.||We cannot undo the Break Link action. To restore the links, you must reenter the external reference formulas.|
- 80+ Excel Shortcuts with ALT Key || Best Hotkey of Keyboard Shortcuts
- 90+ Best Excel Shortcut Keys and Hotkeys with CTRL Key
Latest posts by Sudipta Mondal (see all)
- Excel SUMIFS Function - October 27, 2019
- How to use INDEX MATCH Function in Excel with 04 Best Examples - October 13, 2019
- 09 Steps to Reduce Excel File size || Evaluate Existing Formulas || - September 5, 2019
Advance Excel Forum
Paste Special in Excel Vs Break Link - Which one is better?