Windows Tools, Help & Guides

This time I’m going to show you how to quickly copy or duplicate cell content using a drag’n’drop-feature. This feature allows you to drag content from one cell to another, filling in values, series or copying formulas.

Click-Drag’n’Drop, or AutoFill

This Click-Drag’n’Drop feature (also known as AutoFill) can be used on a single cell and on a series of cells. It will copy and duplicate both content and formatting. This feature is available in every Excel Version available and works exactly the same all over. But enough fill-in text, let’s get started.

Open a blank Excel Sheet and type in “Monday” and “Tuesday” as in the example below. Then highlight the two cells containing the text to duplicate.

Left Click

Take a closer look in the lower right corner of the highlighted area. Notice the little black square ? That’s our designated helper. Hover your mouse over the handle, notice how the mouse pointer changes from a white cross to a smaller black one?  Now LEFT-Click the little handle (square) and drag down (about 5 or 6 rows). When you release the button, you will see that we have duplicated the words, Monday and Tuesday. Repeat the action but this time to the right. Same thing happens.

Right Click

If you were to make a schedule for Mondays and Tuesdays this would come in handy, but what if we want to create a schedule for an entire week? Delete everything, except the two first cells we started with. Highlight the cells, but this time RIGHT-click it.

This time when you drag the content, Excel will try to look for series or decide what kind of content you are copying. This time when you release the button, Excel will present you with a Context menu, giving you several options. Choose Days.

Notice that you now have a list of days, for the entire week. Had you chosen Weekdays the list would skip Saturday and Sunday. Now highlight the entire area plus one, and Right Click the handle dragging everything 10 rows down. Choose Days from the Context menu, and behold the results.

This time you have not only duplicated the days but you also copied the blank cell underneath it, creating a space between the weeks. If not for anything else, so for the beauty of it.

What can be copied ?

One thing it Does Not copy is Cell Width and Height, other than that; you can copy anything, from content to formatting.  The above example shows you how it’s done. Using the Right-Click method, Excel will always try to determine what kind of data you are duplicating. If it cannot, it will copy the content as is and see if there are any patterns to copy. Try typing in the following information in separate cells, and use the different methods I’ve described to see what happens: 10, 15, 20  and 10.01.2011, 12.01.2011, 14.01.2011.

Duplicating Formulas

When duplicating formulas Excel will update cell references automatically depending on how many cells left, right, up and down you place it. To lock part of or an entire Cell reference we use the F4-button, which will add a $ (dollar sign) somewhere in the reference depending on how many times you press the F4-button.

1. Highlight a Cell, and type: =C3
2. Press F4 Once, and it changes to =$C$3
   This will protect the entire reference so that no matter where you copy it it will always retrieve data from cell C3
3. Press F4 once more, and it changes to =C$3
   Now the formula only protects the Row, but allows Excel to alter the Column reference (D$3, E$e, F$3 etc.)
4. Press F4 once more, and the formula changes to =$C3
   This time we protect the formulas Column reference instead of the Row Reference ($C3, $C4, $C5 etc.)
5. Press F4 one last time to remove the protection all together.

Once you get the hang of the AutoFill feature I’m sure you will wonder how you ever managed without it.. Good luck.

Free PC tips by email