Microsoft Office Development and Consultancy
 Home|

Excel

|VBA|C#|Finance|Tools|Newsletter|Feedback|Contact 
 Excel > AutoFill > Getting Started | Next > 

 

What is AutoFill ?

 
 

AutoFill allows you to automatically populate your cells with a sequence or list as well as letting you quickly copy values to adjacent cells.

 
 

You can save time by using this feature to complete a series of labels or numbers on your worksheet.

 
 

This allows you to create a series of fixed or incremental values on a worksheet by dragging the fill handle with the mouse.

 
 

Using AutoFill is an alternative to entering repetitive data when it follows in a sequence.

 
 

Both numbers as well as text can be entered automatically and it can also be used to extend formulas as well as formatting.

 
 

You can use the AutoFill feature with your Mouse or by using the (Edit > Fill) sub menu.

 

 

Using (Edit > Fill) sub menu

 
 

The (Edit > Fill) sub menu provides you with several options for automatically populating your cells.

 
   
 

Down - Copies both the values and the formats from the first row of cells in the selected range down to the cells below.

 
 

Right - Copies both the values and the formats from the first column in the selected range to the cells to the right.

 
 

Up - Copies both the values and the formats from the last row of cells in the selected range up to the cells above.

 
 

Left - Copies the values and the formats from the last column of cells in the selected range to the cells to the left.

 
 

Across Worksheets -

 
 

Series - Displays the Series dialog box to let you populate the selected range of cells with a series of values. The contents of the first row or column in the selection are used as the starting values for the series.

 
 

Justify - This splits the current cell entry and distributes it into two or more adjacent rows. This is the only option that actually changes the contents of the cell. Redistributes the text contents of cells to fill the selected range. Numbers or formulas cannot be filled and justified. Except for the left column, cells in the range should be blank.

 

 

Using The Mouse

 
 

Every time you select a cell (or range of cells) you should see a small black square in the bottom right hand corner of the selection.

 
 

If you do not see this square then check your (Tools > Options)(Edit tab, Allow Cell Drag and Drop).

 
   
 

This black square in the bottom right corner is referred to as the Fill Handle.

 
 

When you place the mouse over this fill handle the cursor changes to a small black cross.

 
 

This indicates that you can drag the cells with the left mouse button.

 

 

Dragging the Fill Handle with a single cell highlighted will copy the value to all the subsequent cells.

 
 

Dragging the Fill Handle down with a single cell highlighted and holding down the Ctrl key will increment the values by 1.

 
 

Dragging the Fill Handle up with a single cell highlighted and holding down the Ctrl key will decrement the values by 1.

 
 

Dragging the Fill Handle with more than one cell highlighted will continue the sequence of values in the subsequent cells.

 

 

The sequence of values can be controlled by the initial values entered in the first two or more cells.

 
 

You can also double click the fill handle to quickly copy relative formulas across a row or down a column.

 
 

Dragging the Fill Handle and holding down the Shift key allows you to quickly insert or delete cells.

 
 

If you are entering a long list of names with no blank cells between them and you need to insert duplicate values, a quick way to do this is to right mouse click and select Pick from List on the shortcut menu. This displays a distinct list of all the values currently entered.

 

 

AutoFill Shortcut Menu

 
 

Instead of dragging the fill handle with the left mouse button you could alternatively drag it with the right mouse button.

 
 

When you place the mouse over the fill handle and press the right mouse button you will notice that the cursor changes to an arrow (instead of a black cross).

 
   
 

Dragging with the right mouse button (instead of the left) will display an additional shortcut menu.

 
 

Some commands will often be disabled as it depends on the type of data entered, whether it is numbers, text or dates.

 
   
 

The middle block of fill options will only be available if you enter a date, month or day.

 

 

Entering Numbers, Text and Formulas

 
 

Excel can also work with patterns of text if your labels contain both text and numbers the text remains static but the numbers will increment.

 
 

You can establish any kind of pattern and use this feature to repeat the text either down a column or across a row.

 
 

You can also use AutoFill to create similar types of formulas.

 
 

When you drag a cell containing a formula the formula will be modified automatically.

 

 

Things to Remember

 
 
  • The AutoFill options will not be displayed if you have any rows filtered on the active worksheet.

     
     
  • Using the Fill Handle lets you quickly populate cells with either a copy of a value or with the continuation of a sequence.

     
     
  • You can use the shortcut key (Ctrl + D) to perform (Edit > Fill > Down).

     
     
  • You can use the shortcut key (Ctrl + R) to perform (Edit > Fill > Right).

     
     
  • To create a series of labels that increases by more than one unit, type and enter the first two labels in the series.

     
     
  • If you frequently enter the same data into a worksheet you can speed up the process by creating a custom list.

     

     © Better Solutions Limited 06-Dec-2013Top | Next >