Windows Guides Feed

ms excel logo Excel: Duplicate and Copy Cell content using AutoFillThis 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

Copy first Excel: Duplicate and Copy Cell content using AutoFillTake 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

Copy RightClick Excel: Duplicate and Copy Cell content using AutoFillIf 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.

Copy test Excel: Duplicate and Copy Cell content using AutoFill

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.



 Excel: Duplicate and Copy Cell content using AutoFill

About Thomas

Computer geek from the age of 7, which amounts to 30 years of computer experience. From the early days (when every computer company had their own OS) of DOS, Windows 1.0 through Seven...

Free PC tips by email

Search Windows Guides




Comments

Comments are closed. Join the conversation at Windows Forums.


Computer tips in your inbox
Sign up for the Windows Guides newsletter to get PC tips and access to free Windows books (More details)

Enter your email address:
 

Popular Guides

See which sites have been visited on your PC (even if private browsing mode is used)

Create a Windows 7 System Repair Disc

Best Free Anti-malware

Hibernate vs. Sleep vs. Shut-Down

i3, i5, and i7; Dual, Quad, Hexa Core Processors. How to they Differ?

Intel's Ivy Bridge Processor: new Features

Submit Your Tip
Submit your computer tip to us; receive full credit for all published tips

Comments
Windows Guides on Facebook