Paste special in Excel. Excel Paste Special Doesn't work in Excel Paste Special
Team " Menu" - "Edit" - "Specialinsert" (or through the right-click context menu) allows you to expand the capabilities of regular copying.
Let's look at a few examples.
To replace formulas in cells with final values, select the required range, copy it, select where you want to copy the values, run " Menu" - "Edit" - "Special insert", mark the item " values" and click OK.
To copy formulas and paste them without changing the format of the final cells, use the " formulas".
It often happens that a table contains numeric values stored as text.
To fix this, select a blank cell, click copy, then select the range that you want to convert to numbers, run " Special insert", select the operation " fold" and click OK.
In general, you can change each value of the selected range in accordance with the selected item " operation". Suppose we need to reduce each value of the range by 100 times. Write 100 in an empty cell, copy it, select the range that needs to be changed, run " Special insert", select " surgery" "divide" and click OK.
To perform the operation only on non-empty cells, you can additionally check the box " skip empty cells".
If it is necessary to transpose the table, i.e. swap rows and columns, do the following. Copy the source table, select where to paste the final data, run " Special insert", tick " transpose" and click OK.
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 this 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 designed to insert a specific expression into an Excel sheet in the form that the user needs. 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.
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.
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 completely preserving the original 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.
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.
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 NotesUsing Paste Special, you can quickly copy notes.
Using paste special, you can perform the operation of transposing tables, matrices and other objects in which you need to swap columns and rows.
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, Paste Special is a 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 in working with this technology will significantly help users on their way to mastering the Excel program as a whole.
In this article, we will show you several more useful options that the Paste Special tool is rich in, namely: Values, Formats, Column Widths and Multiply / Divide. With these tools, you can customize your tables and save time on formatting and reformatting data.
If you want to learn how to transpose, remove references, and skip blank cells using the Paste Special tool, refer to the article Paste Special in Excel: Skip blank cells, transpose, and delete references.
We insert only valuesLet's take, for example, a spreadsheet that reports profits from cookie sales at a charity bake 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 we had a week ago and the profit we made this week. Do you see =D2+C3 in the formula bar? Cell D3 shows the result of this formula - $100. In other words, cell D3 displays the value. And now comes the most interesting part! In Excel, using the Paste Special tool, you can copy and paste the value of this cell without a formula or formatting. This opportunity is sometimes vital, and I will show why later.
Suppose, after selling cookies for 15 weeks, you need to submit a general report on the results of the profit received. You may want to simply 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 copy and paste action ended up copying only the formula from the cell? You need to copy and paste special the value itself. That's what we'll do! We use the Paste Special command with the Values parameter to ensure everything is done as it should.
Notice the difference in the image below.
By using Paste Special > Values, we paste the values themselves, not the formulas. Great job!
You may have noticed something else. When we used the Paste Special > Values command, we lost the formatting. See how the bold font and number format (dollar signs) were not copied? You can use this command to quickly remove formatting. Hyperlinks, fonts, and number formats can be quickly and easily cleared, leaving you with just the values without any decorative stuff that might get in the way in the future. Great, right?
In fact, Paste Special > Values is one of my favorite tools in Excel. It is vital! Often I am asked to create a table and present it at work or in community organizations. I'm always worried that other users might wreak havoc on the formulas I've entered. After I'm done with the formulas and calculations, I copy all my data and use Paste Special > Values on top of it. This way, when other users open my spreadsheet, the formulas can no longer be changed. It looks like this:
Notice the contents of the formula bar for cell D3. It no longer contains the formula =D2+C3, but instead contains the value 100.
And one more very useful thing regarding the special insert. Let's say that in the table for accounting for profits from a charity cookie sale, I want to leave only the bottom row, i.e. delete all rows except week 15. See what happens if I just delete all these rows:
That annoying #REF error appears! (#LINK!). It appears because the value in this cell is calculated using a formula that refers to the cells above it. After we deleted these cells, the formula had nothing to refer to and reported an error. Instead, use the Copy and Paste Special > Values commands on top of the original data (as we did above), and then delete the extra rows. Great job:
- by right-clicking and selecting Paste Special from the context menu.
- On the Home tab, click the small triangle under the Paste button and select Paste Special from the drop-down menu.
Paste Special > Formats is another very useful tool in Excel. I like it because it makes it quite easy to customize the appearance of the data. There are many uses for the Paste Special > Formats tool, but I'll show you the most notable one. I think you already know that Excel is great for working with numbers and performing various calculations, but it is also great when you need to present information. Besides creating tables and counting values, you can do a variety 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 the Winter 2010 schedule template and loved the formatting, font, color and design.
I don’t need the schedule itself, especially for the winter of 2010, I just want to remake the template for my purposes. What would you do if you were me? You can create a draft of the 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 this will also take a lot of time. It's much easier to copy the template and do Paste Special > Formats on a new sheet of your workbook. Voila!
Now you can enter data while maintaining all formats, fonts, colors and designs.
Have you ever wasted a lot of time and energy circling around your spreadsheet trying to adjust the column sizes? My answer is of course yes! Especially when you need to copy and paste data from one table to another. The existing column width settings may not be suitable, and even though automatic column width adjustment is a convenient tool, in some places it may not work as you would like. Paste Special > Column Widths is a powerful tool that should be used by those who know exactly what they want. Let's look at the list of the best US MBA programs as an example.
How could this happen? You can see how carefully the column width was adjusted to fit the data in the image above. I copied the top ten business schools and placed them on another sheet. Look what happens when we just copy and paste the data:
The content is inserted, but the column widths are far from appropriate. You want to get the exact same column widths as the original sheet. Instead of adjusting it manually or using auto column width, just copy and paste Special > 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 will be if the Excel sheet contains hundreds of columns.
You can also adjust the width of empty cells to format them before manually entering text. Look at columns E and F in the picture above. In the image below, I used the Paste Special > Column Widths tool to widen the columns. So, without any fuss, you can design your Excel sheet the way you want!
Remember our cookie example? Good news! A giant corporation found out about our charity event and offered to increase profits. After five weeks of sales, they will be donated to our charity, so the income will double (become twice as large) compared to what it was at the beginning. Let's go back to the table in which we kept track of the profits from the charity cookie sale, and recalculate the profits taking into account the new investments. I added a column showing that after five weeks of sales, profits will double, i.e. will be multiplied by 2.
All income from week 6 onwards will be multiplied by 2. To show the new numbers, we need to multiply the corresponding cells in column C by 2. We can do this manually, but it will be much nicer if Excel does it for us using the Paste Special > Multiply command. To do this, copy cell F7 and apply the command to cells C7:C16. Totals have been updated. Great job!
As you can see, the Paste Special > Multiply tool can be used in a variety of situations. The same is true with Paste Special > Divide. You can divide an entire range of cells by a specific number quickly and easily. You know what else? Using Paste Special with the Add or Subtract option you can quickly add or subtract a number.
Paste Special > Divide/Multiply: HighlightsSo, in this lesson, you learned some very useful features of the Paste Special tool, namely: you learned how to paste only values or formatting, copy column widths, multiply and divide data by a given number, and add and remove a value directly from a range of cells.
Word for Office 365 Outlook for Office 365 PowerPoint for Office 365 Access for Office 365 Word 2019 Outlook 2019 PowerPoint 2019 Access 2019 Word 2016 Outlook 2016 PowerPoint 2016 Access 2016 Visio 2013 Word 2013 Outlook 2013 PowerPoint 2013 Access 2013 Word 2010 Outlook 201 0 PowerPoint 2010 Access 2010 Visio 2010 Word 2007 Outlook 2007 PowerPoint 2007 Access 2007 Word Starter 2010 Less
You can set formatting when you insert slides, text, pictures, objects, and tables from other programs or the Internet into Office applications such as Access, Word, PowerPoint, Excel, and Outlook.
Using a special insertLike pictures and other objects, text in a presentation has its own formatting - such as font size, color, and font. When you copy text that has different formatting in an Office application, programs such as PowerPoint or Word automatically format that text to match the destination text. However, use Paste Special to preserve the original formatting and paste them as a link or image, for example.
InsertTo set the format for the inserted slide, text, or object, click the Insert button, and then choose one of the values listed in the Format list below.
|
Rich text |
|
|
Unformatted text |
You want to use the formatting of the Office program into which you paste the text. |
|
Microsoft Office Graphics Object |
You want the contents of the clipboard to be output as a Microsoft Office picture object. |
|
Picture (GIF) |
You want the contents of the clipboard to be output as a GIF image. The GIF file format supports only 256 colors, so it is more suitable for scanned images such as illustrations and less suitable for color photographs. The GIF format is also good for drawings, black and white images, small text, and animations. |
|
Drawing (JPEG) |
You want the contents of the clipboard to be output as a JPEG image.  The JPEG file format supports 16 million colors and is best suited for photographs and complex graphics |
|
Drawing (PNG) |
You want the contents of the clipboard to be output as a PNG image. The PNG file format is similar to GIF but provides better color support. PNG compresses solid areas of color while maintaining sharp detail in sketches, logos, or illustrations with text. Saving, restoring and resaving PNG images occur without loss of quality. Unlike GIF, PNG does not support animation. Additionally, some legacy browsers and applications do not support PNG. |
|
Figure (Windows Metafile) |
You want the contents of the clipboard to be output as a WMF image. |
|
Figure (extended metafile) |
You want the contents of the clipboard to be output in EMF format. |
|
Drawing (DIB) |
You want the contents of the clipboard to be output in DIB format. For example, you can save a slide as a graphic to use on web pages. The DIB format is a representation of a graphic object in computer memory in the form of rows and columns of dots. The value of each point (whether it is filled or not) is recorded in one or more data bits. |
|
Bitmap |
You want the contents of the clipboard to be output as a bitmap. The Microsoft Windows BMP format can display millions of colors. Since it is supported by many programs, it is recommended to use it if the person you are providing the drawing to may not have the application in which you created the image. |
Note: The Link option will not be available if you cut or copy content from a document that does not support it, or if the document you are trying to link to has not yet been saved.
To have an OLE application icon instead of the actual content, select the As icon check box. You can click the icon to open the application and view the content. The As Icon check box is only available when you use the Link option or when you insert content as an OLE object. You can change the icon for pasted content.
The volumes of information stored in Excel sometimes reach very impressive sizes, have different sources and can partially copy themselves. In addition to data, table formatting and formulas play an important role. All this is useful to be able to manage correctly regarding copying and pasting.
Paste from web pagesThe text of Internet pages almost always has an individual style, which is saved in the clipboard and recognized by the application. But Excel sheets may have their own design that needs to be saved.
For example, let's copy the exchange rates of the Central Bank of the Russian Federation from the main page of Yandex to a worksheet. The image shows that the application has changed its appearance quite a bit, but in general everything remains the same as in the source code.
Let's repeat the insertion, but now taking into account the final formatting. Find the Clipboard area on the Home tab ribbon and expand the Paste Options menu by clicking the arrow below the Paste icon.
2 options will be provided. The first one is called "Keep Source Formatting". It is similar to a simple insert. The second method, called “Use Final Formatting,” is the opposite of the first, but preserves the position of the elements relative to each other. Those. its only difference is the removal of hyperlinks, font style, borders, merging cells and other things.
The insert options menu also contains 2 additional items:
- Updatable web request – available only when copying elements from the Internet and allows you to save the data of the entire web page according to the specified parameters;
- Paste Special is always available, but has different options depending on the data source.
Excel provides many more paste options when copying internally than when copying from external sources. Below is a screenshot of these parameters and their description (order from top left to right):
- Paste – simple insertion.
- Insert formulas – only formulas are inserted. Relative links are shifted.
- Formulas and number formats – similar to the previous point, but retains the number format as in the source data.
- Save original formatting – similar to the first point, because copies formulas, format, and conditional formatting.
- No Borders – Removes borders for the copied data, if any. If the boundaries are set by conditional formatting, they remain.
- Maintain the width of the original columns – when pasting, the width of the original cells is taken into account.
- Transpose - Changes the orientation of data so that columns become rows and rows become columns.
- Merge conditional formatting - similar to simple paste, but if there is conditional formatting in the source and destination ranges, it merges them.
- The next 3 options (Values, Values and Number Formats, and Values and Source Formatting) insert the results of the cells, but you can optionally keep the number format or all formatting. This is convenient when copying ranges with formulas;
- Formatting – inserts only formatting, including conditional;
- Insert Link – sets links to the source range;
- Picture – Excel will create a picture of the copied data;
- Related figure - differs from the previous paragraph in that it is updated when the source data changes.
To display the contents of the clipboard on the main tab, click on the arrow located in the lower right corner of the area of the same name. A panel will open showing the copied items, if any. Most likely, the latest data will be shown there.