Instructions and tips on using the cut, copy and paste functions in Excel, and using absolute and relative references.

Being able to copy and paste saves considerable time in large spreadsheets where information is used in several locations, or where formulas are used multiple times.

Users can copy text, numbers or formulas within a spreadsheet, or to other spreadsheets or applications.

Accessing Copy Commands

There are 4 ways to access Copy and Paste commands:

• Finding the word copy or paste on the main Edit menu.
• Right clicking the box you want to copy and left clicking on copy.
• Clicking on the Copy or Paste icons in the standard toolbar.
• Using the keyboard commands. Copy is Ctrl C, and Paste is Ctrl V.

Using the Copy and Paste Commands

Once you have identified the cell you want to copy, click copy by one of the methods above, then move to where you want to paste and then select Paste. If there multiple cells to be copied, first highlight all the cells, click anywhere in the highlighted area, and then move to where you want to paste.

You can also copy entire rows or columns. Instead of “Paste”, consider whether “Insert Copied Cells” is more appropriate. Paste will overwrite whatever is already in the cells, and Insert will create a new row or column and not overwrite.

The Difference between Cut and Copy

Cut is used when the user wants to move information from one cell to another, and not leave any data in the original cell. Copy is used when both the original and new cell should contain the same data or formula.

Be careful when using Cut, since any formulas that depend on that cell will reference the new cell, including sums.

Absolute and Relative References

Copying formulas that reference other cells can be tricky in Excel. The default setting is for the references as relative.

For instance, consider a formula that adds the first 5 rows of column B in cell B6. If the formula is copied to cell C6, the sum in that cell will be of the first 5 rows in column C. This is normally what the user would want.

For the next step in the above example, the user may want to divide cell C1 by C6 to get a percentage in cell D1. Copying that result to D2 will not work, because the result in D2 will be =C2/C7, not C6, using the relative reference.

Using Absolute References

Absolute references work differently. By making the reference at cell 6 absolute, the user can copy to all the cells in column D to get the correct answer. Make the reference absolute by clicking on the formula and placing the cursor on the cell name that you want to fix. Then either hit the F4 key or place a $ sign before the cell reference.

For example, =C2/C6 will not copy properly, but C2/$C$6 can be copied to cell D3, and the result will be =C3/$C$6.

Using Paste Special

Paste Special allows the user to copy only certain aspects of the copied cell to the destination cell. It is commonly used to transfer only the results of formulas from one cell to another or even from one worksheet to another.

To accomplish this, copy as usual, and paste special to the destination cell. Be sure to click values instead of all.

Whether using Microsoft Excel in a business setting or for personal use, knowing the shortcuts and helpful hints can make a person more productive. Excel has many features that can make assignments quicker, allowing more time for analysis or other projects.