Why is Paste Special not active? Excel insert. Inserting workbook content

Paste Special in Excel is the ability to process the contents of the clipboard. When copying an object, Excel allows you to modify it somehow; this is done to simplify operations. The same operations can be done without a special insert, but with it this is done in one or two clicks.

How does this work

For example, you copied some table. And you want to transpose it, that is, swap rows and columns. To do this, you right-click on the place where you will insert the table and you will see a special paste window. You do your manipulations, for example, check a box "transpose" and "Ok".

What can be changed

To make it easier, let's look at what is in a specific cell.

A cell can contain a formula, the result of which is visually shown (value), has a specific format for both the cell and text or number. It may have notes, attributes (for example, or). It can be part of a table, which has its own structure consisting of rows and columns.

Paste Special allows you to manipulate any of these cell or table properties.

Let's look more specifically at examples:

If you need to remove and insert only the cell value, you need to select the item during paste special "values" or "number values and formats" .
Often you only need to copy . For example, you have a table and you want to make a similar one, but with different variables. If you just copy and paste, you will take away all the attributes of the table, but the second table may have its own formatting, then you only need to isolate the formula. Then you need a point "formulas" or "formulas and number formats" .
The formatting is also transferred, i.e. color, background, text and cell style. Paragraph "formats" . Notes are transferred just as easily.
By default, when you copy a cell, its width remains unchanged. But here I have worksheets where the width of each column is manually determined for better clarity. The more important ones are enlarged, the unimportant ones are reduced or hidden. If I need a similar table, I will have to use the point "column widths" .
If you need to move or , you need an item "conditions on values" .
The paragraph will help you transpose, i.e., convert columns into rows "transpose" .
Arithmetic operations can be done on two ranges or cells. Copy the cell, select the range and use Paste Special to select the desired operation.

4 minute video:

Paste or Ctrl+V is perhaps the most effective tool available to us. But how good are you at it? Did you know that there are at least 14 in various ways inserting data into Excel sheets? Surprised? Then read this post to become a paste master.

This post consists of 2 parts:

— Basic insertion techniques

— Insertion using data processing

1. Paste values

If you just want to paste values from cells, press the Z, M, and Z keys in sequence while holding down the Alt key, and finally press the Enter key. This is necessary when you need to get rid of formatting and work only with data.

Beginning with Excel 2010, the Paste Values feature appears in a pop-up menu when you right-click

2. Paste formats

14. What is your favorite insertion method?

There are many more hidden ways insertions such as inserting XML data, images, objects, files, etc. But I'm wondering what interesting insertion techniques you use. Write what is your favorite insertion method?

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 a vector format, the picture will not be blurry when stretched; when choosing a raster format, there will be blur when stretching the picture.

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 and select “Copy” in the context menu that opens (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 an 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 “Paste Special” item in the context menu (Fig. 28), 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, a given 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 object copying (that is, changes in the original range of cells will be displayed in the figure when the link is updated) Fig. 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 empty cells" checkbox will save the current cell values at the insertion location if the corresponding cells were empty at the copy location.

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 into an Excel table either a picture (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 opens 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) Right-click 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 taken into account that this type paste works when no cells (range of cells) are copied. If this condition is not fulfilled, then context menu when selecting a row/column (rows/columns), it 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 in this case deletes the copied range from the clipboard.

Not much different from the special one. Basically, they duplicate each other's parameters, but the latter allows you to perform additional operations with data.

Location of the described function: “Home” tab -> “Clipboard” area -> drop-down menu of paste options -> “Paste Special”.

The article's material is divided into consideration of special insertion from web pages and for the contents of the book.

Copying from Internet pages

The application provides only three insertion options for such information:

HTML – supports source formatting;
Text – only text is inserted, without decoration. The relative position of elements may be lost.
Unicode text – similar to the previous point, but with some differences. Use it if, when copying onto a workbook sheet, question marks are displayed instead of text or its individual parts (pay special attention special characters and letters of the alphabet other than Latin). This means that the page from which the text is transferred has an encoding different from the application encoding (most often UTF-8).

Inserting workbook content

As you can see in the image below, most of the options are the same as the paste settings described in a separate article. Therefore, they will not be considered here.

Excel's Paste Special allows you to perform some mathematical operations with information: addition, subtraction, multiplication and division. Even when you copy only formulas, these operations remain available. The program will simply substitute the formulas of the original range into the contents of the final range. If the sequence of calculations of the resulting calculations can be broken, then the application encloses its parts in parentheses.

< Назад

If the materials from office-menu.ru helped you, please support the project so that we can develop it further.

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

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

If you copied data with calculations and want to paste only the values of this data, without formulas. Do this - Copy desired cells - select the 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 let’s look at all the proposed options.

Option "Insert:"

-formulas - values-formats -notes

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

— insert conditions on values. We take it literally, we insert conditions on top of the values. This is either data validation() or .

Those. if additional conditions need to be imposed.

— insert with the original topic. A theme in Excel is the format of an entire workbook or worksheet. Used for consistent color schemes or chart formats. If one theme is applied to this sheet, but you need to insert another. this clause applies. To be honest, I rarely use this function.

- paste without frame. Paste without border format

- insert column widths. Insert column width formatting.

- formulas and number formats. Both formulas and formats are inserted

- number values and formats. Very convenient. Not only values are inserted, but also number formats.

Section Operation (add, subtract, divide, multiply)

Cool stuff! For example, you have two ranges

And you need to divide the data from column D by data from column B (see 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 Paste Special. And check the “split” checkbox

Voila. Try it yourself - you'll like it.

Finally, three special insert bombs

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