How to Fill Series

  • Post author:
  • Post category:Uncategorized

Fill Series is a helpful feature in Excel that improves efficiency in creating spreadsheets.  It allows instant filling of values in selected cells based on the intervals, type, units and step values specified by the user. This article will assist all levels of Excel users in using the Fill Series in filling in numbers and dates.  

Figure 1.  Final result:  How to Fill Series

Fill Series can be done by using the fill handle or by using the Fill Series options in Home tab.  

What is the fill handle?

The fill handle is located at the lower right corner of any selected cell.  When we hover the mouse over the bottom right corner of the cell, the cursor shows a plus sign, which is the fill handle.

Figure 2.  Fill Handle tool

The fill handle is a tool that enables us to copy values, dates or formulas down a column or across a row by simply dragging the fill handle, and without the use of any function.  

How to use fill handle?

In order to use the fill handle tool, we simply click and drag it up/down or left/right to copy the values or contents of the selected cells to the new cells.  When there are values directly to the left of the selected cell, we can double click the fill handle to initiate copying of the values along the entire column.  

Fill Series

In order to launch the Fill Series dialog box, we click the Home tab > Fill Series

Figure 3.  Fill Series option in Home tab

The Series dialog box will appear.

Figure 4.  Series dialog box

Fill Series examples

Prepare our data

Suppose we have the following data where column C contains the initial values.  We want to fill columns D to G by applying the parameters given in column B.

Figure 5.  Sample data for Fill Series

Fill Series by 1

We want to create a Fill Series with an initial value of 1, and succeeding values incremented by 1

  • Select cells C3 to G3
  • Click Home tab > Fill Series
  • Tick RowsLinear with Step value “1

As a result, D3:G3 will be filled with numbers 2, 3, 4 and 5, which shows a numeric series of interval 1.

Figure 6.  Output: Fill Series by 1

Linear Series by 100

We want to create a linear series with an initial value of 1, and succeeding values incremented by 100

  • Select cells C4 to G4
  • Click Home tab > Fill Series
  • Tick RowsLinear with Step value “100

As a result, D4:G4 will be filled with numbers 101, 201, 301 and 401, which shows a linear series of interval 100.

Figure 7.  Output: Linear Series by 100

Growth Series by 100

We want to create a growth series with an initial value of 1, and succeeding values with a power of 100

  • Select cells C5 to G5
  • Click Home tab > Fill Series
  • Tick RowsGrowth with Step value “100

As a result, D5:G5 will be filled with numbers 100, 10,000, 1,000,000 and 100,000,000, which shows a growth series raised to the power of 100.  

Figure 8.  Output: Growth Series by 100

Date Series by Day

We want to create a date series with an initial value of 01/05/2009 and succeeding dates with a step value of 1 day

  • Select cells C6 to G6
  • Click Home tab > Fill Series
  • Tick RowsDateDay with Step value “1

As a result, D6:G6 will be filled with dates having an interval of one day.  

Figure 9.  Output: Date Series by Day

Date Series by Weekday

  • Select cells C7 to G7
  • Click Home tab > Fill Series
  • Tick RowsDateWeekday with Step value “1

As a result, D7:G7 will be filled with dates having an interval of one weekday. Notice that the dates  04/05/2019 and 05/05/2019 are skipped because these dates fall on weekends.

Figure 10.  Output: Date Series by Weekday

Date Series by Month and Year

  • Follow the same procedure but tick Month or Year in the Date unit.  

Below table shows all cells filled up through the Fill Series feature.  

Figure 11. Output:  Fill Series