Supercharged Copying and Pasting – Using the Extend Box in Excel
Most of you probably know about the Extend box in Excel, even if you don’t know its name. The Extend box is the little square at the bottom right corner of the current cell or range. When you put your cursor on it, the cursor changes to a small black cross and the fun begins.
Extending a Cell’s Contents
The simplest way to use the Extend box is to drag it, either down or across, over a range of cells. When you let go, the range will fill with the contents of the original cell or range, based on these rules:
- Text will be copied
- A single number will be repeated.
- A range of numbers will create a series based on the differences between the numbers
- A formula will be copied and all cell references will change appropriately.
- A day, month, or quarter name will be extended into a list: Monday will be followed by Tuesday, Wednesday, etc.
- A custom list will be expanded. (You can create custom lists by highlighting the range, then selecting Tools | Options | Custom Lists and adding the range.)
Smart-Tag Options
With the advent of Smart Tags in Excel 2002, when you finish extending, a Smart Tag will appear. Clicking the Smart Tag will display a list of choices that will vary based on the type of data you are extending:
- Text or formula will let you extend with or without formatting, or just the formatting.
- Numbers will let you create a series (1, 2, 3, etc.) and extend with or without formatting.
- Dates will let you fill days, weekdays, months, years, with or without formatting.
Right-Dragging the Extend Box
If you extend by right-dragging, you’ll get a Quick Menu with the Smart Tag options as well as a link to the Fill Series dialog box, where you can create quite complex series, if you need them.
Double-Clicking Magic
Often, when you are working with a list, you often need to add a formula, as shown in the top picture above. If you double-click the formula (in C2 and D2 above), Excel will extend this formula to the length of the list, as shown in the picture on the bottom. (In our example, we used a range of two cells. We could have done C2 and D2 individually.) This is especially helpful if you list is a long one. Be aware that it will stop at the first blank cell in the column to the left.