Excel Tip of the Day

Quick shortcuts for copying data (Excel 97/2000/2001/2002)

As you enter data in a worksheet, you'll often find that data is duplicated from one row to the next or in adjacent columns. Retyping that information is a waste of time, so you probably rely on the familiar copy and paste shortcuts, [Ctrl]C and [Ctrl]V ([command]C and [command]V on the Mac).

Using these shortcuts is likely second-nature, due to their consistent use in most applications. However, Excel has several other shortcuts that may be more efficient for your needs.

First, you've no doubt noticed the marquee frame that surrounds your cell selection when you copy a range. What you may not realize is that when Excel is in this mode, you don't need to use [Ctrl]V to paste your data. When you copy, Excel assumes that the next action you're going to perform is a paste, so all you have to do is select your destination range and press [Enter].

In addition, the data you need to copy is often found immediately above your active cell. You can use shortcuts to easily copy this data. To copy the value stored in the cell above the active cell, use the shortcut [Ctrl][Shift]["] ([command][shift]["] on the Mac).

To copy the formula from the cell above, use the shortcut [Ctrl]['] ([command]['] on the Mac). In either case, the insertion point remains active at the end of the copied text so that you can make modifications. Also note that these shortcuts can be executed from the Formula bar.

Although the previously described shortcuts are convenient, there are a couple of drawbacks to be aware of. First, because the insertion point remains active, you need to press [Enter] after each time you copy. In addition, you can only copy one cell at a time. If you're frequently copying several cells, or a complete row, and changing just a few items, this can be inconvenient. Lastly, when you use the [Ctrl]['] shortcut, the formula is copied exactly as it's written in the cell above.

The consequence of this last aspect may not immediately be clear. Let's say that you have a cell with the simple formula =A1. When you copy and paste using traditional means, Excel updates the formula so it's relevant to the new destination. So, copying it one row down produces =A2, copying one more row down produces =A3, and so on. If you uses the [Ctrl]['] shortcut, every newly populated cell contains the formula =A1, which is likely not the result you want.

To counter the shortcomings we discussed, you can use fill shortcuts. Using these lets you copy multiple cells at once and updates relative cell references appropriately.

To copy from cells immediately above your current selection, use the [Ctrl]D shortcut.

To copy from cells to the left of your selection, use the shortcut [Ctrl]R.

Note that the Mac also uses the [control] key for both of these shortcuts, not the [command] key.

(Content provided by Element K Journals)

(Note: Your browser is set to refuse cookies. As a result, you may frequently see previously-viewed tips)
PrintPrint CloseClose

Copyright © 2001 - 2023 MJ Technologies, LLC.  All rights reserved.