This article describes how to use the Windows clipboard and Microsoft Office, as well as features of copying and pasting data into Excel. Understanding and correct use these operations can significantly speed up the execution routine operations when processing data.

Clipboard operating system

In terms information systems clipboard clipboard) is a public area 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. Correct use of this programming interface is a standard when developing Windows applications. That is, any program must provide the user with the ability to use the clipboard when using the same keyboard shortcuts or menu items. Next we will talk about Windows, although, in principle, the description of the principle of the clipboard operation is identical for any modern operating system personal computers or mobile devices.

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

• Ctrl+c – copy copy)
• Ctrl+x – cut cut)
• Ctrl+v – paste paste)

Keyboard shortcuts of similar 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 override their default behavior. For example, pressing Shift+Del in Windows Explorer Instead of cutting the moved file as expected, it will be deleted bypassing the trash. That is, instead of moving, permanent data loss may occur.

If you prefer to use a mouse instead of a keyboard, standard input fields in Windows applications usually support a context menu with copy, cut, and paste operations.

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

Example	Text as a set of characters without formatting
	HTML formatted text
(\rtf1\ansi\ansicpg1252\uc1 (\b Example)(\par ))	RTF formatted text
	Screen block bitmap

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

If you use regular data insertion, the most suitable format for this program will be automatically selected. Excel also supports the operation of inserting data in various formats, similar to Word, if the information on the clipboard came 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 pasting of values, formulas, formats, etc.), which does not use the operating system clipboard.

Some other applications also implement their own copy-paste routines without using the clipboard. So, for example, in Explorer, the "copy" operation does not move the entire file to the Windows clipboard. Instead, only the link to that 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 timely cleaning of the clipboard area. In its current implementation, the standard Windows clipboard allows you to store only one block of copied information. When a new copy procedure is called, this block is preemptively cleared, and the memory area reserved for it becomes available for use as a clipboard.

To improve the ability to work with the user interface in Microsoft Office, starting with version 2000 (9.0), an expanded clipboard has been implemented with the ability to simultaneously store several (up to 24) copied blocks of information. The user is provided with an interface for selecting and inserting any of these blocks into any open Office application (Excel, Word, PowerPoint, OneNote, etc.). It might be more logical to implement similar functionality at the operating system level (Windows), although this would require changing standards for all applications. Now it turns out that multiple clipboards work 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 multiple clipboard interface in Office 2010 is opened and 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 clipboards, then there are different opinions. I personally never use this functionality– it’s easier to copy it again. But, most likely, this is a force of habit.

Copy and paste in Excel

As noted, Excel fully supports the buffer Office sharing, but in addition, this program supports native copy and paste operations without using the clipboard.

It should be noted here that the reuse of objects through copying and pasting is one of the determining factors for 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 on the operating system's clipboard in several formats: plain text, rich text, bitmap, etc. Thus, you can, for example, use graphic editor and insert there a screen display of a block of selected cells. If you paste the same block back into Excel, the picture will be inserted:

Secondly (and this is the main thing), when copying Excel performs an internal operation to work with cells spreadsheet. By pressing the Ctrl+C key combination, a context menu item or the copy button, the links to the selected cells. There can be a huge number of these cells. They can be located in one rectangular range, or in several unrelated ranges (to select such ranges, hold down the Ctrl key while selecting with the mouse). Theoretically, it is possible to copy cells to different sheets(several sheets can also be selected by holding the Ctrl key on the sheet shortcut), but these cells must be located at the same address, and subsequent insertion is also possible only on the same selected sheets. In practice, it is better to avoid copying and pasting on several sheets at the same time, since this operation is not very clear and often leads to data loss.

It is also possible to copy links between different files opened in the same Excel application. 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. It's not an error, just several at the same time open source software Excel occupy different areas of memory and there cannot be any links between them. When copying and pasting in this case, only the Office clipboard is used. To correct the situation, open the files in one Excel application.

Please note once again that when starting a copy 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 surrounds the copied cells with an animated border.

Once the ranges have been copied, the paste operation becomes available. Before doing this, you need to select one or more ranges or cells to receive data from the copied area.

Insertion is available as long as the user has not performed any action that changes the spreadsheet data. You can select cells and ranges, move between sheets and files. Also, the paste operation itself does not cancel the copy area. This allows you to copy cells multiple times in a row for different ranges. Any other user interface operations, such as data entry, grouping, sorting, formatting, will reset the previously copied link. You can forcefully 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 spread the data multiple times or paste only part of the data. In some cases this may not be 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 borders expanding to the left or down through the “Paste copied cells” context menu item.

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 Excel applications.

By default, when calling the insert operation, all the attributes of the original range will be distributed 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 change. In fact, the formulas are copied in the R1C1 format and remain unchanged (you can check by switching the Excel sheet view to R1C1). Displaying in the usual A1 format simply transforms the formula into new coordinates.

A cut operation, unlike a copy operation, clears the original range after the paste occurs. If the insertion has not been completed, then no action will be taken.

Special insert

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

• meaning
• format
• formula
• note
• conditions on the value (data verification)

IN different versions Excel's set of Paste Special elements is slightly different. But regardless of this, you can use the repeated attribute insertion operation. For example, to insert formulas with notes, but without formats, you need to copy the source range once, and then perform two special pastes sequentially on the same range: pasting only the formulas, then pasting only the notes.

The Paste Special dialog also contains a block of switches 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 addition, subtraction, multiplication or division coefficients. In most cases, a single coefficient is used for the entire range. For example, you can copy the number 10, then select the 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 paste special is transposition range. After performing this operation, the resulting range will be rotated by 90 degrees - data from the rows will fall into the columns and vice versa.

Dragging with the mouse

Beginner Excel users learn to copy data most quickly by dragging and dropping cells. For this purpose there is a special pointer on the frame of the selected range. By the way, this feature can be disabled in general parameters Excel.

The operation of dragging cells with the mouse is in most cases analogous to copying and pasting for adjacent ranges of cells. WITH technical point The main difference is that dragging the mouse does not save any data on the clipboard. Excel only performs the internal paste procedure and then clears the copy source information. From a user interface perspective distinctive feature Drag and drop 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 so, the program itself generates the magnification factor as the average value in the selected range. In the example pictures this is the number 2.

If all selected cells contain formulas before dragging begins, 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 might say that drag-and-drop is faster on smaller data ranges, but in general copy-paste operations are more flexible.

The volumes of information stored in Excel sometimes reach very impressive sizes, have different sources and can partially copy themselves. In addition to data, table formatting and formulas play an important role. All this is useful to be able to manage correctly regarding copying and pasting.

Paste from web pages

The text of Internet pages almost always has an individual style, which is saved in the clipboard and recognized by the application. But Excel sheets may have their own design that needs to be preserved.

For example, let’s copy the exchange rates of the Central Bank of the Russian Federation from home page Yandex to worksheet. The image shows that the application has changed its appearance quite a bit, but in general everything remains the same as in the source code.

Let's repeat the insertion, but now taking into account the final formatting. Find the Clipboard area on the Home tab ribbon and expand the Paste Options menu by clicking the arrow below the Paste icon.

2 options will be provided. The first one is called "Keep Source Formatting". It is similar to a simple insert. The second method, called “Use Final Formatting,” is the opposite of the first, but preserves the position of the elements relative to each other. Those. its only difference is the removal of hyperlinks, font style, borders, merging cells and other things.

The insert options menu also contains 2 additional items:

• Updatable web request – available only when copying elements from the Internet and allows you to save the data of the entire web page according to the specified parameters;
• Paste Special is always available, but has different options depending on the data source.

Copying book contents

Excel provides many more ways to insert when copying internally than when copying from external sources. Below is a screenshot of these parameters and their description (order from top left to right):

• Paste – simple insertion.
• Insert formulas – only formulas are inserted. Relative links are shifted.
• Formulas and number formats – similar to the previous point, but retains number format, as in the original data.
• Save original formatting – similar to the first point, because copies formulas, format, and conditional formatting.
• No Borders – Removes borders for the copied data, if any. If the boundaries are set by conditional formatting, they remain.
• Maintain the width of the original columns – when pasting, the width of the original cells is taken into account.
• Transpose - Changes the orientation of data so that columns become rows and rows become columns.
• Merge conditional formatting - similar to simple paste, but with conditional formatting in the initial and final ranges, combines them.
• The next 3 options (Values, Values ​​and Number Formats, and Values ​​and Source Formatting) insert the results of the cells, but you can optionally keep the number format or all formatting. This is convenient when copying ranges with formulas;
• Formatting – inserts only formatting, including conditional;
• Insert Link – sets links to the source range;
• Picture – Excel will create a picture of the copied data;
• Related figure - differs from the previous paragraph in that it is updated when the source data changes.

Clipboard

To display the contents of the clipboard on the main tab, click on the arrow located in the lower right corner of the area of ​​the same name. A panel will open showing the copied items, if any. Most likely, the latest data will be shown there.

Some of you must have noticed Excel's Paste Special tool. Many people may have experienced confusion, if not frustration, when copying and pasting data into Excel. You inserted data and got something completely different from what you expected. I'll show you some cool tricks using Paste Special and tell you how to use some of the tool's useful features to ensure your data always looks exactly the way you want it to.

After reading this article, you will learn how to transpose, remove references, and skip blank cells using the Paste Special tool. Following each section is a short step-by-step summary. Use the quick access links or print out these summaries on paper, place them near your computer and use them as a handy guide. Well, let's get started!

If you want to use the Paste Special tool to learn how to paste just values ​​or formatting, copy column widths, multiply and divide data by given number, as well as adding and removing values ​​from an entire range of cells at once, see the article Paste Special in Excel: values, formats, column widths.

Basic knowledge of Paste Special

Before using the Paste Special tool, you must have something on your clipboard, otherwise, when you try to use these functions, they will be grayed out and not active, as in the image below.

First of all, you must copy the data to the clipboard, otherwise you will not be able to use the Paste Special function. The figure shows that on the Tape and in context menu, the Paste Special tool is not active:

First you must select and copy any information. After this action, Excel stores the copied data in the clipboard. Now you can use Paste Special. There are two ways to call this function. Both open the Paste Special dialog box, which gives access to a whole set of useful settings(see picture below).

Use one of the following methods:

On the Home tab, click on the small triangle under the word Paste and select Paste Special from the drop-down menu.

Right-click and then select Paste Special from the context menu.

Note that after you have copied the data, Paste Special becomes available.

Convert columns to rows (or vice versa)

Let's leave the basics behind and let's cheer things up a little. In the following example, we will use a table created to track calories while dieting. Let's say you're happy with the table, but you don't like that the product names are arranged vertically. I would like to arrange the days of the week vertically and the names of the products in a row. In other words, you want to transpose this data.

To do this, select the data, copy it, place the cursor in the desired cell, open the Paste Special tool menu and select Transpose - all this is shown in the images below. See how easy it is to make a table the way you want? Imagine all the possibilities that the Transpose tool gives you.

First example of table transposition:

Another use of transpose that I really like is to copy data from some source and add it to the current table. This is a great way to bring together and reconcile data from two different sources. All data will look uniform, and you can easily customize formulas.

For example, you get this template (see below) provided by the company to fill out an expense report when attending a conference.

However, at the conference you attended, an electronic report was provided in the following form:

Just copy necessary information(in our case B1:B11), place the cursor in the cell where you want to paste this information (cell C3 in the template), click Paste Special, and then select the Transpose option. Here's what happens:

Great! Now all the information looks consistent, and you can continue to add rows for all the trips you make.

Transpose - briefly about the main thing

Select data.

Copy the selected data. The Cut command will not allow you to use Paste Special, so use the Copy command.

Place the cursor in the cell where you want to paste the data.

Note: Make sure there is enough space to insert the data. Don't highlight an entire row or column unless you have enough free space. Make sure that new data will not be written over existing data that you need.

Click Paste Special. This can be done in 2 ways:

• Right-click and select Paste Special from the context menu.
• On the Home tab, under the Paste command, click the small triangle and select Pa ste Special from the menu that opens.

Both methods will open the Paste Special dialog box, where you will find many useful settings.

Check the Transpose option box.

Removing hyperlinks (a lot and quickly)

It was fun! Now let's try another cool feature of Paste Special. This trick is especially useful when you need to insert hyperlink-rich text. Hyperlinks sometimes come to hand when working with data in Excel. Notice the blue underlined text in the image below. Each cell in column A contains a hyperlink. Each time you click on a cell, the computer opens a hyperlink. You can right-click on each cell and select Remove hyperlink, but that will take forever. Imagine if your table contains 2000 rows and 1000 columns.

Instead, you can use Paste Special to remove all of these hyperlinks at once. Ready? Select all the cells from which you want to remove hyperlinks and copy them. Place the cursor in the new cell.

You can try pasting the copied data into the same cells, but in this case Excel may retain the blue color and underlining of the font. If this happens, simply clear the format.

Click Paste Special. In the dialog box of the same name that appears, select Values ​​and click OK. After this, all hyperlinks will be removed and you can move the data to its original location. Very simple, right? Look at the pictures below, they show the step-by-step execution of this operation.

Quick removal of hyperlinks - briefly about the main thing

Select all cells from which you want to remove hyperlinks.

Copy them.

Place the cursor in the cell where you want to paste the copied data.

Click Paste Special.

Select Values.

Click O K.

Cut and paste the cells wherever you need.

Skip empty cells

Here's another example (see below). Let's say I have a list of family members' birthdays and I want to replace the family member's name with their real name (from my family contact list). You could copy and paste each cell into the correct location individually, but this will take a very long time (especially if the list is long). Let's try to do this using Paste Special.

The regular Paste command will not help here. Why? – can be seen in the figure below. If you copy the information (A6:A19) and paste it into cell E4, those pesky blank cells from column A will be written over the data in column E that you would like to keep. Would you like to know a way to skip empty cells?

To do this, copy cells A5:A19, then place the cursor in the first cell of the area where you want to paste the copied data (E4). Next, click Paste Specia l (Special Paste), check the Skip Blanks option (Skip blank cells) and click OK. Voila! You have successfully copied the data from Column A to Column E, keeping all the data you want. Great job! This is a simple example, but it shows all the possibilities that the Skip Blanks tool opens up for you and your spreadsheets.

Skip empty cells - briefly about the main thing

Select the data you want to copy.

Copy them.

Place the cursor in the starting cell of the area you want to copy to.

Click Paste Special.

Select Skip Blanks.

Click OK.

Now you know the basic principles of the Paste Special tool and some cool and super-cool tricks. You can transpose, remove hyperlinks, and skip blank cells.

We are all accustomed to the fact that we can copy and paste data from one cell to another using standard operating commands Windows systems. To do this we need 3 keyboard shortcuts:

• Ctrl+C copy data;
• Ctrl+X cut;
• Ctrl+V paste information from the clipboard.

So, Excel has a more advanced version of this feature.

Paste Special is a universal command that allows you to paste copied data from one cell to another individually.

For example, you can paste separately from a copied cell:

• Comments;
• Cell Format;
• Meaning;
• Formula;
• Decor.

How Paste Special Works

First, let's find where the command is located. Once you've copied a cell, you can open Paste Special in several ways. You can right-click on the cell where you want to paste data and select “Paste Special” from the drop-down menu. In this case, you have the opportunity to use quick access to the insertion functions, and also by clicking on the link at the bottom of the list to open a window with all the possibilities. This item may be different in different versions, so do not be alarmed if you do not have an additional drop-down menu.

You can also open Paste Special on the Home tab. At the very beginning, click on the special arrow, which is located under the Insert button.

The window with all the functions looks like this.

Now we’ll go through it in order and start with the “Insert” block.

• All is a common function that allows you to completely copy all the data of a cell to a new location;
• Formulas - only the formula that was used in the copied cell will be transferred;
• Values ​​- allows you to copy the result that is obtained during execution in a formula cell;
• Formats - only the cell format is transferred. Also, the cell design will be copied, for example, background fill;
• Notes - Copy a cell note. In this case, the data (formula, values, etc.) is not copied;
• Conditions for values ​​- using this option you can copy, for example, the criterion for acceptable data (drop-down list);
• With the original theme - the cell is copied while maintaining the design. For example, if you use a background fill in a cell, that will be copied too. In this case, the formula will be copied;
• Without frame - if a cell has a frame on either side, it will be removed when copying;
• Column widths - will copy the column width from one cell to another. This feature is very convenient to use when you are copying data from one sheet to another. Only column widths are transferred;
• Formulas and number formats - the formula and number format are transferred;
• Values ​​and number formats - the result and number format are transferred.

Let's look at a few examples. There is a table in which the full name column is collected using the Concatenate function. We need to insert ready-made values ​​instead of the formula.

To replace a formula with results:

• Copy the full name column;
• Right-click on the top cell and select Paste Special;
• Set the Value item to active and press the OK key.

Now the column contains the results instead of the formula.

Let's look at another example. To do this, copy and paste an existing table next to it.

As you can see, the table did not save the width of the columns. Our task now is to transfer the width of the columns to the new table.

• Copy the entire source table;
• Stand on the top left cell of the new table and right-click. Next, select Paste Special;
• Set the Column Width item to active and press the OK key.

Now the table looks exactly the same as the original one.

Now let's move on to the Operations block.

• Add - the inserted information will be added to the values ​​​​already in the cell;
• Subtract - the inserted information will be subtracted from the values ​​​​already in the cell;
• Multiply - the value in the cell will be multiplied by the inserted value;
• Divide - the value in the cell will be divided by the value being inserted.

Let's look at an example. There is a table in which there is a column with numerical values.

Task: multiply each number by 10. What you need to do to do this:

• IN new cell you need to put the number 10 and copy it to the clipboard;
• Select all the cells of the column in which we will multiply;
• Right-click on any of the selected cells and select Paste Special;
• Set Multiply to active.

In the end we get the required result.

Let's consider one more problem. It is necessary to reduce the results obtained in the previous example by 20%.

• In the new cell we set 80% and copy it;
• Select all the cells of the column in which we will calculate the percentage;
• Right-click on any of the selected cells and select Paste Special;
• Set Multiply to active.

As a result, we get values ​​reduced by 20% from the original ones.

There is only one note here. When you work with the Operations block, try to set the Value option in the Insert block, otherwise when pasting, the formatting of the cell that we are inserting will be copied and the formatting that was originally there will be lost.

There are the last two options left that can be activated at the bottom of the window:

• Skip empty cells - allows you to skip pasting empty cells from the copied range. The program will not erase data in a cell into which a blank cell is inserted;
• Transpose - changing the orientation of the copied cells, i.e. rows become columns and columns become rows.

That's all, if you have any questions, be sure to ask them in the comments below.

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 20 10 PowerPoint 2010 Access 2010 Visio 2010 Word 2007 Outlook 2007 PowerPoint 2007 Access 2007 Word Starter 2010 Less

You can set formatting when you insert 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 insert

Like pictures and other objects, text in a presentation has its own formatting - such as font size, color, and font. When copying text that has different formatting in Office application A program such as PowerPoint or Word automatically formats this text to match the destination text. However, use Paste Special to preserve 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 the Insert button, and then choose one of the values ​​listed in the Format list below.

Format

Date and time:

Rich text
Unformatted text	Do you want to use formatting Office programs, into which text is inserted.
Microsoft Office Graphics Object	You want the contents of the clipboard to be output as a Microsoft Office picture object.
Picture (GIF)	You want the contents of the clipboard to be output as a GIF image. In format GIF files It only supports 256 colors, so it's more suitable for scanned images such as illustrations and less suitable for color photographs. The GIF format is also good for drawings, black and white images, small text, and animations.
Drawing (JPEG)	You want the contents of the clipboard to be output 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. Format PNG files similar to GIF but provides better support colors. PNG compresses solid areas of color while maintaining sharp details in sketches, logos, or illustrations with text. Saving, restoring and resaving PNG images occur without loss of quality. Unlike GIF, PNG does not support animation. Additionally, some legacy browsers and applications do not support PNG.
Figure (Windows Metafile)	You want the contents of the clipboard to be output as a WMF image.
Figure (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 to use on web pages. The DIB format is a representation of a graphic object in computer memory in the form of rows and columns of dots. The value of each point (whether it is filled or not) is recorded in one or more data bits.
Bitmap	You want the contents of the clipboard to be output 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 providing the drawing to may not have the application in which you created the image.

Insert Link

Note: The Link option will not be available 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 application icon instead of the actual content, select the As icon check box. You can click the icon to open the application and view the content. The As Icon check box is only available when you use the Link option or when you paste content as OLE object. You can change the icon for pasted content.