Chapter 4 of my Ebook "5 Reasons Your Spreadsheet is Misbehaving", which you can download by clicking here.
Be sure you capture every cell you need to copy.
Learn how to use Paste / Special.
Copy and paste is one of Excel’s best and most used features. It’s an incredible time-saver, allowing you to replicate text, numbers, and formulas to multiple cells with one click of the mouse. Thanks to copy and paste, Excel enables you to be faster, more efficient, and productive in creating spreadsheets.
However, if you don’t use copy and paste correctly, you will quickly fill your spreadsheet with all kinds of errors. Each mistake will have to be located and corrected, with the result you will be slower, less efficient, and less productive in creating spreadsheets.
We have already seen how copy and paste can lead to errors if you don’t understand absolute and relative cell addresses. Incorrect use of copy and paste can also cause you to place the wrong text and numbers in cells. Here’s the first rule for copy and paste:
Rule #1: Whenever possible, use a cell reference instead of copy and paste.
Using a cell reference instead of copy and paste can significantly reduce the chance of error in your spreadsheet. Why? First, you have a much simpler formula in the cell with the cell reference. Second, you don’t have to worry if the original formula is properly set with absolute or relative cell addresses. This is because you are pointing to a cell, not copying a formula. The picture below illustrates how this works.
Difference between using Copy / Paste and a cell reference to copy data
See how much easier it is to use a cell reference instead of copy and paste? Using a cell reference means you only have to look at the referenced cell (B7). By contrast, with copy and paste, you have to confirm that the correct cells are being added and that the cell references are absolute or relative. Imagine if your formula involved more than just adding two numbers!
Rule #2: Select all of the cells you need before you
copy and paste your data to the correct location.
The picture below shows what happens when you don’t select all of the cells you need to copy. When you paste the incomplete selection to another section of your worksheet, you do not transfer all of the data you need. Without the required data in the proper space, any calculations based on this data will be wrong.
Results of failing to select all cells needed before you copy
Now look at what happens when you paste your data to the wrong location on the worksheet.
Results of pasting data to the wrong location on the spreadsheet
Rule #3: Do Not Merge Cells.
Instead, use Center Across Selection.
Another common copy and paste problem occurs when you work with merged cells. Excel won’t let you copy from an unmerged cell to a merged cell. If you try to do so, Excel displays a message box that looks like this:
To correct this error can be a tedious process. You have to find the cells that are merged, then unmerge them. You can probably imagine what a chore this would be if you had to change not one cell, but an entire column.
What You Need to Know
If you have to spread text over several cells do not use Merge Across.
Instead use Center Across Selection so you never have copy and paste problems.
Center Across Selection looks exactly the same as Merge Across, but Center Across Selection is much more flexible and is better for preserving the structure of your worksheet. Center Across Selection is found on the Format Cells dialog box, which you call up from the Format section of the Home Ribbon.
Rule #4: Use Paste Special
Sometimes your copy and paste error occurs because you copy everything in a cell. Even though a cell appears to have only a number, text, or formula, in fact, there are many elements present in a cell: formulas, values, formats, comments, data validation, and borders.
At times you may only want to copy one element from one cell to another cell. Here are some examples:
Your cell contains a formula, but you only want to copy the results of the formula.
Your cell has bold type and yellow shading and you only want to copy the format.
Your cell has comments and you want to copy the comments, not the contents.
Your cell contains a formula and you want to copy the formula, not the format.
You have a row of data and you want to copy it elsewhere as a column.
Excel has a nifty feature that enables you to do all of these things and more. It is called Paste Special. You can learn more about this feature in my blog post on Paste Special and Go To Special.
Here’s a list describing the most commonly used Paste Special options. I’d encourage you to experiment with the others as well, so you’ll be able to use it should the need arise.
Formulas - Pastes only the formulas. Use this when your target cells are already properly formatted.
Values – Pastes only the values from the copied cells. If your original cell has a formula, the target cell will have a value. Use this when you want your numbers to stay the same, even if you change the original formula.
Formats – Pastes only the formats from the source cell to the target cell. This is the best option if you want one range of cells to look the same as another range of cells you have formatted. It is much easier and quicker to use Paste Special | Formats than to highlight each cell in the new range to manually change the formats.
Operation – This allows you to apply an arithmetic operation to the copied cells, using the target cell. This is most useful when you want to do a calculation using data in two columns of numbers. This is an alternative to using a formula. A formula requires you to have a third column containing the formulas. Paste Special | Operation enables you to have only two columns, each of which only has values.
Transpose – Flips the data, from a row to column, or from column to row. You can use Transpose by itself or in combination with other Paste Special options.
In summary, copy and paste is one of Excel’s best features. Just be sure you have used it properly to avoid errors in your spreadsheet.