What is excel special paste. Excel Paste Special: Values, Formats, Column Widths

I have often regretted that MS Excel does not have such a convenient keyboard shortcut for pasting a value as shortcuts Ctrl + Shift + V which is used in Calc... Oddly enough, but in Excel there really are NO standard and working by default keyboard shortcuts for inserting values. but alternative ways there is. Below we will look at them.

Method one , the most famous, the most common and ... the most time consuming.

Cell A1 contains a formula that sums up the values ​​of cells B1 (1) and C1 (2). Instead, we want to insert the result of its work (3). What are we doing for this?

1. Select cell A1
2. Right-click to call context menu
3. In the menu, select the item Copy

4. Again, right-click the context menu (because it disappears after selecting any command from it)
5. Choose an item Paste special

6. In the window that appears, select meaning

7. Click OK

That's it, now instead of the formula = B1 + C1 we have the value 3

Don't you think that seven steps for such simple action like inserting a value, a bit too much?

Method two(works in all versions of Excel), little-known, but much faster.

1. Select cell A1

2. Move the mouse over the lower border of the cell until we get the cursor in the form of a cross, each of the rays of which is crowned with arrows
3. Press the right mouse button and drag the cell somewhere (it is enough even to the adjacent one) and return it back
4. Release the right mouse button and select the item in the menu that appears Copy only values

Ready. But even this is long, moreover, it is not suitable for those who are used to using "hot keys" rather than the mouse.

Method three(works in versions of Excel afterExcel 2003) , unknown (I did not find it on the Internet), but it runs even faster, although it requires a one-time pre-configuration of Excel.

So, first let's add the insert values ​​button to the panel quick access(PBG).

1. Click on the PBD settings button and select the item from the list that appears Other commands ...

2. In the menu Select teams from: select item All teams and from the list that appears, select the icon marked as Paste values.

3. Press the button Add and move the icon that appears in the list on the right to the top of the list.

4. Our button appeared on the PBG

5. Click Alt and we see the number of our button (in this case, it is number 1)

That's it, the setup is complete.

How do you use this button now?

It is possible for its intended purpose:
1. Select cell A1
2. Copy (you can use the mouse, you can use a keyboard shortcut)
3. Press the button Paste values

But remember, I said that you can do without a mouse? Then the process will look like this:
1. Select cell A1
2. Ctrl + C
3. Alt + 1, where 1 is the number of our button on the PBD.

Quickly? Comfortable? I think hotkey lovers will appreciate this method :)

Method four, by a macro.

Paste this code into the standard module of your personal macro book and when you start Excel, you will always have access to quick insertion of values ​​with the assigned keyboard shortcut (in the example, this combination Ctrl + Shift + V).

Sub CopyPasteValue ()
Selection.PasteSpecial Paste: = xlPasteValues
End Sub

This method has two disadvantages:
1. After applying the macro, the change history stack is cleared and undoing the last actions becomes impossible.
2. Macros must be allowed.

Method five, the most exotic and the most ... useless I mention it only for completeness of the picture, in no way pretending that someone will use it.

This method is based on the fact that formulas are not saved when exchanging data between applications. Accordingly, if you copy data from Excel of one version and paste into another, then the insertion will be made by values ​​and not by formulas. In the video, I am copying a formula from Excel 2010 to Excel 2003 and vice versa. In either case, the value is inserted.

And how to change the data to fit the desired conditions from the clipboard before pasting it into a cell? To do this, use the Excel Paste Special tool. Those. if you need to insert data without formatting, or insert only formulas, then the tool is for you. The function is extremely useful - I highly recommend taking a closer look.

In principle, all the same can be done in several actions without a special insert, but with it, you will see now, it is much more convenient.

If you have copied the data with calculations and want to paste only the values ​​of this data, already without formulas. Do - Copy desired cells - select a cell / range to paste - right-click and select Paste Special () .

Then the table from picture number 1 will appear. Select the "values ​​only" checkbox.

This was an example, and now we will analyze all the proposed options.

Option "Insert:"

It seems clear here that you can insert separately what is contained in the cell. We inserted values ​​in the example above.

- insert conditions for values. We understand literally, we insert conditions on top of the values. This is either data validation () or .

Those. if you need to impose additional conditions.

- paste with the original theme. A topic in Excel is the format of an entire workbook or sheet. Applies to consistent color scheme or chart formats. If you have one theme applied to this sheet and you need to insert another. this clause applies. To be honest, I rarely use this feature.

- insert without frame... Insert without border format

- insert column widths... Insert formatting Column width.

- formulas and formats of numbers. Both formulas and formats are inserted

- values ​​and formats of numbers. Very comfortably. Not only values ​​are inserted, but also number formats.

Operation section (add, subtract, divide, multiply)

Cool stuff! For example, you have two ranges

And you need to split the data from column D by the data from column B (see the picture). No formulas. This is where Paste Special in Excel comes in handy.

Select the range B3: B5. We copy. Select the range D3: D5. Click on Paste Special. And mark the checkbox "split"

Voila. Try it yourself - you will like it.

Finally, three bombs special insert

I guarantee these features will save you a lot of time.

Word for Office 365 Outlook for Office 365 PowerPoint for Office 365 Access for Office 365 Word 2019 Outlook 2019 PowerPoint 2019 Access 2019 Word 2016 Outlook 2016 PowerPoint 2016 Access 2016 Visio 2013 Word 2013 Outlook 2013 PowerPoint 2013 Access 2013 Word 2010 Outlook 2010 PowerPoint 2010 Access 2010 Visio 2010 Word 2007 Outlook 2007 PowerPoint 2007 Access 2007 Word Starter 2010 Less

You can set formatting when you paste slides, text, pictures, objects, and tables from other programs or the Internet into Office applications such as Access, Word, PowerPoint, Excel, and Outlook.

Using a special paste

Like pictures and other objects, text in a presentation has its own formatting — for example, font size, color, and font. When you copy text that is formatted differently in an Office application, programs such as PowerPoint or Word automatically format the text to match the destination text. However, use Paste Special to keep the original formatting and paste them as a link or image, for example.

Insert

To set the format for the inserted slide, text, or object, click Insert and then select one of the values ​​shown in the list Format below.

Format	Date and time:
Formatted text
Unformatted text	Do you want to use formatting Office programs into which the text is inserted.
Graphic object Microsoft Office	You want the contents of the clipboard to be displayed as a Microsoft Office drawing object.
Picture (GIF)	You want the contents of the clipboard to be displayed as a GIF image. In the format GIF files only 256 colors are supported, so it is more suitable for scanned images, such as illustrations, and less for color photographs. GIF is also good for drawings, black and white images, small text, and animations.
Picture (JPEG)	You want the contents of the clipboard to be displayed as a JPEG image. Format JPEG files supports 16 million colors and is best suited for photographs and complex graphics
Drawing (PNG)	You want the contents of the clipboard to be output as a PNG image. The PNG file format is similar to GIF but provides better support colors. PNG compresses solid areas of color while maintaining crisp detail in sketches, logos, or illustrations with text. Save, restore and re-save PNG images occur without loss of quality. Unlike GIF, PNG format does not support animation. Also, some legacy browsers and applications do not support PNG.
Drawing (Windows metafile)	You want the contents of the clipboard to be output as a WMF drawing.
Drawing (extended metafile)	You want the contents of the clipboard to be output in EMF format.
Drawing (DIB)	You want the contents of the clipboard to be output in DIB format. For example, you can save a slide as a graphic for use in web pages. DIB format is a representation graphic object in the computer memory in the form of rows and columns of points. The value of each point (whether it is full or not) is recorded in one or more data bits.
Bitmap	You want the contents of the clipboard to be displayed as a bitmap. Format Microsoft Windows BMP can display millions of colors. Since it is supported by many programs, it is recommended to use it if the person you are submitting the drawing to may not have the application in which you created the image.

Insert Link

Note: Parameter To tie will be unavailable if you cut or copy content from a document that does not support it, or if the document you are trying to link to has not yet been saved.

To have an OLE app icon instead of the actual content, select the check box In the form of an icon... You can click the icon to open the app and view the content. Checkbox In the form of an icon available only when using the parameter To tie or when pasting content as an OLE object. You can change the icon for the inserted content.

The article describes the possibilities of using the Windows clipboard and Microsoft Office, as well as the peculiarities of copying and pasting data in Excel. Understanding and correct use These operations can significantly speed up the execution of routine operations in data processing.

Operating system clipboard

In terms information systems clipboard clipboard) is a public domain for different applications. random access memory... The operating system provides a low-level programming interface for moving data to and from the clipboard at the user's request. The correct application of this API is the standard when developing Windows applications. That is, any program should provide the user with the ability to use the clipboard when using the same keyboard shortcuts or menu items. Further we will talk about Windows, although, in principle, the description of the principle of operation of the clipboard is identical for any modern operating system personal computers or mobile devices.

Copy and paste are standard operations for all Windows applications. For this, universal hotkey combinations are reserved, available in almost any program:

• Ctrl + c- copy (eng. copy)
• Ctrl + x- cut (eng. cut)
• Ctrl + v- insert (eng. paste)

Shortcuts similar in functionality are also often mentioned: Ctrl + Ins - copy, Shift + Ins - paste, Shift + Del - cut. However, we do not recommend using these combinations, as some applications replace their default behavior with something else. For example, pressing Shift + Del in Windows Explorer instead of the expected cut of the relocatable file, it will cause it to be deleted bypassing the recycle bin. That is, instead of moving, an irrecoverable loss of data may occur.

If you prefer to use a mouse instead of a keyboard, the standard input fields of Windows applications usually support a context menu with operations for copying, cutting and pasting text.

The copied text or other block of data can be pasted into another application, depending on the capabilities of the latter. For example, the text copied in Notepad cannot then be pasted into the Paint editor. However, the same text typed in Word is successfully pasted into Paint as a bitmap. This possibility is implemented at the software level by moving data to the clipboard in several formats at the same time. If you type the word Example in bold in Word, then copy it, then several blocks of information will appear on the clipboard:

Now, if you try to paste data into Notepad, the program will select from the clipboard the only available version of information - text without formatting. If you do the same in Paint, the last area, the drawing, will be processed. The set of available formats for copying and pasting depends on the capabilities of a particular program. If the application supports several formats of information (pictures, text, complex objects), then it allows you to select the insertion option. For example, in Microsoft Word this procedure is implemented through the menu item Paste special:

If you use the usual data insertion, then the most suitable format for this program will be automatically selected. Excel also supports the operation of pasting data in various formats, similar to Word, if the information came to the clipboard from another application. If a range of cells was copied in the same application, then Paste Special is replaced by an internal Excel operation (separate insertion of values, formulas, formats, etc.), which does not use the operating system's clipboard.

Some other applications also implement their own copy-and-paste routines without using the clipboard. For example, in Explorer, the "copy" operation does not move the entire file to the Windows clipboard. Instead, only the link to this file is remembered and will be processed when the insert operation is performed.

Microsoft Office Clipboard

As noted above, the operating system is responsible for operations with the standard clipboard. One of the tasks in this case is the correct use of RAM. The operating system, in particular, takes care of the timely clearing of the clipboard area. In the current implementation, the standard Windows clipboard allows only one block of copied information to be stored. When a new copy procedure is called, this block is preliminarily cleared, and the memory area reserved for it becomes available for use as a clipboard.

To improve the possibilities of working with the user interface in Microsoft Office, starting from version 2000 (9.0), an extended clipboard has been implemented with the ability to simultaneously store several (up to 24x) copied blocks of information. The user is presented with an interface for selecting and inserting any of these blocks into any open Office application(Excel, Word, PowerPoint, OneNote, etc.). Perhaps it would be more logical to implement such functionality at the operating system (Windows) level, although this would require changing standards for all applications. Now it turns out that the multiple clipboard works as long as at least one Office application is open. If it is closed, then only the Windows clipboard with a single block of copied information becomes available.

The interface of the multiple clipboard in Office 2010 opens and is configured on the Home ribbon in the block of the same name (arrow in the lower right corner).

If we talk about the usefulness and convenience of working with multiple clipboard, then there are different opinions. I personally never use this functionality- it's easier to copy again. But, most likely, it is a force of habit.

Copy and paste in Excel

As noted, Excel fully supports the Office clipboard, but it also supports native copy and paste operations without using the clipboard.

It should be noted here that the reuse of objects through copy and paste is one of the determining factors in speeding up information processing when using Excel spreadsheets.

What actually happens in Excel when you click the copy button when you select a range of cells?

First, as in other Windows applications, a set of information is placed in the operating system clipboard in several formats: plain text, formatted text, bitmap, etc. Thus, for example, you can use graphic editor and insert a screen display of a block of selected cells there. If you insert the same block back into Excel, then a picture will be inserted:

Secondly (and this is the main thing), when copy excel performs an internal operation to work with cells in a spreadsheet. By pressing the key combination Ctrl + C, the context menu item or the copy buttons are saved in memory links to the selected cells. These cells can be great amount... They can be located in one rectangular range, or in several unconnected ranges (to select such ranges, hold down the Ctrl key while selecting with the mouse). Theoretically, it is possible to copy cells on different sheets (several sheets can also be selected through holding Ctrl keys on the sheet label), but these cells must be located at the same address, while subsequent insertion is also possible only on the same selected sheets. In practice, it is better to refuse to copy-paste on several sheets at the same time, since this operation is not very intuitive and often leads to data loss.

You can also copy links between different, but open in the same Excel application, files. A typical situation that causes misunderstanding on the part of the user is the processing of data in several simultaneously open applications Excel. When you try to copy data from one file to another, the program pastes the result only as formatted values ​​without formulas. This is not a mistake, just several at the same time open programs Excel takes up different areas of memory and there can be no links between them. Copy and paste in this case only uses the Office clipboard. To correct the situation, open the files in one Excel application.

Once again, we draw your attention to the fact that when you start the copying operation, not data (text, formulas, formats) are written to the program memory, but only links to the addresses of the selected cells. For clarity, the Excel interface outlines the copied cells with an animated frame.

After copying the ranges, the paste operation becomes available. Before that, you must select one or more ranges or cells to receive data from the copied area.

The insert is available as long as the user has not performed any action that changes the data in the spreadsheet. You can select cells and ranges, move between sheets and files. Also, the paste operation itself does not override the copy area. This allows cells to be copied multiple times in a row for different ranges. Any other operations with the user interface, for example, data entry, grouping, sorting, formatting, will reset the previously copied link. You can forcibly reset the copy area by pressing the Esc key.

If the selected area of ​​the worksheet before pasting does not match the size of the copied area, Excel will try to propagate the data multiple times or paste only part of the data. In some cases this is not possible (for example, the copy and paste areas overlap), then the program displays an error message.

In addition to simple pasting, the copied range can be added to the sheet area with the expansion of the borders to the left or down through the context menu item "Paste copied cells".

If you use the Office clipboard to paste data, data will be added with the loss of formulas, similar to the example with copying between different applications Excel.

By default, when the insert operation is called, all the attributes of the original range will be propagated to the selected range, namely: formula, format, value, note, conditions. Sometimes a complex description of the rules for copying formulas is given, since they seem to be automatically converted when the addresses of the destination range are changed. In fact, the formulas are copied in the R1C1 format and at the same time remain unchanged (you can check by switching the view Excel worksheet in R1C1). Displaying in the familiar A1 format simply converts the formula to new coordinates.

The cut operation, as opposed to copying, clears the original range after pasting. If the insert has not been performed, no action will be taken.

Paste special

Another important feature Excel Ranges Copy is to separately paste the attributes of the copied ranges. In particular, you can only paste comments from the copied range into a new location in the worksheet. The set of attributes available for split copy is displayed in the Paste Special dialog:

• meaning
• format
• formula
• note
• value conditions (data validation)

V different versions Excel's paste special stencil is slightly different. But regardless of this, you can use the repeated operation of inserting the attribute. For example, to paste formulas with annotations but no formats, you must copy the original range once and then do two special pastes in succession on the same range: paste only formulas, then paste only annotations.

The Paste Special dialog also contains a radio button block that allows you to perform mathematical operations on a range of data: add, subtract, multiply, and divide. The operation will be applied to the range selected before insertion. And the copied cells will contain the coefficients of addition, subtraction, multiplication or division. In most cases, a single factor is used for the entire range. For example, you can copy the number 10, then select a range and select Paste Special with Multiplication - as a result, all data in the selected range will be multiplied by 10. If the cells contained a formula, then it will be converted according to mathematical rules:

Another option for special paste is transposition range. After performing this operation, the resulting range will be rotated by 90 degrees - data from rows will fall into columns and vice versa.

Dragging with the mouse

Excel novice users are quickest to master copying data by dragging and dropping cells. For this there is a special pointer on the frame of the selected range. By the way, this feature can be turned off in the general settings of Excel.

The operation of dragging and dropping cells with the mouse is in most cases analogous to copying and pasting for adjacent ranges of cells. From a technical point of view, the main difference is that no data is saved on the clipboard when you drag the mouse. Excel performs only the internal paste procedure, and then clears the information about the copy source. From point of view user interface a distinctive feature of dragging and dropping is the ability to fill cells based on an automatically determined number series in the selected range. Many people think that Excel can only continue a sequentially increasing series by adding one. This is not the case, the program itself forms the magnification factor as an average value in the selected range. In the pictures of the example, this number is 2.

If all the selected cells contain formulas before starting dragging, then the procedure will be completely identical to the copy and paste operations. In addition, using a special pointer, you can explicitly start the copy operation without changing the values ​​(the "Copy cells" option):

You can say that drag and drop on small ranges of data is faster, but in general, copy-and-paste operations are more flexible.

Command " Menu" - "Edit" - "Specialinsert"(or through the context menu called by the right mouse button) allows you to expand the possibilities of conventional copying.

Let's look at a few examples.

To replace formulas in cells with final values, select the required range, copy it, select where you want to copy the values, run " Menu" - "Edit" - "Paste special", check the item" meaning"and click OK.

To copy the formulas and paste them without changing the format of the final cells, use the item " formulas".

It often happens that a table contains numeric values ​​stored as text.
To fix this, select an empty cell, click copy, then select the range you want to convert to numbers, run " Paste special", select the operation" fold"and click OK.

In general, you can change each value of the selected range in accordance with the selected item " operation". Suppose we need to decrease each value of the range by 100 times. We write 100 in an empty cell, copy it, select the range that needs to be changed, run" Paste special", choose" operation" "divide"and click OK.

In order for the operation to be performed only on non-empty cells, you can additionally check the " skip empty cells".

If you need to transpose the table, i.e. swap rows and columns, do the following. We copy the source table, choose where to insert the final data, run " Paste special", check the box" transpose"and click OK.