Familiarity with Excel’s copy/paste capabilities will significantly improve your ability to get more done in less time. Copy/paste seems simple enough but the feature offers far more than the simple name suggests. In this article, guest blogger Nick Williams explores copy/paste in depth.Nick is an Access tutor based in the U.K.
Copy and pasting is always a useful way of moving data from one location to another, however, in Excel it can be so much more. The copy as picture and paste special options add some really useful functions, yet many people are not aware of their capabilities.
Copy as picture
The copy as picture feature does exactly what it says, allowing part of the worksheet to be copied and pasted elsewhere a picture. This is particularly useful for taking snapshots when working with data that updates frequently. It is also useful when pasting data into another program, for example, Word or PowerPoint, as when copied as a picture, your table or chart will look exactly as it does in Excel when pasted, saving the need to tidy it up. The copy as picture feature can be accessed through the drop down menu next to the copy icon on the Home ribbon.
The standard paste tool will copy both the design and contents of a cell or selection of cells. Whilst more often than not this is what most people want to do, there are times when the additional features of paste special are handy. It can be accessed through the drop down menu below the paste icon on the Home ribbon.
The options available upon clicking on paste special will vary depending on what’s currently stored on the clipboard. An image will not offer any choices beyond what type of image to paste it as, whereas a cell copied from elsewhere within Excel will offer plenty of options and is where paste special is most useful.
When pasting a cell or group of cells from within Excel, there are two main option groups; paste and operation. The various paste options allow the user to pick what features from the cell they are copying to paste. The operation option allows basic mathematical operations to be applied to cells.
The below example aims to demonstrate how some of the paste functions work. Column A simply shows the numbers 1 to 5 with one decimal place, with cell A7 summing these 5 numbers with a simple formula. Some basic formatting to the background colour and cell borders has been applied and there is conditional formatting in place giving cells with a value of less than 3 a bold, red font. There is also a comment on cell A4.
All – Copies everything in the cell; the content, visual formatting, number formatting and comments. The pasted cells in Column B of the example appear exactly as they do in Column A, other than the width of the column itself.
Formulas and Values – In the example above, the formulas and values columns look identical to one another. The numbers from Column A have carried over into Column C and Column D, however the visual and number formatting have not, nor has the comment. The difference between the two is the formulas are copied if Formulas are selected, but not when Values are selected i.e. the formula bar for cell C7 would show =SUM(C2:C6) whereas the formula bar for Cell D7 would simply show the number 10. Replacing formulas with numbers can be a good way of getting a snapshot of data in a spreadsheet that is updated frequently.
Formats – Selecting formats will see the visual formats carried over (including conditional formatting), but neither the data nor comments are copied with it. Number formatting is not copied, so if the number 1 was to be entered into one of the cells in Column E in the above example, it would show as 1 rather than 1.0.
Comments – This will paste only the comments, and not any of the formatting or data contained in the copied cell, a great time saver if the same comment needs to be used repeatedly throughout a spreadsheet.
Validation – It is difficult to show validation on the example as it works in the background. It does what it says, and pastes only validation rules from the copied sell into the pasted cell. If the cell being pasted into already contains a value that does not meet the validation rules, it will not be flagged up unless the cell contents are edited.
All using Source theme – This is difficult to show in the example as it only works if pasting into a different spreadsheet. As well as formatting cells individually, it is possible to set a theme for the whole spreadsheet which will control things like font and colour. When copy and pasting data from one spreadsheet to another, formatting set by the theme will not be copied unless this option has been selected.
Column widths – Pasting column widths allows the width of one column to be applied to another without affecting any of the data or formatting in it. Pasting column widths so that they are all equal can make a spreadsheet look much neater. In the example, Column H, where column widths have been pasted, is the only column with the same width as Column A.
Formulas and number formats – As with pasting a formula, this option will paste the formula from one cell to another. The difference here is that number format, for example currency or number of decimal places, will also be copied.
Values and number formats – Similarly, pasting values and number formats will keep the number formatting, but will convert any formulas to values.
All merging conditional formats – Although conditional formatting is pasted along with any other formatting, it will override any conditional formatting already contained in the cells. This option allows the conditional formatting in both the source and destination cells to be applied. In the above example, Column K had a conditional format applied telling it to make all cells with a value of less than 3 brown. Rather than overwrite this, both this conditional format, and the one in the copied cells turning the font bold and red have been applied.
The example below shows how the operations work within paste special. They will add, subtract, multiply or divide the value in the pasted cell by the value in the copied cell. In the example, Columns D to G originally contained the numbers 10, 20, 30, 40 and 50, as shown in Column C. When copying the number 10 from Cell A2 and pasting into Columns D to G using a mathematical operation, the number 10 is added, subtracted, multiplied or divided from the original number.
Skip blanks – This is something that may be used when copy and pasting multiple cells. If one of the copied cells is blank, Excel will not overwrite data in a pasted cell with a blank, rather it will leave the contents as they were originally, while overwriting any cells where the copied cells do contain data. Cell C4 in the below example shows how when a blank cell is copied and skip blanks is selected from the paste special menu, the original contents of the cell remain.
Transpose – Transposing data allows rows to be turned into columns and vice versa.
Paste link – Rather than paste the value or formula that is in a cell, selecting paste link will create a formula linking to the data from the copied cell, rather than the actual formula or data it contains. This is useful if the data is likely to change but has to appear in multiple places throughout a workbook.