Microsoft Office Development and Consultancy
 Home|

Excel

|VBA|C#|Finance|Tools|Newsletter|Feedback|Contact 
 Excel > AutoFill > Fill Series< Previous | Next > 

 

Step 1 - Automatic Fill Series

 
 

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

 
 

Entering the number 15 into cell "B2" and holding down the Ctrl key while dragging the Fill Handle will create the following series:

 
   
 

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

 
 

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

 
 

This can be used to quickly enter an incrementing (or decrementing) series of numbers.

 
 

Typing the values 15, 30 and 45 into the cells B2, B3 and B4 respectively and dragging the Fill Handle creates the following series:

 
   
 

Only simple series can be entered automatically using the Fill Handle. For more complicated series you can use the Fill Series dialog box.

 

 

Step 2 - Fill Series dialog box

 
 

You can fine tune your series after you have dragged the fill handle by displaying the Fill Series dialog box.

 
 

This can be done by selecting (Edit > Fill > Series).

 
 

The series always starts with the value in the selected cell or first cell when more than one cell is selected.

 
 

 (Edit > Fill > Series) dialog box

 
 

Series in - Decides whether the series is to be filled across the page (across the selected columns) or down the page (across the selected rows).

 
 

Trend - Calculates a best-fit line (for linear series) or geometric curve (for growth series). The step values for the trend are calculated from the existing values at the top or left of the selection. Any value in the Step value box is ignored if the Trend check box is selected. Excel will calculate the average change between the values already entered and uses that information to calculate a series to populate the remaining cells.

 
 

Step Value - Enter a positive or negative number to indicate the amount by which you want a series to increase or decrease. The default value for any series is 1.

 
 

Stop Value - Enter a positive or negative number to indicate the value at which you want the series to end. If the selection is filled before the series reaches the stop value, the series stops at that point. If the selection is larger than needed to fill the series, the remaining cells of the selection are left blank. You do not need a value in the Stop value box to fill a series.

 

 

Step 3 - Fill Types

 
 

Linear -

 
 

Growth -

 
 

Date - Fills a series with dates. The Date unit options are only applicable when this type of series is selected. The type of date series that is incremented depends on the option selected under Date unit. This is covered in more detail on a subsequent page.

 
 

AutoFill - Fills blank cells in a selection with a series based on data included in the selection. Selecting this option produces the same results as dragging the fill handle to fill a series. Any value in the Step value box and any selected Date unit option are ignored.

 

 

Step 4 - Date Series

 


 

Step 5 - AutoFill Series

 
 

Entering one number increases by +1

 
 

Entering two numbers the difference is the increment (or decrement) and this value is then used to increase or decrease all the other numbers respectively.

 

 

Enter 2 dates does the increment work ?

 
 

Does it change if you drag up/down and left/right ?

 
 

Enter 2 days

 

 

weekdays, months, years - autofill options button not shortcut menu - is there a difference ?

 

 

Step 6 - Things to Remember

 
 
  • When you place the mouse over the Fill Handle the cursor changes to a small black cross to indicate that you can drag the cells with the mouse.

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

     
     
  • If you include any blank cells in your selection, then the blank cells will be included in the fill.

     
     
  • Using the shortcut menu or the smart tag will create the same series.

     

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