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

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

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

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

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

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

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. IN different versions This item may vary, so don't be alarmed if you don't have an additional drop-down menu.

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

The window with all the functions looks like this.

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

• All- this is a common function that allows you to completely copy all cell data 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- copying 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 criteria for acceptable data (drop-down list);
• With 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;
• No frame- if a cell has a frame on either side, it will be deleted when copying;
• Column widths- the column width will be copied 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;
• Number values ​​and 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 item to active Meaning and press the key OK.

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 item to active Column widths and press the key OK.

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

Now let's move on to the block Operations.

• Fold- the inserted information will be added to the values ​​already in the cell;
• Subtract- the inserted information will be subtracted from the values ​​already present in the cell;
• Multiply- the value in the cell will be multiplied by the inserted one;
• 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 the 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 it to active Multiply.

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 it to active Multiply.

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

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

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

• Skip empty cells- allows you not to paste 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 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.

Probably, many inexperienced users tried to copy some data into Excel, but as a result of their actions, the output was either a completely different value or an error. This is due to the fact that there was a formula in the primary copy range, and it was the formula that was pasted, not the value. Such problems could have been avoided if these users were familiar with such a concept as "Insert Special". It can also be used to perform many other tasks, including arithmetic. Let's figure out what this tool is and how to work with it.

Paste special is primarily intended to insert a specific expression into Excel sheet in the form in which the user needs it. Using this tool, you can paste not all copied data into a cell, but only individual properties (values, formulas, format, etc.). In addition, using the tools, you can perform arithmetic operations (addition, multiplication, subtraction and division), as well as transpose the table, that is, swap rows and columns in it.

In order to go to Paste Special, you first need to perform a copy action.

Method 1: working with values

If you need to transfer the values ​​of cells in which the result is displayed using calculation formulas, then paste special is just designed for this case. If you use regular copying, the formula will be copied, and the value displayed in it may not be the one you need.

Method 2: Copy Formulas

But there is also the opposite situation, when you need to copy exactly the formulas.

But it is possible to transfer formulas while preserving the number format or even with complete preservation original formatting.

Method 3: Transfer formatting

If the user does not need to transfer data, but only wants to copy the table to fill it with completely different information, then in this case, you can use a certain paste special clause.

Method 4: Copy the table while maintaining the size of the columns

It is no secret that if we simply copy a table, it is not a fact that all the cells of the new table will be able to contain all the information in the source. You can also correct this situation when copying using paste special.

Method 5: Insert a picture

Thanks to the capabilities of paste special, you can copy any data displayed on a sheet, including a table, as a picture.

You cannot perform this operation in the Paste Special window.

Method 6: Copy Notes

Using Paste Special, you can quickly copy notes.

Method 7: Transpose the table

Using paste special, you can perform the operation of transposing tables, matrices and other objects in which you need to swap columns and rows.

Method 8: Using Arithmetic Operations

Using the tool we describe in Excel, you can also perform common arithmetic operations:

• Addition;
• Multiplication;
• Subtraction;
• Division.

Let's see how this tool is used using the example of multiplication.

The same principle can be used to perform division, addition and subtraction. Only for this in the window you will need to move the switch accordingly to the position "Divide", "Fold" or "Subtract". Otherwise, all actions are similar to the manipulations described above.

As you can see, the special insert is very useful tool for the user. With its help, you can copy not only the entire block of data in a cell or range, but by dividing it into various layers (values, formulas, formatting, etc.). It is possible to combine these layers with each other. In addition, using the same tool you can perform arithmetic operations. Of course, acquiring skills to work with this technology will significantly help users on their way to mastering Excel programs generally.

This technique describes three ways to obtain the data contained in a web page: insert a static copy of the information; create an updated link to the site; open the page directly in Excel.

Inserting static information

One way to get data from a web page into a worksheet is to simply highlight the text in the browser, click Ctrl+C to copy it to the clipboard and then paste the text into the table. The result may vary depending on which browser you are using. If this Internet Explorer, then the inserted data will likely be very similar to the original - complete with formatting settings, hyperlinks and graphics.

If you are using a browser other than Internet Explorer, select Home Clipboard Paste, you can paste everything you copied from a web page into one cell, which is probably not what you want. The solution is to choose a team Home Clipboard Paste Paste Special and then try various options inserts.

Inserting updated information

If you want to regularly access updated data from a web page, create a web request. In Fig. Figure 176.1 shows a website that contains exchange rates in a table with three columns. Having completed next steps, you can create a web query that allows you to retrieve this information and then update it at any time with one click.

1. Select Data Receiving external data from the Internet Creating a Web Request.
2. In the field Address enter the site URL and click the button Start. For this example, the URL of the web page shown in Figure. 176.1, will be like this: http://cbr.ru. Please note that the dialog box Creating a Web Request contains a mini browser (Internet Explorer). You can follow links and visit sites until you find data that interests you. When a web page is displayed in a window Creating a Web Request, you see one or more yellow arrows that correspond to different elements on the web page.
3. Click on the yellow arrow and it will turn into a green checkbox, indicating that the item's data will be imported. You can import as many elements as we need. For this
   For example, I'll click on the arrow next to the courses table.
4. Click the button Import to open a dialog box Import data.
5. In the window Import data specify a location for the imported data. This can be a cell in an existing or new worksheet.
6. Click the button OK, and Excel imports the data (Figure 176.2).

By default, the imported data is a web request. To update your information, click right click mouse on any cell of the imported range and select in context menu team Update. If you do not want to create an updateable query, indicate this in step 5 of the previous list of actions. In the Import data window, click the button Properties and uncheck Save Query Definition.

Opening a web page directly

Another way to get web page data into a worksheet is to open the URL directly, using the command File Open. Just enter the full URL in the field File name and press the button Open. The result will vary depending on the layout of the web page, but in most cases you will be happy with it. Sometimes quite a lot of extraneous information is extracted this way.

Paste only into visible lines inExcel numbers, formulas, text can be done in several ways. When you need to insert numbers, formulas, text into not all rows of the table, you can use a filter. How to install a filter and how to filter in Excel, see the article “Filter in Excel”. But to insert data only into visible cells, you need your own methods, especially if there are many rows.
The first way is ordinary .
Let's take a table like this. The table will be the same for all examples.
Let's use a filter to remove all digits 2 from the table. In the remaining visible cells we put the number 600. In cell B2 we put the number 600, then copy it down the column (pull the lower right corner of cell B2). Values ​​were copied only to visible cells. You can also insert formulas in the same way. We write the following formula in cell C2. =A2*10
It turned out like this.
Let's cancel the filter. The result is a table like this.
The formula and numbers were inserted only into the filtered rows.
Second way.
We will also filter the data. In the first cell we write a number, formula, text, etc. Now, if there are thousands of rows, then select the cells like this: press the keys “Ctrl” + “Shift” + the down arrow button (or the up button, depending on where we want to select the cells - below or above the cell in which the number was written) .
Now, or press the key combination “Ctrl” + G, or the F5 key. The Transition dialog box will appear. Click the “Select...” button. And, in the new “Select a group of cells” dialog box, check the box next to the words “Only visible cells”.Click "OK". Then insert as usual.

Another way to bring up the Select Group of Cells dialog box.On the “Home” tab, in the “Editing” section, click on the “Find and Select” button. In the list that appears, click on the “Select a group of cells” function.

To fill visible cells in selected ones Excel columns , press the key combination “Ctrl” + D. And all selected columns will be filled with data or a formula, as in the first cell. In our example, we wrote the number 800 in cell D2, column D.

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 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 insertion methods 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?