Menu
Is free
check in
the main  /  Education / Excel Special insert split. Copying data cells with insertion only attributes

Excel Special insert split. Copying data cells with insertion only attributes

In this article, we will show you a few more useful options that the tool is rich. Special insert , namely: values, formats, column widths and multiply / divided. With these tools you can configure your tables and save time on formatting and reformatting data.

If you want to learn to transpose, delete links and skip empty cells using a tool Paste Special (Special insertion) Refer to the article Special insert in Excel: We skip empty cells, transponing and delete links.

Insert only values

Take for example a table of accounting for profit from sales of cookies at a charitable sale of baking. You want to calculate which profits were obtained in 15 weeks. As you can see, we used the formula that adds the sales amount, which was a week ago, and the profit received this week. See in the formula row \u003d D2 + c3? Cell D3. shows the result of this formula - $100 . In other words, in the cell D3. Displaying a value. And now it will be the most interesting! In Excel with tool Paste Special (Special insert) You can copy and paste the value of this cell without formula and formatting. This feature is sometimes vital, then I will show why.

Suppose, after you have sold cookies for 15 weeks, you must submit a general report on the results of the profit. You may want to simply copy and insert a string that contains a general result. But what happens if you do this?

Oops! It's not at all what you expected? As you can see, the usual action will copy and insert as a result copied only the formula from the cell? You need to copy and perform a special insertion of the value. So we will do! We use the team Paste Special (Special insert) with a parameter Values. (Values) so that everything is done as it should.

Note the difference in the image below.

Apply Special insert > Values, we insert the values \u200b\u200bthemselves, not the formula. Great job!

Perhaps you have noticed something else. When we used the team Paste Special (Special insert)\u003e Values. (Values), we lost formatting. See that fat font and numeric format (dollar signs) were not copied? You can use this command to quickly delete formatting. Hyperlinks, fonts, numeric format can be quickly and easily cleaned, and you will have only values \u200b\u200bwithout any decorative stuff that may interfere in the future. Great, right?

Actually, Special insert > Values - This is one of my favorite tools in Excel. It is vital! Often they are asked to create a table and present it at work or in public organizations. I always worry that other users can lead to the chaos introduced by me formulas. After I complete work with formulas and calculation, I copy all my data and use Paste Special (Special insert)\u003e Values. (Values) on top of them. Thus, when other users open my table, the formulas cannot be changed. It looks like this:

Pay attention to the contents of the line formulas for the cell D3.. There is no more formula \u003d D2 + c3, instead of this is recorded there 100 .

And one more very useful thing relating to a special insert. Suppose in the accounting table arrived from a charity sale of cookies I want to leave only the bottom line, i.e. Delete all lines except the week 15. See what happens if I just delete all these lines:

This annoying error appears. #Ref! (# Fell!). It appeared because the value in this cell is calculated by the formula that refers to the cells above. After we have deleted these cells, the formula has become nothing to refer to, and she reported mistake. Use the team instead Copy (Copy) and Paste Special (Special insert)\u003e Values. (Values) over the source data (so we have already done above), and then delete unnecessary lines. Great job:

Special Insert\u003e Values: Highlights

  1. Highlight the data
  2. Copy them. If the data is not copied, and cut, then the command Paste Special (Special insertion) will be unavailable, so be sure to copy.
  3. Select the cell in which you want to insert copied data.
  4. Click Paste Special (Special insert). It can be done:
    • changing the right mouse button and selecting in the context menu Paste Special (Special insert).
    • on the tab HOME. (Home), press a small triangle under the button Paste (Paste) and in the drop-down menu select Paste Special (Special insert).
  5. Note the option Values. (Data).
  6. Click OK.

Insert only formats

Special insert > Formats This is another very useful tool in Excel. I like it to make it easy to customize appearance data. There are many tool applications Special insert > FormatsBut I'll show you the most remarkable. I think you already know that Excel is great for working with numbers and to perform various calculations, but it also copes excellent when you need to provide information. In addition to creating tables and calculate the values, you can do a variety of things in Excel, such as schedules, calendars, labels, inventory cards, and so on. Look more carefully on the templates that Excel offers when creating a new document:

I came across the template Winter 2010 Schedule. And I liked it in it formatting, font, color and design.

I do not need a schedule itself, and even more so for the winter of 2010, I want to just redo the template for my goals. What would you do in my place? You can create a draft table and manually repeat the design of the template in it, but it will take a lot of time. Or you can delete all the text in the template, but it will also take a lot of time. It is much easier to copy the template and make Paste Special (Special insert)\u003e Formats. (Formats) on the new sheet of your working book. Voila!

Now you can enter data while retaining all formats, fonts, colors and design.

Special Insert\u003e Formats: Highlights

  1. Select data.
  2. Copy them.
  3. Highlight the cell to which you want to insert data.
  4. Click Paste Special (Special insert).
  5. Note the option Formats. (Formats).
  6. Click OK.

Copy the width of columns on another sheet

Have you ever lost a lot of time and strength, circle around your table and trying to adjust the sizes of columns? My answer is, of course, yes! Especially when you need to copy and insert data from one table to another. Existing column width settings may not come up and even despite the fact that automatic setting column widths - it comfortable tool, it can not work in places as you would like. Special insert > Column widths - This is a powerful tool to be used by those who know exactly what wants. Let's consider the list for example best programs US MBA.

How could this happen? You see how carefully the width of the data width in the figure was adjusted in the figure above. I copied the ten best business schools and placed them on another sheet. See what happens when we simply copy and insert the data:

The content is inserted, but the width of the columns is far from suitable. You want to get exactly the same column width as on the source sheet. Instead of setting it up manually or use the column width auto attachment, just copy and make Paste Special (Special insert)\u003e Column Widths. (Column widths) on the area where you want to adjust the width of the columns.

See how simple? Although it is a very simple example, but you can already imagine how such a tool will be useful if excel sheet Contains hundreds of columns.

In addition, you can adjust the width of empty cells to set them the format before manually enter text. Look at the columns E. and F. In the picture above. In the picture below I used the tool Special insert > Column widthsTo expand columns. So, without any fuss you can issue your leaf of Excel as you please!

Special insert\u003e Column width: Highlights

  1. Select data.
  2. Copy the selected data.
  3. Put the cursor to that cell, the width of which you want to configure.
  4. Click Paste Special (Special insert).
  5. Note the option Column Widths. (Column widths).
  6. Click OK.

Special insert: divided and multiply

Remember our example with cookies? Good news! The gigantic corporation learned about our charity event and offered to increase profits. After five weeks of sales, they put in our charity, so that the income will double (will be twice as much) compared to what he was at the beginning. Let's return to that table in which we tagged profit from the charitable sale of cookies, and recalculate profit with the records of new investments. I added a column showing that after five weeks sales profit will double, i.e. will be multiplied by 2 .

All income, starting from the 6th week, will be multiplied by 2 . To show new numbers, we need to multiply the corresponding column cells C. on the 2 . We can perform it manually, but it will be much nicer if you do Excel for us with the team Paste Special (Special insert)\u003e Multiply. (Multiply). To do this copy the cell F7. and apply the command to the cells C7: C16.. The final values \u200b\u200bare updated. Great job!

As you can see, the tool Special insert > Multiply Can be used in a variety of situations. The situation with Paste Special (Special insert)\u003e Divide (Divided). You can split a whole range of cells on a certain number quickly and simple. Do you know what else? With help Paste Special (Special insert) with option Add. (Folded) or Subtract (Subtract) You can quickly add or subtract the number.

Special Insert\u003e Divide / Multiply: Highlights

  1. Select the cell with the number to be multiplied or divided.
  2. Copy the selected data.
  3. Put the cursor to those cells in which you want to perform multiplication or division.
  4. Click Paste Special (Special insert).
  5. Note the option Divide (Divided) or Multiply. (Multiply).
  6. Click OK.

So, in this lesson you learned some very useful instrument capabilities. Special insertNamely: learned to insert only values \u200b\u200bor formatting, copy the width of the columns, multiply and divide the data to a specified number, as well as add and delete the value immediately from the range of cells.

In this lesson, I will tell you what is a special insertion in Excel and how to use it.

  • What is a special insertion
  • How does a special insert work

What is a special insertion

We are all used to the fact that we can copy and paste data from one cell to another with standard teams operating system Windows. To do this, we will need 3 shortcuts:

  • Ctrl + C. Copying data;
  • Ctrl + X. cut;
  • Ctrl + V. Insert information from the clipboard.

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

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

For example, you can separately insert from the copied cell:

  • Comments;
  • Cell format;
  • Value;
  • Formula;
  • Registration.

How does a special insert work

First, let's find where the team is located. After you have copied the cell, you can open a special insert in several ways. You can click the right mouse button on the cell where you want to insert data, and select the "Special Insert" item from the drop-down menu. In this case, you have the opportunity to use fast access to insert functions, as well as by clicking on the link at the bottom of the list, open the window with all the capabilities. IN different versions This item may be different, so do not be afraid if you do not have an additional drop-down menu.

Also, you can open a special insert on the tab the main. At the very beginning, click on a special arrow, which is under the button Insert.

The window with all functions is as follows.

Now we will deal with the order and start with the "Paste" block.

  • Everything - this is a common function that allows you to completely copy all the cell data into a new place;
  • 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 the execution in the cell of the formula;
  • Formats - only the cell format is transferred. Also, cell registration will be copied, for example, pouring background;
  • Notes - Copying cell notes. In this case, the data (formula, values, etc.) are not copied;
  • Conditions on meaning - using this option you can copy, for example, the validative data criteria (drop-down list);
  • With the original topic - The cell is copied with the preservation of the design. For example, if you use the fill of the background in the cell, it will also be copied too. In this case, the formula will be copied;
  • Without frame - if the cell has a frame from any of the parties, then when copying it will be removed;
  • Column widths - A column width from one cell to another will be copied. This feature is very convenient to use when you copy data from one sheet to another. Transferred only columns width;
  • Formulas and formats of numbers - transferring formula and format of numbers;
  • Values \u200b\u200band formats of numbers - The result and format of numbers are postponed.

Consider several examples. There is a table in which the FULL column is collected using the Capture function. We need to insert ready-made values \u200b\u200binstead of the formula.

In order to replace the result formula:

  • Copy the FULL column;
  • Click on the upper cell right mouse button and select a special insert;
  • We put an active point Value and press the key OK.

Now in the column instead of the formula, results are entered.

Consider another example. To do this, copy and insert the existing table nearby.

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

  • Copy fully source table;
  • We become on the top left cell of the new table and click the right mouse button. Next, choose a special insertion item;
  • We put an active point Column widths and press the key OK.

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

We now turn to the block Operations.

  • Fold - the inserted information will be folded with the values \u200b\u200bexisting in the cell;
  • Subtract - inserting information will be deducted from the existing values \u200b\u200bin the cell;
  • Multiply - the value in the cell will be multiplied by inserted;
  • Split - The value in the cell will be divided into inserted.

Let's analyze an example. There is a table in which there is a column with numeric values.

Task: multiply each of the numbers to 10. What needs to be done for this:

  • In the new cell, you must put the number 10 and copy it to the buffer;
  • Allocate all cells of the column in which we will multiply;
  • Click the right key along any of the selected cells and select a special insertion item;
  • We put active Multiply.

At the end we get the necessary result.

Consider another task. It is necessary to reduce the results obtained in the previous example by 20%.

  • In the new cell, we put 80% and copy it;
  • We allocate all cells of the column in which we will calculate the percentage;
  • Click the right mouse button along any of the selected cells and choose a special insertion item;
  • We put active Multiply.

As a result, we obtain values \u200b\u200breduced by 20% of the initial.

There is one only remark here. When you work with a block Operations, try to put in the block Insert option ValueOtherwise, when inserting, the formatting of the cell, which insert and will be lost, then formatting, which was originally will be lost.

The last two options remained, which can be activated at the bottom of the window:

  • Skip empty cells - Allows you not to insert empty cells from the copied range. The program will not erase the data in the cell in which an empty cell is inserted;
  • Transpose - Changing the orientation of copied cells, i.e. Rows become columns, and columns - rows.

On this, everything is if you have any questions, then definitely ask them in the comments below.

Today I will tell about quick switching between Excel sheets. It may need you when open Document Too many sheets and get to the right thing is not so easy. In Excele, this case has several solutions, so you can choose the most suitable for yourself.

In the past lesson, I already told how to remove duplicates using a special function that appeared from 2007 version. Today we will talk about the possibility that is suitable for earlier versions. We will use an extended filter.

In this lesson, I will tell you how to make the drop-down list in Excel. It is mainly created with one goal - to limit the possibility of entering data to a certain list. For example, when there is a special cell in the table in which you note to which department to treat one or another employee. This list is always the same. Much more convenient to choose a department from the list than to enter it each time manually or copy from other cells.

In this lesson, I will tell you how to set up the date and text in Excel. Suppose you have several cells, one of which is the date. It is necessary to get a cell in which the recording "Treaty No. 15 of 28.12.2015" will be stored, provided that the type of document, its number and date will be stored in different cells.

In this lesson, I will tell you how to fix a string or column in Excel. The fastened areas will always be visible on the screen with a vertical or horizontal scrolling.

Probably, many inexperienced users tried to copy some data in Excele, but as a result of the actions, they had or 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 it inserted, not a meaning. Such problems would be avoided if these users were familiar with such a concept as "Special Insert". With its help, many other tasks can also be performed, including arithmetic. Let's see what is this tool And how to work with it.

Work with a special insert

Special insert, first of all, is intended to insert a specific expression on the Excel sheet as in which it is necessary to the user. With this tool, you can insert into a cell not all copied data, but only individual properties (values, formula, format, etc.). In addition, using the tools, it is possible to produce arithmetic actions (addition, multiplication, subtraction and division), as well as to transpose table, that is, changing the string and columns in it.

In order to go to a special insert, first of all, you need to perform copying.

  1. Select a cell or range to be copied. We highlight it with the cursor by closing left button Mice. We click on highlighting the right mouse button. The context menu is activated, in which you want to select the "Copy" item.

    Also, instead of the above procedure, you can, while in the Home tab, click on the "Copy" icon, which is placed on the ribbon in the clipboard group.

    You can copy the expression by selecting it and gaining a combination of hot keys Ctrl + C.

  2. To directly proceed to perform the procedure, select the area on the sheet where we plan to insert previously copied elements. Click to highlight the right mouse button. In the running context menu, select the "Special Box ..." position. After that opens additional listwhere you can choose different types of actions divided into three groups:
    • Insertion ("insert", "transpose", "formulas", "formulas and formats of numbers", "without frames", "save the width of the column of the original" and "save the initial formatting");
    • Insert values \u200b\u200b("value and source formatting", "values" and "values \u200b\u200band formats of numbers");
    • Other insert parameters ("Formatting", "Figure", "Insert Communication" and "Related Figure").

    As we see, the tools of the first group makes copying the expression contained in the cell or range. The second group is designed primarily for copying values, not formulas. The third group produces formatting and appearance.

  3. In addition, in the same additional menu there is another point that has the same name - "Special insert ...".
  4. If you go over to it, you open a separate window of a special insert with tools that are broken into two large groups: "Insert" and "Operation". It is, thanks to the tools of the last group, the arithmetic actions can be performed about which the conversation was above. In addition, this window has two items that are not included in individual groups: "skip empty cells" and "transpose".
  5. In a special insert you can get not only through context menuBut through Tools on the tape. To do this, you need to sit in the "Home" tab, click on the icon in the form of an angular direction down the triangle, which is located under the "Paste" button in the clipboard group. Then the list opens possible actions, Including the transition to a separate window.

Method 1: Working with values

If you need to transfer the values \u200b\u200bof the cells, the result in which is derived using computational formulas, the special insert is just intended for such a case. If you apply the usual copy, the formula will be copied, and the value that is displayed in it may not be at all that you need.

  1. In order to copy the values, select the range that contains the result of calculations. Copy it with any of those methods that we spoke above: the context menu, the tape button, the combination of hot keys.
  2. We highlight the area on the sheet where we plan to insert data. Go to the menu in one of those methods that were discussed above. In the "Insert values" block, select the "Values \u200b\u200band Formats" position. This item is most suitable in this situation.

    The same procedure can be made through the window previously described by us. In this case, in the "Paste" block, switch the switch to the "Values \u200b\u200band the numbers" position and click on the "OK" button.

  3. Whatever option you choose, the data will be transferred to the dedicated range. It is the result without transferring formulas.

Lesson: How to remove the formula in Excel

Method 2: Copying formulas

But there is a reverse situation when you need to copy exactly the formula.

  1. In this case, perform a copy procedure in any available way.
  2. After that, we highlight the area on the sheet where you should insert a table or other data. Activate the context menu and select the "Formula" item. In this case, only formulas and values \u200b\u200bwill be inserted (in those cells where there is no formulas), but it will be lost formatting and configuring numeric formats. Therefore, for example, if the date format was present in the source domain, then after copying it will be reflected incorrectly. The corresponding cells will need to additionally format.

    In the window of this action corresponds to the movement of the switch to the "Formula" position.

But it is possible to carry out the transfer of formulas with the preservation of the number of numbers or even with the complete preservation of the original formatting.

  1. In the first case, in the menu, select the position "Formulas and formats of numbers".

    If the operation is performed through the window, then in this case you need to rearrange the switch to the "Formulas and formats of numbers" then click on the "OK" button.

  2. In the second case, when you need to save not only formulas and numeric formats, but also complete formatting, you should select "Save original formatting" item.

    If the user decides to perform this task by switching to the window, then in this case you need to rearrange the switch to the "with the source theme" position and click on the "OK" button.

Method 3: Formatting Transfer

If the user does not need to tolerate the data, and it only wants to copy the table to fill it with completely different information, then in this case you can use a specific clause insertion.

  1. Copy the source table.
  2. On the sheet, we highlight the space where we want to insert the layout of the table. Call the context menu. In it in the "Other Insert Parameters" section, select "Formatting".

    If the procedure is performed through the window, then in this case, we rearrange the switch to the "Formats" position and click on the "OK" button.

  3. As we see, after these actions, the layout of the original table with preserved formatting takes place, but absolutely not filled with data.

Method 4: Copy Table Conservation Columns

It is no secret that if we fulfill a simple copy of the table, it is not a fact that all cells of the new table will be able to accommodate all source information. Correct this situation when copying can also be used using a special insertion.

  1. First, any of the above methods copy the source table.
  2. After starting the menu already familiar to us, select the value "save the width of the column of the original."

    A similar procedure can be performed through a special insert window. To do this, you need to rearrange the switch to the "column width" position. After that, as always, click on the "OK" button.

  3. The table will be inserted with the preservation of the original column width.

Method 5: Insertion Figure

Thanks to the possibilities of a special insertion, you can copy any data displayed on the sheet, including tables like a picture.

  1. Copy the object using conventional copy tools.
  2. We highlight the place on the sheet where the drawing should be placed. Call the menu. Select it in it "Figure" or "Related Figure". In the first case, the inserted pattern will not be connected with the source table. In the second case, when changing values, the table will automatically be updated in the table.

In the Special Insert window, this operation cannot be done.

Method 6: Copying Notes

Through a special insertion, you can quickly copy the notes.

  1. Select cells in which notes are contained. We perform them to copy them through the context menu, by means of a tape button or by pressing the CTRL + C key combination.
  2. We highlight the cells in which the notes should be inserted. Go to the special insert window.
  3. In the window that opens, we rearrange the switch to the "Notes" position. Click on the "OK" button.
  4. After that, the notes will be copied to the selected cells, and the remaining data will remain unchanged.

Method 7: Table Transposition

With the help of a special insertion, you can perform the transposition of tables, matrices and other objects in which the columns and strings must be swapped.

  1. We highlight the table that you want to turn over and make it a copy of one of the ways already known to us.
  2. We highlight the range on the sheet, where it is planned to put an inverted version of the table. Activate the context menu and select "Transpose" in it.

    This operation can also be carried out with the help of the windows familiar to us. In this case, you will need to install a check mark near the item "Transpose" and click on the "OK" button.

  3. And in that, in another case, the output will be an inverted table, that is, such a table that columns and lines have changed places.

Lesson: How to flip the table in exile

Method 8: Using arithmetic action

Through the tool described by us in Excel, you can also perform common arithmetic action:

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

Let's see how this tool is applied on the example of multiplication.

  1. First of all, fit into a separate empty cell number on which we plan to multiply the data range by means of a special insertion. Next, perform it to copy it. This can be done as by pressing the Ctrl + C key combination and causing the context menu or using the capabilities to copy to the ribbon.
  2. We highlight the range on the sheet that we have to multiply. Click on highlighting the right mouse button. In the context menu that opens, we double-click on the items "Special Box ...".
  3. The window is activated. In the Operation Parameters group, set the switch to the "Multiply" position. Next, click on the "OK" button.
  4. As we can see, after this action, all values \u200b\u200bof the selected range were multiplied by a copied number. In our case, this is the number 10.

By the same principle you can perform division, addition and subtraction. Only for this in the window will need to rearrange the switch respectively to the "split" position, "fold" or "subtract". Otherwise, all actions are similar to the above manipulation.

As you can see, a special insert is very useful tool for the user. With it, you can copy not only the entire data block in the cell or in the range, and dividing them to different layers (values, formulas, formatting, etc.). At the same time, it is possible to combine these layers with each other. In addition, through the same tool, arithmetic action can be performed. Of course, the acquisition of working skills with this technology will significantly help users on the development of the Excel program as a whole.

We are glad that you could help you solve the problem.

Ask your question in the comments, while playing the essence of the problem in detail. Our specialists will try to answer as quickly as possible.

Will this article help you?

Where is the stored information after copying? In the clipboard. And how to change the data under the desired conditions from the clipboard before inserting into the cell? To do this, the special insert tool in Excel is applied. Those. If you need to insert data without formatting, transpose or insert only formulas, then the tool is for you. The function is extremely useful - I strongly recommend to look closely.

In principle, everything else can be done in several actions without a special insertion, but with it, you will now see more comfortable.

Special insert in Excel. Example of use

If you copied the data with the calculations and want to insert only the values \u200b\u200bof this data, without formulas. Make - copy (Ctrl + C) The desired cells - select the cell / range to insert - click the right mouse button and select a special insert (Ctrl + Alt + V).

Then appears a table from the picture number 1. Select the "only values" checkbox.

It was an example, and now we will analyze all the options offered.

Option "Paste:"

Formulas - values -Formats - Primer

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

Insert conditions on values. We understand literally, insert the conditions over the values. This is either data check (drop-down list) or conditional formatting.

Those. If you need to apply additional conditions.

Insert with the source theme. The topic in Excel is the format of the entire book or sheet. It is used for a uniform color scheme or diagrams formats. If one topic is applied to this sheet, and you need to insert another. This item applies. Honestly, I rarely use this feature.

Insert without frame. Insert without borders

Insert column widths. Insert formatting the column width.

Formulas and formats of numbers. Forms and formats are inserted

Values \u200b\u200band formats of numbers. Very comfortably. Not only values \u200b\u200bare inserted, but also the formats of numbers.

Section Operation (folded, deduct, divide, multiply)

Cool! For example, you have two ranges

And you need to divide the data from the d column to data from the B column (see picture). Without formulas. Here, a special insert in Excel is appreciated.

Select the B3: B5 range. Copy. Select the D3: D5 range. Press a special insert. And celebrate the checkbox "divided"

Voila. Try yourself - like it.

Finally, three bombs of a special insertion

I guarantee these opportunities will save you a bunch of time

1. Skip empty cells

You have a range of D. column. And you need to insert it into B without data loss in V.

Well, you understood. If you do not use the "skip empty cells" function of a special insert, then non-empty values \u200b\u200bfrom B. This is saddened. Helps a special insert.

2. Transpose. When the columns need to be made by rows and vice versa. I even wrote a separate article

3. In the lower left corner there is a button insert communication. I repent, I learned about it only a few months ago. Everything is simple, connections are inserted on those cells (in a simple link) that you have allocated. If you have a burst range super-comfort!

Subscribe to the site and go to the VC group:

(Visited 1 508 Times, 2 Visits Today)

Special copying data between files in Excel includes a command. Special insert on the menu Edit. Unlike normal copying data using the command Insert team Special insert It can be used to calculate and convert information, as well as to bind data to Excel's workbooks (these features will be discussed in the next chapter).

Team Special insert It is often used to copy the cell formatting attributes.

1. Select the cell or cell to copy.

2. Select Edit, copy.

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

4. Select the dialog box Special insert Contains several parameters for inserting data (Fig. 1).

Fig. 1. Special insert

5. Install the required parameters, such as formats (when copying formats, only formatting changes, not the meaning of the cells).

6. Select OK .

The first group of parameters of the dialog box Special insert Allows you to select the contents or attributes of formatting that you want to insert. When choosing a parameter Everything The contents and attributes of each copy of the cell to a new place are inserted. 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 \u200b\u200band describes the operations performed on the insertion in the cells that already contain the data (Table 1).

Table 1. Command parameters Special insert

Parameter Result of the insert
Fold Inserting information will be folded with existing values.
Subtract Inserting information will be deducted from existing values.
Multiply Existing values \u200b\u200bwill be multiplied by the inserted information
Split Existing values \u200b\u200bwill be divided into inserted information
Skip empty cells You can perform actions only for cells containing information, i.e., with a special copying, empty cells will not destroy the existing data
Transpose The orientation of the inserted area will be switched from the rows on the columns and vice versa

Choice Not Means that the copied information simply replaces the contents of the cells. When choosing other options for operations, we obtain that current content will be combined with the insertion insertion and the result of such a merge will be the new contents of the cells.

The exercise

Perform calculations using the "Special Insert" command

Enter the data as shown in Table. 2.

Table 2. Source data

BUT IN FROM D. E. F. G. N.
1
2 5 2 1 2
3 12 3 10 3
4 8 2 15 4

1. Select the area to copy A2: A4.

2. Select Edit Copy.

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

4. Select Edit, special insert.

5. Set the parameter Multiply.

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

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

8. Select Edit, special insertion And set the parameter Transpose.

9. Copy your own column formats G. in column N. And get the table. 3.

Table 3. Command Result Special Insert

BUT B. C. D. E. F. G. N.
1
2 5 10 5 12 8 1 2
3 12 36 10 3
4 8 16 15 4

I often regretted that in MS Excel there is no such convenient combination of keys to insert a value like combinations Ctrl + SHIFT + Vused in Calc.. Oddly enough, but in Excel there are really no full-time and default combinations of keys to insert values. but alternative methods there is. Below we will look at them.


Fashion first , the most famous, most common and ... most time sustainable.

In cell A1, there is a formula that sums the values \u200b\u200bof cells B1 (1) and C1 (2). Instead, we want to insert the result of her work (3). What do we do for this?

1. Select a cell A1
2. Right-click the context menu
3. Select the item in the menu Copy

4. Again, right-click the context menu (because it disappears after selecting any command from it)
5. Select item Special insert

6. In the window that appears, select values

7. Hem OK

Everything, now we have instead of formula \u003d B1 + C1 value 3

Do not you think that seven steps for such simple actionHow is the inset of values, a bit?

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

1. Select a cell A1

2. Mouse to be on the lower border of the cell until we get the cursor in the form of a cross, each of the rays of which, crowned by arrows
3. Press the right mouse button and drag the cell somewhere (even enough to the neighboring) and return back
4. We release the right mouse button and select the item in the menu that appears. Copy only values

Ready. But it is long, besides, it is not suitable for those who are used to using the hot keys, and not the mouse.

Method Third (Works in Excel versions, published afterExcel 2003.) Unknown (I did not find it on the Internet), but it is fulfilled even faster, although it requires a one-time preset Excel.

So first add the insert button on the panel quick access (PBD).

1. Press the PBD settings button and select the item from the list that appears. Other teams ...

2. In the menu Select commands from: Select item All teams and from the list that appears we choose the icon labeled as Insert values.

3. Click the button Add And move the icon to the list appearing in the list on the right.

4. Our button appeared on the PBD

5. Press Alt. And we see the number of our button (in this case it is number 1)

Everything, the setting is completed.

And how to use this button now?

You can directly intended:
1. Select a cell A1
2. Copy (can be mouse, you can combine the keys)
3. Press the button Insert values

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

Fast? Conveniently? I think hotkeev lovers will appreciate this way :)

Fourth way, macro.

Insert this code into the standard Macro Personal Book Module and starting Excel You will always be available fast inserting values \u200b\u200bassigned key combination (in the example this combination Ctrl + SHIFT + V).

Sub CopyPasteValue ()
Selection.PasteSpecial Paste: \u003d XLPastevalues
End Sub.

This method has two drawbacks:
1. After applying the macro, the stack of changes history is cleared and the abolition of recent actions becomes impossible.
2. Macros must be allowed.

Fifth method, the most exotic and most ... Useless I mention it only for the completeness of the picture, in no case claiming that someone will use it.

This method is based on the exchange of data between applications, formulas are not saved. Accordingly, if you copy data from Excel of one version and insert to another, the insert will be made values \u200b\u200band not formulas. On the video, I copy the formula from Excel 2010 to Excel 2003 and vice versa. And in the case, the value is inserted.

Insert or Ctrl + V, perhaps, the most effective tool Available to us. But how well do you own them? Do you know that there is at least 14 different ways Data inserts in ECXEL sheets? Surprised? Then we read this post to become a past master.

This post consists of 2 parts:

- Basic insertion techniques

- Insert using data processing

1. Insert values

If you want to simply insert the values \u200b\u200bfrom the cells, press the keys, M and s sequentially, while holding the ALT key, and in the end, press the input key. It happens when you need to get rid of formatting and work only with data.

Starting with Excel 2010, the insertion function of the values \u200b\u200bis displayed in the pop-up menu while pressing the right mouse button.

2. Insert formats

14. What is your favorite way insert?

There are still many other hidden ways of insertion, such as inserting XML data, images, objects, files, etc. But I wonder what interesting insertion techniques you use. Write what is your favorite way inserts?