Paste Special in Excel Vs Break Link - Which one is better?

Paste Special in Excel Vs Break Link – Which one is better?

By

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 OptionsExcel ShortcutDescription
01AllAlt+E+S / Alt+Ctrl+VThis 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.
02FormulasAlt+E+S+F / Alt+Ctrl+V+FPastes the formulas used in the copied cells.
03ValuesAlt+E+S+V / Alt+Ctrl+V+VPastes only the results of formulas, not the formulas themselves.
04FormatsAlt+E+S+T / Alt+Ctrl+V+TPastes only the formatting, not the data.
05CommentsAlt+E+S+C / Alt+Ctrl+V+CPastes only comment attached to the cell.
06ValidationAlt+E+S+N / Alt+Ctrl+V+NPastes data validation rules of a copied cell to the entire range(s).
07All Using Source ThemeAlt+E+S+H / Alt+Ctrl+V+HPastes 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.
08All Except BordersAlt+E+S+X / Alt+Ctrl+V+XPastes everything except any borders that were applied to the copied cells.
09Column WidthsAlt+E+S+W / Alt+Ctrl+V+WPastes the column widths so they match the width of the columns of the copied cells.
10Formulas and Number FormatsAlt+E+S+R / Alt+Ctrl+V+RThis 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.
11Values and Number FormatsAlt+E+S+U / Alt+Ctrl+V+UPastes 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 OptionExcel ShortcutDescription
01NoneAlt+E+S+O / Alt+Ctrl+V+ODo not perform any mathematical operation to the pasted cells.
02AddAlt+E+S+D / Alt+Ctrl+V+DAdd the values in the copied cells to those in the pasted cells.
03SubtractAlt+E+S+S / Alt+Ctrl+V+SSubtract the values in the copied cells from those in the pasted cells.
04MultiplyAlt+E+S+M / Alt+Ctrl+V+MMultiply the values in the copied cells to those in the pasted cells.
05DivideAlt+E+S+I / Alt+Ctrl+V+IDivide the values in the copied cells into those in the pasted cells.
06Skip BlanksAlt+E+S+B / Alt+Ctrl+V+BAvoid copying blank cells to paste area.
07TransposeAlt+E+S+E / Alt+Ctrl+V+EThis 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

 

How to use Paste Special in Excel_1
Figure 1: 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. 
  •  

Reduce Excel File size-28 (Convert into Values the Unused Pivot Table)

 

(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. 

Pasting all 'Formulas' of the copied Ranges_using formulas and number formats

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.

Pasting all 'Formulas' of the copied Ranges_using Formulas option

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. 

Pasting all 'Formulas' of the copied Ranges_final result
Pasting all ‘Formulas’ of the copied Ranges_final result

 

(03) Pasting similar ‘Formatting’ of the copied Ranges

Pastes only the formatting of the copied data, but not the data.

Pasting similar Formatting of the copied Ranges_1
Figure: Pasting similar ‘Formatting’ of the copied Ranges

 

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. 

Pasting similar Formatting of the copied Ranges_2
Figure: Applied similar Formatting of the copied ranges

 

(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.

Pasting similar Column Widths of the copied Ranges_1
Figure: Pasting similar Column Widths of the copied Ranges

 

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. 

Pasting similar Column Widths of the copied Ranges_2
Figure: Applied similar Column Widths of the copied ranges

 

(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.   

Pasting all 'Comments' to the entire Ranges_1

 

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.   

Pasting all 'Comments' to the entire Ranges_2

 

Entire selected area copied with the same comments. 

Pasting all 'Comments' to the entire Ranges_3

 

  • Example-2: Similarly, we can copy all the comments sequentially from a range into the other range. 

Pasting all 'Comments' from one range to other range_1

 

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

Pasting all 'Comments' from one range to other range_2

 

All the comments copied sequentially from one range to the other range successfully.  

Pasting all 'Comments' from one range to other range_3  

 

(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.

   Pasting 'Validation' rules to the entire Ranges_step1

 

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. 

Pasting 'Validation' rules to the entire Ranges_step2

 

Data Validation is applied and ready to use.  

Pasting 'Validation' rules to the entire Ranges_step3

 

(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.

Pasting 'Validation' rules to the entire Ranges_step4

 

Data Validation is applied in the selected range. 

Pasting 'Validation' rules to the entire Ranges_step5

 

 

(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.

Adding Similar Value in the 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).

Adding Different Values in the Range

 

(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.

Adding Few numbers of  Values in the Range

 

(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.    

Using Paste Special to Multiply

 

  • 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.

Using Paste Special to avoid Skip blanks

 

  • 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.

Using Paste Special to Transpose Rows and Columns_1

 

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. 

Using Paste Special to Transpose Rows and Columns_2

 

Finally, the dataset is transposed!

Using Paste Special to Transpose Rows and Columns_3


(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

Best Uses of Break Links instead of Paste Special_Example1
Example 1_Best Uses of Break Links instead of Paste Special_Step1

 

(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. 

Best Uses of Break Links instead of Paste Special_Step2
Example 1_Best Uses of Break Links instead of Paste Special_Step2

 

(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. 

Example 2_Best Uses of Break Links instead of Paste Special_Step1
Example 2_Best Uses of Break Links instead of Paste Special_Step1

 

Example 2_Best Uses of Break Links instead of Paste Special_Step2
Example 2_Best Uses of Break Links instead of Paste Special_Step2

(IV). Comparison between Paste Special and Break Link in Excel

 

Table 3: Differences between Paste Special & Break Link in Excel

CharacteristicsPaste Special in ExcelBreak Link in Excel
SourceSelect cell(s) or range(s) ➪ Copy ➪ Home ➪ Clipboard ➪ Paste SpecialData ➪ 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 ActivityVersatile. 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.
DurationIf 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 applyPaste Special in Excel works with both internal and external formulas.Break Link in Excel works with the external reference formulas only.
Working with Filtered DatabasePaste Special does not work with the conditions applied filtered database. In this case, unfiltered all the conditions applied in the database ➪ Copy ➪ use Paste SpecialAn 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 ActionWe 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.

 


Learn-More-5

Sudipta Mondal
Follow AEF
Advance Excel Forum

Paste Special in Excel Vs Break Link - Which one is better?

Editor's Rating:
5

Leave a Comment

You may also like