Excel. Copy and paste. Paste Special in Excel: skipping empty cells, transposing and removing links Why Paste Special is needed

Copy

To copy a cell (range of cells), you must select the required cell (range of cells) as described in paragraph 3, and then use one of the following methods.

Copying using tape

After selecting the desired cell (range of cells), open the "Home" tab on the ribbon. Then, in the “Clipboard” group (by default, the first group on the left), click the “Copy” button (Fig. 18). The selected cell (range of cells) will be copied to the clipboard.

View - option for displaying the copied area (as on the screen or as in print, since the display on the screen and when printed may differ);

Format. When choosing vector format the picture will not be blurry when stretched; if you select a raster format, there is blur when the picture is stretched.

When copied as a picture, the selected area can be pasted as a picture into ECXEL, WORD or another application.

Copying using hotkeys

After selecting the desired cell (range of cells), simultaneously press the "CTRL" + "C" keys on the keyboard. The selected cell (range of cells) will be copied to the clipboard.

After selecting the desired cell (range of cells), right-click in the window that opens. context menu select "Copy" (Fig. 21). The selected cell (range of cells) will be copied to the clipboard.

When copying, the selected cell (or range of cells) remains untouched at its location; as the name of the operation “copy” implies, a copy is made of it.

When using the cut operation, the selected cell (or range of cells) is removed from its original location, and the cells remaining from the cut range have all formatting as if they were a blank worksheet.

Cutting

To cut a cell (range of cells), you need to select the required cell (range of cells) as described in paragraph 3, and then use one of the following methods.

Cutting using tape

After selecting the desired cell (range of cells), open the "Home" tab on the ribbon. Then, in the “Clipboard” group (by default, the first group on the left), click the “Cut” button (Fig. 22). The selected cell (range of cells) will be cut to the clipboard.

Cutting using hotkeys

After selecting the desired cell (range of cells), simultaneously press the "CTRL" and "X" keys on the keyboard. The selected cell (range of cells) will be cut to the clipboard.

Copy using the context menu

After selecting the desired cell (range of cells), right-click and select “Cut” in the context menu that opens (Fig. 23). The selected cell (range of cells) will be cut to the clipboard.

Insert

Before performing the insertion, you need to select the cell into which the insertion will actually be performed. When copying one cell, one cell is selected for pasting (Fig. 24), when copying a range of cells, the cell that will be the top left in the pasted range is selected (Fig. 25), a range of cells is selected to fill with the same value(s), which needs to be filled in with these values ​​(Fig. 26). Unlike copying, there are significantly more pasting options. For a simple “as is” insertion, 3 options are also possible:

1) Click on the “Insert” button on the main tab of the ribbon (Fig. 27);

2) Click the right mouse button and select “Insert” in the context menu that appears (Fig. 28);

3) By pressing the key combination Ctrl+V.

In addition to simple insertion, there are several more varieties. If on the main tab you click the arrow under the "Insert" button (Fig. 27) or select the item " Special insert" (Fig. 28), then the paste special menu will appear (Fig. 29):

Button meanings from top to bottom, from right to left:

- Insert- inserting content “as is”. This means that if you selected a rectangular fragment containing numerical values, formulas, cells with text and pictures, then at the insertion point there will be the same rectangular fragment with the same content. In Fig. 30 you can see how the fragment located at the top, which contains numerical values, formulas, lines and a picture, was copied to the underlying cells. However, the formula that referenced cells A1:C1 has been moved and now references cells A11:C11. The rest of the objects (including formatting, color fill, borders) were transferred without changes.

- Formulas- if there are formulas in the selected fragment, then they are inserted exactly as formulas, while the links in these formulas remain relative. This means that it is better to copy both the formula and the cells in which the arguments of this formula are located, otherwise you can get an unpredictable result. If the selected fragment contains a picture, it is not copied by this command. In Fig. 31 it is clear that the formula was copied correctly, but the picture was not inserted.

- No frames- if you copied cells that have a border, then only the contents of the cells without borders are pasted, the rest of the formatting is preserved. The drawings are not copied (Fig. 33).

- Transpose- when this command is executed, all columns (along with their values ​​and formulas) become rows, and rows become columns. With all these movements, the connections between formulas and the corresponding cells are preserved, and if one of the cells contained a formula that sums the value in a row, then it turns into a formula that sums the value in a column, while the formatting is preserved. The drawings are not copied (Fig. 35).

- Paste values ​​and number formats- except for the values ​​from the source cells, number formats are copied, the rest of the formatting is not copied. The drawings are also not copied (Fig. 37).

- Paste Formatting- the formatting of the original cells is copied to the insertion location; values, formulas and pictures are not copied (Fig. 39).

- Insert picture- at the insertion point, the specified fragment (regardless of what it is in its original form) is inserted in the form of a raster image (Fig. 41).

- Insert a picture with a link- at the insertion point, the specified fragment (regardless of what it is in its original form) is inserted in the form of a raster image, and this image is assigned a connection with the original copy object (that is, changes in the original range of cells will be displayed in the image when the connection is updated) rice. 42).

The "Insert" radio button group allows you to select the insertion mode. Some modes have already been discussed, so we will only focus on the remaining ones:

- Notes- only notes associated with the copied cells are inserted;

- Conditions for values- only verification rules for copied cells are inserted;

- With original theme- if the theme at the place of insertion is different from the theme at the place of copying, then after copying the formulas and values, the copied theme will also be applied to the copied range;

- Column widths- the same column widths will be applied at the insertion point as at the copying point; values ​​and formulas are not copied.

You can use the Operation radio button group in the Paste Special window to select a mathematical operation to perform between the values ​​you are pasting and the values ​​that are already at the insertion location. If the copied cell contains the value 2, the value 3 is inserted at the insertion location, and you select the “No” operation, then the value 2 will be inserted. If you select the “Multiply” operation, then the value 6 will be inserted at the insertion location (that is, the inserted value 2 is multiplied by 3).

Selecting the "Skip" checkbox empty cells" will save the current cell values ​​at the place of insertion if the corresponding cells were empty at the place of copying.

A completely different window for the Paste Special command opens if there is a picture or other object on the clipboard that is not a fragment of an Excel table (Fig. 44).

This window allows you to insert Excel spreadsheet or a drawing (and in different formats), or a document created in another program, or a fragment of a document. Moreover, if you paste a fragment of a document from the clipboard, you can either simply paste a fragment of the document, or insert a link to this document by checking the “Link” switch. If a link is inserted, then all changes that are made to the inserted fragment in the source document will be reproduced in the Excel table. However, if you move the source document to a new location, the inserted object will no longer change. True, you can correct the link address manually (when you select an object, it is displayed in the formula bar).

- Paste as hyperlink- There are two options for executing this command. If the target cell is empty, the inserted object is hyperlinked to the source. If the cell is not empty, then its contents are not changed, but are hyperlinked to the inserted object. This means that the contents of the cell are underlined and will begin to act like a link on a web page, that is, clicking on that link will open the linked document.

Paste without copying

As the name suggests, for this type of pasting you do not need to copy anything first. Used when adding rows/columns to a table. Sequence of actions for pasting without copying:

a) Select a row/column (several rows/columns);

b) Press right click mouse anywhere in the selected area;

c) In the context menu that appears, select “Insert” (Fig. 45);

d) The new row/column(s) will appear before the selected row/column(s).

Important: the number of added rows/columns is equal to the number of selected ones, that is, if 2 rows are selected, then 2 rows will also be added. It should also be noted that this type of paste works when not a single cell (range of cells) has been copied. If this condition is not met, then the context menu when selecting a row/column (rows/columns) will look like in Fig. 46, and instead of the “Paste” line there will be a “Paste copied cells” line. If you copied (for example, accidentally) some cell (range of cells) and you need to add a row/column, you can press the "Esc" key on the keyboard and then insert the row/column. Pressing the "Esc" key in this case deletes the copied range from the clipboard.

Excel. Copy and paste| 2015-07-10 10:20:56 | Super User | Excel | https://site/media/system/images/new.png | Copying To copy a cell (range of cells), you must | dr.web journal key, windows settings, write protection

There are two ways to insert specific cell content or attributes, such as formulas, formats, or notes. You can use a specific paste option directly from the menu Insert or select a team Special insert, and then - the right option in the dialog box Special insert.

Select the cell with the attribute you want to copy and press CTRL+C.

Advice: To copy a selection to another sheet or workbook, click the tab for the desired sheet or go to the desired workbook.

Click the top left cell of the insertion area and select the desired item from the menu Insert or in the dialog box Special insert.

Insert menu items

On the tab home click the button Insert and select the desired insert option. For example, to paste only the formatting from a copied cell, click formatting. The table below shows the Insert menu options:

Paste Special Options

To use the Paste Special window options, select home > Insert > Special insert.

Keyboard shortcut: CTRL+ALT+V.

In the dialog box Special insert select the attribute you want to insert.

Note: Depending on the type of data copied and the option selected in the Insert, some options may be disabled.

Frequently used menu options "Paste" and "Paste Special"

Some insert special options can be selected from the menu Insert and in the window Special insert. The names of the items may differ slightly, but the result remains the same.

Formatting

Formats

Pastes only the format of copied cells.

values

Inserting values ​​(only visible results of formula calculations).

formulas

Pastes only formulas from copied cells.

number format formulas

Formulas and number formats in the Paste Special window.

Pastes only formulas from copied cells and their number formats (not text).

values ​​formatting numbers

Number values ​​and formats in the Paste Special window.

Pastes only values ​​(not formulas) from copied cells and their number formats.

without Borders

No frame in the Paste Special window.

Pastes the entire contents of the copied cells except for the borders.

Keep original formatting

With original theme in the Paste Special window.

Pastes the contents of copied cells while maintaining formatting.

When copying data to Excel Online you can insert them in the following ways:

With original formatting and formulas if it's a calculated value

Formulas and their meanings

Values ​​only

Formats only

Transpose data

Advice: If you can't copy or paste, click edit a book > edit in Excel Online.

To paste the copied cell contents, click the cell where you want to paste the text, and then on the tab home click icon Insert >Insert.

Here's what you can insert:

Data Validation Options

Advice: You can also paste by right-clicking the cell you want to paste in and selecting Insert.

You can insert formulas (formulas with values ​​or values-only values) within a workbook or across different workbooks in Excel Online. For more information, see Copy and paste a formula into another cell or sheet.

Inserting values

After you've copied data from calculated cells and pasted values ​​without formulas, click the cell where you want to paste the values ​​and choose insert > Paste values.

You can insert values ​​within a workbook or across different workbooks in Excel Online.

Paste Formatting

Sometimes you need to reuse formatting in cells that contain different data. For example, in the image below, the cells are formatted in store inserted into a column store B .

In Excel Online, you can paste formatted data within a workbook or across different workbooks.

Here's how to paste just the formatting.

Select the cell or range of cells with the formatting you want to copy and press CTRL+C.

Select the cells where you want to paste the formatting and click insert> Paste format.

Paste and Transpose

You can insert data into a workbook as data. Transpose repositions the contents of copied cells when pasting. Row data will be inserted into columns and vice versa.

Here's how to insert the contents of a cell to transpose:

Copy a range of cells.

Select the empty cells where you want to paste data.

On the tab home click icon Insert and select a team Paste Transpose.

Note: This page has been automatically translated and may contain inaccuracies and grammatical errors. It is important to us that this article is useful to you. Was the information useful? For convenience, we also provide a link to the original (in English).

The Paste Special command in Excel allows you to easily perform certain manipulations with data in a spreadsheet. This note shows how to use the Paste Special command:

1. How can I move calculation results (not formulas) to another part of the sheet?

In Fig. Cell range 1 E4:H9 contains the names, number of games played, total score, and game score for five 10- to 11-year-old basketball players from Bloomington, Indiana. In cell range H5:H9, I used data from cell range F5:G9 to calculate the number of points each child scored per game.

Download the note in or format, examples in format

Let's say we want to copy this data and game scores - but not the formulas that do the calculations - to another range of cells (for example, E13:H18). All you need to do is select the range E4:H9, copy it to the clipboard (for example, pressing Ctrl+C), go to the upper left corner of the range where you are going to copy the data (in our case, this is cell E13). After this, right-click, select Paste Special from the context menu, and in the dialog box that opens, set the parameters as shown in Fig. 2. After you click OK, the range E13:H18 will contain the data, but not the formulas, from the cell range E4:H9. To verify this, go to cell H16. You will see the value (7), but not the formula for calculating Gregory's points per game average. Please note: if you use the Paste Special command, select the radio button Values, and then paste the data into the same range from which it was copied, the formulas will disappear from it too.

Rice. 2. Paste Special dialog box with the radio button selected Values. When you select this switch, only the values ​​are copied, no formulas

2. I have a list of names listed in a separate column. How can I make the list appear in a row instead of a column?

To rearrange data from a row to a column (or vice versa), use the commands Copy And Special insert with option Transpose. Essentially a switch Transpose The Paste Special dialog box "flips" the selected cells around an axis so that the first row of the copied range becomes the first column of the range into which data is pasted, and so on.

Let's say you want to display the player names in one line (starting in cell E13). Simply select the range E5:E9, copy it, select Paste Special and then select the radio buttons in the dialog box that opens Values And Transpose. When you click OK, the player names will appear in a line (Figure 3).

Rice. 3. The Transpose option of the Paste Special dialog box moves a row of data into a column of data and vice versa

Let's say you want to move the contents of spreadsheet cells E4:H9 to the range starting with cell E17. First of all, select the range E4:H9, copy it, go to the upper left corner of the range where you are going to paste the transposed data (E17). Select Paste Special, select the radio button Transpose(leave the option All) and then click OK. The contents of the range E4:H9 will be transposed (rotated) (Fig. 4). Note the range F20:J20: Excel is smart enough to change the game scoring formula so that the average for each player is now calculated based on the data in the corresponding column rather than the row.

Try selecting Paste Special and clicking OK instead Insert Link. The new cells will be linked to the original cells, and the changes you make to the original cells appear in the copies. If you change the value in cell F5 to 7, the value in cell F18 also changes to 7, and in cell F20 Dan's average points per game becomes 4 (Figure 5).

Rice. 5. Team Insert Link

3. I downloaded from the US Treasury website at Excel list interest rates on bills. A rate of 5% in the list is indicated by the number 5, a rate of 8% by the number 8, and so on. How can I quickly divide the results by 100 so that a 5% rate appears as, say, 0.05?

In Fig. Figure 6 lists the annual interest rates on three-month U.S. Treasury bills for each month between January 1970 and February 1987.

In January 1970, the one-year Treasury bill interest rate was 8.01%. Let's say we want to earn an annual return on $1 we invest in buying a Treasury bill at that interest rate. The formula for calculating income is 1 + (annual percentage rate)/100. It would be much easier to calculate the income received if all the data on annual interest rates in the column were divided by 100.

Section features Operation dialog box Special insert allow us to add, subtract, multiply and divide each number in a range by a given number, which means we can easily divide all annual interest rates by 100. In this case, we need to divide all the numbers in column D. First, enter our number (100) . It can be entered into any cell in the spreadsheet. Select the cell with this number and copy it into memory. Then highlight the range of numbers you want to change. To select all data in column D, click cell D10, press Ctrl+Shirt, and then press Down Arrow. This keyboard shortcut useful for selecting a "high" range of cells (to select a "wide" set of data in one row, click the first cell of data, press Ctrl+Shirt and then right arrow). Now select the Paste Special command and select the Split switch (Fig. 7).

Rice. 7. Option divide section Operation window Special insert

When you click OK, Excel will divide each of the highlighted numbers in column D by 100 (Figure 8). When selecting an operation fold cell D10 would display the value 108.01; when choosing subtract- value –91.99; multiply- value 801.

Rice. 8. Result of switch installation divide in the dialog box Special insert

Test tasks

1. In Fig. Figure 9 shows the statistics of the games of the Dallas Mavericks basketball team for the 2002-2003 season. Player names are listed in Column A, section names are listed in Row 3, and results are listed in Rows 4-20.

a) Change spreadsheet such that the player names are listed on one line and the statistics sections are listed in one column.

b) Field shooting percentage, free throw percentage, and three-point shooting percentage are shown as decimal numbers. For example, Steve Nash makes 91.9 free throws out of 100, which is listed as 0.919. Modify the spreadsheet so that all hit percentages are reported as a number between 1 and 100.

2. In Fig. Figure 10 shows quarterly sales data for four products. Copy this data to another range of cells. Link the copied data to the original data so that the copied cells reflect the changes made in rows 2–5 when calculating annual sales.

Answers in Excel file

When writing this note, materials from the book by Wayne L. Winston were used. Microsoft Excel. Data Analysis and Business Model Building, Chapter 13.

Using Excel tables, regardless of version, greatly simplifies working with numerical values. Therefore, Excel is one of the indispensable desktop programs for economists, accountants, purchasing and sales managers.

One of the interesting and useful functions Excel tables is "Paste Special". This operation helps calculate the values ​​in the underlying number cells. This eliminates the need to mess around with columns or rows, fill lots of space with numbers, or write them down.

Paste Special in Excel

Using Paste Special in Excel, you can multiply, add, divide, or subtract multiple values ​​in one operation. Everything is extremely simple, it essentially consists of two functions - “Copy” and “Paste”.

A special insert allows you to significantly expand the capabilities of these most popular operations:

• copy values ​​without formulas;
• insert a formula from another cell, preserve the format of the original one;
• bulk add, divide, subtract and multiply given values;
• copy and paste notes from another cell;
• transfer formats;
• copy the given width of another column;
• transfer conditional cell formats;
• transpose tables.

Also, thanks to a special insert, you can combine all of the above operations.

Addition and other mathematical operations

Let's try adding several values ​​using insert special. To do this, open a new Excel table. In column “A” we enter a series of numerical values. To simplify the example, let’s take round numbers: 100, 200, 300, 400, 500. Let’s say we need to add the number 18 to all of them.

In this case, put the number 18 in a separate cell and press “Enter.” After that, move the cursor over this cell and right-click on it. Having selected the “Copy” function, we proceed to working with column “A”. To add 18 to all available base values, hold down left button mouse, select the entire column “A” with numbers. After that, right-click on it. In the menu that appears, select insert special. An additional functional window opens in which we will be offered:

• actions that can be performed with numbers;
• various manipulations with the values ​​and formats of copied cells.

In the “Insert” menu, you must select and place the pointer on the “All” item (if you want to add the specified number to all available values). And in the “Operation” list, select “Fold”. After that, without checking any other boxes, click the “OK” button.

Let's look at the result. It turned out that in each of the cells the number increased by 18: 118, 218, 318, 418, 518.

Other commands are executed in the same way. Only the pointer in the second menu item is moved to:

• multiply;
• divide;
• fold;
• subtract.

Everything else remains unchanged.

Formatting

Let's try to format the cells. To do this, select the values ​​in one column and, as in the addition option, right-click to bring up the menu. There we select “Copy”. Once the values ​​are copied to the clipboard, they will be highlighted with a dotted dynamic border. Then select the column that needs to be formatted in the specified style. Select all the necessary cells from this column, and then right-click. In the menu that appears, select “Paste Special.” And in the window that opens, place the pointer next to the “Formats” section, and then click the “OK” button. Congratulations, the specified cells are formatted!

Thanks to the Paste Special function, working with a table will take less time, even with serious and large-scale computational operations. After the table has been generated, you can view it by selecting “ Preview» in the main menu, and then print it.

Special copying of data between files in Excel includes the command Special insert on the menu Edit. Unlike simply copying data using the command Insert team Special insert can be used to calculate and transform information, as well as to link Excel workbook data (these capabilities will be discussed in the next chapter).

Team Special insert often used to copy cell formatting attributes.

1. Select the cell or cells you want to copy.

2. Select Edit, Copy.

3. Select the cell or cells in which the source data will be placed.

4. Select Dialog Box Special insert contains several parameters for inserting data (Fig. 1).

Rice. 1. Special insert

5. Set the necessary options, such as formats (copying formats only changes the formatting, not the cell values).

6. Select OK .

First group of dialog box parameters Special insert Allows you to select the content or formatting attributes that you want to paste. When you select the option All the contents and attributes of each copied cell are pasted to a new location. Other options allow you to insert different combinations of content and/or attributes.

The second group of parameters applies only when inserting formulas or values ​​and describes the operations performed on the inserted information into cells that already contain data (Table 1).

Table 1. Paste Special command options

Choice No means that the copied information simply replaces the contents of the cells. By choosing other operation options, we get that the current contents will be combined with the inserted information and the result of such a combination will be the new contents of the cells.

Exercise

Perform calculations using the Paste Special command

Enter the data as shown in the table. 2.

Table 2. Initial data

1. Select the area to copy A2:A4.

2. Select Edit. Copy.

3. Click cell B2 (upper left corner of the area where the data will be placed).

4. Select Edit, Paste Special.

5. Set the parameter Multiply.

6. Click OK. Please note that the border of the selection area remains on the screen.

7. Click cell C2, which will be the start of the insertion area.

8. Select Edit, Paste Special and set the parameter Transpose.

9. Copy the column formats yourself G to column N and get a table. 3.

Table 3. Result of the Paste Special command