Paste Special in Excel: Values, Formats, Column Widths. Paste in visible rows in Excel How to add cell data in excel

Those who constantly work with Microsoft Excel often have to wonder about the correct copying of table data while maintaining formatting, formulas or their values.

How to insert a formula into an Excel table and keep the table format? After all, when solving this problem, you can save a car of time. To do this, we will use the "Paste Special" function - this is a simple tool that allows you to quickly solve complex problems and master the general principles of its use. Using this simple tool allows you to quickly format all tables in the same format and get the result you need.

How to copy a table with column width and row height

Let's say we have such a table, the format of which needs to be preserved when copying:

When copying to another sheet using the usual Ctrl + C - Ctrl + V method. We get the unwanted result:

Therefore, you have to manually “expand” it in order to bring it into a convenient form. If the table is large, then it will take a long time to “mess around”. But there is a way to significantly reduce temporary losses.

Method 1: Use a special insert

She received the original parameters and looks perfectly accurate.

Method 2: Select columns before copying

The secret of this method is that if you select its columns along with the headings before copying the table, then when you paste the width of each column will also be copied.

For each case, it is rational to apply its own method. But it is worth noting that the second method allows us not only to quickly transfer the table along with the format, but also copy the height of the rows. After all, there is no “line height” option in the paste special menu. Therefore, to solve such a problem, you should act like this:

Insert formula values while maintaining the table format

Paste Special, while not perfect, should not underestimate its capabilities. For example, how to insert a formula value into an Excel spreadsheet and keep the cell format.

To solve such a problem, you should perform 2 operations using a special paste in Excel.

Operation I:

Operation II:

Since the copied range is still on the clipboard after copying, we immediately call the special paste again where we select the “formats” option. Click OK.

We have pasted the formula values into the table and saved the cell formats. As you guessed, you can do a third operation to copy the width of the columns, as described above.

Useful advice! In order not to perform the second operation, you can use the Format Painter tool.

Microsoft Excel provides users with almost unlimited possibilities for calculating the simplest functions and performing a number of other procedures. Using the program allows you to set formats, save cell values, work with formulas, transfer and change them in a way that is convenient for users.

Insert and delete rows, columns, and cells to best fit your data on the sheet.

Note: Microsoft Excel has the following row and column limits: 16,384 columns wide by 1,048,576 rows high.

Inserting and deleting a column

To insert a column, select it and then on the tab home press the button Insert and select the item Insert columns on sheet.

To remove a column, select it and then on the tab home press the button Insert and select the item Remove columns from sheet.

You can also right-click at the top of a column and select Insert or Delete.

Inserting and deleting a row

To insert a row, select it and then on the tab home press the button Insert and select the item Insert rows on a sheet.

To delete a line, select it and then on the tab home press the button Insert and select the item Delete lines from sheet.

You can also right-click the highlighted row and select Insert or Delete.

Inserting a cell

Select one or more cells. Right click and select command Insert.

In the window Insert select the row, column, or cell to insert.

For example, to insert a new cell between the Summer and Winter cells:

Click the "Winter" cell.

On the tab home Insert and select command Paste Cells (Shift Down).

A new cell is added above the "Winter" cell:

Inserting rows

To insert one line: Right-click the entire line above which you want to insert a new one and select command Insert Rows.

To insert multiple rows, do the following: Highlight the same number of rows above which you want to add new ones. Right-click the selection and select command Insert lines.

insert columns

To insert one new column, do the following: Right-click the entire column to the right of where you want to add the new column. For example, to insert a column between columns B and C, right-click column C and choose Insert columns.

To insert multiple columns, follow these steps: Select the same number of columns to the right of which you want to add new ones. Right-click the selection and select command Insert columns.

Deleting cells, rows and columns

If you no longer need any cells, rows, or columns, here's how to remove them:

Select the cells, rows or columns you want to delete.

On the tab home click the arrow below the button Delete and select the desired option.

When rows or columns are deleted, the following rows and columns are automatically shifted up or to the left.

Advice: If you change your mind immediately after deleting a cell, row, or column, simply press CTRL+Z to restore it.

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

ctrl+c data copying;
Ctrl+X cut;
ctrl+v paste information from the clipboard.

So, in Excel there is 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 individually.

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

Comments;
cell format;
Meaning;
formula;
Registration.

How Special Paste Works

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

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

The window with all functions looks like this.

Now we will understand in order and start with the "Insert" block.

Everything- 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 the formula cell;
Formats- only the cell format is transferred. Also, the design of the cell will be copied, for example, the background fill;
Notes- copy cell note. In this case, the data (formula, values, etc.) is not copied;
Conditions on values- using this option, you can copy, for example, the criterion of valid data (drop-down list);
With original theme- the cell is copied with the design preserved. For example, if you use a background fill in a cell, it will be copied too. In this case, the formula will be copied;
no frame- if a cell has a frame on either side, then it will be deleted when copying;
Column Widths- will copy the column width from one cell to another. This feature is very useful when you are copying data from one sheet to another. Only column widths wrap;
Formulas and number formats- the formula and format of numbers are transferred;
Meanings and number formats- transfer result and number format.

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 topmost cell and select Paste Special;
Set active item Meaning and press the key OK.

The column now contains the results instead of the formula.

Let's consider one more example. To do this, copy and paste the existing table next to it.

As you can see, the table didn't save the column widths. Our task now is to transfer the width of the columns to the new table.

Copy the entire original table;
We stand on the top left cell of the new table and click the right mouse button. Next, select Paste Special;
Set active item Column Widths and press the key OK.

Now the table looks exactly like 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 in the cell;
Multiply- the value in the cell will be multiplied by the value to be inserted;
Divide- the value in the cell will be divided by the value to be inserted.

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

Task: multiply each of the numbers by 10. What you need to do for this:

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

At the end we get the desired result.

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

In a new cell, 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 active Multiply.

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

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

There are the last two options that 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 data in a cell into which an empty cell is inserted;
Transpose- changing the orientation of the copied cells, i.e. rows become columns and columns become rows.

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

In this article, we will show you some more useful options that the tool is rich in. Special insert, namely: Values, Formats, Column Widths and Multiply/Divide. With these tools, you can customize your spreadsheets and save time formatting and reformatting data.

If you want to learn how to transpose, remove links, and skip blank cells with the Paste Special(Paste Special) refer to the article Paste Special in Excel: skip blank cells, transpose and remove references.

Paste only values

Take, for example, a spreadsheet showing profits from sales of cookies at a bakery charity sale. You want to calculate how much profit was made in 15 weeks. As you can see, we used a formula that adds up the amount of sales that were made a week ago and the profit made this week. See in the formula bar =D2+C3? Cell D3 shows the result of this formula − $100 . In other words, in a cell D3 value is displayed. And now it will be the most interesting! In Excel using the tool Paste Special(Paste Special) You can copy and paste the value of this cell without formula or formatting. This feature is sometimes vital, and I'll show you why later.

Let's say that after you've been selling cookies for 15 weeks, you need to submit a general report on the results of the profits received. You may want to just copy and paste the line that contains the grand total. But what happens if you do this?

Oops! Is this not at all what you expected? As you can see, the usual action copy and paste as a result copied only the formula from the cell? You need to copy and paste the value itself. So we will do it! We use the command Paste Special(Paste Special) with option values(Values) so that everything is done right.

Notice the difference in the image below.

Applying Special insert > Values, we are inserting the values themselves, not the formulas. Great job!

Perhaps you have noticed something else. When we used the command Paste Special(Special Paste) > values(Values), we've lost the formatting. See how the bold and number format (dollar signs) weren't copied over? You can use this command to quickly remove formatting. Hyperlinks, fonts, number format can be quickly and easily cleaned up and you are left with just the values without any decorations that might get in the way in the future. It's great, right?

Actually, Special insert > Values is one of my favorite tools in Excel. It is vital! I am often asked to create a spreadsheet and present it at work or community organizations. I'm always worried that other users might mess up the formulas I've entered. After I'm done with formulas and calculations, I copy all my data and use Paste Special(Special Paste) > values(Values) on top of them. Thus, when other users open my spreadsheet, the formulas can no longer be changed. It looks like this:

Pay attention to the contents of the formula bar for the cell D3. It no longer has a formula =D2+C3, instead the value is written there 100 .

And one more very useful thing with regards to the special paste. Let's say I want to keep only the bottom line in the cookie giveaway profit spreadsheet, i.e. delete all lines except week 15. See what happens if I just delete all these lines:

This annoying error appears #ref!(#REF!). It appeared because the value in this cell is calculated by a formula that refers to the cells above. After we removed those cells, the formula had nothing to refer to and reported an error. Use commands instead Copy(copy) and Paste Special(Special Paste) > values(Values) on top of the original data (as we already did above), and then remove the extra lines. Great job:

Paste Special > Values: Highlights

Highlight data
Copy them. If the data is not copied, but cut, then the command Paste Special(Paste Special) will not be available, so be sure to use copy.
Select the cell where you want to paste the copied data.
Click Paste Special(Special insert). It can be done:
- by right-clicking and selecting from the context menu Paste Special(Special insert).
- tab Home(Home), press the small triangle under the button paste(Insert) and from the drop-down menu select Paste Special(Special insert).
Check option values(Data).
Click OK.

Insert only formats

Special insert > Formats this is another very useful tool in excel. I like it because it allows you to easily customize the appearance of the data. There are many uses for the tool Special insert > Formats but I will show you the most remarkable. I think you already know that Excel is great for working with numbers and for performing various calculations, but it also does a great job when you need to present information. In addition to creating tables and counting values, you can do all sorts of things in Excel, such as schedules, calendars, labels, inventory cards, and so on. Take a closer look at the templates that Excel offers when creating a new document:

I came across a pattern Winter 2010 schedule and I liked the formatting, font, color and design in it.

I don't need the schedule itself, much less for winter 2010, I just want to remake the template for my purposes. What would you do in my place? You can create a draft table and manually repeat the template design in it, but this will take a very long time. Or you can delete all the text in the template, but that will also take a lot of time. It's much easier to copy the template and do Paste Special(Special Paste) > Formats(Formats) on a new sheet of your workbook. Voila!

Now you can enter data, keeping all formats, fonts, colors and designs.

Paste Special > Formats Highlights

Highlight the data.
Copy them.
Select the cell where you want to paste the data.
Click Paste Special(Special insert).
Check option Formats(Formats).
Click OK.

Copying column widths to another sheet

Have you ever wasted a lot of time and energy circling around your table and trying to adjust column sizes? My answer is yes, of course! Especially when you need to copy and paste data from one table to another. Existing column width settings may not work, and even though the automatic column width setting is a handy tool, in places it may not work as you would like. Special insert > Column Widths is a powerful tool that should be used by those who know exactly what they want. Let's take a look at the list of the best US MBA programs as an example.

How could this happen? You can see how neatly the column width has been adjusted to fit the data in the figure above. I copied the top ten business schools and put them on another sheet. See what happens when we just copy and paste the data:

The content is pasted, but the column widths are far from appropriate. You want exactly the same column widths as the original worksheet. Instead of configuring it manually or using autofit column width, just copy and paste Paste Special(Special Paste) > Column Widths(Column Widths) to the area where you want to adjust the column widths.

See how simple it is? Although this is a very simple example, you can already imagine how useful such a tool would be if an Excel sheet contains hundreds of columns.

In addition, you can adjust the width of empty cells to format them before manually entering text. Look at the columns E And F in the picture above. In the picture below, I used the tool Special insert > Column Widths to expand columns. So, without too much fuss, you can design your Excel sheet the way you want!

Paste Special > Column Width Highlights

Highlight the data.
Copy the selected data.
Place the cursor on the cell whose width you want to adjust.
Click Paste Special(Special insert).
Check option Column Widths(column widths).
Click OK.

Paste Special: Divide and Multiply

Remember our cookie example? Good news! A giant corporation found out about our charity event and offered to increase our profits. After five weeks of sales, they will invest in our charity, so that the income will double (become twice as much) compared to what it was at the beginning. Let's go back to the spreadsheet we used to record the profits from the cookie charity sale and recalculate the profits to account for the new investment. I've added a bar showing that after five weeks of sales, profits will double, i.e. will be multiplied by 2 .

All income starting from the 6th week will be multiplied by 2 .To show the new numbers, we need to multiply the corresponding cells of the column C on the 2 . We can do this manually, but it will be much nicer if Excel does it for us using the command Paste Special(Special Paste) > Multiply(Multiply). To do this, copy the cell F7 and apply command on cells C7:C16. The totals have been updated. Great job!

As you can see, the tool Special insert > Multiply can be used in a variety of situations. The same is true with Paste Special(Special Paste) > Divide(Divide). You can divide an entire range of cells by a specific number quickly and easily. Do you know what else? With help Paste Special(Special Paste) with option Add(Add) or Subtract(Subtract) You can quickly add or subtract a number.

Paste Special > Divide/Multiply: Highlights

Select the cell containing the number you want to multiply or divide by.
Copy the selected data.
Place the cursor on the cells where you want to multiply or divide.
Click Paste Special(Special insert).
Check option Divide(Split) or Multiply(Multiply).
Click OK.

So, in this tutorial, you have learned some very useful features of the tool. Special insert, namely: they learned to insert only values or formatting, copy the width of columns, multiply and divide data by a given number, and also add and remove a value immediately from a range of cells.

In Excel, you always have to carry out various manipulations with tables, and then we will look at how to copy a table in Excel, and how to paste a table into Excel. These operations are actually very simple, but when using them, it is necessary to take into account a number of subtleties when working in this program.

To copy a table to Excel, of course, you must first select it. The fastest way to copy is to use the keyboard shortcut "Ctrl+C" or use the corresponding menu item "Copy" tab "Home".

Next to the menu item "Copy" there is an arrow, when clicked, an additional option appears "Copy as picture...". If this option is selected, the table selected by us will be copied as a picture, but a dialog box with additional copy settings will be displayed.

The table copied in this way in Excel will be stored on the clipboard as a picture.

We will assume that we managed to copy the table, now it's time to figure out how to insert the table into Excel. Here, too, everything can be very simple, since you can use the key combination "Ctrl+V". But when inserting a table into Excel, as well as when inserting any other data, one should take into account the fact that the inserted data will, starting from the selected cell on the sheet, occupy the necessary space, replacing all the information placed in the cells required for this table. Data is inserted starting from the top left cell. And it should also be remembered that if, after copying a table or other data, any manipulation is performed on the sheet, except for moving the cursor, then the data from the clipboard will be deleted. This means that you need to insert a table into Excel immediately after copying it.