Paste Special In Microsoft Excel

By Author: Ed Bolton
Total Articles: 17
Comment this article

Copy and paste. In Windows, there are very few functions that everyone will use, but the ability to copy data in one place, and put it in another is considered to be one of the most basic IT skills. Even the keyboard shortcuts (Ctrl+C, Ctrl+V) are known by office workers all across the world.


On the internet, you might copy a link into your web browser. In a presentation, you may copy an image. In both cases, you don’t mind copying everything across to the destination. Unfortunately, if you are developing a spreadsheet in Microsoft Excel, it isn’t so simple.


In Word, the natural unit to copy is exactly that; a word. Due to the layout of a spreadsheet, the natural unit is not a number, but instead it is a complete cell. So instead of copying a value, you are copying a cell, complete with all its formatting and formulas. As Excel formulas transpose from one location to another, this allows you to copy a function across many rows of a spreadsheet. Even Excel experts benefit from the speed at which you can edit a spreadsheet. However, it can also ...
... become a great source of frustration to the beginner.


Imagine you’ve built a table and spent a decent period of time applying borders. The data looks fantastic and can be inserted into Powerpoint. Next month the data has changed and you wish to copy it in from another source. If you use a basic Copy and Paste, you lose the borders. You may also lose the font, you can lose the color, you can even lose the text alignment. The whole reason for pasting data is that it saves time and it could now end up costing you time.


The solution is to use the Paste Special function. When you right-click on a destination within Excel, you will this option appear on the dropdown menu. You will then be confronted with a number of additional options. Let’s say you want to copy just the values from source to destination. In that case, you can select the Values option, hit OK, and none of the formatting will change.


Pasting values also has the effect of discarding any associated formulas. Therefore it is often essential to paste special as values when copying calculated data from one spreadsheet to another. Otherwise Excel will attempt to link the two spreadsheets which is often highly undesirable as it can throw up a bewildering array of messages when opening the file.


Sometimes you only want to copy the formulas. This is the case if you are trying to apply formulas down the length of a table but e.g. the first line has a thick top border. Actually, the “All except Borders” option would do the job perfectly adequately if not better. All the Paste Special descriptions should be fairly self-evident and it’s worth experimenting each and every time you indulge in spreadsheet development.


Below the main options in Paste Special are a number of Operations. These allow you to add, subtract, multiply or divide all your entries by the same number. A common use arises when you open a text document that contains numbers. Sometimes Microsoft Excel will continue to treat numerical entries as text. This makes it impossible to manipulate the data. A nice trick is to enter “1” in a blank cell, copy it, and multiply all the text-formatted cells by it. Excel will work out they are numbers whilst leaving the data unchanged.


Paste Special is one of Excel’s most important functions. In Office 2010, Microsoft have decided it deserves even greater prominence. Now when you copy data and right-click on a destination, a number of different paste options are automatically displayed. It’s testimony to the fact that Paste Special is so ubiquitous that certain options are now considered almost as common as the traditional Paste.


About the author: Ed Bolton is the founder of Excel4Business, Excel experts.